Cara Menggunakan Fungsi XLOOKUP dalam Microsoft Excel

XLOOKUP baru Excel akan menggantikan VLOOKUP, memberikan penggantian yang kuat untuk salah satu fungsi Excel yang paling popular. Fungsi baru ini menyelesaikan beberapa batasan VLOOKUP dan mempunyai fungsi tambahan. Inilah yang perlu anda ketahui.

Apa itu XLOOKUP?

Fungsi XLOOKUP baru mempunyai penyelesaian untuk beberapa had terbesar VLOOKUP. Selain itu, ia juga menggantikan HLOOKUP. Sebagai contoh, XLOOKUP dapat melihat ke kiri, lalai dengan padanan tepat, dan membolehkan anda menentukan julat sel dan bukannya nombor lajur. VLOOKUP tidak mudah digunakan atau serba boleh. Kami akan menunjukkan kepada anda bagaimana semuanya berfungsi.

Buat masa ini, XLOOKUP hanya tersedia untuk pengguna dalam program Insiders. Sesiapa sahaja boleh menyertai program Insiders untuk mengakses ciri Excel terbaru sebaik sahaja tersedia. Microsoft akan segera melancarkannya kepada semua pengguna Office 365.

Cara Menggunakan Fungsi XLOOKUP

Mari selami terus dengan contoh XLOOKUP dalam tindakan. Ambil contoh data di bawah. Kami ingin mengembalikan jabatan dari lajur F untuk setiap ID di lajur A.

Ini adalah contoh pencarian padanan tepat klasik. Fungsi XLOOKUP hanya memerlukan tiga keping maklumat.

Gambar di bawah menunjukkan XLOOKUP dengan enam argumen, tetapi hanya tiga yang pertama diperlukan untuk perlawanan yang tepat. Oleh itu mari kita fokus kepada mereka:

Look__alue: Apa yang anda cari.
Look__array: Tempat mencari.
Return_array: julat yang mengandungi nilai untuk dikembalikan.

Formula berikut akan berfungsi untuk contoh ini: =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)

Mari kita terokai beberapa kelebihan yang dimiliki XLOOKUP berbanding VLOOKUP di sini.

Tiada Lagi Nombor Indeks Lajur

Argumen ketiga terkenal dari VLOOKUP adalah untuk menentukan bilangan lajur maklumat yang akan dikembalikan dari array jadual. Ini bukan lagi masalah kerana XLOOKUP membolehkan anda memilih julat untuk kembali dari (lajur F dalam contoh ini).

Dan jangan lupa, XLOOKUP dapat melihat data yang tersisa dari sel yang dipilih, tidak seperti VLOOKUP. Lebih lanjut mengenai perkara di bawah.

Anda juga tidak lagi mempunyai masalah formula yang rosak ketika lajur baru dimasukkan. Sekiranya itu berlaku dalam hamparan anda, julat pengembalian akan disesuaikan secara automatik.

Padanan Tepat adalah Lalai

Itu selalu membingungkan ketika belajar VLOOKUP mengapa anda harus menentukan padanan yang dikehendaki.

Nasib baik, XLOOKUP tidak mengikut padanan tepat - alasan yang lebih biasa untuk menggunakan formula carian). Ini mengurangkan keperluan untuk menjawab hujah kelima dan memastikan lebih sedikit kesalahan oleh pengguna yang baru menggunakan formula.

Jadi ringkasnya, XLOOKUP menanyakan lebih sedikit soalan daripada VLOOKUP, lebih mesra pengguna, dan juga lebih tahan lama.

XLOOKUP boleh Melihat ke Kiri

Mampu memilih julat carian menjadikan XLOOKUP lebih serba boleh daripada VLOOKUP. Dengan XLOOKUP, susunan lajur jadual tidak menjadi masalah.

VLOOKUP dikekang dengan mencari lajur paling kiri jadual dan kemudian kembali dari sebilangan lajur yang ditentukan ke kanan.

Dalam contoh di bawah, kita perlu mencari ID (lajur E) dan mengembalikan nama orang itu (lajur D).

Formula berikut dapat mencapai ini: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

Apa yang Perlu Dilakukan Sekiranya Tidak Ditemui

Pengguna fungsi carian sangat akrab dengan mesej ralat # N / A yang menyambut mereka ketika VLOOKUP atau fungsi MATCH mereka tidak dapat menemui apa yang diperlukannya. Dan selalunya ada alasan logik untuk ini.

Oleh itu, pengguna dengan cepat meneliti cara menyembunyikan ralat ini kerana tidak betul atau berguna. Dan, tentu ada cara untuk melakukannya.

XLOOKUP dilengkapi dengan argumen "jika tidak dijumpai" terbina dalam untuk mengatasi kesilapan tersebut. Mari kita melihatnya beraksi dengan contoh sebelumnya, tetapi dengan ID yang tersalah taip.

Rumus berikut akan memaparkan teks "ID Tidak Betul" dan bukannya mesej ralat: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,"Incorrect ID")

Menggunakan XLOOKUP untuk Range Lookup

