Cara Menggunakan VLOOKUP di Excel
VLOOKUP adalah salah satu fungsi Excel yang paling berguna, dan juga salah satu yang paling tidak dipahami. Dalam artikel ini, kami mengungkap VLOOKUP dengan contoh kehidupan nyata. Kami akan membuat Template Faktur yang dapat digunakan untuk perusahaan fiktif.
VLOOKUP adalah fungsi Excel . Artikel ini akan mengasumsikan bahwa pembaca sudah memiliki pemahaman yang baik tentang fungsi Excel, dan dapat menggunakan fungsi dasar seperti SUM, AVERAGE, dan TODAY. Dalam penggunaan yang paling umum, VLOOKUP adalah fungsi database , artinya berfungsi dengan tabel database - atau lebih sederhananya, daftar hal-hal di lembar kerja Excel. Hal seperti apa? Yah, apa saja. Anda mungkin memiliki lembar kerja yang berisi daftar karyawan, atau produk, atau pelanggan, atau CD dalam koleksi CD Anda, atau bintang di langit malam. Itu tidak terlalu penting.
Berikut adalah contoh daftar, atau database. Dalam hal ini adalah daftar produk yang dijual oleh perusahaan fiktif kami:
Biasanya daftar seperti ini memiliki semacam pengenal unik untuk setiap item dalam daftar. Dalam hal ini, pengenal unik ada di kolom "Kode Barang". Catatan: Agar fungsi VLOOKUP bekerja dengan database / daftar, daftar tersebut harus memiliki kolom yang berisi pengenal unik (atau "kunci", atau "ID"), dan kolom tersebut harus menjadi kolom pertama dalam tabel . Database sampel kami di atas memenuhi kriteria ini.
Bagian tersulit dalam menggunakan VLOOKUP adalah memahami dengan tepat kegunaannya. Jadi mari kita lihat apakah kita bisa menjelaskannya terlebih dahulu:
VLOOKUP mengambil informasi dari database / daftar berdasarkan contoh yang diberikan dari pengenal unik.
Dalam contoh di atas, Anda akan memasukkan fungsi VLOOKUP ke dalam spreadsheet lain dengan kode item, dan ini akan mengembalikan kepada Anda baik deskripsi item yang sesuai, harganya, atau ketersediaannya (kuantitas "Tersedia") seperti yang dijelaskan dalam dokumen asli Anda daftar. Manakah dari informasi berikut yang akan dikembalikan kepada Anda? Nah, Anda harus memutuskan ini saat Anda membuat rumus.
Jika yang Anda butuhkan hanyalah satu bagian informasi dari database, akan sangat merepotkan untuk membuat rumus dengan fungsi VLOOKUP di dalamnya. Biasanya Anda akan menggunakan fungsi semacam ini di spreadsheet yang dapat digunakan kembali, seperti template. Setiap kali seseorang memasukkan kode item yang valid, sistem akan mengambil semua informasi yang diperlukan tentang item yang sesuai.
Mari buat contohnya: Template Faktur yang dapat kita gunakan berulang kali di perusahaan fiktif kita.
Pertama kita memulai Excel, dan kita membuat sendiri faktur kosong:
Beginilah cara kerjanya: Orang yang menggunakan templat faktur akan mengisi serangkaian kode item di kolom “A”, dan sistem akan mengambil deskripsi dan harga setiap item dari database produk kami. Informasi tersebut akan digunakan untuk menghitung total baris untuk setiap item (dengan asumsi kami memasukkan jumlah yang valid).
Untuk membuat contoh ini tetap sederhana, kami akan menemukan database produk pada lembar terpisah di buku kerja yang sama:
Pada kenyataannya, kemungkinan besar database produk akan ditempatkan di buku kerja terpisah. Itu membuat sedikit perbedaan pada fungsi VLOOKUP, yang tidak terlalu peduli jika database terletak di lembar yang sama, lembar yang berbeda, atau buku kerja yang sama sekali berbeda.
Jadi, kami telah membuat database produk kami, yang terlihat seperti ini:
Untuk menguji rumus VLOOKUP yang akan kami tulis, pertama-tama kami memasukkan kode item yang valid ke sel A11 dari faktur kosong kami:
Selanjutnya, kami memindahkan sel aktif ke sel di mana kami ingin informasi diambil dari database oleh VLOOKUP untuk disimpan. Menariknya, ini adalah langkah yang dilakukan kebanyakan orang. Untuk menjelaskan lebih lanjut: Kami akan membuat rumus VLOOKUP yang akan mengambil deskripsi yang sesuai dengan kode item di sel A11. Di mana kita ingin deskripsi ini diletakkan saat kita mendapatkannya? Di sel B11, tentu saja. Jadi di situlah kami menulis rumus VLOOKUP: di sel B11. Pilih sel B11 sekarang.
Kita perlu menemukan daftar semua fungsi yang tersedia yang ditawarkan Excel, sehingga kita dapat memilih VLOOKUP dan mendapatkan bantuan dalam melengkapi rumus. Ini ditemukan dengan mengklik tab Rumus terlebih dahulu , lalu mengklik Sisipkan Fungsi :
Sebuah kotak muncul yang memungkinkan kita untuk memilih salah satu fungsi yang tersedia di Excel.
Untuk menemukan yang kita cari, kita bisa mengetikkan istilah pencarian seperti "pencarian" (karena fungsi yang kita minati adalah fungsi pencarian ). Sistem akan mengembalikan kepada kita daftar semua fungsi yang berhubungan dengan pencarian di Excel. VLOOKUP adalah yang kedua dalam daftar. Pilih itu dan klik OK .
Kotak Argumen Fungsi muncul, meminta kami untuk semua argumen (atau parameter ) yang diperlukan untuk menyelesaikan fungsi VLOOKUP. Anda dapat menganggap kotak ini sebagai fungsi yang menanyakan pertanyaan-pertanyaan berikut kepada kami:
- Pengenal unik apa yang Anda cari di database?
- Dimana database nya?
- Bagian informasi mana dari database, terkait dengan pengenal unik, yang ingin Anda dapatkan kembali untuk Anda?
Tiga argumen pertama ditampilkan dengan huruf tebal , yang menunjukkan bahwa itu adalah argumen wajib (fungsi VLOOKUP tidak lengkap tanpanya dan tidak akan mengembalikan nilai yang valid). Argumen keempat tidak tebal, artinya opsional:
Kami akan menyelesaikan argumen secara berurutan, dari atas ke bawah.
Argumen pertama yang perlu kita selesaikan adalah argumen Lookup_value . Fungsi tersebut membutuhkan kita untuk memberitahukannya di mana menemukan pengenal unik ( kode item dalam hal ini) yang harus dikembalikan deskripsinya. Kita harus memilih kode barang yang kita masukkan sebelumnya (di A11).
Klik ikon pemilih di sebelah kanan argumen pertama:
Kemudian klik sekali pada sel yang berisi kode 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 memberi tahu VLOOKUP di mana menemukan database / daftar. Klik ikon pemilih di sebelah argumen kedua:
Sekarang cari database / list dan pilih seluruh daftar - tidak termasuk baris header. Dalam contoh kami, database terletak di lembar kerja terpisah, jadi kami pertama kali mengklik tab lembar kerja itu:
Selanjutnya kami memilih seluruh database, tidak termasuk baris header:
… Dan tekan Enter . Rentang sel yang mewakili database (dalam hal ini "'Database Produk'! A2: D7") dimasukkan secara otomatis untuk kita ke dalam argumen kedua.
Sekarang kita perlu memasukkan argumen ketiga, Col_index_num . Kami menggunakan argumen ini untuk menentukan ke VLOOKUP bagian informasi mana dari database, yang terkait dengan kode item kami di A11, yang ingin kami kembalikan kepada kami. Dalam contoh khusus ini, kami ingin deskripsi item dikembalikan kepada kami. Jika Anda melihat pada lembar kerja database, Anda akan melihat bahwa kolom "Description" adalah kolom kedua dalam database. Ini berarti kita harus memasukkan nilai “2” ke dalam kotak Col_index_num :
Penting untuk dicatat bahwa kami tidak memasukkan "2" di sini karena kolom "Deskripsi" ada di kolom B pada lembar kerja itu. Jika database dimulai di kolom K lembar kerja, kita masih akan memasukkan "2" di bidang ini karena kolom "Deskripsi" adalah kolom kedua dalam set sel yang kita pilih saat menentukan "Tabel_array".
Terakhir, kita perlu memutuskan apakah akan memasukkan nilai ke dalam argumen VLOOKUP terakhir, Range_lookup . Argumen ini memerlukan nilai benar atau salah , atau harus dikosongkan. Saat menggunakan VLOOKUP dengan database (seperti halnya 90% dari waktu), cara untuk memutuskan apa yang akan dimasukkan ke dalam argumen ini dapat dianggap sebagai berikut:
Jika kolom pertama database (kolom yang berisi pengenal unik) diurutkan menurut abjad / numerik dalam urutan menaik, maka Anda dapat memasukkan nilai true ke dalam argumen ini, atau biarkan kosong.
Jika kolom pertama database tidak diurutkan, atau diurutkan dalam urutan menurun, Anda harus memasukkan nilai false ke dalam argumen ini.
Karena kolom pertama database kami tidak diurutkan, kami memasukkan false ke dalam argumen ini:
Itu dia! Kami telah memasukkan semua informasi yang diperlukan untuk VLOOKUP untuk mengembalikan nilai yang kami butuhkan. Klik tombol OK dan perhatikan bahwa deskripsi yang sesuai dengan kode item "R99245" telah dimasukkan dengan benar ke sel B11:
Rumus yang dibuat untuk kita terlihat seperti ini:
Jika kita memasukkan kode item yang berbeda ke dalam sel A11, kita akan mulai melihat kekuatan fungsi VLOOKUP: Sel deskripsi berubah agar sesuai dengan kode item baru:
Kita dapat melakukan satu set sama langkah-langkah untuk mendapatkan item harga kembali ke dalam sel E11. Perhatikan bahwa rumus baru harus dibuat di sel E11. Hasilnya akan terlihat seperti ini:
… Dan rumusnya akan terlihat seperti ini:
Perhatikan bahwa satu-satunya perbedaan antara kedua rumus tersebut adalah argumen ketiga ( Col_index_num ) telah berubah dari "2" menjadi "3" (karena kami ingin data diambil dari kolom ke-3 dalam database).
Jika kami memutuskan untuk membeli 2 item ini, kami akan memasukkan "2" ke sel D11. Kami kemudian akan memasukkan rumus sederhana ke dalam sel F11 untuk mendapatkan total baris:
= D11 * E1
… Yang terlihat seperti ini…
Melengkapi Template Faktur
Kami telah belajar banyak tentang VLOOKUP sejauh ini. Faktanya, kita telah mempelajari semua yang akan kita pelajari di artikel ini. Penting untuk dicatat bahwa VLOOKUP dapat digunakan dalam keadaan lain selain database. Ini kurang umum, dan mungkin tercakup dalam artikel How-To Geek mendatang.
Template faktur kami belum lengkap. Untuk menyelesaikannya, kami akan melakukan hal berikut:
- Kami akan menghapus kode item sampel dari sel A11 dan "2" dari sel D11. Ini akan menyebabkan rumus VLOOKUP yang baru kami buat menampilkan pesan kesalahan:
Kita dapat memperbaikinya dengan menggunakan fungsi IF () dan ISBLANK () Excel secara bijaksana . Kami mengubah rumus kami dari ini… = VLOOKUP (A11, 'Database Produk'! A2: D7,2, FALSE) … menjadi ini… = IF (ISBLANK (A11), ””, VLOOKUP (A11, 'Database Produk'! A2 : D7,2, SALAH)) - Kami akan menyalin rumus di sel B11, E11 dan F11 ke baris item sisa faktur. Perhatikan bahwa jika kita melakukan ini, rumus yang dihasilkan tidak akan lagi merujuk ke tabel database dengan benar. Kita bisa memperbaikinya dengan mengubah referensi sel untuk database menjadi referensi sel absolut . Atau - dan bahkan lebih baik - kita bisa membuat nama rentang untuk seluruh database produk (seperti "Produk"), dan menggunakan nama rentang ini sebagai ganti referensi sel. Rumusnya akan berubah dari ini… = IF (ISBLANK (A11), ””, VLOOKUP (A11, 'Product Database'! A2: D7,2, FALSE)) … menjadi ini… = IF (ISBLANK (A11), ”” , VLOOKUP (A11, Products, 2, FALSE)) … lalu salin rumus ke baris item faktur lainnya.
- Kami akan mungkin “kunci” sel yang berisi rumus kami (atau lebih tepatnya membuka yang lain sel), dan kemudian melindungi lembar kerja, dalam rangka untuk memastikan bahwa formula kami hati-hati dibangun tidak sengaja ditimpa ketika seseorang datang untuk mengisi faktur.
- Kami akan menyimpan file sebagai template , sehingga dapat digunakan kembali oleh semua orang di perusahaan kami
Jika kami merasa sangat pintar, kami akan membuat database semua pelanggan kami di lembar kerja lain, dan kemudian menggunakan ID pelanggan yang dimasukkan di sel F5 untuk secara otomatis mengisi nama dan alamat pelanggan di sel B6, B7 dan B8.
Jika Anda ingin berlatih dengan VLOOKUP, atau hanya melihat Templat Faktur yang kami hasilkan, itu dapat diunduh dari sini.
