Cara Cari Data dengan VLOOKUP di Excel

01 dari 03

Cari Perlawanan Anggaran ke Data dengan VLOOKUP Excel

Cari Diskaun Harga dengan VLOOKUP. © Ted French

Bagaimana Fungsi VLOOKUP berfungsi

Fungsi VLOOKUP Excel, yang bermaksud pencarian menegak , boleh digunakan untuk mencari maklumat khusus yang terletak dalam jadual data atau pangkalan data.

VLOOKUP biasanya mengembalikan satu bidang data sebagai outputnya. Bagaimana ia berlaku:

  1. Anda memberikan nama atau lookup_value yang memberitahu VLOOKUP di mana baris atau rekod jadual data untuk mencari data yang dikehendaki
  2. Anda membekalkan nombor lajur - yang dikenali sebagai col_index_num - dari data yang anda cari
  3. Fungsi ini mencari lookup_value dalam lajur pertama jadual data
  4. VLOOKUP kemudian menempatkan dan mengembalikan maklumat yang anda cari dari medan lain yang sama dengan menggunakan nombor lajur yang dibekalkan

Menyusun Data Pertama

Walaupun tidak selalu diperlukan, biasanya adalah yang terbaik untuk menyusun rangkaian data yang pertama yang dicari oleh VLOOKUP dalam urutan menaik menggunakan lajur pertama julat untuk kunci jenis.

Jika data tidak disusun, VLOOKUP mungkin mengembalikan hasil yang salah.

Sintaks dan Argumen Fungsi VLOOKUP

Sintaks fungsi merujuk kepada susun atur fungsi dan termasuk nama, kurungan, dan argumen fungsi itu.

Sintaks untuk fungsi VLOOKUP ialah:

= VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)

lookup _value - (diperlukan) nilai untuk dicari - seperti kuantiti yang dijual dalam imej di atas

table_array - (diperlukan) ini adalah jadual data yang dicari VLOOKUP untuk mencari maklumat yang anda cari.

col_index_num - (diperlukan) nombor lajur nilai yang anda mahu dijumpai.

range_lookup - (pilihan) menunjukkan sama ada atau tidak julat disusun dalam urutan menaik.

Contoh: Cari Kadar Diskaun untuk Kuantiti yang Dibeli

Contoh dalam imej di atas menggunakan fungsi VLOOKUP untuk mencari kadar diskaun yang berbeza bergantung kepada kuantiti item yang dibeli.

Contohnya menunjukkan bahawa diskaun untuk pembelian 19 item ialah 2%. Ini kerana lajur Kuantiti mengandungi julat nilai. Akibatnya, VLOOKUP tidak dapat mencari padanan tepat. Sebaliknya, perlawanan anggaran mesti dijumpai untuk memulangkan kadar diskaun yang betul.

Untuk mencari padanan anggaran:

Contohnya, formula berikut yang mengandungi fungsi VLOOKUP digunakan untuk mencari diskaun untuk kuantiti barangan yang dibeli.

= VLOOKUP (C2, $ C $ 5: $ D $ 8.2, TRUE)

Walaupun rumus ini hanya boleh ditaip ke sel lembaran kerja, satu lagi pilihan, seperti yang digunakan dengan langkah-langkah yang disenaraikan di bawah, adalah menggunakan kotak dialog fungsi untuk memasukkan hujah-hujahnya.

Membuka Peti Dialog VLOOKUP

Langkah-langkah yang digunakan untuk memasuki fungsi VLOOKUP yang ditunjukkan dalam imej di atas ke dalam sel B2 adalah:

  1. Klik pada sel B2 untuk menjadikannya sel aktif - lokasi di mana hasil fungsi VLOOKUP dipaparkan
  2. Klik pada tab Formula .
  3. Pilih Lookup & Rujukan dari reben untuk membuka senarai drop-down fungsi
  4. Klik pada VLOOKUP dalam senarai untuk memaparkan kotak dialog fungsi

02 dari 03

