Cara Menggunakan Fungsi XLOOKUP dalam Excel 2016

Excel 2016 untuk pelanggan Office 365 pada Windows dan Mac kini menyokong fungsi XLOOKUP baharu, disebut-sebut sebagai pengganti yang jauh lebih mudah dan serba boleh untuk fungsi carian menegak yang sangat popular (namun sering difitnah), VLOOKUP (tidak tahu apa X dalam XLOOKUP bermaksud; luas, mungkin?).

Bagi anda yang belum biasa dengan VLOOKUP (dianggap fungsi ketiga paling banyak digunakan selepas SUM dan AVERAGE), fungsi ini mencari secara menegak mengikut baris dalam lajur paling kiri jadual carian yang ditetapkan dari atas ke bawah sehingga ia menemui nilai dalam lajur carian yang ditetapkan oleh nombor offset yang sepadan atau melebihi nombor yang anda cari. Walaupun amat berguna untuk mencari item tertentu dalam senarai panjang atau lajur jadual data dalam lembaran kerja anda, fungsi VLOOKUP mempunyai beberapa had yang tidak dikongsi oleh fungsi carian baharu ini, seperti XLOOKUP:

• Lalai untuk mencari padanan tepat untuk nilai carian anda dalam julat carian
• Boleh mencari secara menegak (mengikut baris) dan mendatar (mengikut lajur) dalam jadual, dengan itu menggantikan keperluan untuk menggunakan fungsi HLOOKUP apabila mencari secara mendatar mengikut lajur
• Boleh mencari ke kiri atau kanan supaya julat carian dalam jadual carian anda tidak perlu terletak dalam lajur di sebelah kiri yang ditetapkan sebagai julat pulangan agar fungsi berfungsi
• Apabila lalai padanan tepat digunakan, berfungsi walaupun apabila nilai dalam julat carian tidak diisih mengikut susunan tertentu
• Boleh mencari dari baris bawah ke atas dalam julat tatasusunan carian, menggunakan hujah mod carian pilihan

Fungsi XLOOKUP mempunyai lima argumen yang mungkin, tiga yang pertama diperlukan dan dua yang terakhir pilihan, menggunakan sintaks berikut:

XLOOKUP( lookup_value , lookup_array , return_array ,[ match_mode ],[ search_mode ])

Argumen lookup_value yang diperlukan menetapkan nilai atau item yang anda cari. Argumen tatasusunan look_up yang diperlukan menetapkan julat sel yang akan dicari untuk nilai carian ini dan argumen return_array menetapkan julat sel yang mengandungi nilai yang anda mahu dikembalikan apabila Excel menemui padanan yang tepat.

* Perlu diingat apabila menetapkan argumen lookup_array dan return_array dalam fungsi XLOOKUP anda, kedua-dua julat mestilah sama panjang, jika tidak, Excel akan mengembalikan #VALUE! ralat kepada formula anda. Ini lebih-lebih lagi sebab untuk anda menggunakan nama julat atau nama lajur bagi jadual data yang ditetapkan semasa mentakrifkan hujah ini daripada menunjukkannya atau menaip rujukan selnya .

Argumen match_mode pilihan boleh mengandungi mana-mana daripada empat nilai berikut:

• 0 untuk padanan tepat (lalai, sama seperti apabila tiada hujah match_mode ditetapkan)
• -1 untuk padanan tepat atau nilai seterusnya yang lebih rendah
• 1 untuk padanan tepat atau nilai seterusnya yang lebih besar
• 2 untuk padanan separa menggunakan aksara kad bebas yang digabungkan dengan rujukan sel dalam argumen lookup_value

Argumen search_mode pilihan boleh mengandungi mana-mana daripada empat nilai berikut:

• 1 untuk mencari pertama-ke-akhir, iaitu, dari atas ke bawah (lalai, sama seperti apabila tiada hujah mod_carian ditetapkan)
• -1 untuk mencari akhir-ke-pertama, iaitu, bawah ke atas
• 2 untuk carian binari dalam tertib menaik
• -2 untuk carian binari dalam susunan menurun

Cara terbaik untuk memahami kuasa dan kepelbagaian fungsi XLOOKUP baharu ialah melihatnya dalam tindakan dalam lembaran kerja Excel. Dalam rajah berikut, saya mempunyai lembaran kerja dengan jadual data jualan ringkas 2019 yang disusun mengikut negara. Untuk menggunakan XLOOKUP untuk mengembalikan jumlah jualan daripada jadual ini dalam sel E4 berdasarkan negara yang anda masukkan dalam sel D4 lembaran kerja, anda mengambil langkah berikut:

Letakkan kursor sel dalam sel E4 lembaran kerja

Klik pilihan Carian & Rujukan pada tab Formula diikuti oleh XLOOKUP berhampiran bahagian bawah menu lungsur untuk membuka kotak dialog Argumen Fungsinya.

Klik sel D4 dalam lembaran kerja untuk memasukkan rujukan selnya ke dalam kotak teks argumen Lookup_value.

Tekan Tab untuk memilih kotak teks argumen Lookup_array, kemudian klik sel A4 dan tahan Shift sambil anda menekan anak panah Ctrl-down untuk memilih A4:A8 sebagai julat untuk dicari (kerana julat A3:B8 ditakrifkan sebagai jadual data Excel, Jadual1[Negara] muncul dalam kotak teks menggantikan julat A4:A8).

Tekan Tab untuk memilih kotak teks argumen Return_array, kemudian klik sel B4 dan tahan Shift sambil anda menekan anak panah Ctrl-down untuk memilih B4:B8 sebagai julat yang mengandungi nilai yang akan dikembalikan berdasarkan hasil carian (yang muncul sebagai Jadual1[Jumlah Jualan] dalam kotak teks).

