Cara Menggunakan VLOOKUP dan XLOOKUP dalam Excel: Panduan Lengkap dan Contoh Praktikal

Pernahkah anda mendapati diri anda tersesat di antara beribu-ribu baris dan lajur hamparan apabila mencari data tertentu? Jika anda bekerja secara kerap dengan Excel, anda mungkin pernah mendengar tentang VLOOKUP dan XLOOKUP, dua fungsi yang membuat perbezaan apabila ia datang untuk mengautomasikan carian dan menyelaraskan tugas. Menguasai fungsi ini mewakili lonjakan dalam kualiti dalam kehidupan harian mana-mana pengguna, daripada mereka yang menguruskan senarai kenalan mudah kepada mereka yang menganalisis besar pangkalan data dalam syarikat.

Artikel ini ialah panduan utama anda untuk memahami dan menggunakan VLOOKUP dan XLOOKUP dalam Excel seperti seorang profesional. Di sini kami pecahkan semua rahsia, kelebihan, batasan dan Trik praktikal, bermula dari asas dan menyelidiki contoh, sintaks dan cara untuk memanfaatkannya sepenuhnya, supaya carian dalam Excel tidak akan menghalang anda lagi.

Apakah VLOOKUP dan XLOOKUP dalam Excel?

VLOOKUP dan XLOOKUP tergolong dalam keluarga fungsi carian dan rujukan dalam Excel. Ini adalah alat yang memudahkan untuk mencari data tertentu dalam senarai atau jadual besar, mengembalikan maklumat berkaitan dengan cepat dan tanpa ralat. Penggunaannya adalah pelbagai seperti yang diperlukan: ​​invois, inventori, laporan, sumber manusia, CRM, perakaunan... Jika anda perlu mencari sesuatu dalam lautan data, ciri ini adalah kawan baik anda.

VLOOKUP ialah formula klasik untuk mencari data secara menegak., iaitu, dalam lajur. Ia telah menjadi tunjang utama dalam Excel selama bertahun-tahun. Walau bagaimanapun, dengan versi baharu hadir XLOOKUP, fungsi yang lebih berkuasa dan fleksibel yang menyelesaikan banyak batasan VLOOKUP.

Mengapa sangat penting untuk mempelajari kedua-duanya? Kerana banyak syarikat masih bekerja dengan versi Excel yang lebih lama, di mana XLOOKUP tidak wujud, dan kerana VLOOKUP masih berguna dan sangat serasi. Sekarang, jika anda mempunyai versi yang betul, SEARCHX membuka dunia kemungkinan baharu untuk anda.

Untuk apa setiap satu? Kegunaan asas dan situasi kehidupan sebenar

Fungsi VLOOKUP digunakan untuk mencari data dengan merujuk kepada nilai dalam lajur paling kiri jadual., mengembalikan sesuatu yang berkaitan dalam lajur lain dalam baris yang sama. Contohnya: anda mempunyai senarai produk dengan kod, perihalan dan harga, dan anda mahu penerangan serta harga dipaparkan secara automatik apabila anda memasukkan kod. VLOOKUP memudahkan anda.

SEARCHX membawa konsep ini ke peringkat seterusnya: membolehkan anda mencari secara menegak dan mendatar, dalam mana-mana lajur atau baris, dan mengembalikan satu atau lebih data tanpa sekatan VLOOKUP. Selain itu, anda boleh menyesuaikan gelagat carian, hasil carian sekiranya berlaku ralat, carian dengan kad bebas dan ciri lanjutan lain.

Mari kita lihat ini dengan contoh praktikal yang sangat biasa: pengurusan invois. Bayangkan anda adalah PKS yang menjual bekalan perubatan. Anda tidak mempunyai sistem pengebilan lanjutan dan perlu mengekstrak data daripada senarai lebih daripada 3.000 item setiap kali pesanan masuk. Pengisian data secara manual akan kekal dan mudah ralat. Dengan VLOOKUP atau XLOOKUP anda boleh mengautomasikan proses supaya, dengan hanya kod produk, penerangan, persembahan dan harga diisi secara automatik.

Perbezaan Utama: Apakah yang Mengehadkan VLOOKUP dan Mengapa XLOOKUP Evolusi?

VLOOKUP ialah fungsi yang hebat, tetapi ia mempunyai beberapa batasan. yang tidak selesa dalam helaian kompleks:

• Hanya cari dari kiri ke kanan. Data rujukan mesti sentiasa berada dalam lajur pertama julat yang ditunjukkan. Jika data yang anda cari tiada, anda perlu menyusun semula jadual atau membuat kombinasi tambahan.
• Mengembalikan nilai tunggal. Jika anda perlu mengekstrak berbilang item data berkaitan (cth., nama pertama, nama keluarga dan jabatan), anda perlu menggunakan berbilang formula.
• Anda mesti menunjukkan nombor lajur yang akan dikembalikan. Jika lajur kemudian disusun semula dalam jadual, formula mungkin memecahkan atau mengembalikan hasil yang salah.
• Hasil lalai untuk data tidak ditemui ialah ralat #N/A.. Ia mesti digabungkan dengan YA.RALAT untuk memaparkan mesej yang diperibadikan.
• Tidak menyokong kad bebas atau carian terbalik (bottom-up), mahupun carian binari lanjutan.

XLOOKUP mengalih keluar hampir semua sekatan ini dan menambah penambahbaikan yang menentukan:

• Membolehkan anda mencari dalam mana-mana lajur atau baris, tanpa mengira kedudukan. Anda boleh mencari di lajur tengah dan mengembalikan sesuatu daripada yang pertama, sebagai contoh.
• Kembalikan berbilang nilai apabila menggunakan julat berbilang lajur, memudahkan carian untuk pangkalan data yang lebih kompleks.
• Anda tidak perlu mengira lajur. Anda terus memilih julat hasil.
• Anda boleh menyesuaikan mesej tidak ditemui tanpa formula tambahan.
• Carian tepat, anggaran dan kad bebas tersedia untuk mencari sebahagian atau tepat apa yang anda cari.
• Kemungkinan carian terbalik (bawah ke atas) dan binari (sesuai untuk data tersusun).
• Mencari berbilang jadual serentak dan menggunakan XLOOKUP bersarang, yang memperluaskan kemungkinan automasi.

Versi Excel yang manakah membenarkan setiap fungsi?

Fungsi VLOOKUP tersedia dalam semua versi moden Excel., daripada yang tertua kepada yang terkini. Anda akan mendapati kedua-duanya dalam Microsoft 365 seperti dalam versi kekal Excel untuk desktop.

XLOOKUP, sebaliknya, hanya tersedia dari Excel versi 1910 dan seterusnya.. Jika anda mempunyai Excel 2016 atau salah satu versi awal dari 2019, anda mungkin tidak memilikinya. Untuk mengetahui sama ada anda boleh menggunakan XLOOKUP, hanya taip =XLOOKUP dalam bar formula dan lihat jika ia muncul dalam senarai cadangan.

Jika versi anda masih belum menyokongnya, jangan putus asa. Kuasai VLOOKUP selagi anda boleh, dan apabila tiba masanya, lompat ke XLOOKUP untuk memanfaatkan penambahbaikannya.

Sintaks VLOOKUP: Memecahkan Setiap Hujah

Mari kita pecahkan formula VLOOKUP untuk mengetahui dengan tepat cara ia berfungsi.:

=VLOOKUP(lookup_value; table_array; column_indicator; )

• nilai_cari: Data yang anda ingin cari (boleh ditulis terus atau dalam sel rujukan).
• susunan_jadual: Julat penuh untuk dicari, termasuk lajur tempat data rujukan berada dan lajur maklumat untuk diambil semula.
• lajur_penunjuk: Nombor (bermula dengan 1) lajur yang anda ingin keluarkan hasil dalam julat yang dipilih. Jika anda mahukan data daripada lajur kedua julat, anda akan meletakkan 2 di sini.
• : Pilihan. Jika BENAR atau ditinggalkan, cari padanan anggaran; Jika SALAH, hanya mengembalikan padanan tepat.

Contoh tipikal: Anda mempunyai jadual pelanggan dalam julat A1:D16. Anda ingin mencari nombor telefon (yang terdapat dalam lajur 4 atau lajur D) untuk pelanggan dengan ID 8. Formulanya ialah:

=VLOOKUP(8;A1:D16;4;SALAH)

Jika data tidak wujud dan anda meminta padanan tepat, ralat #N/A akan muncul. Jika anda membenarkan pemadanan anggaran, ia akan mengembalikan hasil yang paling hampir di bawah.

Trik dan Varian VLOOKUP Lanjutan

VLOOKUP tidak terhad kepada carian mengikut lajur pertama: anda boleh mentakrifkan semula julat untuk mencari mengikut mana-mana lajur. Contohnya, jika anda mempunyai nama dalam lajur B dan anda perlu mencari mengikut nama dan bukannya ID, hanya tentukan julat dari lajur B dan seterusnya dan tetapkan penunjuk lajur:

=VLOOKUP("Miguel Perea Ramos";B1:D16;3;SALAH)

Ini akan mencari mengikut nama dan mengembalikan, sebagai contoh, nombor telefon yang sepadan.

