Lupakan VLOOKUP IN EXCEL: Inilah sebabnya saya menggunakan XLOOKUP

Pautan cepat

• sintaks: xlookup dan vlookup

• Tidak perlu mengira lajur

🎜> Lebih banyak pilihan perlawanan yang hampir sama

lebih banyak mod carian (arahan)

Tentukan output ralat

🎜> Kembalikan array tumpah

Walau bagaimanapun, pengenalan Microsoft XLOOKUP pada tahun 2019 mengubah segala -galanya. Sebaik sahaja saya menyedari betapa berguna XLOOKUP, saya tahu saya tidak akan melihat ke belakang.

Saya akan menggunakan rujukan sel langsung untuk mencontohkan mata saya dalam artikel ini, kerana ia lebih jelas daripada rujukan berstruktur. Saya juga tidak akan bercakap secara eksplisit mengenai hlookup kerana -yang lain daripada arahnya -ia berfungsi dengan cara yang sama seperti vlookup. Xlookup ke Vlookup, saya akan menunjukkan kepada anda bagaimana mereka berfungsi.

• a
• b (diperlukan) adalah array carian,
• c
d
• (pilihan) adalah teks untuk kembali jika nilai carian ( a ) tidak dijumpai Dalam array carian ( b ), e (pilihan) adalah mod perlawanan, dan
pilihan) adalah mod carian.
• Dalam contoh ini, saya mahu Excel mencari ID pekerja berdasarkan nama dalam sel H1, dan mengembalikan hasilnya ke sel H2.
Untuk melakukan ini, saya akan menaip formula berikut ke dalam sel H2:

Dalam kes ini, sel H1 mengandungi nilai Excel yang perlu dicari (Mary), B2 hingga B12 adalah di mana nilai itu dapat dijumpai (nama pekerja), A2 hingga A12 adalah di mana hasil yang sama akan ditarik dari (ID pekerja), dan "nama tidak sah" adalah apa yang saya mahu cemerlang untuk kembali jika nilai carian tidak ada di mana sahaja dapat ditemui dalam array carian. dan saya mahu Excel mencari dari bahagian atas array carian (lebih banyak lagi di kemudian hari). Argumen:

di mana

• a (diperlukan) adalah nilai carian,
• b (diperlukan) adalah array carian dan kembali,
• c (diperlukan) ialah nombor indeks lajur, dan
• d (pilihan) adalah mod perlawanan. Contoh ini, saya mahu Excel mencari kewarganegaraan berdasarkan ID pekerja dalam sel H4, dan mengembalikan hasilnya ke sel H5. untuk melakukan ini, dalam sel H5, saya akan menaip

kerana sel H4 mengandungi nilai carian (ID 3264), sel A2 hingga E12 adalah di mana Excel perlu perlu Cari nilai itu dan pulangan yang sama, lajur kelima (negara) adalah array di mana hasilnya akan dijumpai, dan saya mahukan perlawanan yang tepat (palsu).

Satu perbezaan utama antara VLOOKUP dan XLOOKUP adalah bahawa bekas memerlukan saya untuk menentukan nombor indeks lajur di mana hasilnya akan dijumpai, sedangkan yang terakhir tidak. Ini kerana VLOOKUP menggabungkan array carian dan pulangan ke dalam satu hujah, sedangkan XLLOOKUP mentakrifkannya dalam dua argumen yang berasingan. : 3

Menambah atau mengeluarkan lajur boleh menjejaskan ketepatan nombor indeks lajur.

Arahan carian mestilah lajur paling kiri, dan array kembali mestilah di sebelah kanan. Ini menyekat kepelbagaian VLOOKUP. Sebaliknya, Xlookup mengandungi array carian sebagai hujah yang berasingan, yang bermaksud anda dapat menghargai manfaat berikut:

• Tidak ada pengiraan yang terlibat! Cukup gunakan tetikus anda untuk memilih array kembali apabila anda sampai ke bahagian formula anda. Ini menjimatkan ketepatan masa dan AIDS.
• Kerana array kembali berada dalam julat sel yang ditentukan, mengeluarkan atau menambah lajur ke spreadsheet anda tidak akan menjejaskan formula XLOOKUP anda.
• Arahan pulangan boleh berada di kedua -dua belah array carian, yang bermaksud Xlookup lebih serba boleh daripada VLOOKUP.

Pilihan perlawanan lebih anggaran

kedua -dua VLOOKUP dan XLOOKUP boleh mengembalikan perlawanan yang tepat (nilai yang sesuai dalam satu baris) dan perlawanan anggaran (nilai yang sepadan).

vlookup

lebih khusus, perlawanan anggaran VLOOKUP (ditunjukkan oleh benar dalam formula) mencari array carian sehingga ia mendapati nilai yang lebih besar daripada nilai carian. Ia kemudian mengembalikan nilai yang satu baris dari sana.

