Formula generik
=XLOOKUP(max,dates,results,,-1) // latest match by date
Ringkasan
Untuk mendapatkan padanan terbaru dalam satu set data mengikut tarikh, anda boleh menggunakan XLOOKUP dalam mod padanan anggaran dengan menetapkan match_mode ke -1. Dalam contoh yang ditunjukkan, formula dalam G5, disalin ke bawah, adalah:
=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)
di mana tarikh (C5: C15), item (B5: B15) dan harga (D5: D15) dinamakan julat.
Penjelasan
XLOOKUP menawarkan beberapa ciri yang menjadikannya sangat baik untuk pencarian yang lebih rumit. Dalam contoh ini, kami mahukan harga terkini untuk item mengikut tarikh. Sekiranya data disusun mengikut tarikh mengikut urutan menaik, ini akan sangat mudah. Walau bagaimanapun, dalam kes ini, data tidak disusun.
Secara lalai, XLOOKUP akan mengembalikan perlawanan pertama dalam set data. Untuk mendapatkan perlawanan terakhir, kita dapat menetapkan search_mode argumen pilihan, menjadi -1 untuk menyebabkan XLOOKUP mencari "terakhir hingga pertama". Namun, kami tidak dapat menggunakan pendekatan ini di sini kerana tidak ada jaminan bahawa harga terbaru untuk item muncul terakhir.
Sebagai gantinya, kita dapat menetapkan match_mode argumen pilihan ke -1 untuk memaksa perkiraan padanan "tepat atau terkecil berikutnya", dan menyesuaikan nilai pencarian dan tatasusunan pencarian seperti yang dijelaskan di bawah. Rumus dalam G5, disalin, adalah:
=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)
Mengusahakan hujah satu persatu, nilai_uplikasi adalah tarikh (terkini) terbesar dalam data:
MAX(date) // get max date value
Lookup_array diturunkan dengan ungkapan logik boolean:
(item=F5)*date
Dengan membandingkan setiap item dengan nilai di F5, "Belt", kami mendapat pelbagai nilai BENAR / SALAH:
(TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE)
di mana nilai BENAR mewakili entri untuk "Belt". Susunan ini bertindak seperti penapis. Apabila didarabkan dengan nilai dalam julat tarikh yang dinamakan , nilai BENAR / SALAH dinilai menjadi 1 dan 0:
=(1;0;0;0;0;0;1;0;1;0;0)*date
Hasilnya adalah susunan yang hanya mengandungi nol dan tarikh untuk tali pinggang:
=(43484;0;0;0;0;0;43561;0;43671;0;0)
Nota: nombor siri adalah tarikh Excel yang sah.
Susunan ini dihantar terus ke XLOOKUP sebagai argumen lookup_array.
Return_array adalah harga julat yang dinamakan (D5: D15)
Argumen pilihan not_found tidak diberikan.
Match_mode ditetapkan ke -1, untuk pencocokan tepat, atau item terkecil berikutnya.
XLOOKUP melihat susunan carian untuk nilai tarikh maksimum. Oleh kerana array telah disaring untuk mengecualikan tarikh yang tidak dikaitkan dengan "Belt", XLOOKUP hanya mencari padanan terbaik (sama ada tarikh tepat, atau tarikh terkecil seterusnya) yang sesuai dengan tarikh terkini.
Hasil akhirnya adalah harga yang berkaitan dengan tarikh terkini. Rumus akan terus berfungsi apabila data disusun mengikut urutan.
