VLOOKUP di Excel, bagian 2: Menggunakan VLOOKUP tanpa database

Dalam sebuah artikel baru-baru ini, kami memperkenalkan fungsi Excel yang disebut VLOOKUP dan menjelaskan bagaimana hal itu dapat digunakan untuk mengambil informasi dari database ke dalam sel di lembar kerja lokal. Dalam artikel tersebut kami menyebutkan bahwa ada dua kegunaan untuk VLOOKUP, dan hanya satu dari mereka yang menangani query database. Pada artikel ini, yang kedua dan terakhir dalam seri VLOOKUP, kami memeriksa penggunaan lain yang kurang dikenal untuk fungsi VLOOKUP ini.

Jika Anda belum melakukannya, bacalah artikel VLOOKUP yang pertama - artikel ini akan mengasumsikan bahwa banyak konsep yang dijelaskan dalam artikel tersebut sudah diketahui pembaca.

Saat bekerja dengan database, VLOOKUP dilewatkan sebagai "pengenal unik" yang berfungsi untuk mengidentifikasi data record mana yang ingin kami temukan di database( misalnya kode produk atau ID pelanggan).Pengenal unik harus ada di database, jika VLOOKUP mengembalikan kesalahan kepada kami. Pada artikel ini, kita akan memeriksa cara menggunakan VLOOKUP dimana identifier tidak perlu ada di database sama sekali. Ini hampir seolah-olah VLOOKUP dapat mengadopsi pendekatan "cukup dekat cukup bagus" untuk mengembalikan data yang kami cari. Dalam keadaan tertentu, ini adalah

persis apa yang kita butuhkan.

Kami akan mengilustrasikan artikel ini dengan contoh dunia nyata - yaitu menghitung komisi yang dihasilkan dari serangkaian angka penjualan. Kita akan memulai dengan skenario yang sangat sederhana, dan kemudian membuatnya semakin rumit, sampai solusi rasional hanya masalahnya adalah dengan menggunakan VLOOKUP.Skenario awal di perusahaan fiktif kami bekerja seperti ini: Jika seorang penjual menghasilkan lebih dari $ 30.000 penjualan pada tahun tertentu, komisi yang mereka dapatkan pada penjualan tersebut adalah 30%.Jika tidak komisi mereka hanya 20%.Sejauh ini adalah lembar kerja yang cukup sederhana:

Untuk menggunakan lembar kerja ini, tenaga penjualan memasukkan angka penjualan mereka di sel B1, dan formula di sel B2 menghitung tingkat komisi yang benar yang berhak mereka terima, yang digunakan di sel B3 untuk menghitungtotal komisi yang menjadi wiraniaga terutang( yang merupakan perkalian sederhana antara B1 dan B2).

Sel B2 berisi satu-satunya bagian yang menarik dari lembar kerja ini - formula untuk menentukan tingkat komisi yang akan digunakan: di bawah ambang batas $ 30.000, atau yang di atas ambang batas .Rumus ini menggunakan fungsi Excel yang disebut IF .Bagi pembaca yang tidak terbiasa dengan IF, bekerja seperti ini:

IF( kondisi , nilai jika benar, nilai jika salah)

Dimana kondisi adalah ungkapan yang mengevaluasi atau palsu. Pada contoh di atas, kondisi adalah ekspresi B1 & lt; B5 , yang dapat dibaca sebagai "Apakah B1 kurang dari B5?", Atau, dengan kata lain, "Apakah total penjualan kurang dari ambang batas".Jika jawaban untuk pertanyaan ini adalah "ya"( true), maka kita menggunakan nilai jika parameter benar fungsinya, yaitu B6 dalam kasus ini - tingkat komisi jika total penjualan di di bawah ambang batas .Jika jawaban untuk pertanyaan adalah "tidak"( false), maka kita menggunakan nilai jika parameter salah, yaitu B7 dalam kasus ini - tingkat komisi jika total penjualan di di atas ambang batas.

Seperti yang Anda lihat, dengan menggunakan jumlah penjualan sebesar $ 20.000 memberi tingkat komisi 20% di sel B2.Jika kita memasukkan nilai $ 40.000, kita mendapatkan tingkat komisi yang berbeda:

Jadi spreadsheet kita bekerja.

Mari kita membuatnya lebih kompleks. Mari kita kenalkan ambang kedua: Jika tenaga penjual menghasilkan lebih dari $ 40.000, tingkat komisi mereka meningkat sampai 40%:

Cukup mudah untuk dipahami di dunia nyata, namun di sel B2, formula kita semakin kompleks. Jika Anda melihat secara dekat rumusnya, Anda akan melihat bahwa parameter ketiga dari fungsi IF asli( nilai jika salah) sekarang merupakan keseluruhan fungsi IF dengan sendirinya. Ini disebut fungsi bersarang ( fungsi dalam fungsi).Ini sangat valid di Excel( bahkan berhasil!), Tapi lebih sulit dibaca dan dimengerti.

Kita tidak akan masuk ke mur dan baut bagaimana dan mengapa ini berhasil, dan kita juga tidak akan meneliti nuansa fungsi bersarang. Ini adalah tutorial tentang VLOOKUP, bukan di Excel pada umumnya.