Walaupun tidak sepadan dengan pencocokan tepat, penggunaan formula pencarian yang sangat efektif adalah mencari nilai dalam julat. Ambil contoh berikut. Kami ingin mengembalikan diskaun bergantung kepada jumlah yang dibelanjakan.

Kali ini kami tidak mencari nilai tertentu. Kita perlu tahu di mana nilai dalam lajur B berada dalam julat di lajur E. Itu akan menentukan potongan yang diperoleh.

XLOOKUP mempunyai argumen kelima opsional (ingat, ia sesuai dengan padanan tepat) dinamakan mod padanan.

Anda dapat melihat bahawa XLOOKUP mempunyai kemampuan yang lebih besar dengan perkadaran yang hampir sama dengan VLOOKUP.

Terdapat pilihan untuk mencari padanan terdekat yang lebih kecil daripada (-1) atau yang terdekat lebih besar daripada (1) nilai yang dicari. Terdapat juga pilihan untuk menggunakan watak wildcard (2) seperti? atau *. Tetapan ini tidak diaktifkan secara lalai seperti pada VLOOKUP.

Rumus dalam contoh ini mengembalikan nilai yang paling dekat daripada nilai yang dicari jika pencocokan tepat tidak dijumpai: =XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,,-1)

Walau bagaimanapun, terdapat kesalahan dalam sel C7 di mana kesalahan # N / A dikembalikan (argumen 'jika tidak dijumpai' tidak digunakan). Ini semestinya memberikan potongan 0% kerana perbelanjaan 64 tidak mencapai kriteria untuk diskaun.

Kelebihan lain dari fungsi XLOOKUP adalah bahawa ia tidak memerlukan julat pencarian dalam urutan menaik seperti yang dilakukan oleh VLOOKUP.

Masukkan baris baru di bahagian bawah jadual carian dan kemudian buka formula. Luaskan julat yang digunakan dengan mengklik dan menyeret sudut.

Rumus segera membetulkan ralat. Bukan masalah dengan memiliki "0" di bahagian bawah julat.

Secara peribadi, saya masih menyusun jadual mengikut lajur carian. Memiliki "0" di bahagian bawah akan membuat saya gila. Tetapi hakikat bahawa formula itu tidak pecah adalah sesuatu yang luar biasa.

XLOOKUP Mengganti Fungsi HLOOKUP Terlalu

Seperti disebutkan, fungsi XLOOKUP juga ada di sini untuk menggantikan HLOOKUP. Satu fungsi untuk menggantikan dua. Hebat!

Fungsi HLOOKUP adalah carian mendatar, digunakan untuk mencari sepanjang baris.

Tidak begitu terkenal dengan VLOOKUP saudara kandungnya, tetapi berguna untuk contoh seperti di bawah tajuk di lajur A, dan data berada di sepanjang baris 4 dan 5.

XLOOKUP dapat melihat ke dua arah - lajur bawah dan juga di sepanjang baris. Kita tidak lagi memerlukan dua fungsi yang berbeza.

Dalam contoh ini, formula digunakan untuk mengembalikan nilai penjualan yang berkaitan dengan nama di sel A2. Ia kelihatan di sepanjang baris 4 untuk mencari nama, dan mengembalikan nilai dari baris 5:=XLOOKUP(A2,B4:E4,B5:E5)

XLOOKUP Boleh Melihat Dari Bahagian Bawah

Biasanya, anda perlu mencari senarai untuk mencari terjadinya nilai pertama (hanya sahaja). XLOOKUP mempunyai argumen keenam bernama mod carian. Ini membolehkan kita menukar carian untuk bermula di bahagian bawah dan mencari senarai untuk mencari kemunculan terakhir nilai sebagai gantinya.

Dalam contoh di bawah, kami ingin mencari tahap stok untuk setiap produk di lajur A.

Jadual carian mengikut urutan tarikh, dan terdapat banyak pemeriksaan stok setiap produk. Kami ingin mengembalikan tahap stok dari kali terakhir diperiksa (kejadian terakhir ID Produk).

Argumen keenam fungsi XLOOKUP memberikan empat pilihan. Kami berminat untuk menggunakan pilihan "Cari yang terakhir hingga yang pertama".

Formula yang lengkap ditunjukkan di sini: =XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,,-1)

Dalam formula ini, hujah keempat dan kelima tidak diendahkan. Ini adalah pilihan, dan kami mahukan default pertandingan yang tepat.

Pusingkan

Fungsi XLOOKUP adalah pengganti yang dinanti-nantikan kedua-dua fungsi VLOOKUP dan HLOOKUP.

Pelbagai contoh digunakan dalam artikel ini untuk menunjukkan kelebihan XLOOKUP. Salah satunya ialah XLOOKUP dapat digunakan di seluruh helaian, buku kerja dan juga dengan jadual. Contohnya ringkas dalam artikel untuk membantu pemahaman kita.

Oleh kerana array dinamik diperkenalkan ke Excel tidak lama lagi, ia juga dapat mengembalikan pelbagai nilai. Ini semestinya sesuatu yang patut diterokai dengan lebih lanjut.

Hari-hari VLOOKUP dihitung. XLOOKUP ada di sini dan tidak lama lagi akan menjadi formula carian de facto.