01 03
Temukan Perkiraan Kecocokan untuk Data dengan Excel VLOOKUP
Bagaimana Fungsi VLOOKUP Bekerja
Fungsi VLOOKUP Excel, yang merupakan singkatan dari pencarian vertikal , dapat digunakan untuk mencari informasi spesifik yang terletak di tabel data atau basis data.
VLOOKUP biasanya mengembalikan satu bidang data sebagai outputnya. Bagaimana hal ini terjadi:
- Anda memberikan nama atau lookup_value yang memberi tahu VLOOKUP di mana baris atau catatan tabel data untuk mencari data yang diinginkan
- Anda memberikan nomor kolom - yang dikenal sebagai col_index_num - dari data yang Anda cari
- Fungsi mencari nilai_penampilan di kolom pertama dari tabel data
- VLOOKUP kemudian menempatkan dan mengembalikan informasi yang Anda cari dari bidang lain dari rekaman yang sama menggunakan nomor kolom yang disediakan
Menyortir Data Pertama
Meskipun tidak selalu diperlukan, ini adalah cara terbaik untuk terlebih dahulu mengurutkan rentang data yang VLOOKUP sedang mencari dalam urutan menaik menggunakan kolom pertama rentang untuk kunci pengurutan.
Jika data tidak diurutkan, VLOOKUP mungkin mengembalikan hasil yang salah.
Sintaks dan Argumen Fungsi VLOOKUP
Sintaks fungsi mengacu pada tata letak fungsi dan termasuk nama fungsi, tanda kurung, dan argumen .
Sintaks untuk fungsi VLOOKUP adalah:
= VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
lookup _value - (diperlukan) nilai yang dicari - seperti kuantitas yang dijual dalam gambar di atas
table_array - (diperlukan) ini adalah tabel data yang VLOOKUP pencarian untuk menemukan informasi yang Anda cari.
- The table_array harus mengandung setidaknya dua kolom data
- Kolom pertama biasanya berisi lookup_value
col_index_num - (diperlukan) nomor kolom dari nilai yang ingin Anda temukan.
- Penomoran dimulai dengan kolom search_key sebagai kolom 1
- Jika col_index_num diatur ke angka yang lebih besar dari jumlah kolom yang dipilih dalam argumen table_array sebuah #REF! kesalahan dikembalikan oleh fungsi
range_lookup - (opsional) menunjukkan apakah kisaran diurutkan dalam urutan menaik atau tidak.
- Data di kolom pertama digunakan sebagai kunci pengurutan
- Nilai Boolean - TRUE atau FALSE adalah satu-satunya nilai yang dapat diterima
- Jika dihilangkan, nilai ditetapkan ke TRUE secara default
- Jika disetel TRUE atau dihilangkan dan kolom pertama rentang tidak diurutkan dalam urutan menaik, hasil yang salah mungkin terjadi
- Jika disetel TRUE atau dihilangkan dan pencocokan tepat untuk nilai pencarian tidak ditemukan, kecocokan terdekat yang lebih kecil dalam ukuran atau nilai digunakan sebagai kunci_penelusuran
- Jika disetel ke FALSE, VLOOKUP hanya menerima pencocokan tepat untuk nilai_penelusuran . Jika ada beberapa nilai yang cocok, nilai pencocokan pertama dikembalikan
- Jika disetel ke FALSE dan tidak ada nilai yang cocok untuk pencarian_key ditemukan, kesalahan # N / A dikembalikan oleh fungsi
Contoh: Temukan Tingkat Diskon untuk Kuantitas yang Dibeli
Contoh pada gambar di atas menggunakan fungsi VLOOKUP untuk menemukan tingkat diskonto yang bervariasi tergantung pada jumlah barang yang dibeli.
Contoh ini menunjukkan bahwa diskon untuk pembelian 19 item adalah 2%. Ini karena kolom Kuantitas berisi rentang nilai. Akibatnya, VLOOKUP tidak dapat menemukan yang sama persis. Sebaliknya, kecocokan perkiraan harus ditemukan untuk mengembalikan tingkat diskonto yang benar.
Untuk menemukan pertandingan perkiraan:
- mengurutkan data dalam table_array dalam urutan menaik;
- atur argumen range_lookup ke TRUE
Dalam contoh, rumus berikut yang mengandung fungsi VLOOKUP digunakan untuk menemukan diskon untuk jumlah barang yang dibeli.
= VLOOKUP (C2, $ C $ 5: $ D $ 8,2, BENAR)
Meskipun rumus ini hanya dapat diketik ke dalam sel lembar kerja, opsi lain, seperti yang digunakan dengan langkah-langkah yang tercantum di bawah ini, adalah menggunakan kotak dialog fungsi untuk memasukkan argumennya.
- Menggunakan kotak dialog sering membuatnya lebih mudah untuk memasukkan argumen fungsi dengan benar.
Membuka Kotak Dialog VLOOKUP
Langkah-langkah yang digunakan untuk memasukkan fungsi VLOOKUP yang ditunjukkan pada gambar di atas ke dalam sel B2 adalah:
- Klik pada sel B2 untuk menjadikannya sel aktif - lokasi di mana hasil fungsi VLOOKUP ditampilkan
- Klik pada tab Rumus .
- Pilih Lookup & Referensi dari pita untuk membuka daftar drop-down fungsi
- Klik VLOOKUP dalam daftar untuk memunculkan kotak dialog fungsi
02 03
Memasukkan Argumen Fungsi VLOOKUP Excel
Menunjuk ke Referensi Sel
Argumen untuk fungsi VLOOKUP dimasukkan ke dalam baris terpisah dari kotak dialog seperti yang ditunjukkan pada gambar di atas.
Referensi sel yang akan digunakan sebagai argumen dapat diketik ke dalam baris yang benar, atau, seperti yang dilakukan dalam langkah-langkah di bawah ini, menunjuk, yang melibatkan sorot kisaran sel yang diinginkan dengan penunjuk tetikus, dapat digunakan untuk memasukkan mereka ke dalam kotak dialog .
Keuntungan menggunakan penunjuk meliputi:
- Lebih cepat daripada mengetik;
- Lebih sedikit kesalahan dibuat memasukkan referensi sel yang benar.
Menggunakan Referensi Sel Relatif dan Absolut dengan Argumen
Tidak jarang menggunakan banyak salinan VLOOKUP untuk mengembalikan informasi yang berbeda dari tabel data yang sama. Untuk mempermudah melakukan ini, sering VLOOKUP dapat disalin dari satu sel ke sel lainnya. Ketika fungsi disalin ke sel lain, perawatan harus dilakukan untuk memastikan bahwa referensi sel yang dihasilkan benar diberikan lokasi baru fungsi tersebut.
Pada gambar di atas, tanda dolar ( $ ) mengelilingi referensi sel untuk argumen table_array yang menunjukkan bahwa mereka adalah referensi sel absolut , yang berarti mereka tidak akan berubah jika fungsi disalin ke sel lain. Ini diinginkan karena banyak salinan VLOOKUP akan mereferensikan tabel data yang sama sebagai sumber informasi.
Referensi sel yang digunakan untuk nilai pencarian , di sisi lain , tidak dikelilingi oleh tanda dolar, yang membuatnya menjadi referensi sel relatif. Referensi sel relatif berubah ketika mereka disalin untuk mencerminkan lokasi baru mereka relatif terhadap posisi data yang mereka rujuk.
Memasukkan Argumen Fungsi
- Klik pada Lookup _value line di kotak dialog VLOOKUP
- Klik pada sel C2 di lembar kerja untuk memasukkan referensi sel ini sebagai argumen search_key
- Klik pada baris Table_array dari kotak dialog
- Sorot sel C5 ke D8 di lembar kerja untuk memasukkan rentang ini sebagai argumen Table_array - judul tabel tidak termasuk
- Tekan tombol F4 pada keyboard untuk mengubah rentang ke referensi sel absolut
- Klik pada baris Col_index_num dari kotak dialog
- Ketik a 2 pada baris ini sebagai argumen Col_index_num , karena tingkat diskon terletak di kolom 2 dari argumen Table_array
- Klik pada baris Range_lookup dari kotak dialog
- Ketik kata True sebagai argumen Range_lookup
- Tekan tombol Enter pada keyboard untuk menutup kotak dialog dan kembali ke lembar kerja
- Jawaban 2% (tingkat diskon untuk kuantitas yang dibeli) akan muncul di sel D2 dari lembar kerja
- Ketika Anda mengklik pada sel D2, fungsi lengkap = VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE) muncul di bilah rumus di atas lembar kerja
Mengapa VLOOKUP Dikembalikan 2% sebagai Hasilnya
- Dalam contoh, kolom Kuantitas tidak berisi pencocokan tepat untuk nilai kunci_penelusuran 19.
- Karena argumen is_sorted diatur ke TRUE, VLOOKUP akan menemukan kecocokan perkiraan ke nilai search_key .
- Nilai terdekat dalam ukuran yang masih lebih kecil dari nilai search_key 19 adalah 11.
- VLOOKUP, oleh karena itu, mencari persentase diskon dalam baris yang mengandung 11, dan, sebagai hasilnya, mengembalikan tingkat diskonto 2%.
03 03
Excel VLOOKUP Tidak Bekerja: Kesalahan # N / A dan #REF
Pesan Kesalahan VLOOKUP
Pesan galat berikut ini terkait dengan VLOOKUP.
A # N / A ("value not available") Kesalahan Ditampilkan Jika:
- Nilai pencarian tidak ditemukan di kolom pertama dari argumen rentang
- Argumen Table_array tidak akurat. Misalnya, argumen mungkin menyertakan kolom kosong di sisi kiri rentang
- Argumen Range_lookup diatur ke FALSE dan pencocokan tepat untuk argumen search_key tidak dapat ditemukan di kolom pertama rentang
- Argumen Range_lookup disetel ke TRUE dan semua nilai di kolom pertama rentang lebih besar daripada kunci_penelusuran
A #REF! ("referensi di luar jangkauan") Kesalahan Ditampilkan Jika:
- Argumen Col_index_num lebih besar daripada jumlah kolom dalam Table_array