Cara Menggunakan VLOOKUP dalam Excel

VLOOKUP adalah salah satu fungsi Excel yang paling berguna, dan ia juga merupakan salah satu fungsi yang paling kurang difahami. Dalam artikel ini, kami mengungkap VLOOKUP sebagai contoh kehidupan nyata. Kami akan membuat Templat Invois yang boleh digunakan untuk syarikat rekaan.

VLOOKUP adalah fungsi Excel . Artikel ini akan mengandaikan bahawa pembaca sudah memahami tentang fungsi Excel, dan dapat menggunakan fungsi asas seperti SUM, AVERAGE, dan HARI INI. Dalam penggunaannya yang paling umum, VLOOKUP adalah fungsi pangkalan data , yang bermaksud ia berfungsi dengan jadual pangkalan data - atau lebih ringkas lagi, senarai perkara dalam lembaran kerja Excel. Perkara macam apa? Well, apa-apa perkara seperti. Anda mungkin mempunyai lembaran kerja yang mengandungi senarai pekerja, atau produk, atau pelanggan, atau CD dalam koleksi CD anda, atau bintang di langit malam. Tidak begitu penting.

Berikut adalah contoh senarai, atau pangkalan data. Dalam kes ini, senarai produk yang dijual oleh syarikat rekaan kami:

Biasanya senarai seperti ini mempunyai semacam pengecam unik untuk setiap item dalam senarai. Dalam kes ini, pengecam unik ada di lajur "Item Code". Catatan: Agar fungsi VLOOKUP berfungsi dengan pangkalan data / senarai, senarai itu mesti mempunyai lajur yang mengandungi pengecam unik (atau "kunci", atau "ID"), dan lajur itu mestilah lajur pertama dalam jadual . Contoh pangkalan data kami di atas memenuhi kriteria ini.

Bahagian paling sukar dalam menggunakan VLOOKUP adalah memahami apa sebenarnya. Oleh itu, mari kita lihat apakah kita dapat menerangkannya terlebih dahulu:

VLOOKUP mengambil maklumat dari pangkalan data / senarai berdasarkan contoh pengenal unik yang disediakan.

Dalam contoh di atas, anda akan memasukkan fungsi VLOOKUP ke dalam spreadsheet lain dengan kod item, dan ia akan mengembalikan kepada anda sama ada keterangan item yang sesuai, harganya, atau ketersediaannya (kuantiti "Dalam stok") seperti yang dijelaskan dalam asal anda senarai. Manakah antara maklumat berikut yang akan anda sampaikan? Anda boleh memutuskan ini semasa anda membuat formula.

Sekiranya semua yang anda perlukan adalah satu maklumat dari pangkalan data, akan menjadi banyak masalah untuk membina formula dengan fungsi VLOOKUP di dalamnya. Biasanya anda akan menggunakan fungsi seperti ini dalam spreadsheet yang boleh digunakan semula, seperti templat. Setiap kali seseorang memasukkan kod item yang sah, sistem akan mengambil semua maklumat yang diperlukan mengenai item yang sesuai.

Mari kita buat contoh ini: Templat Invois yang boleh kita gunakan berulang kali di syarikat rekaan kita.

Mula-mula kita mulakan Excel, dan kita buat invois kosong sendiri:

Beginilah cara kerjanya: Orang yang menggunakan templat invois akan mengisi serangkaian kod item di lajur "A", dan sistem akan mengambil keterangan dan harga setiap item dari pangkalan data produk kami. Maklumat itu akan digunakan untuk mengira jumlah baris untuk setiap item (dengan asumsi kami memasukkan kuantiti yang sah).

Untuk tujuan menjadikan contoh ini mudah, kami akan mencari pangkalan data produk pada helaian berasingan dalam buku kerja yang sama:

Pada hakikatnya, kemungkinan besar pangkalan data produk terletak di buku kerja yang berasingan. Tidak banyak perbezaan dengan fungsi VLOOKUP, yang tidak begitu peduli jika pangkalan data terletak pada helaian yang sama, lembaran yang berbeza, atau buku kerja yang sama sekali berbeza.

Oleh itu, kami telah membuat pangkalan data produk kami, yang kelihatan seperti ini:

Untuk menguji formula VLOOKUP yang akan kami tulis, pertama-tama kami memasukkan kod item yang sah ke dalam sel A11 invois kosong kami:

Seterusnya, kita memindahkan sel aktif ke sel di mana kita mahu maklumat yang diambil dari pangkalan data oleh VLOOKUP disimpan. Menariknya, ini adalah langkah yang salah orang. Untuk menjelaskan lebih lanjut: Kami akan membuat formula VLOOKUP yang akan mengambil keterangan yang sesuai dengan kod item dalam sel A11. Di manakah kita mahu penerangan ini diletakkan semasa kita mendapatkannya? Dalam sel B11, tentu saja. Jadi di situlah kami menulis formula VLOOKUP: di sel B11. Pilih sel B11 sekarang.