Bagaimanapun, ini semakin parah! Bagaimana bila kita memutuskan bahwa jika mereka memperoleh lebih dari $ 50.000 maka mereka berhak mendapatkan komisi 50%, dan jika mereka memperoleh lebih dari $ 60.000 maka mereka berhak mendapatkan komisi 60%?

Sekarang formula di sel B2, meski benar, telah menjadi hampir tidak terbaca. Tidak ada yang harus menulis rumus dimana fungsinya bersarang empat level dalam! Tentunya pasti ada cara yang lebih sederhana?

Pasti ada. VLOOKUP untuk menyelamatkannya!

Mari kita mendesain ulang lembar kerja sedikit. Kami akan menyimpan semua angka yang sama, namun mengaturnya dengan cara baru, cara yang lebih luas:

Luangkan waktu sejenak dan verifikasi sendiri bahwa Tabel Tingkat yang baru bekerja persis sama dengan rangkaian ambang di atas.

Secara konseptual, apa yang akan kita lakukan adalah menggunakan VLOOKUP untuk mengetahui total penjualan tenaga penjual( dari B1) di tabel tarif dan mengembalikan tingkat komisi yang sesuai kepada kami. Perhatikan bahwa wiraniaga mungkin telah menciptakan penjualan yang bukan salah satu dari lima nilai dalam tabel tarif( $ 0, $ 30.000, $ 40.000, $ 50.000 atau $ 60.000).Mereka mungkin telah menciptakan penjualan sebesar $ 34.988.Penting untuk dicatat bahwa $ 34.988 tidak tidak muncul di tabel tarif. Mari kita lihat apakah VLOOKUP dapat menyelesaikan masalah kita lagi. ..

Kita memilih sel B2( lokasi yang ingin kita gunakan untuk formula kita), dan kemudian memasukkan fungsi VLOOKUP dari Formule

Kotak Argumen untuk VLOOKUP muncul. Kami mengisi argumen( parameter) satu per satu, dimulai dengan Lookup_value , yang dalam hal ini, jumlah penjualan dari sel B1.Kami menempatkan kursor di Lookup_value lapangan dan kemudian klik sekali pada sel B1:

Selanjutnya kita perlu menentukan ke VLOOKUP tabel apa untuk mencari data ini. Dalam contoh ini, ini adalah tabel tarif, tentu saja. Kami menempatkan kursor di bidang Table_array , lalu menyorot seluruh tabel tingkat - tidak termasuk judul :

Selanjutnya, kami harus menentukan kolom mana dalam tabel berisi informasi yang kami inginkan agar formula kami kembali kepada kami. Dalam kasus ini, kami menginginkan tingkat komisi, yang ditemukan di kolom kedua dalam tabel, jadi kami memasukkan 2 ke dalam field Col_index_num :

Akhirnya, kami memasukkan nilai di bidang Range_lookup .

Penting: Ini adalah penggunaan bidang ini yang membedakan dua cara menggunakan VLOOKUP.Untuk menggunakan VLOOKUP dengan database, parameter akhir ini, Range_lookup , harus selalu diset ke FALSE , namun dengan penggunaan VLOOKUP lainnya, kita harus membiarkannya kosong atau memasukkan nilai TRUE .Saat menggunakan VLOOKUP, penting bagi Anda untuk membuat pilihan yang tepat untuk parameter akhir ini.

Secara eksplisit, kita akan memasukkan nilai yang benar di bidang Range_lookup .Sebaiknya biarkan kosong, karena ini adalah nilai default:

Kami telah menyelesaikan semua parameternya. Sekarang kita klik tombol OK , dan Excel membuat formula VLOOKUP untuk kita:

Jika kita bereksperimen dengan beberapa jumlah penjualan yang berbeda, kita dapat memuaskan diri kita sendiri bahwa formula tersebut bekerja.

Kesimpulan

Dalam versi "database" dari VLOOKUP, di mana Range_lookup parameter adalah FALSE , nilai yang dilewatkan pada parameter pertama( Lookup_value ) harus ada di database. Dengan kata lain, kami mencari pencocokan tepat.

Tapi dalam penggunaan VLOOKUP yang lain ini, kita belum tentu mencari pencocokan sama persis. Dalam kasus ini, "cukup dekat sudah cukup baik".Tapi apa yang kita maksud dengan "cukup dekat"?Mari kita gunakan sebuah contoh: Saat mencari tingkat komisi dengan total penjualan sebesar $ 34,988, rumus VLOOKUP kami akan mengembalikan nilai 30%, yaitu jawaban yang benar. Mengapa memilih baris dalam tabel yang berisi 30%?Apa sebenarnya "cukup dekat" dalam kasus ini? Mari kita tepat:

Ketika Range_lookup diatur ke TRUE ( atau diabaikan), VLOOKUP akan terlihat di kolom 1 dan cocok dengan nilai tertinggi yang tidak lebih besar dari parameter Lookup_value .

Penting juga untuk dicatat bahwa agar sistem ini dapat bekerja, tabel harus diurutkan berdasarkan urutan di kolom 1 !

Jika Anda ingin berlatih dengan VLOOKUP, file contoh yang diilustrasikan dalam artikel ini dapat didownload dari sini.