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, silakan 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, kita akan mempelajari cara menggunakan VLOOKUP di mana identifier tidak perlu ada dalam database sama sekali. Ini hampir seolah-olah VLOOKUP dapat mengadopsi pendekatan yang "cukup mendekati cukup baik" untuk mengembalikan data yang kita 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:
Untuk menggunakan lembar kerja ini, penjual memasukkan angka penjualan mereka di sel B1, dan rumus dalam sel B2 menghitung tingkat komisi yang benar yang berhak mereka terima, yang digunakan dalam sel B3 untuk menghitung total komisi yang dibayar oleh penjual (yang adalah penggandaan sederhana dari B1 dan B2).
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:
JIKA(kondisi, nilai jika benar, nilai jika salah)
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:
Jadi spreadsheet kami berfungsi.
Mari membuatnya lebih rumit. Mari kenalkan 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 lebih dekat pada 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 untuk dibaca dan dipahami.
Kami tidak akan masuk ke dalam mur dan baut tentang bagaimana dan mengapa ini bekerja, juga tidak akan kami memeriksa nuansa fungsi bertingkat. Ini adalah tutorial tentang VLOOKUP, bukan pada Excel secara umum.
Pokoknya, semakin buruk! Bagaimana ketika kita 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%?
Sekarang rumus dalam sel B2, sementara yang benar, telah menjadi hampir tidak dapat dibaca. Tidak seorang pun harus menulis rumus di mana fungsi-fungsi tersebut bertingkat empat tingkat! Tentunya harus ada cara yang lebih sederhana?
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 kita lakukan adalah menggunakan VLOOKUP untuk mencari total penjualan tenaga penjual (dari B1) di tabel tarif dan mengembalikan kepada kami tingkat 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 dicatat 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:
Jika kami bereksperimen dengan beberapa jumlah penjualan yang berbeda, kami dapat memuaskan diri kami bahwa rumus tersebut berhasil.
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 pasangan yang tepat.
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 kita gunakan contoh: Ketika mencari tingkat komisi pada total penjualan $ 34.988, rumus VLOOKUP kami akan mengembalikan nilai 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:
Kapan Range_lookup diatur ke BENAR (atau dihilangkan), VLOOKUP akan terlihat di kolom 1 dan cocok nilai tertinggi yang tidak lebih besar dari itu Nilai lookup parameter.
Penting juga untuk dicatat 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.
