Cara menggunakan fungsi XLOOKUP dalam Excel

Apakah fungsi Xlookup dalam Excel ? Bagaimana untuk menggunakan Xlookup dalam Excel ? Jom ketahui dengan LuckyTemplates.com!

Jika anda kerap menggunakan Excel, anda mungkin sudah biasa dengan fungsi carian. Jika anda tidak tahu, anda hanya perlu memahaminya sebagai fungsi Excel yang digunakan untuk mencari data atau nilai tertentu pada baris atau lajur dalam hamparan.

Fungsi ini amat berguna apabila anda perlu memproses banyak data dalam hamparan Excel. Dalam Excel, anda mempunyai beberapa jenis fungsi LOOKUP. XLOOKUP adalah salah satu daripadanya. Jadi apakah fungsi XLOOKUP, apakah formula XLOOKUP dan cara menggunakannya? Jom ketahui dengan LuckyTemplates.com!

Perkara yang perlu diketahui tentang fungsi XLOOKUP dalam Excel

• Apakah XLOOKUP?
  • Bagaimana untuk mengakses fungsi XLOOKUP
  • Sintaks fungsi XLOOKUP
• Kebaikan dan keburukan fungsi XLOOKUP dalam Excel
  • Kelebihan fungsi XLOOKUP
  • Kelemahan fungsi XLOOKUP
• Cara menggunakan fungsi XLOOKUP
  • Tiada nombor indeks lajur tambahan
  • Padanan tepat secara lalai
  • XLOOKUP boleh didapati di sebelah kiri
  • Gunakan XLOOKUP untuk mencari julat
  • XLOOKUP menggantikan fungsi HLOOKUP
  • XLOOKUP boleh dilihat dari bawah ke atas

Apakah fungsi XLOOKUP dalam Excel?

Pada dasarnya, XLOOKUP menggantikan fungsi lama seperti HLOOKUP, VLOOKUP dan LOOKUP. Ia menyokong mencari data secara menegak dan mendatar.

Dengan kata lain, fungsi XLOOKUP dalam Excel membantu anda mencari nilai dengan cepat dalam set data yang disediakan, secara mendatar dan menegak. Kemudian ia mengembalikan nilai yang sepadan mengikut baris dan lajur yang berbeza.

Terdapat banyak cara untuk memaksimumkan penggunaan fungsi XLOOKUP.

Bagaimana untuk mengakses fungsi XLOOKUP

Pada masa ini, XLOOKUP hanya tersedia untuk pengguna program Insiders. Sesiapa sahaja yang menyertai program Insider boleh mengakses ciri Excel terkini apabila ia tersedia. Microsoft akan melancarkan fungsi ini tidak lama lagi kepada semua pengguna Office 365. Malangnya, XLOOKUP tidak tersedia untuk pengguna yang menjalankan versi Office 2010, 2013, 2016 dan 2019.

Jika anda tidak menggunakan suite Microsoft 365 pada komputer anda, anda mungkin perlu menaik taraf kepada Microsoft 365 untuk mendapatkan akses kepada fungsi XLOOKUP. Dan jika anda menggunakan Microsoft 365, anda akan melihat pilihan didayakan dalam Excel. XLOOKUP juga tersedia di Office 365 Online.

Sintaks fungsi XLOOKUP

Sintaks fungsi XLOOKUP adalah serupa dengan sintaks fungsi VLOOKUP dan HLOOKUP. Jika anda pernah menggunakannya sebelum ini, anda akan mendapati penggunaan XLOOKUP lebih mudah. Berikut ialah sintaks untuk fungsi XLOOKUP dalam Excel:

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

Fungsi XLOOKUP menyokong sehingga 6 argumen dan berikut adalah nilainya.

1. lookup_value (diperlukan): Nilai yang anda ingin cari.

2. lookup_array (diperlukan): Tatasusunan tempat anda ingin mencari nilai carian.

3. return_array (diperlukan): Tatasusunan dari tempat anda ingin mengambil dan mengembalikan nilai apabila nilai carian ditemui.

4. [jika_tidak_ditemui] (pilihan): Apabila tiada padanan ditemui, ini ialah nilai yang dikembalikan.

5. [match_mode] (pilihan): Argumen ini membolehkan anda menentukan jenis padanan yang anda cari. Terdapat nilai yang berbeza untuk menentukannya:

• 0 - Mencari padanan tepat dan nilai mesti sepadan dengan nilai dalam lookup_array dengan tepat. Nilai ini juga ditetapkan sebagai lalai apabila tidak disebut.
• -1 - Mencari padanan yang tepat dan apabila ditemui, akan kembali kepada nilai yang lebih kecil seterusnya.
• 1 - Mencari padanan yang tepat dan apabila ditemui mengembalikan nilai seterusnya yang lebih besar.
• 2 - Lakukan pemadanan separa menggunakan kad bebas di mana *, ? dan ~ mempunyai makna yang istimewa.

6. [mode_carian] (pilihan): Digunakan untuk menentukan mod carian XLOOKUP dalam lookup_array. Terdapat nilai yang berbeza untuk menentukan yang sama:

• 1 - Lakukan carian bermula dari item pertama. Nilai ini ditetapkan sebagai lalai apabila tiada apa yang ditentukan.
• -1 - Melakukan carian terbalik bermula dari item terakhir.
• 2 - Lakukan carian binari dalam lookup_array di mana data perlu diisih dalam tertib menaik. Jika data tidak diisih, ia boleh menghasilkan ralat atau hasil yang salah.
• -2 - Lakukan carian binari dalam lookup_array di mana data perlu diisih mengikut tertib menurun. Jika data tidak diisih, ia boleh menghasilkan ralat atau hasil yang salah.

Kebaikan dan keburukan fungsi XLOOKUP dalam Excel

XLOOKUP masih merupakan fungsi yang berfaedah berbanding VLOOKUP dan INDEX/ATCH. Tetapi ia juga mempunyai kelemahan tersendiri.

Kelebihan fungsi XLOOKUP

• Rahang berfungsi secara menegak dan mendatar.
• Hanya 3 argumen diperlukan, bukannya 4 seperti dalam VLOOKUP dan INDEX MATCH.
• Sentiasa lalai kepada padanan tepat.
• Pencarian separa boleh dilakukan menggunakan kad bebas.
• Carian boleh dilakukan dalam susunan menurun.
• Gunakan satu fungsi dan bukannya dua dalam INDEX MATCH.

Kelemahan fungsi XLOOKUP

• Hujah pilihan mungkin kelihatan rumit kepada pemula.
• Ia boleh memakan masa yang lebih lama apabila memilih dua julat dan mempunyai terlalu banyak sel dalam hamparan.
• Mengembalikan ralat semasa mencari dan mengembalikan tatasusunan yang tidak sama panjang.
• Ingat kedua-dua julat carian dan pulangan.

Faedah menggunakan fungsi XLOOKUP Excel

• XLOOKUP menjadikan formula yang paling banyak digunakan dalam Excel lebih mudah dan kurang terdedah kepada ralat . Anda hanya perlu menulis =XLOOKUP (data untuk mencari, senarai, senarai hasil ) dan anda akan mendapat jawapan atau #N/A jika nilainya tidak dijumpai.
• Keputusan carian lalai adalah tepat . Salah satu kelemahan VLOOKUP ialah anda perlu menyebut FALSE sebagai parameter terakhir untuk mendapatkan hasil yang betul. XLOOKUP membetulkannya dengan melakukan padanan tepat secara lalai. Anda boleh menggunakan parameter mod padanan untuk menukar gelagat carian jika anda mahu.
• Parameter keempat menyokong kes di mana nilai tidak ditemui . Dalam kebanyakan situasi perniagaan, anda akan dipaksa untuk "membungkus" formula carian dengan IFERROR atau IFNA untuk menghapuskan ralat. XLOOKUP menyediakan parameter ke-4 supaya anda boleh mengetahui hasil lalai yang diingini jika nilai tidak ditemui.
• XLOOKUP menyediakan parameter pemilihan untuk mencari situasi khas . Anda boleh mencari dari atas atau bawah, mengikut aksara khas dan pilihan yang lebih pantas untuk mencari data yang ditapis.

Cara menggunakan fungsi XLOOKUP

Lihat contoh di bawah untuk memahami cara XLOOKUP berfungsi. Dalam contoh ini, kita perlu mengembalikan bahagian dari lajur F untuk setiap ID dalam lajur A.

Ini adalah contoh yang mencari hasil yang tepat, tetapi fungsi XLOOKUP hanya memerlukan tiga jenis maklumat.