Klik OK untuk memasukkan formula XLOOKUP dalam sel E4.

Mencipta formula dengan XLOOKUP dalam sel E4 yang mengembalikan jualan berdasarkan negara yang dimasukkan dalam sel D4.

Excel memasukkan formula XLOOKUP ke dalam sel E4 lembaran kerja dan mengembalikan 4900 sebagai hasilnya kerana Costa Rica kini dimasukkan ke dalam sel carian D4 dan seperti yang anda lihat dalam jadual jualan 2019, ini sememangnya jumlah jualan yang dibuat untuk negara ini.

Oleh kerana XLOOKUP berfungsi dari kanan ke kiri dan juga dari kiri ke kanan, anda boleh menggunakan fungsi ini juga untuk mengembalikan negara daripada jadual jualan ini berdasarkan angka jualan tertentu. Rajah berikut menunjukkan kepada anda bagaimana anda melakukan ini. Kali ini, anda mencipta formula XLOOKUP dalam sel D4 dan menetapkan nilai yang dimasukkan dalam sel E4 (11,000, dalam kes ini) sebagai hujah lookup_value.

Di samping itu, anda memasukkan -1 sebagai argumen match_mode untuk mengatasi lalai padanan tepat fungsi supaya Excel mengembalikan negara dengan padanan tepat kepada nilai jualan yang dimasukkan dalam sel carian E4 atau yang mempunyai jumlah jualan yang lebih rendah seterusnya (Mexico dengan $10,000 dalam kes ini kerana tiada negara dalam jadual ini dengan $11,000 daripada jumlah jualan). Tanpa menetapkan hujah mod_padan untuk formula ini, Excel akan mengembalikan #NA sebagai hasilnya, kerana tiada padanan tepat kepada $11,000 dalam jadual jualan ini.

Mencipta formula dengan XLOOKUP dalam sel D4 yang mengembalikan negara berdasarkan jualan yang dimasukkan dalam sel E4

Oleh kerana fungsi XLOOKUP sama-sama selesa mencari secara mendatar mengikut lajur kerana ia mencari secara menegak mengikut baris, anda boleh menggunakannya untuk mencipta formula yang melakukan carian dua hala (menggantikan keperluan untuk mencipta formula yang menggabungkan fungsi INDEX dan MATCH sebagai pada masa lalu). Angka berikut, yang mengandungi jadual jadual pengeluaran 2019 untuk nombor bahagian, AB-100 hingga AB-103 untuk bulan April hingga Disember, menunjukkan kepada anda cara ini dilakukan.

Mencipta formula dengan XLOOKUP bersarang berfungsi untuk mengembalikan bilangan unit yang dihasilkan untuk bahagian dalam bulan tertentu

Dalam sel B12, saya mencipta formula berikut:

=XLOOKUP(part_lookup,$A$3:$A$6,XLOOKUP(date_lookup,$B$2:$J$2,$B$3:$J$6))

Formula ini bermula dengan mentakrifkan fungsi XLOOKUP yang mencari secara menegak mengikut baris untuk padanan tepat kepada entri bahagian yang dibuat dalam sel bernama part_lookup (sel B10, dalam kes ini) dalam julat sel $A$3:$A$6 jadual pengeluaran . Walau bagaimanapun, ambil perhatian bahawa argumen return_array untuk fungsi LOOKUP asal ini ialah fungsi XLOOKUP kedua.

Fungsi XLOOKUP bersarang kedua ini mencari julat sel $B$2:$J$2 secara mendatar mengikut lajur untuk padanan tepat dengan entri tarikh yang dibuat dalam sel bernama date_lookup (sel B11, dalam kes ini). Argumen return_array untuk fungsi XLOOKUP bersarang kedua ini ialah $B$3:$J$6, julat sel semua nilai pengeluaran dalam jadual.

Cara formula ini berfungsi ialah Excel terlebih dahulu mengira hasil fungsi XLOOKUP bersarang kedua dengan melakukan carian mendatar yang, dalam kes ini, mengembalikan tatasusunan dalam julat sel D3: D6 lajur Jun-19 (dengan nilai: 438, 153, 306, dan 779) sebagai hasilnya. Hasil ini, seterusnya, menjadi argumen return_array untuk fungsi XLOOKUP asal yang melakukan carian menegak mengikut baris untuk padanan tepat dengan entri nombor bahagian yang dibuat dalam sel B11 (dinamakan part_lookup). Kerana, dalam contoh ini, sel part_lookup ini mengandungi AB-102, formula hanya mengembalikan nilai pengeluaran Jun-19, 306, daripada hasil fungsi XLOOKUP kedua, seterusnya.

Itulah anda mempunyainya! Pandangan pertama pada XLOOKUP, fungsi carian baharu yang berkuasa, serba boleh dan agak mudah digunakan yang bukan sahaja boleh melakukan carian nilai tunggal yang dilakukan oleh fungsi VLOOKUP dan HLOOKUP tetapi juga carian nilai dua hala yang dilakukan dengan menggabungkan INDEX dan MATCH juga berfungsi.

* Malangnya, fungsi XLOOKUP tidak serasi ke belakang dengan versi terdahulu Microsoft Excel yang hanya menyokong fungsi VLOOKUP dan HLOOKUP atau serasi dengan versi semasa yang belum lagi menyertakannya sebagai salah satu fungsi carian mereka, seperti Excel 2019 dan Excel Online . Ini bermakna jika anda berkongsi buku kerja yang mengandungi formula XLOOKUP dengan rakan sekerja atau pelanggan yang menggunakan versi Excel yang tidak menyertakan fungsi carian baharu ini, semua formula ini akan mengembalikan #NAME? nilai ralat apabila mereka membuka lembaran kerjanya.