Memasuki Argumen Fungsi VLOOKUP Excel

Memasuki Argumen ke dalam Peti Dialog VLOOKUP. © Ted French

Menunjuk kepada Rujukan Sel

Argumen untuk fungsi VLOOKUP dimasukkan ke dalam barisan berasingan kotak dialog seperti yang ditunjukkan dalam imej di atas.

Rujukan sel yang akan digunakan sebagai argumen boleh diketik ke dalam baris yang betul, atau, seperti yang dilakukan dalam langkah-langkah di bawah, menunjuk, yang melibatkan menyerlahkan rangkaian sel yang dikehendaki dengan penunjuk tetikus, boleh digunakan untuk memasukkannya ke dalam kotak dialog .

Kelebihan menggunakan penunjuk ialah:

Menggunakan Rujukan Sel Relatif dan Mutlak dengan Argumen

Ia tidak biasa menggunakan beberapa salinan VLOOKUP untuk mengembalikan maklumat yang berbeza dari jadual data yang sama. Untuk menjadikannya lebih mudah untuk melakukan ini, seringkali VLOOKUP boleh disalin dari satu sel ke yang lain. Apabila fungsi disalin ke sel lain, penjagaan mesti diambil untuk memastikan rujukan sel yang dihasilkan adalah betul dengan lokasi baru fungsi tersebut.

Dalam imej di atas, tanda dolar ( $ ) mengelilingi rujukan sel untuk argumen table_array yang menunjukkan bahawa ia adalah rujukan sel mutlak , yang bermaksud ia tidak akan berubah jika fungsi itu disalin ke sel lain. Ini adalah wajar kerana banyak salinan VLOOKUP akan merujuk jadual data yang sama sebagai sumber maklumat.

Rujukan sel yang digunakan untuk lookup_value, sebaliknya , tidak dikelilingi oleh tanda dolar, yang menjadikannya rujukan sel relatif. Rujukan sel relatif berubah apabila ia disalin untuk mencerminkan lokasi baru mereka berbanding kedudukan data yang mereka rujuk.

Memasuki Argumen Fungsi

  1. Klik pada garis Cari _value dalam kotak dialog VLOOKUP
  2. Klik pada sel C2 dalam lembaran kerja untuk memasukkan rujukan sel ini sebagai hujah carian_key
  3. Klik pada baris Table_array kotak dialog
  4. Sorot sel C5 hingga D8 dalam lembaran kerja untuk memasukkan julat ini sebagai argumen Table_array - tajuk jadual tidak dimasukkan
  5. Tekan kekunci F4 pada papan kekunci untuk menukar julat ke rujukan sel mutlak
  6. Klik pada baris Col_index_num kotak dialog
  7. Taipkan 2 pada baris ini sebagai hujah Col_index_num , kerana kadar diskaun terletak pada lajur 2 argumen Table_array
  8. Klik pada baris Range_lookup kotak dialog
  9. Taip perkataan Benar sebagai hujah Range_lookup
  10. Tekan kekunci Enter pada papan kekunci untuk menutup kotak dialog dan kembali ke lembaran kerja
  11. Jawapan 2% (kadar diskaun untuk kuantiti yang dibeli) sepatutnya muncul di sel D2 lembaran kerja
  12. Apabila anda mengklik pada sel D2, fungsi lengkap = VLOOKUP (C2, $ C $ 5: $ D $ 8.2, TRUE) muncul di bar formula di atas lembaran kerja

Kenapa VLOOKUP Kembali 2% sebagai Keputusan

03 dari 03

Excel VLOOKUP Tidak Bekerja: # N / A dan #REF Kesalahan

VLOOKUP Mengembalikan #REF! Mesej Ralat. © Ted French

Mesej Gangguan VLOOKUP

Mesej ralat berikut dikaitkan dengan VLOOKUP.

A # N / A ("nilai tidak tersedia") Ralat Dipaparkan Jika:

A #REF! ("rujukan daripada rentang") Ralat Dipaparkan Jika: