Excel 2020: Dua Belas Faedah XLOOKUP - Petua Excel

Fungsi XLOOKUP baru dilancarkan ke Office 365 mulai November 2019. Joe McDaid dari pasukan Excel merancang XLOOKUP untuk menyatukan orang yang menggunakan VLOOKUP dan orang yang menggunakan INDEX / MATCH. Bahagian ini akan membincangkan 12 faedah XLOOKUP:

  1. Padanan tepat adalah lalai.
  2. Hujah ketiga berdasarkan bilangan bulat VLOOKUP kini menjadi rujukan yang tepat.
  3. IFNA terbina dalam untuk menangani nilai yang hilang.
  4. XLOOKUP tidak mempunyai masalah ke kiri.
  5. Cari padanan berikutnya-lebih kecil atau seterusnya-besar tanpa menyusun jadual.
  6. XLOOKUP boleh melakukan HLOOKUP.
  7. Cari perlawanan terakhir dengan mencari dari bawah.
  8. Kad liar "dimatikan" secara lalai, tetapi anda boleh menghidupkannya semula.
  9. Kembalikan semua 12 bulan dalam satu formula.
  10. Boleh mengembalikan rujukan sel jika XLOOKUP berada di sebelah titik dua seperti XLOOKUP (); XLOOKUP ()
  11. Boleh melakukan perlawanan dua hala seperti yang boleh dilakukan oleh INDEX (, MATCH, MATCH).
  12. Boleh menjumlahkan semua carian dalam satu formula seperti yang boleh dilakukan LOOKUP.

Berikut adalah sintaks: = XLOOKUP (Lookup_Value, Lookup_Array, Results_Array, (if_not_found), (match_mode), (search_mode)).

Keuntungan XLOOKUP 1: Padanan Tepat Secara Lalai

99% formula VLOOKUP saya berakhir pada, SALAH atau, 0 untuk menunjukkan padanan yang tepat. Sekiranya anda selalu menggunakan versi pencocokan tepat VLOOKUP, anda boleh mula meninggalkan match_mode dari fungsi XLOOKUP anda.

Pada gambar berikut, anda mencari W25-6 dari sel A4. Anda mahu mencari item itu dalam L8: L35. Apabila ia dijumpai, anda mahukan harga yang sesuai dari lajur N. Tidak perlu menyatakan False sebagai match_mode kerana XLOOKUP lalai untuk padanan tepat.

XLOOKUP nilai dalam A4. Lihat di L8: L35. Kembalikan harga yang sepadan dari N8: N35.

Keuntungan XLOOKUP 2: Hasil_Array adalah rujukan dan bukannya bilangan bulat

Fikirkan formula VLOOKUP yang akan anda gunakan sebelum XLOOKUP. Argumen ketiga adalah 3 untuk menunjukkan bahawa anda ingin mengembalikan lajur ke-3. Selalu ada bahaya bahawa rakan sekerja yang tidak mengerti akan memasukkan (atau menghapuskan) lajur di dalam jadual anda. Dengan lajur tambahan dalam jadual, VLOOKUP yang telah mengembalikan harga akan mula memberikan keterangan. Kerana XLOOKUP menunjuk pada rujukan sel, rumus menulis semula untuk terus menunjukkan harga yang sekarang ada di lajur O.

VLOOKUP lama akan gagal jika seseorang memasukkan lajur baru di jadual carian. XLOOKUP terus berfungsi.

Keuntungan XLOOKUP 3: IFNA dibina sebagai argumen pilihan

Kesalahan # N / A yang ditakuti dikembalikan apabila nilai carian anda tidak dijumpai dalam jadual. Pada masa lalu, untuk mengganti # N / A dengan yang lain, anda harus menggunakan IFERROR atau IFNA yang melilit VLOOKUP.

Apabila item tidak dijumpai, ia akan mengembalikan # N / A dari VLOOKUP atau XLOOKUP…