Ambil skor pelajar D's 65 dalam contoh di bawah. VLOOKUP mengambil nilai carian 65, mengulas array carian, mendapati nilai pertama yang lebih besar daripada nilai carian (dalam kes ini, 70), dan kemudian mengembalikan gred dari baris di atas (gred C). >

Ini membentangkan dua kelemahan. Pertama, array carian mesti disenaraikan dalam urutan menaik. Kedua, saya perlu menambah baris gagal ke array carian, kerana saya tidak mempunyai pilihan dalam vlookup untuk menyatakan hujah yang tidak sepadan. XLOOKUP menawarkan tiga alternatif kepada perlawanan anggaran satu dimensi VLOOKUP:

• -1: Ini mengembalikan nilai terkecil seterusnya dalam array carian jika tidak ada padanan yang tepat.
• 1: Ini mengembalikan nilai terbesar seterusnya dalam array carian jika tidak ada pertandingan yang tepat.
• 2: Ini menggunakan kad liar untuk membolehkan pencarian yang lebih fleksibel.

Sekali lagi, ambil pelajar D sebagai contoh. Dengan skor 65, Excel akan melihat bahawa nilai terkecil seterusnya dalam array carian adalah 60, dan nilai terbesar seterusnya adalah 70. Oleh kerana pelajar belum mencapai ambang untuk gred B (70), saya perlu cemerlang Ambil nilai terkecil seterusnya dalam array carian (60) untuk mengembalikan Gred C dari array kembali. Jadi, saya akan menaip -1 sebagai pilihan yang sepadan dalam formula. array untuk mencari nilai yang lebih tinggi atau lebih rendah jika tidak ada perlawanan yang tepat. Saya juga boleh menghilangkan baris gagal dari array carian saya kerana jika skor pelajar tidak sepadan dengan mana -mana gred, saya boleh menggunakan hujah keempat dalam sintaks xlookup untuk mengembalikan perkataan gagal.

Semasa mencari VLOOKUP dari pertama hingga terakhir, mengembalikan nilai padanan pertama, XLLOOKUP menawarkan empat pilihan carian. Untuk yang terakhir akan mengembalikan hasil yang anda perlukan. Sebagai contoh, jika anda mempunyai direktori nombor telefon dan nama orang, menggunakan VLOOKUP untuk mencari nombor telefon berdasarkan nama yang anda masukan akan berfungsi dengan baik, kerana nama orang itu hanya akan muncul sekali. XLOOKUP Walau bagaimanapun, XLOOKUP membolehkan anda memilih orientasi carian:

• 1: Cari terlebih dahulu hingga terakhir
• -1: Cari Terakhir ke Pertama
• 2: Carian binari (dengan array carian dalam urutan menaik)
• -2 carian binari (dengan array carian dalam urutan menurun)

disenaraikan dalam urutan tarikh. Untuk mencapai ini dengan VLOOKUP, anda perlu membalikkan urutan data terlebih dahulu. VLOOKUP tidak ada. Untuk membetulkannya, saya selalu menanam fungsi VLOOKUP dalam fungsi IFError supaya saya dapat menentukan output jika VLOOKUP tidak menemui perlawanan. adalah penyelesaian yang munasabah, ia menjadikan formula menulis lebih kompleks dan dapat menyembunyikan isu-isu yang mungkin mempengaruhi ketepatan analisis data anda. Ditemui "hujah, anda boleh menentukan apa yang berlaku jika nilai itu tidak muncul dalam carian, menjimatkan anda daripada perlu membenamkan formula dalam iferror. Ciri -ciri yang degil adalah bahawa ia hanya boleh mengembalikan satu perlawanan, sedangkan Xlookup boleh mengembalikan julat. 🎜> melihat nilai dalam sel i1 (dalam kes ini, kuizpicable saya), cari nilai itu dalam sel A2 hingga A7, dan mengembalikan semua nilai yang sepadan sebagai array yang tumpah. > vlookup

Jika saya cuba meniru ini menggunakan VLOOKUP, saya akan menaip

tetapi ini mengembalikan #REF! Ralat Kerana hujah ketiga (nombor indeks lajur) hanya boleh menjadi satu digit, bukan julat. Ini bermakna bahawa xlookup lebih mudah disesuaikan, kerana ia boleh mengembalikan sama ada satu nilai atau julat, bergantung kepada parameter yang anda tambahkan pada formula. Sokongan XLOOKUP, jangan buang VLOOKUP dan HOLLOWER SEMUA! Masih ada kes apabila anda perlu menggunakannya, seperti jika anda menghantar hamparan kepada seseorang yang belum menaik taraf pakej pejabat mereka selama beberapa tahun.

Atas ialah kandungan terperinci Lupakan VLOOKUP IN EXCEL: Inilah sebabnya saya menggunakan XLOOKUP. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!