Satu lagi kombinasi yang berguna ialah VLOOKUP dengan senarai juntai bawah: Anda boleh membenarkan pengguna memilih nilai daripada senarai dan secara automatik mempunyai sel lain memaparkan maklumat berkaitan menggunakan VLOOKUP. Ia sesuai untuk fail, laporan automatik, katalog, dll.

Dan jika anda ingin mengelakkan kesilapan hodoh, gabungkan VLOOKUP dengan YA.RALAT untuk memaparkan mesej tersuai jika data tidak wujud:

=IF(VLOOKUP(8;A1:D16;4;FALSE);»Pelanggan tidak ditemui»)

Sintaks XLOOKUP: Hujah dan Kuasanya

Formula XLOOKUP berstruktur seperti ini:

=XLOOKUP(lookup_value; lookup_array; return_array; ; ; )

• nilai_cari: Data yang anda ingin cari (sel atau nilai langsung).
• lookup_array: Julat di mana Excel harus mencari data tersebut.
• array_dipulangkan: Julat dari mana untuk mengekstrak hasilnya. Ia boleh menjadi satu lajur atau beberapa jika anda mahukan beberapa keping data sekaligus, tidak seperti VLOOKUP.
• : Pilihan. Jika nilai tidak ditemui, anda boleh menentukan mesej atau data yang anda mahu muncul (contohnya, "Tidak Ditemui").
• : Pilihan. Mentakrifkan cara nilai dicari: 0 untuk tepat, -1 untuk tepat atau lebih rendah seterusnya, 1 untuk tepat atau lebih tinggi seterusnya, 2 untuk padanan kad bebas.
• : Pilihan. Membolehkan anda memilih cara melintasi data: 1 dari atas ke bawah, -1 dari bawah ke atas, 2 carian binari menaik, -2 carian binari menurun.

Contoh biasa penggunaan dengan XLOOKUP: Anda ingin mencari produk dalam senarai (kod dalam lajur A dan harga dalam lajur E), paparkan harga dan sesuaikan mesej jika produk itu tidak wujud. Formulanya ialah:

=XLOOKUP(B2;Harga!$A$1:$A$7000;Harga!$E$1:$E$7000; "Tidak dijumpai"; 0)

Di sini, jika elemen itu tidak wujud, ia akan terus menunjukkan kepada anda "Tidak Ditemui" tanpa perlu YA.RALAT.

Pecahan semua argumen XLOOKUP pilihan

Salah satu kelebihan besar XLOOKUP ialah fleksibiliti hujah pilihannya. Mari kita lihat mereka satu persatu:

• : Anda boleh mengembalikan teks, nombor atau sel kosong dengan memasukkan “”. Lupakan tentang ralat visual #N/A dan peribadikan pengalaman itu.
• :
  • 0: Padanan tepat (lalai).
  • -1: Padanan tepat atau nilai terendah seterusnya.
  • 1: Padanan tepat atau nilai tertinggi seterusnya.
  • 2: Padanan kad liar (* untuk berbilang aksara, ? untuk satu aksara, ~ untuk mengabaikan watak khas).
• :
  • 1: Dari baris pertama hingga terakhir (lalai).
  • -1: Dari terakhir hingga pertama (carian terbalik).
  • 2: Carian binari menaik (memerlukan data diisih dari terendah ke tertinggi).
  • -2: Carian binari menurun (memerlukan data diisih dari tertinggi ke terendah).

Contoh praktikal XLOOKUP dalam tindakan

Kami menunjukkan kepada anda bagaimana LOOKUPX memperoleh kelebihan dalam pelbagai senario kehidupan sebenar:

• Cari satu item data: Mencari nama negara dan mengembalikan kod negaranya.
• Cari berbilang data: Tidak seperti VLOOKUP, anda boleh mengembalikan keseluruhan baris maklumat (nama, jabatan, dll.) dengan satu formula.
• Penyesuaian tanpa ralat: Jika anda ingin mengelakkan #N/A biasa apabila data tidak wujud, anda hanya perlu menggunakan argumen .
• Carian gabungan (menegak dan mendatar): Anda boleh menyarangkan XLOOKUP untuk merentas nilai dalam jadual dalam kedua-dua dimensi, sama seperti menggabungkan INDEX dan MATCH.
• Julat dinamik: Jika anda mempunyai dua julat data (berbilang jadual), anda boleh menggabungkan kedua-duanya dalam formula dan XLOOKUP akan mencari kedua-duanya serentak.
• Carian kad bebas separa: Cari data mengikut padanan separa menggunakan *, ? atau ~ dalam nilai carian anda.

Perbandingan hujah pantas: VLOOKUP lwn. XLOOKUP

