Penggunaan fungsi VLOOKUP dan contoh khusus

Fungsi VLOOKUP dalam Excel amat berguna dalam mencari data. Inilah yang anda perlu tahu tentang menggunakan fungsi VLOOKUP dalam Excel .

Anda ingin menyemak nilai tertentu dalam hamparan Excel dan mencari maklumat mengenainya. Menjalankan VLOOKUP menjimatkan usaha anda dalam kes ini. Ia adalah salah satu cara terbaik untuk melaksanakan pertanyaan menegak dalam Microsoft Excel. Artikel tersebut akan memberitahu anda maksud, penggunaan dan contoh ilustrasi fungsi VLOOKUP dalam Microsoft Excel.

• Ini adalah fungsi paling asas dalam Excel yang perlu anda fahami

Cara menggunakan fungsi VLOOKUP

• Apakah fungsi VLOOKUP?
• Formula fungsi VLOOKUP dalam Excel 2016, 2013, 2010, 2007, 2003
• Tutorial video tentang menggunakan fungsi VLOOKUP
• Contoh 1: Gunakan fungsi VLOOKUP untuk menilai penilaian pelajar dan kakitangan:
• Contoh 2: Gunakan VLOOKUP untuk pengesanan mutlak untuk mendapatkan data
• Contoh 3: Gunakan VLOOKUP untuk mengekstrak data
• Contoh 4: Gunakan fungsi VLOOKUP pada 2 helaian Excel yang berbeza
• Untuk apa menggunakan fungsi VLOOKUP?
• Perkara yang perlu diingat tentang fungsi VLOOKUP

Apakah fungsi VLOOKUP?

Fungsi VLOOKUP ialah fungsi carian data dalam Excel. Saya menganggap bahawa anda mempunyai latar belakang dalam Excel dan boleh menggunakan fungsi asas seperti SUM, AVERAGE dan TODAY. Salah satu fungsi VLOOKUP yang paling biasa ialah fungsi data, bermakna ia beroperasi pada jadual pangkalan data atau, lebih ringkas, senarai item. Senarai itu mempunyai banyak variasi. Anda boleh membuat jadual tentang kakitangan syarikat anda, kategori produk, senarai pelanggan atau apa-apa lagi. Dan di bawah ialah senarai produk yang dijual oleh syarikat A di pasaran:

Jadual pangkalan data selalunya mempunyai pengecam untuk setiap produk. Dalam jadual data di atas, tanda pengenalan khas ialah lajur "Kod Item". Nota: Untuk dapat menggunakan fungsi VLOOKUP, jadual data mesti mempunyai lajur yang mengandungi tanda pengenalan seperti kod atau ID dan mestilah lajur pertama seperti jadual di atas.

VLOOKUP mungkin merupakan fungsi yang paling terkenal dalam Excel, tetapi atas sebab baik dan buruk. Secara terang-terangan, fungsi VLOOKUP mudah digunakan dan melakukan sesuatu yang sangat berguna. Bagi pengguna baharu terutamanya, menonton fungsi VLOOKUP mengimbas jadual, mencari padanan dan mengembalikan hasil yang tepat adalah sangat mengujakan. Penggunaan fungsi VLOOKUP secara cekap adalah kemahiran penting, daripada pemula hingga pengguna Excel mahir.

Di sisi negatif, fungsi VLOOKUP adalah terhad dan mempunyai nilai lalai yang berbahaya. Tidak seperti INDEX dan MATCH (atau XLOOKUP), fungsi VLOOKUP memerlukan jadual lengkap dengan nilai carian dalam lajur pertama. Ini menjadikan penggunaan fungsi VLOOKUP dengan pelbagai kriteria sukar. Selain itu, tingkah laku padanan lalai VLOOKUP memudahkan untuk mendapatkan hasil yang salah. Tapi jangan risau! Kunci untuk berjaya menggunakan fungsi VLOOKUP ialah menguasai asas.

VLOOKUP mempunyai 4 argumen: lookup_value, table_array, column_index_num dan range_lookup. Lookup_value ialah nilai yang perlu dicari dan table_array ialah julat data menegak untuk dicari di dalamnya. Lajur pertama table_array mesti mengandungi nilai carian untuk dicari. Argumen column_index_num ialah nombor lajur bagi nilai untuk diambil, di mana lajur pertama table_array ialah lajur 1.

