Formula generik
(=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0)))
Ringkasan
Untuk mencari padanan terdekat dalam data angka, anda boleh menggunakan INDEX dan MATCH, dengan bantuan fungsi ABS dan MIN. Dalam contoh yang ditunjukkan, formula dalam F5, disalin ke bawah, adalah:
=INDEX(trip,MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0))
di mana perjalanan (B5: B14) dan kos (C5: C14) dinamakan julat.
Dalam F5, F6, dan F7, formula mengembalikan perjalanan paling dekat dengan harga masing-masing 500, 1000, dan 1500.
Catatan: ini adalah formula larik dan mesti dimasukkan dengan kawalan + shift + enter, kecuali di Excel 365.
Penjelasan
Pada intinya, ini adalah formula INDEX dan MATCH: MATCH mencari kedudukan padanan terdekat, memberi makan kedudukan ke INDEX, dan INDEX mengembalikan nilai pada kedudukan itu di lajur Perjalanan. Kerja keras dilakukan dengan fungsi MATCH, yang dikonfigurasi dengan teliti agar sesuai dengan "perbezaan minimum" seperti ini:
MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0)
Mengambil langkah demi langkah, nilai carian dikira dengan MIN dan ABS seperti ini:
MIN(ABS(cost-E5)
Pertama, nilai dalam E5 dikurangkan dari julat kos yang dinamakan (C5: C14). Ini adalah operasi array, dan kerana terdapat 10 nilai dalam julat, hasilnya adalah array dengan 10 nilai seperti ini:
(899;199;250;-201;495;1000;450;-101;500;795)
Angka-angka ini mewakili perbezaan antara setiap kos di C5: C15 dan kos di sel E5, 700. Beberapa nilai negatif kerana kosnya lebih rendah daripada angka di E5. Untuk menukar nilai negatif menjadi nilai positif, kami menggunakan fungsi ABS:
ABS((899;199;250;-201;495;1000;450;-101;500;795))
yang mengembalikan:
(899;199;250;201;495;1000;450;101;500;795)
Kami mencari padanan terdekat, jadi kami menggunakan fungsi MIN untuk mencari perbezaan terkecil, yaitu 101:
MIN((899;199;250;201;495;1000;450;101;500;795)) // returns 101
Ini menjadi nilai carian dalam MATCH. Susunan carian dihasilkan seperti sebelumnya:
ABS(cost-E5) // generate lookup array
yang mengembalikan susunan yang sama seperti yang kita lihat sebelumnya:
(899;199;250;201;495;1000;450;101;500;795)
Kita sekarang mempunyai apa yang kita perlukan untuk mencari kedudukan pertandingan terdekat (perbezaan terkecil), dan kita dapat menulis semula bahagian MATCH formula seperti ini:
MATCH(101,(899;199;250;201;495;1000;450;101;500;795),0) // returns 8
Dengan 101 sebagai nilai carian, MATCH mengembalikan 8, kerana 101 berada di kedudukan ke-8 dalam array. Akhirnya, kedudukan ini dimasukkan ke dalam INDEX sebagai argumen berturut-turut, dengan julat perjalanan dinamakan sebagai array:
=INDEX(trip,8)
dan INDEX mengembalikan perjalanan ke-8 dalam julat, "Sepanyol". Apabila formula disalin ke sel F6 dan F7, ia menemui padanan terdekat dengan 1000 dan 1500, "Perancis" dan "Thailand" seperti yang ditunjukkan.
Catatan: jika ada seri, formula ini akan mengembalikan perlawanan pertama.
Dengan XLOOKUP
Fungsi XLOOKUP menyediakan cara yang menarik untuk menyelesaikan masalah ini, kerana jenis padanan 1 (padanan tepat atau terbesar berikutnya) atau -1 (padanan tepat atau terkecil berikutnya) tidak memerlukan data disusun. Ini bermaksud kita boleh menulis formula seperti ini:
=XLOOKUP(0,ABS(cost-E5),trip,,1)
Seperti di atas, kami menggunakan nilai mutlak (cost-E5) untuk membuat tatapan carian:
(899;199;250;201;495;1000;450;101;500;795)
Kemudian kami mengkonfigurasi XLOOKUP untuk mencari sifar, dengan jenis padanan ditetapkan ke 1, untuk padanan tepat atau terbesar berikutnya. Kami menyediakan perjalanan jarak yang dinamakan sebagai array kembali, jadi hasilnya adalah "Sepanyol" seperti sebelumnya.