Kita perlu mencari senarai semua fungsi yang ada yang ditawarkan Excel, supaya kita dapat memilih VLOOKUP dan mendapatkan bantuan dalam menyelesaikan formula. Ini dijumpai dengan mengklik tab Rumus terlebih dahulu , dan kemudian mengklik Masukkan Fungsi :

Kotak muncul yang membolehkan kita memilih salah satu fungsi yang tersedia di Excel.

Untuk mencari yang kita cari, kita dapat menaip istilah pencarian seperti "lookup" (kerana fungsi yang kita minati adalah fungsi pencarian ). Sistem akan mengembalikan senarai semua fungsi yang berkaitan dengan carian di Excel.  VLOOKUP adalah yang kedua dalam senarai. Pilihnya klik OK .

The Hujah Fungsi kotak muncul, mendorong kami untuk semua hujah-hujah (atau parameter ) yang diperlukan untuk melengkapkan fungsi VLOOKUP ini. Anda boleh menganggap kotak ini sebagai fungsi yang menanyakan soalan berikut kepada kami:

  1. Apakah pengecam unik yang anda cari dalam pangkalan data?
  2. Di manakah pangkalan data?
  3. Maklumat manakah dari pangkalan data, yang dikaitkan dengan pengecam unik, yang ingin anda dapatkan semula?

Tiga argumen pertama ditunjukkan dengan huruf tebal , menunjukkan bahawa mereka adalah argumen wajib (fungsi VLOOKUP tidak lengkap tanpa mereka dan tidak akan mengembalikan nilai yang sah). Hujah keempat tidak berani, yang bermaksud bahawa ia adalah pilihan:

Kami akan menyelesaikan hujah dengan teratur, dari atas ke bawah.

Argumen pertama yang perlu kita lengkapkan adalah argumen Lookup_value . Fungsi memerlukan kita untuk memberitahu di mana untuk mencari pengecam unik ( kod item dalam kes ini) bahawa ia harus mengembalikan keterangan. Kita mesti memilih kod item yang kita masukkan lebih awal (di A11).

Klik pada ikon pemilih di sebelah kanan argumen pertama:

Kemudian klik sekali pada sel yang mengandungi kod item (A11), dan tekan Enter :

Nilai "A11" dimasukkan ke dalam argumen pertama.

Sekarang kita perlu memasukkan nilai untuk argumen Table_array . Dengan kata lain, kita perlu memberitahu VLOOKUP di mana untuk mencari pangkalan data / senarai. Klik pada ikon pemilih di sebelah argumen kedua:

Sekarang cari pangkalan data / senarai dan pilih keseluruhan senarai - tidak termasuk baris tajuk. Dalam contoh kami, pangkalan data terletak pada lembaran kerja yang berasingan, jadi kami pertama kali mengklik tab lembaran kerja itu:

Seterusnya kami memilih keseluruhan pangkalan data, tidak termasuk baris tajuk:

... dan tekan Enter . Julat sel yang mewakili pangkalan data (dalam hal ini "'Pangkalan Data Produk'! A2: D7") dimasukkan secara automatik untuk kami ke dalam argumen kedua.

Sekarang kita perlu memasukkan argumen ketiga, Col_index_num . Kami menggunakan hujah ini untuk menentukan kepada VLOOKUP maklumat mana dari pangkalan data, kaitkan dengan kod item kami di A11, kami ingin kembali kepada kami. Dalam contoh khusus ini, kami ingin agar keterangan item dikembalikan kepada kami. Sekiranya anda melihat lembaran kerja pangkalan data, anda akan melihat bahawa lajur "Penerangan" adalah lajur kedua dalam pangkalan data. Ini bermaksud bahawa kita mesti memasukkan nilai “2” ke dalam kotak Col_index_num :

Penting untuk diperhatikan bahawa kita tidak memasukkan "2" di sini kerana lajur "Penerangan" ada di lajur B pada lembaran kerja tersebut. Sekiranya pangkalan data bermula di lajur K lembar kerja, kita masih akan memasukkan "2" di bidang ini kerana lajur "Keterangan" adalah lajur kedua dalam set sel yang kita pilih ketika menentukan "Table_array".

Akhirnya, kita perlu memutuskan sama ada memasukkan nilai ke dalam argumen VLOOKUP terakhir, Range_lookup . Hujah ini memerlukan sama ada nilai benar atau salah , atau harus dibiarkan kosong. Semasa menggunakan VLOOKUP dengan pangkalan data (seperti yang berlaku 90% dari masa itu), cara untuk memutuskan apa yang harus dimasukkan dalam argumen ini dapat difikirkan seperti berikut:

Sekiranya lajur pertama pangkalan data (lajur yang mengandungi pengenal unik) disusun mengikut abjad / numerik dalam urutan menaik, maka mungkin memasukkan nilai benar ke dalam argumen ini, atau membiarkannya kosong.