Akhir sekali, range_lookup mengawal tingkah laku mencari padanan. Jika range_lookup adalah BENAR, fungsi VLOOKUP melakukan padanan anggaran. Jika range_lookup adalah FALSE, fungsi VLOOKUP akan melakukan padanan tepat. Yang penting, range_lookup adalah pilihan dan lalai kepada TRUE, jadi VLOOKUP akan melakukan padanan anggaran secara lalai.

Formula fungsi VLOOKUP dalam Excel

Fungsi VLOOKUP dalam Excel mempunyai formula berikut:

=VLOOKUP(lookup_value,table_array,col_index_num ,[julat_lookup] )

Di sana:

• VLOOKUP: Adakah nama fungsi
• Parameter dalam huruf tebal diperlukan.
• lookup_value: Nilai yang digunakan untuk carian
• table_array: Jadual yang mengandungi nilai yang hendak dicari, masukkan nilai mutlak dengan tanda $ di hadapan, contohnya: $A$3:$E$40.
• col_index_num: Susunan lajur yang mengandungi nilai carian pada table_array. Contohnya, dalam jadual $A$3:$E$40, lajur B mengandungi nilai yang hendak dicari, maka kol_index_num di sini ialah 2; jadual $C$3:$F$40, lajur E mengandungi nilai carian, kemudian col_index_num di sini ialah 3.
• range_lookup: Adakah julat carian, TRUE bersamaan dengan 1 (pencarian relatif), FALSE bersamaan dengan 0 (pencarian mutlak). Parameter ini tidak selalu diperlukan dalam formula.

Carian relatif hanya boleh digunakan apabila nilai untuk dicari dalam table_array telah disusun mengikut tertib (menaik atau menurun atau mengikut abjad). Dengan jadual sedemikian, anda boleh menggunakan carian relatif, yang serupa dengan menggunakan fungsi IF tak terhingga . Untuk nilai yang tidak boleh dicari atau tidak diisih, gunakan carian mutlak untuk mencari nilai yang tepat.

Tutorial video tentang menggunakan fungsi VLOOKUP

Contoh 1: VLOOKUP berfungsi untuk menilai penilaian pelajar dan kakitangan

Katakan, anda mempunyai transkrip pelajar seperti berikut:

Dan jadual peraturan kedudukan adalah seperti berikut:

Sekarang kita akan menggunakan fungsi VLOOKUP untuk memasukkan rating untuk pelajar. Anda akan melihat bahawa jadual kedudukan telah disusun mengikut urutan dari rendah ke tinggi (dari lemah kepada baik), jadi dalam kes ini kita boleh menggunakan carian relatif.

Dalam Excel 2 jadual ini dibentangkan seperti berikut:

Di sini, nilai yang akan dikesan adalah dalam lajur C, bermula dari baris 6. Julat carian ialah $A$18:$B$21, susunan lajur yang mengandungi nilai yang dikesan ialah 2.

Dalam sel D6, masukkan formula: =VLOOKUP($C6,$A$18:$B$21,2,1). Ini ialah formula carian relatif, anda boleh melakukan carian mutlak jika anda mahu (atau kerana kedudukan tidak diisih) dengan menambah 0 pada formula seperti ini: =VLOOKUP($C6 ,$A$18:$B$21,2, 0). Tekan enter.

Klik pada sel D6, petak kecil muncul di sudut kanan bawah, klik padanya dan seret ke bawah jadual untuk menyalin formula penggredan bagi pelajar yang tinggal.

Pada masa itu, kami mempunyai keputusan menggunakan fungsi VLOOKUP untuk mengklasifikasikan prestasi akademik pelajar seperti berikut:

Adakah anda tertanya-tanya mengapa $ mesti digunakan sebelum C6? $ akan memastikan lajur C tetap, hanya menukar baris apabila anda menyeret formula ke bawah jadual. Terdapat juga $A$18:$B$21 untuk membantu membetulkan jadual peraturan kedudukan, menjadikannya tidak berubah apabila anda menyeret formula.

Contoh 2: Fungsi VLOOKUP mencari secara mutlak untuk mendapatkan data

Katakan, anda mempunyai jadual data pekerja, menyimpan kod pekerja, nama penuh dan jawatan. Meja lain menyimpan kod pekerja, kampung halaman dan tahap pendidikan. Sekarang anda ingin mengisi maklumat kampung halaman dan tahap pendidikan untuk setiap pekerja, apakah yang perlu anda lakukan?

Katakan anda mempunyai jadual pekerja dan kampung halaman pekerja seperti berikut:

