VLOOKUP di Excel, bagian 2 Menggunakan VLOOKUP tanpa basis data
Dalam artikel terbaru, 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 berurusan dengan query database. Dalam artikel ini, yang kedua dan terakhir dalam seri VLOOKUP, kami memeriksa penggunaan lain yang kurang dikenal untuk fungsi VLOOKUP.
Jika Anda belum melakukannya, silakan baca artikel VLOOKUP pertama - artikel ini akan menganggap bahwa banyak konsep yang dijelaskan dalam artikel itu sudah diketahui pembaca..
Saat bekerja dengan basis data, VLOOKUP diberikan "pengidentifikasi unik" yang berfungsi untuk mengidentifikasi catatan data mana yang ingin kami temukan dalam basis data (mis. Kode produk atau ID pelanggan). Pengidentifikasi unik ini harus ada di database, jika tidak VLOOKUP mengembalikan kesalahan kepada kami. Pada artikel ini, kami akan memeriksa cara menggunakan VLOOKUP di mana pengenal tidak perlu ada di 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 penghitungan komisi yang dihasilkan dari serangkaian angka penjualan. Kami akan mulai dengan skenario yang sangat sederhana, dan kemudian membuatnya semakin kompleks, sampai satu-satunya solusi rasional untuk masalah ini adalah dengan menggunakan VLOOKUP. Skenario awal di perusahaan fiktif kami berfungsi seperti ini: Jika seorang tenaga penjualan 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 ini adalah lembar kerja yang cukup sederhana:
Untuk menggunakan lembar kerja ini, tenaga penjualan 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 harus dibayar oleh tenaga penjual (yang adalah perkalian sederhana B1 dan B2).
Sel B2 berisi satu-satunya bagian yang menarik dari lembar kerja ini - formula untuk menentukan tingkat komisi yang akan digunakan: yang satu di bawah ambang batas $ 30.000, atau satu atas ambang batas. Formula ini memanfaatkan fungsi Excel yang disebut JIKA. Untuk pembaca yang tidak terbiasa dengan IF, kerjanya seperti ini:
JIKA(kondisi, nilai jika benar, nilai jika salah)
Dimana kondisi adalah ekspresi yang mengevaluasi keduanya benar atau Salah. Pada contoh di atas, kondisi adalah ekspresinya B1
Seperti yang Anda lihat, menggunakan total penjualan $ 20.000 memberi kami tingkat komisi 20% dalam sel B2. Jika kami memasukkan nilai $ 40.000, kami mendapat tarif komisi yang berbeda:
Jadi spreadsheet kami berfungsi.
Mari kita buat lebih rumit. Mari kita perkenalkan ambang kedua: Jika tenaga penjual menghasilkan lebih dari $ 40.000, maka tingkat komisi mereka meningkat hingga 40%:
Cukup mudah untuk dipahami di dunia nyata, tetapi di sel B2, rumus kami semakin kompleks. Jika Anda memperhatikan formula ini, Anda akan melihat bahwa parameter ketiga dari fungsi IF asli (the nilai jika salah) sekarang seluruh fungsi IF dalam dirinya sendiri. Ini disebut a fungsi bersarang (fungsi dalam suatu fungsi). Ini benar-benar valid di Excel (bahkan berfungsi!), Tetapi lebih sulit untuk dibaca dan dipahami.
Kita tidak akan membahas bagaimana dan mengapa ini bekerja, kita juga tidak akan memeriksa nuansa fungsi bersarang. Ini adalah tutorial tentang VLOOKUP, bukan pada Excel pada umumnya.
Lagi pula, itu semakin buruk! Bagaimana ketika kita memutuskan bahwa jika mereka menghasilkan lebih dari $ 50.000 maka mereka berhak atas komisi 50%, dan jika mereka menghasilkan lebih dari $ 60.000 maka mereka berhak atas komisi 60%?
Sekarang rumus di sel B2, meskipun benar, telah menjadi hampir tidak dapat dibaca. Tidak seorang pun harus menulis formula di mana fungsinya bersarang sedalam empat level! Tentunya harus ada cara yang lebih sederhana?
Pasti 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 datar cara:
Luangkan waktu sejenak dan verifikasi sendiri bahwa itu baru Tabel Tingkat bekerja persis sama dengan rangkaian ambang di atas.
Secara konseptual, apa yang akan kami lakukan adalah menggunakan VLOOKUP untuk mencari total penjualan tenaga penjualan (dari B1) di tabel tarif dan mengembalikan tarif komisi yang sesuai kepada kami. Perhatikan bahwa penjual mungkin memang menciptakan penjualan tidak salah satu dari lima nilai dalam 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 melakukannya tidak muncul di tabel tingkat. Mari kita lihat apakah VLOOKUP dapat menyelesaikan masalah kita ...
Kami memilih sel B2 (lokasi kami ingin meletakkan formula kami), dan kemudian menyisipkan fungsi VLOOKUP dari Formula 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 jendela Nilai lookup bidang lalu klik sekali pada sel B1:
Selanjutnya kita perlu menentukan untuk VLOOKUP tabel apa untuk mencari data ini. Dalam contoh ini, ini adalah tabel tingkat, tentu saja. Kami menempatkan kursor di jendela Table_array bidang, dan kemudian sorot seluruh tabel tingkat - tidak termasuk judul:
Selanjutnya kita harus menentukan kolom mana dalam tabel yang berisi informasi yang kita inginkan rumus kita kembali kepada kita. Dalam hal ini kami ingin tingkat komisi, yang ditemukan di kolom kedua dalam tabel, jadi kami memasukkan a 2 ke dalam Col_index_num bidang:
Akhirnya kami memasukkan nilai dalam Range_lookup bidang.
Penting: Penggunaan bidang ini yang membedakan dua cara menggunakan VLOOKUP. Untuk menggunakan VLOOKUP dengan database, parameter terakhir ini, Range_lookup, harus selalu diatur ke SALAH, tetapi dengan penggunaan VLOOKUP ini yang lain, kita harus membiarkannya kosong atau memasukkan nilai BENAR. Saat menggunakan VLOOKUP, sangat penting bagi Anda untuk membuat pilihan yang benar untuk parameter akhir ini.
Secara eksplisit, kami akan memasukkan nilai benar dalam Range_lookup bidang. Anda juga boleh membiarkannya kosong, karena ini adalah nilai default:
Kami telah menyelesaikan semua parameter. Kami sekarang klik baik tombol, dan Excel membuat formula VLOOKUP kami untuk kami:
Jika kami bereksperimen dengan beberapa jumlah total penjualan yang berbeda, kami dapat memuaskan diri sendiri bahwa formula ini berfungsi.
Kesimpulan
Dalam versi "database" VLOOKUP, di mana Range_lookup parameternya adalah SALAH, nilai yang diteruskan dalam parameter pertama (Nilai lookup) harus hadir dalam database. Dengan kata lain, kami sedang mencari pasangan yang tepat.
Namun dalam penggunaan VLOOKUP lainnya ini, kami tidak harus mencari pasangan yang tepat. Dalam hal ini, "cukup dekat cukup baik". Tetapi apa yang kita maksudkan dengan "cukup dekat"? Mari kita gunakan sebuah contoh: Ketika mencari tingkat komisi pada total penjualan $ 34.988, formula VLOOKUP kami akan mengembalikan nilai 30%, yang merupakan jawaban yang benar. Mengapa memilih baris dalam tabel yang mengandung 30%? Apa, sebenarnya, yang dimaksud "cukup dekat" dalam kasus ini? Mari kita tepat:
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 agar sistem ini berfungsi, tabel harus disortir dalam urutan menaik pada kolom 1!
Jika Anda ingin berlatih dengan VLOOKUP, file sampel yang diilustrasikan dalam artikel ini dapat diunduh dari sini.
