VLOOKUP dalam Excel, bahagian 2 Menggunakan VLOOKUP tanpa pangkalan data
Dalam artikel baru-baru ini, kami memperkenalkan fungsi Excel yang dipanggil VLOOKUP dan menjelaskan bagaimana ia dapat digunakan untuk mendapatkan maklumat dari pangkalan data ke dalam sel dalam lembaran kerja setempat. Dalam artikel itu kita menyebutkan bahawa terdapat dua kegunaan untuk VLOOKUP, dan hanya satu dari mereka yang menangani permintaan pangkalan data. Dalam artikel ini, yang kedua dan terakhir dalam siri VLOOKUP, kami mengkaji ini yang lain, penggunaan yang kurang dikenali untuk fungsi VLOOKUP.
Jika anda belum melakukannya, sila baca artikel VLOOKUP yang pertama - artikel ini akan mengandaikan bahawa banyak konsep yang dijelaskan dalam artikel itu sudah diketahui oleh pembaca.
Apabila bekerja dengan pangkalan data, VLOOKUP diluluskan sebagai "pengecam unik" yang berfungsi untuk mengenal pasti rekod data yang ingin dicari dalam pangkalan data (cth. Kod produk atau ID pelanggan). Pengenal unik ini mestilah wujud dalam pangkalan data, jika tidak, VLOOKUP mengembalikan ralat kepada kami. Dalam artikel ini, kami akan mengkaji cara menggunakan VLOOKUP di mana pengecam tidak perlu wujud dalam pangkalan data sama sekali. Ia hampir seolah-olah VLOOKUP boleh mengamalkan pendekatan "cukup dekat cukup baik" untuk memulangkan data yang kami cari. Dalam keadaan tertentu, ini adalah betul-betul apa yang kita perlukan.
Kami akan menggambarkan artikel ini dengan contoh dunia sebenar - iaitu mengira komisen yang dihasilkan pada satu set angka jualan. Kami akan bermula dengan senario yang sangat mudah, dan kemudian secara progresif menjadikannya lebih kompleks, sehingga satu-satunya penyelesaian yang rasional terhadap masalahnya ialah menggunakan VLOOKUP. Senario awal dalam syarikat rekaan kami berfungsi seperti ini: Jika jurujual mencipta lebih daripada jualan bernilai $ 30,000 dalam satu tahun, komisen yang mereka perolehi pada jualan itu adalah 30%. Jika tidak, komisyen mereka hanya 20%. Setakat ini ini adalah lembaran kerja yang cukup mudah:
Untuk menggunakan lembaran kerja ini, jurujual memasuki angka jualan mereka di dalam sel B1, dan formula dalam sel B2 mengira kadar komisen yang betul yang mereka terima, yang digunakan dalam sel B3 untuk mengira jumlah komisen yang dibayar oleh jurujual (yang adalah pendaraban mudah B1 dan B2).
B2 sel mengandungi satu-satunya bahagian yang menarik dari lembaran kerja ini - formula untuk menentukan kadar komisen yang hendak digunakan: yang satu di bawah ambang $ 30,000, atau yang satu di atas ambang. Formula ini menggunakan fungsi Excel yang dipanggil JIKA. Bagi pembaca yang tidak biasa dengan JIKA, ia berfungsi seperti ini:
JIKAkeadaan, nilai jika benar, nilai jika salah)
Dimanakah keadaan adalah ungkapan yang menilai sama ada benar atau salah. Dalam contoh di atas, keadaan adalah ungkapan B1
Seperti yang anda lihat, dengan menggunakan jumlah jualan sebanyak $ 20,000 memberi kami kadar komisen sebanyak 20% dalam sel B2. Sekiranya kita memasukkan nilai $ 40,000, kita akan mendapat kadar komisen yang berbeza:
Jadi spreadsheet kami berfungsi.
Mari buat lebih kompleks. Mari kita memperkenalkan ambang kedua: Jika jurujual mendapat lebih daripada $ 40,000, maka kadar komisen mereka meningkat kepada 40%:
Cukup mudah untuk difahami di dunia nyata, tetapi dalam sel B2 formula kami semakin kompleks. Jika anda melihat dengan teliti formula, anda akan melihat bahawa parameter ketiga fungsi asal IF (yang nilai jika salah) kini merupakan fungsi IF keseluruhan dalam haknya sendiri. Ini dipanggil a fungsi bersarang (fungsi dalam fungsi). Ia sah dalam Excel (walaupun berfungsi!), Tetapi sukar untuk dibaca dan difahami.
Kami tidak akan masuk ke dalam kacang-kacangan dan baut tentang bagaimana dan mengapa ini berfungsi, dan tidak akan kita periksa nuansa fungsi bersarang. Ini adalah tutorial mengenai VLOOKUP, bukan pada Excel pada umumnya.
Bagaimanapun, ia semakin teruk! Bagaimana pula apabila kami memutuskan bahawa jika mereka memperoleh lebih daripada $ 50,000 maka mereka berhak mendapat komisen 50%, dan jika mereka memperolehi lebih dari $ 60,000 maka mereka berhak untuk komisyen 60%?
Sekarang formula dalam sel B2, sementara yang betul, telah menjadi hampir tidak boleh dibaca. Tidak ada seorang pun yang mesti menulis formula di mana fungsi-fungsi itu bersarang empat peringkat mendalam! Sesungguhnya mesti ada cara yang lebih mudah?
Pasti ada. VLOOKUP untuk menyelamatkan!
Mari ubah semula lembaran kerja sedikit. Kami akan menyimpan semua angka yang sama, tetapi mengaturnya dengan cara yang baru, lebih banyak lagi jadual cara:
Luangkan masa dan pastikan diri anda sendiri yang baru Jadual Kadar berfungsi sama seperti siri ambang di atas.
Secara konseptual, apa yang akan kita lakukan ialah menggunakan VLOOKUP untuk melihat jumlah jualan jurujual (dari B1) dalam jadual kadar dan kembali kepada kami kadar komisen yang sepadan. Perhatikan bahawa jurujual mungkin telah mencipta jualan yang sebenarnya tidak salah satu daripada lima nilai dalam jadual kadar ($ 0, $ 30,000, $ 40,000, $ 50,000 atau $ 60,000). Mereka mungkin telah mencipta jualan sebanyak $ 34,988. Perlu diketahui bahawa $ 34,988 tidak tidak muncul dalam jadual kadar. Mari lihat sama ada VLOOKUP boleh menyelesaikan masalah kami ...
Kami pilih sel B2 (lokasi yang kami mahu letakkan formula kami), dan kemudian masukkan fungsi VLOOKUP dari Formula tab:
The Argumen Fungsi kotak untuk VLOOKUP muncul. Kami mengisi argumen (parameter) satu demi satu, bermula dengan Lookup_value, iaitu, dalam kes ini, jumlah jualan dari sel B1. Kami letakkan kursor di Lookup_value bidang dan kemudian klik sekali pada sel B1:
Seterusnya kita perlu nyatakan ke VLOOKUP apa jadual untuk mencari data ini. Dalam contoh ini, ia adalah jadual kadar, sudah tentu. Kami letakkan kursor di Table_array bidang, dan kemudian menyerlahkan keseluruhan jadual kadar - tidak termasuk tajuk:
Seterusnya kita mesti nyatakan lajur mana dalam jadual mengandungi maklumat yang kami mahu formula kami kembali kepada kami. Dalam kes ini kita mahu kadar komisen, yang terdapat dalam lajur kedua dalam jadual, jadi kami memasuki 2 ke dalam Col_index_num bidang:
Akhirnya kami memasukkan nilai dalam Range_lookup bidang.
Penting: Ia adalah penggunaan medan ini yang membezakan dua cara menggunakan VLOOKUP. Untuk menggunakan VLOOKUP dengan pangkalan data, parameter akhir ini, Range_lookup, mesti sentiasa ditetapkan SALAH, tetapi dengan penggunaan lain VLOOKUP, kita harus meninggalkannya kosong atau memasukkan nilai BENAR. Apabila menggunakan VLOOKUP, adalah penting bahawa anda membuat pilihan yang betul untuk parameter akhir ini.
Untuk menjadi jelas, kami akan memasukkan nilai benar di dalam Range_lookup bidang. Ia juga boleh dibiarkan kosong, kerana ini adalah nilai lalai:
Kami telah menyelesaikan semua parameter. Kami kini mengklik okey butang, dan Excel membina formula VLOOKUP kami untuk kami:
Jika kita bereksperimen dengan beberapa jumlah jualan yang berbeza, kita dapat memuaskan diri kita bahawa formula itu berfungsi.
Kesimpulannya
Dalam versi "pangkalan data" VLOOKUP, di mana Range_lookup parameter ialah SALAH, nilai yang diluluskan dalam parameter pertama (Lookup_value) mestilah hadir dalam pangkalan data. Dengan kata lain, kami sedang mencari perlawanan tepat.
Tetapi dalam penggunaan lain VLOOKUP, kami tidak semestinya mencari perlawanan tepat. Dalam kes ini, "cukup dekat cukup baik". Tetapi apa yang kita maksudkan dengan "cukup dekat"? Mari kita gunakan contoh: Apabila mencari kadar komisen pada jumlah jualan $ 34,988, formula VLOOKUP kami akan mengembalikan nilai 30%, yang merupakan jawapan yang betul. Mengapa ia memilih baris dalam jadual yang mengandungi 30%? Apa sebenarnya, "cukup dekat" dalam kes ini? Baiklah:
Bila Range_lookup ditetapkan untuk BENAR (atau ditinggalkan), VLOOKUP akan melihat dalam lajur 1 dan perlawanan nilai tertinggi yang tidak lebih besar daripada yang Lookup_value parameter.
Ia juga penting untuk diperhatikan bahawa untuk sistem ini berfungsi, jadual mesti disusun dalam urutan menaik pada lajur 1!
Jika anda ingin mengamalkan dengan VLOOKUP, fail sampel yang digambarkan dalam artikel ini boleh dimuat turun dari sini.