Imej di bawah menunjukkan XLOOKUP dengan 5 argumen, tetapi tiga argumen pertama diperlukan untuk hasil yang betul. Jadi mari kita fokus pada mereka:

• Lookup_value : Nilai carian
• Lookup_array : Julat carian
• Return_array : Julat yang mengandungi nilai untuk dikembalikan

Dalam contoh ini, kami akan menggunakan formula:

=XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)

Sekarang mari kita terokai beberapa kelebihan XLOOKUP berbanding VLOOKUP.

Tiada nombor indeks lajur tambahan

Argumen ketiga VLOOKUP adalah untuk menentukan bilangan lajur maklumat untuk dikembalikan daripada julat jadual. Ini bukan lagi masalah dengan XLOOKUP kerana fungsi tersebut membolehkan pengguna memilih julat pulangan. (lajur F dalam contoh ini).

Dan jangan lupa, XLOOKUP boleh melihat baki data sel yang dipilih, tidak seperti VLOOKUP.

Anda juga tidak akan menghadapi ralat formula semasa menambah lajur baharu. Jika ini berlaku dalam hamparan, julat pulangan akan dilaraskan secara automatik.

Padanan tepat secara lalai

Apabila menggunakan VLOOKUP, pengguna mesti menentukan padanan tepat jika dikehendaki. Tetapi dengan XLOOKUP lalai adalah padanan tepat. Ini mengurangkan hujah keempat dan memastikan lebih sedikit kesilapan untuk pengguna baharu. Ringkasnya, XLOOKUP memerlukan lebih sedikit soalan daripada VLOOKUP dan mesra pengguna.

XLOOKUP boleh didapati di sebelah kiri

Keupayaan untuk memilih julat carian memberikan XLOOKUP lebih fleksibiliti daripada VLOOKUP. Dengan XLOOKUP, susunan lajur dalam jadual tidak penting.

VLOOKUP dihadkan dengan mencari lajur paling kiri jadual dan kemudian kembali daripada bilangan lajur yang ditentukan di sebelah kanan.

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

Gunakan formula di bawah:

=XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

Gunakan XLOOKUP untuk mencari julat

Walaupun tidak sebiasa padanan tepat, anda boleh menggunakan formula carian untuk mencari nilai dalam julat. Sebagai contoh, kami ingin memulangkan diskaun bergantung pada jumlah yang dibelanjakan.

Kali ini kita tidak mencari nilai tertentu dan perlu mengetahui julat nilai dalam lajur B yang terdapat dalam lajur E untuk menentukan diskaun.

XLOOKUP mempunyai hujah keempat pilihan (ingat, ia lalai kepada padanan mutlak) yang dipanggil match_mode.

Anda boleh melihat bahawa XLOOKUP boleh mencari padanan anggaran lebih baik daripada VLOOKUP.

Terdapat pilihan untuk mencari padanan terdekat kurang daripada (-1) atau padanan terdekat lebih besar daripada (1) nilai carian. Terdapat juga pilihan untuk menggunakan kad bebas (2), seperti ? atau *. Tetapan ini tidak didayakan secara lalai seperti dengan VLOOKUP.

Formula dalam contoh ini mengembalikan nilai terdekat kurang daripada nilai carian jika tiada padanan tepat ditemui:

=XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,-1)

Walau bagaimanapun, terdapat ralat dalam sel C7, diskaun 0% sepatutnya dikembalikan kerana ia hanya membelanjakan 64, yang tidak memenuhi kriteria untuk menerima diskaun.

Satu lagi kelebihan fungsi XLOOKUP ialah ia tidak memerlukan julat carian dalam susunan menaik seperti VLOOKUP.

Masukkan baris baharu di bahagian bawah jadual carian dan kemudian buka formula. Kembangkan skop penggunaan dengan mengklik dan menyeret sudut.

Formula segera membetulkan ralat. Ia tidak mempunyai masalah dengan mempunyai 0 di hujung jadual julat.

XLOOKUP menggantikan fungsi HLOOKUP

Seperti yang dinyatakan, fungsi XLOOKUP juga boleh menggantikan fungsi HLOOKUP. Satu fungsi boleh menggantikan dua fungsi, apakah yang lebih baik?

Fungsi HLOOKUP melihat ke atas secara mendatar, digunakan untuk mencari mengikut baris.

