Cara menggunakan fungsi Excel XLOOKUP -

Ringkasan

Fungsi Excel XLOOKUP adalah pengganti moden dan fleksibel untuk fungsi lama seperti VLOOKUP, HLOOKUP, dan LOOKUP. XLOOKUP menyokong pemadanan anggaran dan tepat, wildcard (*?) Untuk padanan separa, dan pencarian dalam julat menegak atau mendatar.

Tujuan

Nilai carian dalam julat atau tatasusunan

Nilai pulangan

Nilai yang sepadan dari array kembali

Sintaks

= XLOOKUP (lookup, lookup_array, return_array, (not_found), (match_mode), (search_mode))

Hujah

  • lookup - Nilai carian.
  • lookup_array - Susunan atau julat yang hendak dicari.
  • return_array - Susunan atau julat untuk dikembalikan.
  • not_found - (opsional) Nilai untuk dikembalikan sekiranya tiada perlawanan.
  • match_mode - (pilihan) 0 = padanan tepat (lalai), -1 = padanan tepat atau terkecil seterusnya, 1 = padanan tepat atau seterusnya lebih besar, 2 = pertandingan wildcard.
  • search_mode - (pilihan) 1 = carian dari pertama (lalai), -1 = carian dari terakhir, 2 = carian binari menaik, -2 = carian binari menurun.

Versi

Excel 365

Catatan penggunaan

XLOOKUP adalah pengganti moden untuk fungsi VLOOKUP. Ini adalah fungsi yang fleksibel dan serba boleh yang dapat digunakan dalam pelbagai situasi.

XLOOKUP dapat mencari nilai dalam julat menegak atau mendatar, dapat melakukan padanan anggaran dan tepat, dan menyokong wildcard (*?) Untuk pertandingan separa. Di samping itu, XLOOKUP dapat mencari data bermula dari nilai pertama atau nilai terakhir (lihat jenis padanan dan butiran mod carian di bawah). Berbanding dengan fungsi lama seperti VLOOKUP, HLOOKUP, dan LOOKUP, XLOOKUP menawarkan beberapa kelebihan utama.

Mesej tidak dijumpai

Apabila XLOOKUP tidak dapat mencari padanan, ia mengembalikan ralat # N / A, seperti fungsi padanan lain di Excel. Tidak seperti fungsi pencocokan lain, XLOOKUP menyokong argumen pilihan yang disebut not_found yang dapat digunakan untuk mengatasi kesalahan # N / A apabila ia akan muncul. Nilai khas untuk not_found mungkin "Tidak dijumpai", "Tidak sesuai", "Tidak ada hasil", dll. Semasa memberikan nilai untuk not_found, sertakan teks dalam tanda kutip berganda ("").

Catatan: Hati-hati jika anda menyediakan tali kosong ("") untuk not_found. Sekiranya tiada perlawanan, XLOOKUP tidak akan memaparkan apa-apa dan bukannya # N / A. Sekiranya anda ingin melihat ralat # N / A ketika perlawanan tidak dijumpai, hilangkan argumen sepenuhnya.

Jenis padanan

Secara lalai, XLOOKUP akan melakukan perlawanan tepat. Tingkah laku padanan dikendalikan oleh argumen pilihan yang disebut match_type, yang mempunyai pilihan berikut:

Jenis padanan Kelakuan
0 (lalai) Pertandingan tepat. Akan kembali # N / A jika tiada perlawanan.
-1 Padanan tepat atau item yang lebih kecil seterusnya.
1 Padanan tepat atau item seterusnya yang lebih besar.
2 Perlawanan wildcard (*,?, ~)

Mod carian

Secara lalai, XLOOKUP akan mula sepadan dengan nilai data pertama. Tingkah laku carian dikendalikan oleh argumen pilihan yang disebut search_mode , yang memberikan pilihan berikut:

Mod carian Kelakuan
1 (lalai) Cari dari nilai pertama
-1 Cari dari nilai terakhir (terbalik)
2 Nilai carian binari disusun mengikut urutan menaik
-2 Nilai carian binari disusun mengikut urutan menurun

Pencarian binari sangat cepat, tetapi data mesti disusun mengikut keperluan. Sekiranya data tidak disusun dengan betul, carian binari dapat mengembalikan hasil yang tidak sah yang kelihatan normal.

Contoh # 1 - padanan tepat asas

Secara lalai, XLOOKUP akan melakukan perlawanan yang tepat. Dalam contoh di bawah, XLOOKUP digunakan untuk mendapatkan Penjualan berdasarkan padanan tepat pada Filem. Formula dalam H5 adalah:

=XLOOKUP(H4,B5:B9,E5:E9)

Penjelasan lebih terperinci di sini.

Contoh # 2 - padanan anggaran asas

Untuk mengaktifkan padanan anggaran, berikan nilai untuk argumen "match_mode". Dalam contoh di bawah ini, XLOOKUP digunakan untuk mengira potongan berdasarkan kuantiti, yang memerlukan anggaran yang hampir sama. Rumus dalam F5 membekalkan -1 untuk match_mode untuk membolehkan padanan anggaran dengan tingkah laku "padanan tepat atau terkecil seterusnya":