VLOOKUP sentiasa memerlukan nombor lajur di manakah data akan dikembalikan dan hanya membenarkan carian dalam lajur pertama julat. Sebaliknya, XLOOKUP tidak bergantung pada susunan lajur atau kedudukannya, hanya nyatakan julat yang tepat untuk dicari dan dikembalikan.

Selain itu, XLOOKUP menyokong tatasusunan dinamik.: Apabila return_array mengumpulkan berbilang lajur, anda boleh mengembalikan satu siri data bersebelahan dalam berbilang sel sekaligus, tanpa perlu mengulang formula.

Contoh hujah dan kes yang diselesaikan

Bayangkan jadual negara dengan data tentang populasi, jangka hayat dan kod. Jika anda hanya mahukan populasi, anda takrifkan return_matrix sebagai lajur populasi. Jika anda mahukan berbilang data, anda mentakrifkan return_array sebagai berbilang lajur. Dengan meletakkan formula dalam sel G6, data dalam lajur H6 dan I6 juga akan diisi secara dinamik.

Apakah yang berlaku jika anda mencari data yang tidak wujud? Dengan VLOOKUP, jika anda mencari Uruguay (dan ia tiada dalam senarai) dan anda tidak menggunakannya YA.RALAT, anda akan melihat ralat #N/A. Dengan XLOOKUP, anda memutuskan mesej yang muncul, nombor atau hasil yang anda mahukan.

Di luar carian tepat: mod padanan dan kegunaannya

• Untuk mencari nilai terkecil seterusnya, gunakan -1. Contohnya: Mencari 46.520 dalam jadual diskaun, jika ia tidak wujud, akan mengembalikan diskaun untuk jumlah terendah seterusnya.
• Untuk nilai yang lebih tinggi seterusnya, gunakan 1. Berguna jika anda sedang mencari julat progresif.
• Untuk menggunakan kad bebas, gunakan 2. Contohnya, cari “*selatan*” dan anda akan mendapat “Selatan”, “Tenggara”, “Utara-selatan”…

Bagaimanakah kad bebas berfungsi dalam XLOOKUP?

Asterisk (*) menggantikan sebarang bilangan aksara. Jika anda mencari “*timur”, anda akan melihat “Timur”, “Tenggara”, “Timur Laut”…

Tanda soal (?) menggantikan satu aksara sahaja. Adakah anda mempunyai soalan tentang "María" atau "Mario"? Cuba "María" dan kedua-duanya akan ditemui.

Tilde (~) mengatasi nilai kad bebas. Jika anda mencari "Bagaimana~?" tidak akan menemui anda "Bagaimana", tetapi hanya "Bagaimana?".

Apakah carian binari yang ditawarkan oleh XLOOKUP?

Apabila anda mempunyai senarai pesanan yang besar, carian binari membolehkan anda mencari data dengan cepat.. Bayangkan buku setebal 100 halaman: dengan perduaan anda membahagikannya kepada dua, pilih blok yang boleh dimasukkan dan kurangkan bilangan semakan. Sudah tentu, anda memerlukan julat untuk diisih dengan baik (menaik atau menurun bergantung pada mod yang dipilih), jika tidak, hasilnya tidak akan mempunyai kebolehpercayaan.

Bolehkah saya mencari berbilang jadual atau dengan lebih daripada satu kriteria pada satu masa?

XLOOKUP membolehkan anda mencari berbilang julat yang digabungkan sekali gus menggunakan sintaks yang sesuai. Sebagai contoh, jika anda mempunyai dua jadual negara, lookup_array boleh menjadi kedua-dua kawasan yang dipisahkan oleh titik bertindih, sama seperti return_array. Semua dalam formula yang sama dan tanpa usaha tambahan.

Bagaimana jika saya ingin merujuk silang berbilang kriteria? Di sinilah XLOOKUP bersarang masuk: anda boleh menggunakan satu carian awal untuk menentukan julat lajur dan satu lagi untuk baris, mengembalikan data tepat di persimpangan, seperti versi lanjutan INDEX + MATCH.

Berapa banyak yang boleh saya sesuaikan hasil XLOOKUP?

Kelebihan XLOOKUP yang kurang diketahui ialah anda boleh mengembalikan beberapa jenis hasil sekiranya berlaku ralat.. Anda boleh memaparkan teks tersuai, nombor atau biarkan sel kosong bergantung pada keperluan anda. Selain itu, apabila kami mengembalikan berbilang lajur, hasilnya menduduki sel bersebelahan, yang tidak boleh diedit secara individu daripada Excel, tetapi yang boleh diubah suai dengan memadamkan formula utama.