Dalam artikel baru-baru ini, kami memperkenalkan fungsi Excel yang disebut VLOOKUP dan menjelaskan bagaimana itu bisa digunakan untuk mengambil informasi dari database ke dalam sel di lembar kerja lokal. Dalam artikel itu kami menyebutkan bahwa ada dua kegunaan untuk VLOOKUP, dan hanya satu yang ditangani dengan basis data kueri. Dalam artikel ini, yang kedua dan terakhir dalam seri VLOOKUP, kami memeriksa ini, penggunaan yang kurang dikenal lainnya untuk fungsi VLOOKUP.
Jika Anda belum melakukannya, baca artikel VLOOKUP pertama - artikel ini akan menganggap bahwa banyak konsep yang dijelaskan dalam artikel tersebut sudah diketahui oleh pembaca.
Saat bekerja dengan basis data, VLOOKUP dilewatkan "pengenal unik" yang berfungsi untuk mengidentifikasi rekam data mana yang ingin kami temukan dalam basis data (mis. Kode produk atau ID pelanggan). Pengenal unik ini harus ada dalam database, jika tidak, VLOOKUP mengembalikan kesalahan kepada kami. Dalam artikel ini, kami akan memeriksa cara menggunakan VLOOKUP di mana pengidentifikasi tidak perlu ada dalam database sama sekali. Hampir seolah-olah VLOOKUP dapat mengadopsi pendekatan "cukup dekat cukup baik" untuk mengembalikan data yang kami cari. Dalam keadaan tertentu, ini persis apa yang kita butuhkan.
Kami akan mengilustrasikan artikel ini dengan contoh dunia nyata - yaitu menghitung komisi yang dihasilkan dari serangkaian angka penjualan. Kami akan mulai dengan skenario yang sangat sederhana, dan kemudian secara progresif membuatnya lebih kompleks, sampai satu-satunya solusi rasional untuk masalah ini adalah dengan menggunakan VLOOKUP. Skenario awal di perusahaan fiktif kami bekerja seperti ini: Jika seorang tenaga penjual menciptakan lebih dari $ 30.000 penjualan pada tahun tertentu, komisi yang mereka peroleh dari penjualan itu adalah 30%. Kalau tidak, komisi mereka hanya 20%. Sejauh ini adalah lembar kerja yang cukup sederhana:
B2 sel berisi satu-satunya bagian yang menarik dari lembar kerja ini - rumus untuk memutuskan tarif komisi mana yang akan digunakan: yang satu di bawah ambang batas $ 30.000, atau yang satu atas ambang batas. Rumus ini menggunakan fungsi Excel yang disebut JIKA. Bagi para pembaca yang tidak terbiasa dengan IF, ini berfungsi seperti ini:
IF(condition,value if true,value if false)
Dimana kondisi adalah ekspresi yang mengevaluasi baik benar atau Salah. Dalam contoh di atas, kondisi adalah ekspresi B1, yang dapat dibaca sebagai "Apakah B1 kurang dari B5?", atau, dengan kata lain, "Apakah total penjualan kurang dari ambang". Jika jawaban untuk pertanyaan ini adalah "ya" (benar), maka kami menggunakan nilai jika benar parameter fungsi, yaitu B6 dalam hal ini - tingkat komisi jika total penjualan di bawah ambang batas. Jika jawaban atas pertanyaan tersebut adalah "tidak" (salah), maka kami menggunakan nilai jika salah parameter fungsi, yaitu B7 dalam hal ini - tingkat komisi jika total penjualan atas ambang batas.
Seperti yang Anda lihat, menggunakan total penjualan $ 20.000 memberi kita tingkat komisi 20% dalam sel B2. Jika kami memasukkan nilai $ 40.000, kami mendapatkan tingkat komisi yang berbeda:
Mari membuatnya lebih rumit. Mari perkenalkan ambang kedua: Jika penjual menghasilkan lebih dari $ 40.000, maka tingkat komisi mereka meningkat menjadi 40%:
Cukup mudah dimengerti di dunia nyata, tetapi dalam sel B2 formula kami semakin kompleks. Jika Anda melihat secara dekat rumus, Anda akan melihat bahwa parameter ketiga dari fungsi IF asli (the nilai jika salah) sekarang menjadi keseluruhan fungsi IF dalam dirinya sendiri. Ini disebut a fungsi bertingkat (fungsi dalam fungsi). Ini sangat valid di Excel (bahkan berfungsi!), Tetapi lebih sulit dibaca dan dipahami.
Kami tidak akan membahas dan memahami cara dan mengapa ini berfungsi, juga tidak akan kami periksa nuansa fungsi bertingkat. Ini adalah tutorial tentang VLOOKUP, bukan pada Excel secara umum.
Pokoknya, semakin buruk! Bagaimana jika kami memutuskan bahwa jika mereka mendapat lebih dari $ 50.000, maka mereka berhak atas komisi 50%, dan jika mereka mendapat lebih dari $ 60.000, maka mereka berhak atas komisi 60%?
Tentu saja ada. VLOOKUP untuk menyelamatkan!
Mari kita mendesain ulang lembar kerja sedikit. Kami akan menyimpan semua angka yang sama, tetapi mengaturnya dengan cara baru, lebih banyak lagi datar cara:
Luangkan waktu sejenak dan verifikasi untuk diri sendiri bahwa yang baru Beri Nilai Tabel bekerja persis sama dengan rangkaian ambang batas di atas.
Secara konseptual, yang akan kami lakukan adalah menggunakan VLOOKUP untuk mencari total penjualan tenaga penjualan (dari B1) di tabel tarif dan mengembalikan kepada kami tarif komisi yang sesuai. Perhatikan bahwa penjual mungkin memang menciptakan penjualan yang tidak salah satu dari lima nilai di tabel tarif ($ 0, $ 30.000, $ 40.000, $ 50.000 atau $ 60.000). Mereka mungkin telah menciptakan penjualan $ 34.988. Penting untuk diperhatikan bahwa $ 34.988 tidak tidak muncul di tabel tarif. Mari kita lihat apakah VLOOKUP dapat menyelesaikan masalah kita …
Kami memilih sel B2 (lokasi kami ingin meletakkan rumus kami), dan kemudian memasukkan fungsi VLOOKUP dari Rumus tab:
Itu Argumen Fungsi kotak untuk VLOOKUP muncul. Kami mengisi argumen (parameter) satu per satu, dimulai dengan Nilai lookup, yang, dalam hal ini, total penjualan dari sel B1. Kami menempatkan kursor di Nilai lookup lalu klik sekali pada sel B1:
Selanjutnya kita perlu menentukan ke VLOOKUP apa tabel untuk mencari data ini. Dalam contoh ini, ini adalah tabel tarif, tentu saja. Kami menempatkan kursor di Table_array lapangan, dan kemudian sorot seluruh tabel tarif - tidak termasuk judul:
Selanjutnya kita harus menentukan kolom mana dalam tabel yang memuat informasi yang kita inginkan agar formula kita kembali kepada kita. Dalam hal ini kami menginginkan tingkat komisi, yang ditemukan di kolom kedua dalam tabel, sehingga kami memasukkannya a 2 ke dalam Col_index_num bidang:
Akhirnya kami memasukkan nilai dalam Range_lookup bidang.
Penting: Ini adalah penggunaan bidang ini yang membedakan dua cara menggunakan VLOOKUP. Untuk menggunakan VLOOKUP dengan database, parameter terakhir ini, Range_lookup, harus selalu disetel ke SALAH, tetapi dengan menggunakan VLOOKUP lainnya, kita harus membiarkannya kosong atau memasukkan nilai BENAR. Saat menggunakan VLOOKUP, penting sekali Anda membuat pilihan yang tepat untuk parameter akhir ini.
Agar eksplisit, kami akan memasukkan nilai benar dalam Range_lookup bidang. Akan baik-baik saja membiarkannya kosong, karena ini adalah nilai default:
Kami telah menyelesaikan semua parameter. Kami sekarang klik baik tombol, dan Excel membangun rumus VLOOKUP kami untuk kami:
Kesimpulan
Dalam versi "database" dari VLOOKUP, di mana Range_lookup parameter adalah SALAH, nilai yang diteruskan dalam parameter pertama (Nilai lookup) harus hadir di database. Dengan kata lain, kami mencari yang sama persis.
Namun dalam penggunaan VLOOKUP lainnya, kami tidak perlu mencari yang sama persis. Dalam hal ini, "cukup dekat sudah cukup baik". Tapi apa yang kita maksud dengan "cukup dekat"? Mari gunakan contoh: Saat menelusuri tingkat komisi pada total penjualan $ 34.988, rumus VLOOKUP kami akan mengembalikan nilai sebesar 30% kepada kami, yang merupakan jawaban yang benar. Mengapa ia memilih baris dalam tabel yang berisi 30%? Apa sebenarnya yang "cukup dekat" dalam kasus ini? Mari kita teliti:
When Range_lookup is set to TRUE (or omitted), VLOOKUP will look in column 1 and match the highest value that is not greater than the Lookup_value parameter.
Penting juga untuk diperhatikan bahwa agar sistem ini berfungsi, tabel harus diurutkan dalam urutan menaik pada kolom 1!
Jika Anda ingin berlatih dengan VLOOKUP, file contoh yang diilustrasikan dalam artikel ini dapat diunduh dari sini.