=XLOOKUP(E5,B5:B9,C5:C9,,-1)

Penjelasan lebih terperinci di sini.

Contoh # 3 - pelbagai nilai

XLOOKUP dapat mengembalikan lebih dari satu nilai pada masa yang sama untuk perlawanan yang sama. Contoh di bawah menunjukkan bagaimana XLOOKUP dapat dikonfigurasi untuk mengembalikan tiga nilai padanan dengan satu formula. Formula dalam C5 adalah:

=XLOOKUP(B5,B8:B15,C8:E15)

Perhatikan susunan pulangan (C8: E15) merangkumi 3 lajur: Pertama, Terakhir, Jabatan. Ketiga-tiga nilai dikembalikan dan ditumpahkan ke julat C5: E5.

Contoh # 4 - carian dua hala

XLOOKUP dapat digunakan untuk melakukan pencarian dua arah, dengan meletakkan satu XLOOKUP di dalam yang lain. Dalam contoh di bawah, XLOOKUP "dalaman" mengambil keseluruhan baris (semua nilai untuk Glass), yang diserahkan kepada XLOOKUP "luar" sebagai susunan kembali. XLOOKUP luar mencari kumpulan yang sesuai (B) dan mengembalikan nilai yang sepadan (17.25) sebagai hasil akhir.

=XLOOKUP(I6,C4:F4,XLOOKUP(I5,B5:B9,C5:F9))

Maklumat lebih lanjut di sini.

Contoh # 5 - mesej tidak dijumpai

Seperti fungsi carian lain, jika XLOOKUP tidak menemui nilai, ia mengembalikan kesalahan # N / A. Untuk memaparkan mesej khusus dan bukannya # N / A, berikan nilai untuk argumen "tidak dijumpai" pilihan, yang disertakan dalam tanda petik ganda (""). Sebagai contoh, untuk memaparkan "Tidak dijumpai" ketika tidak ada filem yang sesuai, berdasarkan lembaran kerja di bawah, gunakan:

=XLOOKUP(H4,B5:B9,E5:E9,"Not found")

Anda boleh menyesuaikan mesej ini seperti yang anda mahukan: "Tidak sepadan", "Filem tidak dijumpai", dll.

Contoh # 6 - kriteria kompleks

Dengan kemampuan menangani susunan secara semula jadi, XLOOKUP dapat digunakan dengan kriteria yang kompleks. Dalam contoh di bawah, XLOOKUP sepadan dengan rekod pertama di mana: akaun bermula dengan "x" dan wilayah adalah "timur" dan bulan bukan April:

=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16)

Perincian: (1) contoh mudah, (2) contoh yang lebih kompleks.

Keuntungan XLOOKUP

XLOOKUP menawarkan beberapa kelebihan penting, terutamanya berbanding dengan VLOOKUP:

  • XLOOKUP dapat mencari data di sebelah kanan atau kiri nilai carian
  • XLOOKUP dapat mengembalikan banyak hasil (contoh # 3 di atas)
  • Lalai XLOOKUP ke padanan tepat (lalai VLOOKUP untuk anggaran)
  • XLOOKUP boleh berfungsi dengan data menegak dan mendatar
  • XLOOKUP dapat melakukan carian terbalik (terakhir hingga pertama)
  • XLOOKUP dapat mengembalikan keseluruhan baris atau lajur, bukan hanya satu nilai
  • XLOOKUP boleh berfungsi dengan tatasusunan untuk menerapkan kriteria yang kompleks

Catatan

  1. XLOOKUP boleh berfungsi dengan tatasusunan menegak dan mendatar.
  2. XLOOKUP akan mengembalikan # N / A jika nilai carian tidak dijumpai.
  3. The tatasusun_carian mesti mempunyai dimensi yang serasi dengan return_array hujah, jika tidak XLOOKUP akan kembali #VALUE!
  4. Sekiranya XLOOKUP digunakan di antara buku kerja, kedua buku kerja mesti dibuka, jika tidak, XLOOKUP akan mengembalikan #REF !.
  5. Seperti fungsi INDEX, XLOOKUP mengembalikan rujukan sebagai hasilnya.

Video-video yang berkaitan

Contoh XLOOKUP asas Dalam video ini, kami akan menetapkan fungsi XLOOKUP dengan contoh asas. Sesuai dengan nama Bandar, kami akan mendapatkan Negara dan Penduduk. Padanan anggaran XLOOKUP asas Dalam video ini, kami akan mengatur fungsi XLOOKUP untuk melakukan pemadanan anggaran untuk menghitung potongan berdasarkan kuantiti. XLOOKUP dengan logik boolean Dalam video ini kita akan melihat bagaimana menggunakan fungsi XLOOKUP dengan logik Boolean untuk menerapkan beberapa kriteria. XLOOKUP dengan beberapa nilai pencarian Dalam video ini, kami akan menyiapkan XLOOKUP untuk mengembalikan beberapa nilai dalam array dinamik, dengan memberikan pelbagai nilai pencarian dan bukan satu nilai pencarian.

Artikel menarik...