1Aug
VLOOKUP adalah salah satu fungsi Excel yang paling berguna, dan ini juga salah satu yang paling tidak dimengerti. Pada artikel ini, kita demystify VLOOKUP dengan cara contoh kehidupan nyata. Kami akan membuat Template Faktur yang dapat digunakan _ untuk sebuah perusahaan fiktif.
VLOOKUP adalah fungsi Excel .Artikel ini akan mengasumsikan bahwa pembaca sudah memiliki pemahaman lewat fungsi Excel, dan bisa menggunakan fungsi dasar seperti SUM, RATA-RATA, dan HARI INI.Dalam penggunaannya yang paling umum, VLOOKUP adalah fungsi database, yang berarti bekerja dengan tabel database - atau lebih tepatnya, mencantumkan hal-hal di lembar kerja Excel. Hal macam apaNah, segala macam .Anda mungkin memiliki lembar kerja yang berisi daftar karyawan, atau produk, atau pelanggan, atau CD dalam koleksi CD Anda, atau bintang di langit malam. Tidak masalah.
Berikut adalah contoh daftar, atau database. Dalam kasus ini, daftar produk yang dijual oleh perusahaan fiktif kami:
Biasanya daftar seperti ini memiliki semacam pengenal unik untuk setiap item dalam daftar. Dalam kasus ini, pengenal unik ada di kolom "Kode Barang".Catatan: Untuk fungsi VLOOKUP agar bekerja dengan database / daftar, daftar
harus memiliki kolom yang berisi pengenal unik( atau "kunci", atau "ID"), dan kolom itu harus menjadi kolom pertama di tabel.Database contoh kami di atas memenuhi kriteria ini.Bagian tersulit dari penggunaan VLOOKUP adalah memahami dengan tepat apa tujuannya. Jadi mari kita lihat apakah kita bisa mendapatkan yang pertama dengan jelas:
VLOOKUP mengambil informasi dari database / daftar berdasarkan contoh pengenal unik yang disediakan.
Pada contoh di atas, Anda akan memasukkan fungsi VLOOKUP ke spreadsheet lain dengan kode item, dan akan mengembalikan deskripsi item, harga, atau ketersediaannya yang sesuai( kuantitas "dalam stok") seperti yang dijelaskan di akun Anda.daftar asliManakah dari informasi berikut yang akan mengantarkan Anda kembali? Nah, Anda bisa memutuskan ini saat Anda menciptakan formula.
Jika semua yang Anda butuhkan adalah salah satu bagian dari informasi dari database, akan banyak kesulitan untuk membuat formula dengan fungsi VLOOKUP di dalamnya. Biasanya Anda akan menggunakan fungsi semacam ini di spreadsheet yang dapat digunakan ulang, seperti template. Setiap kali seseorang memasukkan kode item yang valid, sistem akan mengambil semua informasi yang diperlukan tentang item yang sesuai.
Mari kita ciptakan sebuah contoh dari hal ini: Template Faktur yang dapat kita gunakan berulang-ulang di perusahaan fiktif kita.
Pertama kita mulai Excel, dan kita membuat faktur kosong:
Beginilah cara kerjanya: Orang yang menggunakan template faktur akan mengisi serangkaian kode item di kolom "A", dan sistem akan mengambil setiap itemdeskripsi dan harga dari database produk kami. Informasi tersebut akan digunakan untuk menghitung total baris untuk setiap item( dengan asumsi kita memasukkan jumlah yang valid).
Untuk keperluan membuat contoh ini sederhana, kita akan menemukan database produk pada lembar terpisah di buku kerja yang sama:
Kenyataannya, kemungkinan besar database produk akan berada dalam buku kerja yang terpisah. Ini membuat sedikit perbedaan pada fungsi VLOOKUP, yang tidak terlalu peduli jika database berada pada lembar yang sama, lembaran 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 barang yang valid ke dalam sel A11 dari faktur kosong kami:
Selanjutnya, kamipindahkan sel aktif ke sel di mana kita ingin informasi diambil dari database oleh VLOOKUP untuk disimpan. Menariknya, ini adalah langkah yang kebanyakan orang salah. Untuk menjelaskan lebih lanjut: Kami akan membuat formula 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 kita 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 menyelesaikan formula. Ini ditemukan dengan terlebih dahulu mengklik formula tab, dan kemudian mengklik Insert Function :
Sebuah kotak muncul yang memungkinkan kita memilih salah satu fungsi yang tersedia di Excel.
Untuk menemukan yang kami cari, kami bisa mengetikkan istilah pencarian seperti "lookup"( karena fungsi yang kami minati adalah lookup function).Sistem akan mengembalikan daftar semua fungsi pencarian yang terkait di Excel. VLOOKUP adalah yang kedua dalam daftar. Pilih itu klik OK .Kotak
Function Arguments muncul, mendorong kami untuk semua argumen ( atau parameter ) yang diperlukan untuk menyelesaikan fungsi VLOOKUP.Anda bisa menganggap kotak ini sebagai fungsi yang mengajukan pertanyaan berikut kepada Anda:
- Pengenal unik apa yang Anda cari di database?
- Dimana database?
- Bagian informasi dari database, yang terkait dengan pengenal unik, apakah Anda ingin mendapatkannya kembali untuk Anda?
Tiga argumen pertama ditunjukkan dalam huruf tebal , yang menunjukkan bahwa argumen tersebut adalah _sual( fungsi VLOOKUP tidak lengkap tanpa mereka dan tidak akan mengembalikan nilai yang valid).Argumen keempat tidak berani, artinya itu opsional:
Kami akan menyelesaikan argumen secara berurutan, dari atas ke bawah.
Argumen pertama yang perlu kita selesaikan adalah argumen Lookup_value .Fungsi ini perlu kita sampaikan di mana menemukan pengenal unik( kode item dalam kasus ini) bahwa hal itu seharusnya mengembalikan deskripsi. Kita harus memilih kode barang yang kita masukkan tadi( di A11).
Klik pada ikon pemilih di sebelah kanan argumen pertama:
Kemudian klik sekali pada sel yang berisi kode item( A11), dan tekan Masukkan :
Nilai "A11" dimasukkan ke dalam argumen pertama.
Sekarang kita perlu memasukkan nilai untuk argumen Table_array .Dengan kata lain, kita perlu memberi tahu VLOOKUP dimana untuk mencari database / daftar. Klik pada ikon pemilih di samping argumen kedua:
Sekarang cari database / daftar dan pilih keseluruhan daftar - tidak termasuk baris header .Dalam contoh kita, database terletak pada worksheet terpisah, jadi kita klik dulu tab worksheet itu:
Selanjutnya kita memilih keseluruhan database, tidak termasuk baris header:
. .. dan tekan Enter .Rentang sel yang mewakili database( dalam hal ini "'Product Database'! A2: D7") dimasukkan secara otomatis untuk kita ke argumen kedua.
Sekarang kita perlu memasukkan argumen ketiga, Col_index_num .Kami menggunakan argumen ini untuk menentukan VLOOKUP yang merupakan bagian dari informasi dari database, terkait dengan kode barang kami di A11, kami ingin kembali ke kami. Dalam contoh khusus ini, kami ingin membuat item tersebut dikembalikan kepada kami. Jika Anda melihat pada lembar kerja database, Anda akan melihat kolom "Deskripsi" 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 memasukkan "2" di sini karena kolom "Deskripsi" ada di kolom B padalembar kerja ituJika database terjadi pada kolom K dari worksheet, kita masih akan memasukkan "2" di kolom ini karena kolom "Description" adalah kolom kedua dalam rangkaian sel yang kita pilih saat menentukan "Table_array".
Akhirnya, kita perlu memutuskan apakah akan memasukkan nilai ke dalam argumen VLOOKUP akhir, Range_lookup .Argumen ini membutuhkan nilai milik , atau boleh dibiarkan kosong. Bila menggunakan VLOOKUP dengan database( seperti benar 90% dari waktu), cara untuk memutuskan apa yang harus dimasukkan ke dalam argumen ini dapat dianggap sebagai berikut:
Jika kolom pertama dari database( kolom yang berisi pengenal unik)diurutkan menurut abjad / numerik dalam urutan naik, maka dimungkinkan untuk memasukkan nilai true ke dalam argumen ini, atau membiarkannya kosong.
Jika kolom pertama dari database tidak diurutkan, atau diurutkan berdasarkan urutan, maka Anda harus memasukkan nilai palsu ke dalam argumen ini
Sebagai kolom pertama dari basis data yang tidak diurutkan,kita masukkan palsu ke dalam argumen ini:
Itu saja! 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 dalam sel B11:
Rumus yang dibuat untuk kita terlihat seperti ini:
Jika kita memasukkan kode item ke dalam selA11, kita akan mulai melihat kekuatan fungsi VLOOKUP: Perubahan sel deskripsi sesuai dengan kode item baru:
Kita dapat melakukan serangkaian langkah serupa untuk mendapatkan harga item kembali ke sel E11.Perhatikan bahwa formula 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 adalah argumen ketiga( Col_index_num ) telah berubah dari "2" menjadi "3"( karenakami ingin data diambil dari kolom ke-3 dalam database).
Jika kita memutuskan untuk membeli 2 dari barang-barang ini, kita akan memasukkan "2" ke dalam 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. Sebenarnya, kami telah mempelajari semua yang akan kami pelajari di artikel ini. Penting untuk dicatat bahwa VLOOKUP dapat digunakan dalam keadaan lain selain database. Ini kurang umum, dan mungkin dibahas di artikel How-To Geek masa depan.
Template faktur kami belum lengkap. Untuk menyelesaikannya, kami akan melakukan hal berikut:
- Kami akan menghapus contoh kode barang dari sel A11 dan "2" dari sel D11.Ini akan menyebabkan formula VLOOKUP yang baru dibuat untuk menampilkan pesan kesalahan:
Kami dapat memperbaiki ini dengan menggunakan fungsi IF() dan ISBLANK() .Kami mengubah formula kami dari ini. .. = VLOOKUP( A11, 'Database Produk' A2: D7,2, FALSE) . .. untuk ini. .. = IF( ISBLANK( A11), "", VLOOKUP( A11, 'Product Database'A2: D7,2, FALSE))
- Kami akan menyalin formula di sel B11, E11 dan F11 ke sisa baris item faktur. Perhatikan bahwa jika kita melakukan ini, formula yang dihasilkan tidak akan lagi benar merujuk ke tabel database. Kita bisa memperbaikinya dengan mengubah referensi sel untuk database referensi .Sebagai alternatif - dan bahkan lebih baik - kita dapat membuat berbagai nama untuk keseluruhan basis data produk( seperti "Produk"), dan gunakan nama rentang ini dan bukan 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 menyalin formula ke baris item faktur lainnya.
- Kita mungkin akan "mengunci" sel-sel yang mengandung formula kita( atau lebih tepatnya membuka sel lainnya), dan kemudian melindungi lembar kerja, untuk memastikan bahwa formula yang disusun dengan hati-hati tidak ditangguhkan secara tidak sengaja saat seseorang mengisidalam faktur
- Kami akan menyimpan file tersebut sebagai template , sehingga bisa digunakan kembali oleh semua orang di perusahaan kami
Jika kami merasa benar-benar cerdas, kami akan membuat database semua pelanggan kami di lembar kerja lain, dan kemudian menggunakanID pelanggan masuk 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 Template Faktur yang dihasilkan, Anda dapat mendownloadnya dari sini.