VLOOKUP adalah salah satu fungsi Excel yang paling berguna, dan itu juga salah satu yang paling tidak dipahami. Dalam artikel ini, kami mengungkap VLOOKUP dengan contoh nyata. Kami akan membuat dapat digunakan Template Faktur untuk perusahaan fiktif.
VLOOKUP adalah Excel fungsi. Artikel ini akan mengasumsikan bahwa pembaca sudah memiliki pemahaman fungsi Excel, dan dapat menggunakan fungsi dasar seperti SUM, AVERAGE, dan TODAY. Dalam penggunaannya yang paling umum, VLOOKUP adalah a database berfungsi, artinya berfungsi dengan tabel database - atau lebih sederhana, daftar hal-hal dalam lembar kerja Excel. Hal-hal apa? Baik, apa saja semacam itu. 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 contoh daftar, atau basis data. Dalam hal ini adalah daftar produk yang dijual oleh perusahaan fiktif kami:
Biasanya daftar seperti ini memiliki semacam pengidentifikasi unik untuk setiap item dalam daftar. Dalam hal ini, pengenal unik ada di kolom "Kode Item". Catatan: Untuk fungsi VLOOKUP untuk bekerja dengan database / daftar, daftar itu harus memiliki kolom yang berisi pengidentifikasi unik (atau "kunci", atau "ID"), dan kolom itu harus menjadi kolom pertama dalam tabel. Database contoh kami di atas memenuhi kriteria ini.
Bagian tersulit dalam menggunakan VLOOKUP adalah memahami dengan tepat untuk apa itu. Jadi mari kita lihat apakah kita bisa mendapatkan yang jelas pertama:
VLOOKUP retrieves information from a database/list based on a supplied instance of the unique identifier.
Dalam contoh di atas, Anda akan memasukkan fungsi VLOOKUP ke spreadsheet lain dengan kode item, dan itu akan kembali kepada Anda baik deskripsi item yang bersangkutan, harganya, atau ketersediaannya (kuantitas "In stock") seperti yang dijelaskan dalam dokumen asli Anda. daftar. Manakah dari bagian informasi ini yang akan memberikan Anda kembali? Anda harus memutuskan ini saat Anda membuat rumus.
Jika semua yang Anda butuhkan adalah satu bagian informasi dari database, akan banyak masalah yang harus dihadapi untuk membuat rumus dengan fungsi VLOOKUP di dalamnya. Biasanya Anda akan menggunakan fungsi semacam ini dalam spreadsheet yang dapat digunakan kembali, seperti template. Setiap kali seseorang memasukkan kode barang yang valid, sistem akan mengambil semua informasi yang diperlukan tentang item yang sesuai.
Mari buat contoh ini: An Template Faktur yang dapat kita gunakan kembali berulang kali di perusahaan fiktif kami.
Pertama kami memulai Excel, dan kami membuat sendiri faktur kosong:
Untuk keperluan menjaga contoh ini sederhana, kami akan mencari database produk pada lembar terpisah di buku kerja yang sama:
Jadi, kami telah membuat basis data produk kami, yang terlihat seperti ini:
Kita perlu menemukan daftar semua fungsi yang tersedia yang ditawarkan Excel, sehingga kita dapat memilih VLOOKUP dan mendapatkan bantuan dalam menyelesaikan rumus. Ini ditemukan dengan mengklik pertama Rumus tab, lalu klik Masukkan Fungsi:
Untuk menemukan yang kami cari, kami dapat mengetikkan istilah penelusuran seperti "mencari" (karena fungsi yang kami minati adalah menengadah fungsi). Sistem akan mengembalikan daftar semua fungsi yang terkait pencarian di Excel. VLOOKUP adalah yang kedua dalam daftar. Pilih satu klik baik.
Itu Argumen Fungsi kotak muncul, meminta kami untuk semua argumen (atau parameter) diperlukan untuk melengkapi fungsi VLOOKUP. Anda dapat menganggap kotak ini sebagai fungsi yang menanyakan pertanyaan-pertanyaan berikut:
- Identifier unik apa yang Anda cari di database?
- Di mana basis datanya?
- Bagian informasi mana dari database, yang terkait dengan pengidentifikasi unik, apakah Anda ingin mengambilnya untuk Anda?
Tiga argumen pertama ditampilkan dalam huruf tebal, menunjukkan bahwa mereka wajib argumen (fungsi VLOOKUP tidak lengkap tanpa mereka dan tidak akan mengembalikan nilai yang valid). Argumen keempat tidak tebal, artinya opsional:
Argumen pertama yang perlu kita selesaikan adalah Nilai lookup argumen. Fungsi ini membutuhkan kita untuk memberitahukan di mana menemukan pengenal unik (the Kode barang dalam hal ini) bahwa itu harus mengembalikan 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 Memasukkan:
Sekarang kita perlu memasukkan nilai untuk Table_array argumen. Dengan kata lain, kita perlu memberi tahu VLOOKUP di mana menemukan database / daftar. Klik pada ikon pemilih di sebelah argumen kedua:
… dan tekan Memasukkan. Rentang sel yang mewakili basis data (dalam hal ini "'Database Produk'! A2: D7") dimasukkan secara otomatis untuk kami ke argumen kedua.
Sekarang kita perlu memasukkan argumen ketiga, Col_index_num. Kami menggunakan argumen ini untuk menentukan ke VLOOKUP informasi mana dari database, yang dikaitkan dengan kode barang kami di A11, kami ingin kembali kepada kami. Dalam contoh khusus ini, kami ingin memiliki item tersebut deskripsi kembali kepada kami. Jika Anda melihat lembar kerja basis data, Anda akan melihat bahwa kolom “Deskripsi” adalah kedua kolom dalam database. Ini berarti bahwa kita harus memasukkan nilai "2" ke dalam Col_index_num kotak:
Penting untuk dicatat bahwa kami tidak memasukkan "2" di sini karena kolom "Deskripsi" ada di B kolom pada lembar kerja itu. Jika database terjadi mulai di kolom K dari lembar kerja, kita masih akan memasukkan "2" dalam bidang ini karena kolom "Deskripsi" adalah kolom kedua dalam kumpulan sel yang kita pilih saat menentukan "Table_array".
Akhirnya, kita perlu memutuskan apakah akan memasukkan nilai ke dalam argumen VLOOKUP terakhir, Range_lookup. Argumen ini membutuhkan a benar atau Salah nilai, atau harus dibiarkan kosong. Saat menggunakan VLOOKUP dengan basis data (90% dari waktu sebenarnya), cara untuk memutuskan apa yang akan dimasukkan ke dalam argumen ini dapat dianggap sebagai berikut:
If the first column of the database (the column that contains the unique identifiers) is sorted alphabetically/numerically in ascending order, then it’s possible to enter a value of true into this argument, or leave it blank.
If the first column of the database is not sorted, or it’s sorted in descending order, then you must enter a value of false into this argument
Seperti kolom pertama dari basis data kami tidak disortir, kami masukkan Salah ke dalam argumen ini:
Itu dia! Kami telah memasukkan semua informasi yang diperlukan untuk VLOOKUP untuk mengembalikan nilai yang kami butuhkan. Klik baik dan perhatikan bahwa uraian yang terkait dengan kode barang “R99245” telah dimasukkan dengan benar ke dalam sel B11:
Jika kita memasukkan berbeda kode item ke sel A11, kita akan mulai melihat kekuatan fungsi VLOOKUP: Deskripsi sel berubah agar cocok dengan kode item baru:
Kami dapat melakukan serangkaian langkah serupa untuk mendapatkan item harga kembali ke sel E11. Perhatikan bahwa formula baru harus dibuat di sel E11. Hasilnya akan terlihat seperti ini:
Perhatikan bahwa satu-satunya perbedaan antara dua rumus 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 formula 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 yang akan kami pelajari dalam artikel ini. Penting untuk dicatat bahwa VLOOKUP dapat digunakan dalam keadaan lain selain basis data. Ini kurang umum, dan mungkin dibahas di artikel How-To Geek di masa depan.
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 kita buat untuk menampilkan pesan kesalahan:
Image Kami dapat memperbaiki ini dengan menggunakan Excel yang bijaksana JIKA() dan KOSONG() fungsi. Kami mengubah formula kami dari ini … = VLOOKUP (A11, 'Database Produk'! A2: D7,2, FALSE) …untuk ini… = IF (ISBLANK (A11),””, VLOOKUP (A11, 'Database Produk'! A2: D7,2, FALSE))
- Kami akan menyalin rumus dalam sel B11, E11 dan F11 ke sisa baris item dari faktur. Perhatikan bahwa jika kita melakukan ini, rumus yang dihasilkan tidak akan lagi benar mengacu ke tabel database. Kita bisa memperbaikinya dengan mengubah referensi sel untuk database menjadi mutlak referensi sel. Alternatif lain - dan bahkan lebih baik - kita bisa membuat nama rentang untuk seluruh basis data produk (seperti “Produk”), dan gunakan nama rentang ini sebagai ganti referensi sel. Rumusnya akan berubah dari ini … = IF (ISBLANK (A11),””, VLOOKUP (A11, 'Database Produk'! A2: D7,2, FALSE)) …untuk ini… = IF (ISBLANK (A11),””, VLOOKUP (A11, Produk, 2, FALSE)) …dan kemudian salin rumus ke sisa baris item faktur.
- Kami mungkin akan "mengunci" sel-sel yang mengandung formula kami (atau lebih tepatnya membuka kunci itu lain sel), dan kemudian melindungi lembar kerja, untuk memastikan bahwa formula kami yang dibangun dengan hati-hati tidak secara tidak sengaja ditimpa ketika seseorang datang untuk mengisi faktur.
- Kami akan menyimpan file sebagai file template, sehingga bisa digunakan kembali oleh semua orang di perusahaan kami
Jika kami merasa sangat pintar, kami akan membuat database dari semua pelanggan kami di lembar kerja lain, dan kemudian menggunakan ID pelanggan yang dimasukkan dalam sel F5 untuk secara otomatis mengisi nama dan alamat pelanggan di sel B6, B7 dan B8.