Berkat cadangan dari Rico di saluran YouTube saya, pasukan Excel memasukkan argumen keempat pilihan untuk if_not_found. Sekiranya anda ingin mengganti kesalahan # N / A dengan sifar, tambah saja 0 sebagai argumen keempat. Atau, anda boleh menggunakan beberapa teks, seperti "Nilai tidak dijumpai".

Hujah keempat pilihan dalam XLOOKUP adalah "jika tidak dijumpai". Letakkan 0 atau "Tidak dijumpai" di sana.

Keuntungan XLOOKUP 4: Tidak ada masalah untuk melihat ke kiri bidang utama

VLOOKUP tidak dapat melihat ke kiri medan kunci tanpa menggunakan VLOOKUP (A4, PILIH ((1,2), G7: G34, F7: F34), 2, Salah). Dengan XLOOKUP, tidak ada masalah mempunyai Hasil_array di sebelah kiri Lookup_array.

Dengan XLOOKUP, tidak ada masalah untuk mengembalikan Kategori dari lajur F sambil mencari nombor bahagian di lajur G. Ini selalu menjadi kelemahan VLOOKUP: ia tidak dapat melihat ke kiri.

Keuntungan XLOOKUP 5: Pertandingan berikutnya-lebih kecil atau seterusnya-besar tanpa menyusun

VLOOKUP mempunyai pilihan untuk mencari padanan tepat atau nilai yang lebih kecil. Anda boleh meninggalkan argumen keempat dari VLOOKUP, atau menukar False menjadi True. Agar ini berfungsi, jadual pencarian harus disusun mengikut urutan menaik.

Contoh VLOOKUP versi Padanan Kira-kira. Sebarang penjualan dari 10 ribu hingga 20 ribu mendapat bonus $ 12.

Tetapi VLOOKUP tidak mempunyai kemampuan untuk mengembalikan perlawanan yang tepat atau item yang lebih besar seterusnya. Untuk itu, anda harus beralih menggunakan MATCH dengan -1 sebagai match_mode dan anda harus berhati-hati agar jadual carian disusun menurun.

Match_mode argumen kelima pilihan XLOOKUP hanya dapat mencari padanan tepat, sama dengan atau hanya lebih kecil, sama atau lebih besar. Perhatikan bahawa nilai dalam XLOOKUP lebih masuk akal daripada di MATCH:

  • -1 mendapati nilai sama atau lebih kecil
  • 0 cari padanan tepat
  • 1 mendapati nilainya sama atau lebih besar.

Tetapi, bahagian yang paling menakjubkan: jadual carian tidak perlu disusun dan mana-mana kaedah match_mode akan berfungsi.

Di bawah ini, kaedah match_mode -1 cari item seterusnya yang lebih kecil.

Argumen kelima XLOOKUP adalah Match_Mode. 0 adalah untuk padanan tepat. Negatif digunakan untuk Pencocokan Tepat atau Item Lebih Kecil Berikutnya. Positif 1 adalah untuk padanan tepat atau item seterusnya yang lebih besar. 2 adalah untuk Wildcard Match. Untuk mencerminkan apa yang akan dilakukan VLOOKUP dengan True dalam argumen keempat, letakkan yang negatif sebagai argumen match_mode dalam XLOOKUP.

Di sini, kaedah match_mode 1, menemui kenderaan apa yang diperlukan bergantung pada jumlah orang dalam pesta tersebut. Perhatikan bahawa jadual carian tidak disusun mengikut penumpang dan nama kenderaan berada di sebelah kiri kunci.

XLOOKUP boleh melakukan sesuatu yang tidak dapat dilakukan oleh VLOOKUP: mencari padanan tepat atau lebih besar. Dalam kes ini, syarikat pelancongan mempunyai senarai tempahan. Berdasarkan jumlah penumpang, jadual pencarian menunjukkan kenderaan apa yang anda perlukan untuk orang-orang itu.

Jadual mengatakan:

  • Bas memuatkan 64 orang
  • Kereta memuatkan 4 orang
  • Motosikal memuatkan 1 orang
  • Tour Van memuatkan 12 orang
  • Van menahan 6 orang.