Sekarang anda ingin mengisi maklumat kampung halaman untuk pekerja. Dalam sel D4, masukkan formula carian mutlak seperti berikut: =Vlookup($A4,$A$16:$C$25,2,0)

Kemudian tekan Enter. Klik segi empat sama kecil yang muncul di penjuru sel D4 dan seret ke bawah merentasi jadual untuk menyalin formula kepada pekerja lain.

Untuk mengisi maklumat kelayakan pekerja, dalam sel E4, masukkan formula carian mutlak: =VLOOKUP($A4,$A$16:$C$25,3,0)

Anda terus menekan Enter dan tatal ke bawah untuk menyalin formula kepada pekerja yang tinggal, kami akan mendapat keputusan berikut:

Contoh 3: Gunakan VLOOKUP untuk mengekstrak data

Meneruskan set data contoh 2, kini kita akan mencari kampung halaman 3 pekerja: Nguyen Hoang Anh, Tran Van Anh dan Nguyen Quang Vinh. Saya telah mengekstraknya ke dalam jadual baharu F15:G18.

Saya akan melakukan carian ini pada jadual A3:E13, selepas mengisi kampung halaman dan kelayakan saya. Sekarang, masukkan formula carian mutlak berikut ke dalam sel G16: =VLOOKUP($F16,$B$3:$E$13,3,0) > tekan Enter.

Menyalin formula untuk baki 2 pekerja kami mendapat hasil berikut:

Ambil perhatian bahawa dalam contoh ini, nilai pengesanan adalah dalam lajur B, jadi kawasan pengesanan dikira dari lajur B dan bukan dari lajur A.

Contoh 4: Gunakan fungsi VLOOKUP pada 2 helaian Excel yang berbeza

Berbalik kepada set data dalam contoh 2, selepas kelayakan pekerja dan kampung halaman diisi, kami namakan helaian QTM.

Dalam helaian hamparan lain, bernama QTM1, anda perlu mendapatkan maklumat tentang kelayakan dan kedudukan pekerja dengan perubahan susunan pekerja. Ini adalah apabila anda melihat kuasa sebenar fungsi VLOOKUP.

Untuk mencari data tentang "Kelayakan" pekerja, masukkan formula: =VLOOKUP($B4,QTM!$B$3:$E$13,4,0) dalam sel C4 helaian QTM1.

Di sana:

• B4 ialah lajur yang mengandungi nilai yang digunakan untuk pengesanan.
• QTM! ialah nama helaian yang mengandungi jadual dengan nilai yang akan dikesan. Selepas nama helaian, tambahkan !
• $B$3:$E$13 ialah jadual yang mengandungi nilai carian dan helaian yang mengandungi jadual (QTM).
• 4 ialah nombor siri lajur "Kelayakan", dikira daripada lajur "Nama penuh" pada helaian QTM.
• 0 ialah pengesanan mutlak.

Tekan Enter, kemudian salin formula untuk semua pekerja yang tinggal dalam jadual, kami mendapat hasil berikut:

Untuk mencari data "Kedudukan" pekerja, dalam sel D4 helaian QTM1, masukkan formula: =VLOOKUP($B4,QTM!$B$3:$E$13,2,0), tekan Enter.

Menyalin formula untuk pekerja yang tinggal, kami mendapat yang berikut:

Untuk apa menggunakan fungsi VLOOKUP?

Mula-mula kita perlu mencari jawapan yang tepat kepada soalan "untuk apa kita menggunakan fungsi VLOOKUP?".

Fungsi VLOOKUP digunakan untuk menyokong mencari maklumat dalam medan data atau senarai berdasarkan pengecam yang tersedia.

Contohnya, memasukkan fungsi VLOOKUP dengan kod produk ke dalam hamparan lain akan memaparkan maklumat produk yang sepadan dengan kod tersebut. Maklumat itu boleh menjadi penerangan, harga, kuantiti inventori, bergantung pada kandungan resipi yang anda tulis.

Semakin kecil jumlah maklumat yang anda perlukan untuk mencari, semakin sukar untuk menulis fungsi VLOOKUP. Biasanya anda akan menggunakan fungsi ini dalam lembaran kerja boleh guna semula sebagai templat. Setiap kali kod produk yang sesuai dimasukkan, sistem akan mendapatkan semula semua maklumat yang diperlukan tentang produk yang sepadan.

Perkara yang perlu diingat tentang fungsi VLOOKUP

Berikut ialah senarai perkara penting yang perlu diingat tentang fungsi VLOOKUP dalam Excel:

• Apabila range_lookup diabaikan, VLOOKUP akan membenarkan padanan tidak tepat, tetapi masih akan menggunakan padanan tepat jika tersedia.
• Had terbesar fungsi ialah ia sentiasa mengambil nilai di sebelah kanan. Fungsi ini akan mendapatkan semula data dari lajur di sebelah kanan lajur pertama dalam jadual.
• Jika lajur carian mengandungi nilai pendua, fungsi VLOOKUP akan sepadan dengan nilai pertama sahaja.
• Fungsi ini tidak sensitif huruf besar-besaran.
• Katakan terdapat formula VLOOKUP sedia ada dalam lembaran kerja. Dalam kes itu, formula mungkin pecah jika anda memasukkan lajur ke dalam jadual, kerana nilai indeks lajur berkod keras, yang tidak berubah secara automatik apabila lajur dimasukkan atau dipadamkan.
• VLOOKUP membenarkan penggunaan aksara kad bebas, contohnya, asterisk (*) atau tanda tanya (?).
• Katakan dalam jadual anda bekerja dengan fungsi yang mengandungi nombor yang dimasukkan sebagai teks. Jika anda hanya mendapat nombor sebagai teks daripada lajur dalam jadual, itu tidak penting. Tetapi jika lajur pertama jadual mengandungi nombor yang dimasukkan sebagai teks, #N/A! akan dipaparkan jika nilai carian juga bukan teks.
• Ralat #N/A! Berlaku jika fungsi VLOOKUP tidak menemui padanan untuk lookup_value yang disediakan.
• Ralat #REF! berlaku jika:
  • Argumen col_index_num lebih besar daripada bilangan lajur dalam table_array yang dibekalkan
  • Formula cuba merujuk sel yang tidak wujud.
• Ralat #VALUE! berlaku jika:
  • Argumen col_index_num kurang daripada 1 atau tidak dikenali sebagai nilai angka
  • Argumen range_lookup tidak diiktiraf sebagai salah satu nilai logik TRUE atau FALSE.

Perbezaan utama antara fungsi VLOOKUP dan XLOOKUP dalam Excel

Y - Ya

N - Tidak

Lihat lagi:

• Cara menggunakan fungsi IF dalam Excel
• Cara menggunakan fungsi AVERAGEIF dalam Excel

Selepas mengetahui cara menggunakan VLookup, anda juga perlu mengetahui ralat biasa dan cara menyelesaikan masalah.

Ralat biasa apabila menggunakan fungsi VLOOKUP dalam Excel

Nilai carian berada dalam lajur yang salah

Salah satu ralat paling biasa yang anda akan hadapi apabila menggunakan formula VLOOKUP ialah fungsi tersebut hanya mengembalikan nilai #N/A. Ralat ini berlaku apabila ia tidak dapat mencari nilai carian yang anda minta VLOOKUP cari.

Dalam sesetengah kes, anda boleh menggunakan fungsi XLOOKUP dan bukannya VLOOKUP, namun, ralat ini hanya boleh diperbetulkan dengan mudah dalam fungsi VLOOKUP.

Bagaimana untuk menyelesaikan masalah:

Sebab mengapa VLOOKUP tidak menjumpai nilai yang dikehendaki mungkin kerana nilai tersebut tiada dalam jadual. Walau bagaimanapun, jika ia mengembalikan semua nilai #N/A, nilai carian ialah teks, maka kemungkinan besar ia hanya boleh membaca dari kiri ke kanan.

Apabila membuat jadual dalam Excel untuk VLOOKUP, pastikan anda meletakkan nilai carian di sebelah kiri nilai yang anda ingin pulangkan. Cara paling mudah untuk melakukan ini ialah meletakkannya di lajur pertama dalam jadual.

Sekarang formula akan mengembalikan nilai:

Secara keseluruhannya, VLOOKUP ialah cara terbaik untuk membuat pertanyaan data dengan lebih pantas dalam Microsoft Excel. Walaupun pertanyaan VLOOKUP dijalankan secara menegak pada lajur dan mempunyai beberapa had lain, Microsoft sentiasa mengemas kini ciri carian Excel untuk mengembangkan kebolehgunaannya. Contohnya, HLOOKUP, XLOOKUP... boleh membantu anda mencari data dalam pelbagai arah. LuckyTemplates.com akan terus memberikan anda maklumat tentang cara menggunakan fungsi ini dalam artikel berikut.