VLOOKUP

Menggunakan VLOOKUP di Excel

VLOOKUP adalah salah satu fungsi Excel yang paling berguna, dan itu juga salah satu yang paling sedikit dipahami. Pada artikel ini, kita demystify VLOOKUP dengan cara contoh kehidupan nyata. Kami akan membuat Faktur Template digunakan untuk sebuah perusahaan fiktif.
Jadi apa VLOOKUP? Yah, tentu saja itu fungsi Excel. Artikel ini akan mengasumsikan bahwa pembaca sudah memiliki pemahaman yang lewat fungsi Excel, dan dapat menggunakan fungsi dasar seperti SUM, AVERAGE, dan TODAY. Dalam penggunaan yang paling umum, VLOOKUP adalah fungsi database, yang berarti bahwa ia bekerja dengan tabel database - atau lebih sederhana, daftar hal-hal dalam lembar kerja Excel. Hal apa saja? Nah, apapun hal. Anda mungkin memiliki worksheet yang berisi daftar karyawan, atau produk, atau pelanggan, atau CD dalam koleksi CD Anda, atau bintang-bintang di langit malam. Itu tidak terlalu penting.
Berikut ini adalah contoh dari daftar, atau database. Dalam hal ini adalah daftar produk yang perusahaan fiktif kami menjual:

Biasanya daftar seperti ini memiliki semacam pengenal unik untuk setiap item dalam daftar. Dalam kasus ini, pengenal unik ada di kolom "Item Code". Catatan: Untuk fungsi VLOOKUP untuk bekerja dengan database / list, daftar yang harus memiliki kolom yang berisi pengenal unik (atau "kunci", atau "ID"), dan kolom yang harus kolom pertama dalam tabel. Sampel Database kami atas memenuhi kriteria ini.
Bagian tersulit dari menggunakan VLOOKUP adalah memahami apa itu untuk. Jadi mari kita lihat apakah kita bisa mendapatkan yang jelas pertama:

VLOOKUP mengambil informasi dari daftar database / berdasarkan contoh yang diberikan dari identifier yang unik.

Dengan kata lain, jika Anda menempatkan fungsi VLOOKUP ke dalam sel dan menyebarkannya salah satu pengidentifikasi unik dari database Anda, itu akan kembali Anda salah satu dari potongan-potongan informasi yang terkait dengan identifier unik. Dalam contoh di atas, Anda akan melewati VLOOKUP kode item, dan itu akan kembali kepada Anda baik deskripsi item yang sesuai, harga, atau ketersediaan (nya "Stok" kuantitas). Manakah dari potongan-potongan informasi itu akan melewati Anda kembali? Nah, Anda bisa memutuskan ini ketika Anda sedang menciptakan formula.
Jika semua yang Anda butuhkan adalah salah satu bagian dari informasi dari database, akan banyak kesulitan untuk pergi ke untuk membangun formula dengan fungsi VLOOKUP di dalamnya. Biasanya Anda akan menggunakan fungsi semacam ini dalam spreadsheet dapat digunakan kembali, seperti template. Setiap kali seseorang memasuki kode item yang valid, sistem akan mengambil semua informasi yang diperlukan tentang item yang sesuai.
Mari kita membuat contoh ini: Sebuah Template Faktur bahwa kita dapat menggunakan kembali berulang di perusahaan fiktif kami.
Pertama kita mulai Excel ...

... Dan kita menciptakan diri kita faktur kosong:

Ini adalah bagaimana itu akan bekerja: Orang menggunakan template faktur akan mengisi serangkaian kode item dalam kolom "A", dan sistem akan mengambil deskripsi setiap item dan harga, yang akan digunakan untuk menghitung total baris untuk masing-masing item (dengan asumsi kita memasuki jumlah valid).
Untuk tujuan menjaga contoh sederhana ini, kita akan menemukan database produk pada lembar terpisah dalam buku kerja yang sama:

Pada kenyataannya, itu lebih mungkin bahwa database produk akan berada dalam sebuah kerja yang terpisah. Itu membuat sedikit perbedaan dengan fungsi VLOOKUP, yang tidak benar-benar peduli jika database terletak pada lembar yang sama, lembaran yang berbeda, atau buku kerja yang sama sekali berbeda.

Untuk menguji rumus VLOOKUP kita akan menulis, pertama-tama kita masukkan kode item yang valid ke dalam sel A11:

Selanjutnya, kami memindahkan sel yang aktif ke sel di mana kita ingin informasi yang diambil dari database dengan VLOOKUP untuk disimpan. Menariknya, ini adalah langkah yang kebanyakan orang bisa salah. Untuk menjelaskan lebih lanjut: Kami akan membuat rumus VLOOKUP yang akan mengambil deskripsi yang sesuai dengan kode item dalam sel A11. Kita ingin penjelasan ini menempatkan ketika kita mendapatkannya? Dalam sel B11, tentu saja. Jadi, di sanalah kita menulis rumus VLOOKUP - dalam sel B11.
Pilih cell B11:

Kita perlu untuk menemukan daftar semua fungsi yang tersedia yang ditawarkan Excel, sehingga kita bisa memilih VLOOKUP dan mendapatkan beberapa bantuan dalam menyelesaikan formula. Hal ini ditemukan pertama dengan mengklik tab Rumus, dan kemudian klik Insert Fungsi:


Sebuah kotak akan muncul yang memungkinkan kita untuk memilih salah satu fungsi yang tersedia di Excel. Untuk menemukan yang kita cari, kita bisa mengetik istilah pencarian seperti "lookup" (karena fungsi kita tertarik adalah fungsi lookup). Sistem ini akan kembali kita daftar semua pencarian yang berhubungan dengan fungsi dalam Excel. VLOOKUP adalah yang kedua dalam daftar. Pilih itu suatu OK klik ...

Fungsi Argumen kotak muncul, mendorong kami untuk semua argumen (atau parameter) yang diperlukan untuk melengkapi fungsi VLOOKUP. Anda dapat menganggap ini sebagai kotak fungsi ini meminta kami pertanyaan-pertanyaan berikut:

1. Apa pengenal unik yang Anda cari dalam database?
2. Dimana database?
3. Yang sepotong informasi dari database, yang berkaitan dengan identifier unik, apakah Anda ingin memiliki diambil untuk Anda?

Tiga pertama argumen yang ditampilkan dalam huruf tebal, menunjukkan bahwa mereka adalah argumen wajib (fungsi VLOOKUP tidak lengkap tanpa mereka dan tidak akan kembali nilai yang valid). Argumen keempat adalah tidak berani, yang berarti bahwa itu opsional:

Kami akan menyelesaikan argumen dalam rangka, atas ke bawah.
Argumen pertama yang perlu kita menyelesaikan adalah argumen lookup_value. Fungsi kebutuhan kita untuk mengatakan itu di mana untuk menemukan identifikasi unik (item kode dalam kasus ini) bahwa itu harus retuning deskripsi. Kita harus memilih kode barang yang kita masukkan sebelumnya (dalam A11).
Klik pada ikon pemilih di sebelah kanan argumen pertama:

Kemudian klik sekali pada sel yang berisi kode barang (A11), dan tekan Enter:

Nilai "A11" dimasukkan ke dalam argumen pertama.
Sekarang kita harus memasukkan nilai untuk argumen table_array. Dengan kata lain, kita perlu memberitahu VLOOKUP di mana untuk menemukan database / list. Klik pada ikon pemilih di samping argumen kedua:

Sekarang cari database / daftar dan pilih seluruh daftar - tidak termasuk baris header. Database terletak pada lembar kerja yang terpisah, jadi kami pertama klik pada tab lembar kerja:

Selanjutnya kita pilih seluruh database, tidak termasuk baris header:

... Dan tekan Enter. Kisaran sel yang mewakili database (dalam hal ini "'Produk Database' A2:! D7") yang dimasukkan secara otomatis untuk kita ke argumen kedua.
Sekarang kita harus memasukkan argumen ketiga, Col_index_num. Kami menggunakan argumen ini untuk menentukan ke mana VLOOKUP sepotong informasi dari database, kaitkan dengan kode barang kami di A11, kami ingin telah kembali kepada kami. Dalam contoh khusus ini, kami ingin memiliki deskripsi item kembali kepada kami. Jika Anda melihat pada lembar kerja database, Anda akan melihat bahwa "Keterangan" kolom adalah kolom kedua dalam database. Ini berarti bahwa kita harus memasukkan nilai "2" ke dalam kotak Col_index_num:

Penting untuk dicatat bahwa kita tidak memasuki "2" di sini karena "Keterangan" kolom di kolom B pada lembar kerja tersebut. Jika database terjadi mulai di K kolom worksheet, kita masih akan memasukkan "2" di bidang ini.
Akhirnya, kita perlu memutuskan apakah akan memasukkan nilai ke dalam VLOOKUP argumen range_lookup akhir,. Argumen ini membutuhkan baik nilai benar atau salah, atau harus dibiarkan kosong. Bila menggunakan VLOOKUP dengan database (seperti yang berlaku 90% dari waktu), maka cara untuk memutuskan apa yang akan dimasukkan ke dalam argumen ini dapat dianggap sebagai berikut:

Jika kolom pertama dari database (kolom yang berisi pengidentifikasi unik) yang diurutkan sesuai abjad / numerik dalam urutan, maka mungkin untuk memasukkan nilai yang benar dalam argumen ini, atau biarkan kosong.
Jika kolom pertama dari database tidak diurutkan, atau itu diurutkan dalam urutan, maka Anda harus memasukkan nilai palsu ke dalam argumen ini

Sebagai kolom pertama dari database kami tidak diurutkan, kita memasuki palsu ke dalam argumen ini:

Itu saja! Kami telah memasukkan semua informasi yang diperlukan untuk VLOOKUP untuk mengembalikan nilai yang kita butuhkan. Klik tombol OK dan perhatikan bahwa deskripsi yang sesuai dengan kode barang "R99245" telah dimasukkan dengan benar ke dalam sel B11:

Rumus yang diciptakan bagi kita terlihat seperti ini:

Jika kita memasukkan kode item yang berbeda ke dalam sel A11, kita akan mulai melihat kekuatan fungsi VLOOKUP: Perubahan deskripsi Sel untuk mencocokkan kode item baru:

Kita dapat melakukan satu set sama langkah-langkah untuk mendapatkan harga item kembali ke dalam sel E11. Perhatikan bahwa formula baru harus dibuat dalam sel E11. Hasilnya akan terlihat seperti ini:

... Dan rumus akan terlihat seperti ini:

Perhatikan bahwa satu-satunya perbedaan antara keduanya adalah rumus argumen ketiga (Col_index_num) telah berubah dari "2" ke "3" (karena kita ingin data diambil dari kolom ke-3 dalam database).
Jika kita memutuskan untuk membeli 2 item ini, kita akan memasukkan "2" ke dalam sel D11. Kami kemudian akan memasukkan rumus sederhana ke dalam sel F11 untuk mendapatkan total baris:

= D11 * E11

... Yang terlihat seperti ini ...

Melengkapi Template Faktur

Kami telah belajar banyak tentang VLOOKUP sejauh ini. Bahkan, kami telah belajar semua kita akan belajar dalam artikel ini. Sangat penting untuk dicatat bahwa VLOOKUP dapat digunakan dalam situasi lain selain database. Hal ini kurang umum, dan dapat dilindungi di masa depan How-To Geek artikel.
Template faktur kami belum lengkap. Untuk menyelesaikannya, kita akan melakukan hal berikut:

1. Kami akan menghapus kode item sampel dari sel A11 dan "2" dari sel D11. Hal ini akan menyebabkan rumus VLOOKUP baru dibuat kami untuk menampilkan pesan error:
   
   Kita bisa memperbaiki ini dengan bijaksana penggunaan Excel IF () dan ISBLANK () fungsi. Kami mengubah formula kami dari ini ... = VLOOKUP (A11, 'Produk Database' A2:! D7, 2, FALSE)
   ... Ini ...
   = IF (ISBLANK (A11), "", VLOOKUP (A11, 'Produk Database' A2:! D7, 2, FALSE))
   
2. Kami akan menyalin formula dalam sel B11, E11 dan F11 ke sisa baris item faktur. Perhatikan bahwa jika kita melakukan hal ini, rumus yang dihasilkan akan tidak lagi benar merujuk ke tabel database. Kita bisa memperbaikinya dengan mengubah referensi sel untuk database untuk referensi sel absolut. Atau - dan bahkan lebih baik - kita bisa membuat nama range untuk database seluruh produk (seperti "Produk"), dan menggunakan nama rentang bukan referensi sel. Rumus akan berubah dari ini ... = IF (ISBLANK (A11), "", VLOOKUP (A11, 'Produk Database' A2:! D7, 2, FALSE))
   ... Ini ...
   = IF (ISBLANK (A11), "", VLOOKUP (A11, Produk, 2, FALSE))
   ... Dan kemudian salin rumus ke seluruh baris barang faktur.
3. Kami mungkin akan "mengunci" sel-sel yang mengandung formula kita (atau lebih tepatnya membuka sel lain), dan kemudian melindungi worksheet, dalam rangka untuk memastikan bahwa formula kami hati-hati dibangun tidak sengaja ditimpa ketika seseorang datang untuk mengisi faktur.
4. Kami akan menyimpan file sebagai template, sehingga bisa digunakan kembali oleh setiap orang di perusahaan kami

Jika kita merasa benar-benar pintar, kita akan membuat sebuah database dari semua pelanggan kami dalam worksheet lain, dan kemudian menggunakan ID pelanggan dimasukkan dalam sel F5 untuk secara otomatis mengisi nama pelanggan dan alamat dalam sel B6, B7 dan B8.

Jika Anda ingin berlatih dengan VLOOKUP, atau hanya melihat Template Faktur yang dihasilkan kami, dapat didownload dari sini .