Sebagai bonus, data disusun berdasarkan Vehicle (dalam penyelesaian lama, menggunakan MATCH, jadual harus disusun menurun mengikut Kapasiti. Juga: Vehicle berada di sebelah kiri Kapasiti.

Keuntungan XLOOKUP 6: Sideways XLOOKUP menggantikan HLOOKUP

Lookup_array dan results_array boleh mendatar dengan XLOOKUP, menjadikannya mudah untuk menggantikan HLOOKUP.

Di sini jadual carian adalah mendatar. Pada masa lalu, ini memerlukan HLOOKUP, tetapi XLOOKUP dapat menangani jadual yang berjalan ke samping.

Keuntungan XLOOKUP 7: Cari dari bawah untuk perlawanan terbaru

Saya mempunyai video lama di YouTube yang menjawab soalan dari ladang kuda Inggeris. Mereka mempunyai armada kenderaan. Setiap kali kenderaan masuk untuk mendapatkan bahan bakar atau servis, mereka mencatat kenderaan, tarikh, dan jarak tempuh dalam spreadsheet. Mereka mahu mencari jarak tempuh terkini yang diketahui untuk setiap kenderaan. Walaupun MAXIFS era Excel-2017 mungkin menyelesaikannya hari ini, penyelesaiannya bertahun-tahun yang lalu adalah formula tidak senonoh menggunakan LOOKUP dan melibatkan pembahagian dengan sifar.

Hari ini, argumen keenam pilihan XLOOKUP membolehkan anda menentukan bahawa carian harus bermula dari bahagian bawah set data.

Cari perlawanan terakhir dalam senarai.

Catatan

Walaupun ini adalah peningkatan yang baik, ia hanya membolehkan anda mencari perlawanan pertama atau terakhir. Sebilangan orang berharap ini membolehkan anda mencari perlawanan kedua atau ketiga, tetapi itu bukan maksud argumen search_mode.

Awas

Gambar di atas menunjukkan bahawa terdapat mod carian menggunakan carian binari lama. Joe McDaid menasihatkan agar tidak menggunakan ini. Pertama, algoritma carian yang diperbaiki dari 2018 cukup pantas sehingga tidak ada faedah kelajuan yang signifikan. Kedua, anda menghadapi risiko rakan sekerja yang tidak mengira menyusun jadual pencarian dan memperkenalkan jawapan yang salah.

Keuntungan 8 XLOOKUP: Kad liar "dimatikan" secara lalai

Sebilangan besar orang tidak menyedari bahawa VLOOKUP memperlakukan asterisk, tanda tanya, dan tilde sebagai watak wildcard seperti yang dijelaskan dalam "# 51 Use a Wildcard in VLOOKUP" pada halaman 143. Dengan XLOOKUP, wildcard dimatikan secara lalai. Sekiranya anda mahu XLOOKUP memperlakukan watak-watak ini sebagai wildcard, gunakan 2 sebagai Match_Mode.

Sangat sedikit orang yang menyedari bahawa VLOOKUP memperlakukan tanda bintang dalam nilai carian sebagai wildcard. Secara lalai, XLOOKUP tidak menggunakan wildcard, tetapi anda boleh memaksanya untuk berperilaku seperti VLOOKUP jika anda menggunakan Match Mode 2: Wildcard Character Match.

Manfaat XLOOKUP 9: Kembalikan Semua 12 Bulan dalam Satu Formula!

Ini benar-benar faedah Dynamic Arrays, tetapi ini adalah alasan kegemaran saya untuk menyukai XLOOKUP. Apabila anda mesti mengembalikan semua 12 bulan dalam pencarian, satu formula yang dimasukkan dalam B6 dengan return_array segi empat akan mengembalikan banyak hasil. Hasil tersebut akan tumpah ke sel yang berdekatan.

Dalam gambar di bawah, satu formula yang dimasukkan dalam B7 mengembalikan semua 12 jawapan yang ditunjukkan dalam B7: M7.

XLOOKUP tunggal pada lajur Januari mengembalikan nombor untuk Januari hingga Disember. Ini dilakukan dengan menentukan hasil_rarr dengan 12 lajur.

Keuntungan XLOOKUP 10: Boleh mengembalikan Rujukan Sel Sekiranya Berdekatan dengan Kolon

Yang ini kompleks tetapi cantik. Pada masa lalu, terdapat tujuh fungsi yang akan berubah dari mengembalikan nilai sel menjadi mengembalikan rujukan sel jika fungsi tersebut menyentuh titik dua. Sebagai contoh, lihat Gunakan A2: INDEX () sebagai OFFSET yang Tidak Berubah. XLOOKUP adalah fungsi lapan untuk menawarkan tingkah laku ini, bergabung dengan PILIH, JIKA, IFS, INDEX, LANGSUNG, OFFSET, dan PEDAR.

Pertimbangkan gambar berikut. Seseorang memilih Cherry di E4 dan Fig di E5. Anda mahukan formula yang merangkumi segalanya dari B6 hingga B9.

Rajah menunjukkan dua formula XLOOKUP dalam dua sel. Yang pertama mengembalikan 15 dari sel B6. Yang kedua kembali 30 dari B9. Tetapi kemudian di sel ketiga, ada formula yang menggabungkan dua formula XLOOKUP dengan titik dua dan kemudian membungkusnya dalam fungsi SUM. Hasilnya adalah SUM B6: B9, kerana XLOOKUP dapat mengembalikan rujukan sel jika fungsi muncul di sebelah operator seperti titik dua. Untuk membuktikan bahawa ini berfungsi, beberapa angka seterusnya akan menunjukkan formula ini dalam dialog Evaluate Formula.

Dalam gambar di atas, anda dapat melihat bahawa XLOOKUP E4 akan mengembalikan 15 dari sel B6. XLOOKUP E5 akan mengembalikan 30 dari B9. Namun, jika anda mengambil dua fungsi XLOOKUP dari sel D9 dan D10 dan menyatukannya dengan titik dua di antara, tingkah laku XLOOKUP akan berubah. Daripada mengembalikan 15, XLOOKUP pertama mengembalikan alamat sel B6!

Untuk membuktikannya, saya telah memilih D7 dan menggunakan Formula, Evaluate Formula. Setelah menekan Evaluate dua kali, bahagian seterusnya yang akan dikira adalah XLOOKUP ("Cherry", A4: A29, B4: B29), seperti yang ditunjukkan di sini.

Ini menunjukkan dialog Evaluate Formula sebelum menilai XLOOKUP pertama. XLOOKUP ini muncul tepat sebelum titik dua.

Tekan Evaluate sekali lagi dan luar biasa, formula XLOOKUP mengembalikan $ B $ 6 dan bukannya 15 yang disimpan di B6. Ini berlaku kerana terdapat usus besar yang segera mengikuti formula XLOOKUP ini.

Klik Nilai dan XLOOKUP pertama mengembalikan $ B $ 6 dan bukannya 15.

Tekan Evaluate dua kali lagi, dan formula sementara adalah = SUM (B6: B9).

Setelah menilai XLOOKUP kedua, formula sementara adalah = SUM (B6: B9).

Ini adalah tingkah laku luar biasa yang tidak diketahui oleh kebanyakan orang. Excel MVP Charles Williams memberitahu saya bahawa ia boleh dicetuskan dengan salah satu daripada tiga pengendali ini di sebelah XLOOKUP:

  • Kolon
  • Space (Operator persimpangan)
  • Koma (pengendali Union)

Keuntungan XLOOKUP 11: Perlawanan dua hala seperti INDEX (, MATCH, MATCH)

Untuk semua rakan VLOOKUP saya, orang-orang INDEX / MATCH telah menunggu untuk melihat apakah XLOOKUP dapat menangani pertandingan dua hala. Berita hebat: ia dapat melakukannya. Berita buruk: metodologi sedikit berbeza daripada yang diharapkan oleh peminat INDEX / MATCH. Mungkin sedikit di atas kepala mereka. Tetapi saya yakin mereka boleh menggunakan kaedah ini.

Untuk perlawanan dua hala, anda ingin mencari baris mana yang mengandungi nombor akaun A621 yang ditunjukkan di J3. Jadi, XLOOKUP bermula dengan cukup mudah: = XLOOKUP (J3, A5: A15. Tetapi kemudian anda harus memberikan hasil_array. Anda boleh menggunakan muslihat yang sama seperti dalam XLOOKUP Manfaat 9: Kembalikan Semua 12 Bulan dalam Formula Tunggal di atas, tetapi gunakannya untuk mengembalikan vektor menegak. XLOOKUP dalaman mencari bulan J4 dalam tajuk bulan di B4: G4. Return_array ditentukan sebagai B5: G15. Hasilnya adalah bahawa XLOOKUP dalaman mengembalikan array seperti yang ditunjukkan dalam I10 : I20 di bawah. Oleh kerana A621 dijumpai di sel kelima lookup_array dan 104 dijumpai di sel kelima hasil_array, anda mendapat jawapan yang betul dari formula. Di bawah, J6 menunjukkan cara lama. J7 mengembalikan cara baru.

XLookup J3 dalam senarai akaun di A5: A15. Untuk Array Hasil, gunakan XLOOKUP (J4, B4: G4, B5: G15). Dalam formula ini, B4: G4 adalah senarai bulan. B5: G15 ialah susunan nilai segi empat tepat untuk semua akaun selama semua bulan. Di sel lain, hanya XLOOKUP dalaman menunjukkan bagaimana ia mengembalikan keseluruhan lajur nilai untuk bulan Mei.

Keuntungan 12 XLOOKUP: Jumlahkan semua nilai carian dalam satu formula

Fungsi LOOKUP kuno menawarkan dua helah pelik. Pertama, jika anda ingin mengetahui jumlah perbelanjaan bonus yang akan diperoleh, anda boleh meminta LOOKUP untuk mencari semua nilai dalam satu formula. Pada gambar di bawah, LOOKUP (C4: C14 melakukan 11 pencarian. Tetapi fungsi LOOKUP tidak menawarkan padanan yang tepat dan memerlukan jadual pencarian untuk disusun.

Cari 13 nilai dan jumlahkan. Ini digunakan untuk bekerja dengan LOOKUP, tetapi juga berfungsi dengan XLOOKUP. Nyatakan semua nilai carian C4: C14 sebagai argumen pertama. Balut XLOOKUP dalam fungsi SUM.

Dengan XLOOKUP, anda dapat menentukan julat kerana lookup_value dan XLOOKUP akan mengembalikan semua jawapan. Manfaatnya ialah XLOOKUP dapat melakukan perlawanan yang tepat.

Caranya menggunakan LOOKUP untuk menjumlahkan semua hasil carian hanya berfungsi dengan versi pencocokan anggaran dari Lookup. Di sini, XLOOKUP melakukan pencocokan tepat pada semua nama di L4: L14 dan memperoleh keseluruhan hasilnya.

Petua Bonus: Bagaimana dengan LOOKUP Twisted?

Excel MVP Mike Girvin sering menunjukkan tip fungsi LOOKUP di mana Lookup_Vector adalah menegak dan Result_Vector mendatar. XLOOKUP secara semula jadi tidak akan menyokong muslihat ini. Tetapi, jika anda menipu sedikit dan membungkus hasil_rarr dalam fungsi TRANSPOSE, anda boleh menguruskan pencarian yang berpusing.

Di sini susunan carian adalah menegak dan susunan hasilnya mendatar. Fungsi LOOKUP lama dapat menangani ini ,, tetapi untuk melakukannya dengan XLOOKUP, anda harus membungkus salah satu array dalam TRANSPOSE.

Artikel menarik...