Fungsi ini tidak begitu terkenal seperti VLOOKUP tetapi berguna dalam kes apabila tajuk berada dalam lajur A dan data berada dalam baris 4 dan 5 seperti contoh di bawah.

XLOOKUP boleh mencari dalam kedua-dua arah, lajur bawah dan sepanjang baris.

Dalam contoh ini, formula digunakan untuk mengembalikan nilai jualan yang dikaitkan dengan nama dalam lajur A2. Ia melihat pada baris 4 untuk nama dan mengembalikan nilai dari baris 5:

=XLOOKUP(A2,B4:E4,B5:E5)

XLOOKUP boleh dilihat dari bawah ke atas

Biasanya anda perlu melihat dari atas ke bawah dalam senarai untuk mencari kejadian pertama sesuatu nilai. XLOOKUP mempunyai hujah kelima bernama search_mode. Argumen ini membenarkan pengalihan carian untuk bermula dari bahagian bawah senarai untuk mencari kehadiran terakhir sesuatu nilai.

Dalam contoh di bawah, kita perlu mencari tahap inventori setiap produk dalam lajur A.

Jadual carian disusun mengikut susunan tarikh. Kami ingin mengembalikan tahap inventori daripada semakan terakhir (kejadian terakhir ID Produk).

Hujah kelima fungsi XLOOKUP menyediakan empat pilihan. Di sini kami akan menggunakan pilihan Carian terakhir-ke-pertama .

Formula yang digunakan dalam contoh ini:

=XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,-1)

Dalam formula ini, hujah keempat ditinggalkan. Ia adalah pilihan dan kami mahu lalai menjadi padanan tepat.

Fungsi XLOOKUP ialah pengganti yang dijangkakan kepada kedua-dua fungsi VLOOKUP dan HLOOKUP. Satu siri contoh telah digunakan dalam artikel ini untuk menunjukkan kelebihan fungsi XLOOKUP.

XLOOKUP ialah peningkatan yang hebat apabila ia berkaitan dengan fungsi carian. Satu-satunya had fungsi XLOOKUP ialah ia tidak serasi ke belakang. Jika anda berkongsi hamparan dengan versi Microsoft Office Suite yang lebih lama, anda mungkin menerima ralat.

Walau bagaimanapun, anda boleh mengakses XLOOKUP daripada Office 365 Online. XLOOKUP masih dalam pembangunan, jadi ia akan mengambil sedikit masa untuk pengguna Excel untuk mengguna pakainya.

Semoga berjaya!

Perbezaan antara XLOOKUP dan VLOOKUP

Mengapa XLOOKUP lebih baik daripada VLOOKUP?

Walaupun VLOOKUP mempunyai kegunaannya sendiri, ia mempunyai ciri yang lebih terhad berbanding XLOOKUP. Ini menjadikan XLOOKUP pilihan paling popular apabila perlu memproses sejumlah besar data. Akibatnya, ia berjalan lebih cepat dan mempunyai lebih sedikit ralat.

• XLOOKUP tidak mengehadkan julat data : XLOOKUP lebih fleksibel daripada VLOOKUP. VLOOKUP boleh mencari nilai yang terletak hanya di lajur paling kiri jadual dan mengembalikan nilai dari lajur yang sepadan di sebelah kanan. Sementara itu, XLOOKUP hanya memerlukan langkah yang lebih mudah dan boleh mengembalikan nilai untuk mana-mana lajur dalam mana-mana arah.
• Hasil lalai XLOOKUP ialah nilai yang paling tepat .
• Secara lalai, fungsi VLOOKUP dalam Excel sentiasa mencari hasil anggaran . Walau bagaimanapun, jika anda memerlukan hasil yang tepat, anda harus memasukkan FALSE dalam argumen range_lookup.
• Fungsi XLOOKUP mencari nilai carian dalam julat tertentu dan sentiasa lalai kepada nilai yang tepat. Jika anda tidak menemui satu, anda boleh mengedit hujah untuk mencari padanan anggaran yang lebih kecil atau lebih besar daripada nilai carian.

Sebab lain:

• XLOOKUP boleh mengendalikan sisipan atau pemadaman lajur.
• XLOOKUP melaksanakan carian menegak dan mendatar.
• XLOOKUP menyediakan padanan anggaran yang lebih bijak.
• XLOOKUP memerlukan lebih sedikit rujukan sel.