Sekiranya lajur pertama pangkalan data tidak disusun, atau disusun mengikut urutan menurun, maka anda mesti memasukkan nilai palsu ke dalam argumen ini

Oleh kerana lajur pertama pangkalan data kami tidak disusun, kami memasukkan palsu ke dalam argumen ini:

Itu sahaja! Kami telah memasukkan semua maklumat yang diperlukan untuk VLOOKUP untuk mengembalikan nilai yang kami perlukan. Klik butang OK dan perhatikan bahawa keterangan yang sesuai dengan kod item "R99245" telah dimasukkan dengan betul ke dalam sel B11:

Formula yang dibuat untuk kita kelihatan seperti ini:

Sekiranya kita memasukkan kod item yang berbeza ke dalam sel A11, kita akan mula melihat kehebatan fungsi VLOOKUP: Sel keterangan berubah agar sepadan dengan kod item baru:

Kita boleh melakukan set yang sama langkah-langkah untuk mendapatkan item harga kembali ke dalam sel E11. Perhatikan bahawa formula baru mesti dibuat di sel E11. Hasilnya akan kelihatan seperti ini:

... dan formula akan kelihatan seperti ini:

Perhatikan bahawa satu-satunya perbezaan antara kedua formula adalah argumen ketiga ( Col_index_num ) telah berubah dari "2" menjadi "3" (kerana kami ingin data diambil dari lajur ke-3 dalam pangkalan data).

Sekiranya kami memutuskan untuk membeli 2 item ini, kami akan memasukkan "2" ke dalam sel D11. Kami kemudian memasukkan formula mudah ke dalam sel F11 untuk mendapatkan jumlah garis:

= D11 * E1

... yang kelihatan seperti ini ...

Melengkapkan Templat Invois

Kami telah banyak belajar mengenai VLOOKUP setakat ini. Sebenarnya, kami telah mempelajari semua yang akan kami pelajari dalam artikel ini. Penting untuk diperhatikan bahawa VLOOKUP dapat digunakan dalam keadaan lain selain pangkalan data. Perkara ini kurang biasa, dan mungkin akan dibahas dalam artikel How-To Geek di masa depan.

Templat invois kami belum lengkap. Untuk menyelesaikannya, kami melakukan perkara berikut:

  1. Kami akan membuang contoh kod item dari sel A11 dan "2" dari sel D11. Ini akan menyebabkan formula VLOOKUP yang baru dibuat untuk memaparkan mesej ralat:



    Kami dapat menyelesaikannya dengan menggunakan fungsi IF () dan ISBLANK () Excel . Kami mengubah formula kami dari ini…      = VLOOKUP (A11, 'Pangkalan Data Produk'! A2: D7,2, SALAH) ... menjadi ini ... = JIKA (ISBLANK (A11), "", VLOOKUP (A11, 'Pangkalan Data Produk'! A2 : D7,2, SALAH))


  2. Kami akan menyalin formula dalam sel B11, E11 dan F11 ke baris item invois yang selebihnya. Perhatikan bahawa jika kita melakukan ini, formula yang dihasilkan tidak lagi merujuk kepada jadual pangkalan data dengan betul. Kami dapat memperbaikinya dengan mengubah rujukan sel untuk pangkalan data menjadi rujukan sel mutlak . Sebagai alternatif - dan lebih baik lagi - kita dapat membuat nama julat untuk keseluruhan pangkalan data produk (seperti "Produk"), dan menggunakan nama julat ini dan bukannya rujukan sel. Rumus akan berubah dari ini…      = JIKA (ISBLANK (A11), ””, VLOOKUP (A11, ‘Pangkalan Data Produk’! A2: D7,2, SALAH)) … menjadi ini…       = JIKA (ISBLANK (A11), ”” , VLOOKUP (A11, Produk, 2, SALAH)) … dan kemudian salin formula ke baris item invois yang selebihnya.
  3. Kami mungkin akan "lock" sel-sel yang mengandungi formula kami (atau sebaliknya membuka kunci yang lain sel-sel), dan kemudian melindungi lembaran kerja, untuk memastikan bahawa formula kami dibina dengan teliti tidak sengaja ditulis ganti apabila seseorang datang untuk mengisi dalam invois.
  4. Kami akan menyimpan fail tersebut sebagai templat , sehingga dapat digunakan kembali oleh semua orang di syarikat kami

Sekiranya kami merasa benar-benar pintar, kami akan membuat pangkalan data semua pelanggan kami di lembaran kerja lain, dan kemudian menggunakan ID pelanggan yang dimasukkan di sel F5 untuk secara automatik mengisi nama dan alamat pelanggan di sel B6, B7 dan B8.

Sekiranya anda ingin berlatih dengan VLOOKUP, atau hanya melihat Templat Invois yang dihasilkan, ia boleh dimuat turun dari sini.