Contents [show]
Kami baru-baru ini mendedikasikan artikel kepada salah satu fungsi Excel paling berguna yang dipanggil VPR dan menunjukkan cara ia boleh digunakan untuk mengekstrak maklumat yang diperlukan daripada pangkalan data ke dalam sel lembaran kerja. Kami juga menyebut bahawa terdapat dua kes penggunaan untuk fungsi tersebut VPR dan hanya satu daripada mereka berurusan dengan pertanyaan pangkalan data. Dalam artikel ini, anda akan mempelajari satu lagi cara yang kurang dikenali untuk menggunakan fungsi tersebut VPR dalam Excel.
Jika anda belum melakukan ini lagi, maka pastikan anda membaca artikel terakhir tentang fungsi tersebut VPR, kerana semua maklumat di bawah menganggap bahawa anda sudah biasa dengan prinsip yang diterangkan dalam artikel pertama.
Apabila bekerja dengan pangkalan data, berfungsi VPR pengecam unik diluluskan, yang digunakan untuk mengenal pasti maklumat yang ingin kami cari (contohnya, kod produk atau nombor pengenalan pelanggan). Kod unik ini mesti ada dalam pangkalan data, jika tidak VPR akan melaporkan ralat. Dalam artikel ini, kita akan melihat cara menggunakan fungsi ini VPRapabila id tidak wujud dalam pangkalan data sama sekali. Seolah-olah fungsi VPR bertukar kepada mod anggaran dan memilih data yang hendak diberikan kepada kami apabila kami ingin mencari sesuatu. Dalam keadaan tertentu, inilah yang diperlukan.
Contoh dari kehidupan. Kami menetapkan tugas
Mari kita ilustrasikan artikel ini dengan contoh kehidupan sebenar – mengira komisen berdasarkan pelbagai metrik jualan. Kami akan mulakan dengan pilihan yang sangat mudah, dan kemudian kami akan merumitkannya secara beransur-ansur sehingga satu-satunya penyelesaian rasional untuk masalah itu ialah menggunakan fungsi VPR. Senario awal untuk tugas rekaan kami adalah seperti berikut: jika jurujual membuat jualan lebih daripada $30000 dalam setahun, maka komisennya ialah 30%. Jika tidak, komisen hanya 20%. Mari letakkannya dalam bentuk jadual:
Penjual memasukkan data jualan mereka dalam sel B1 dan formula dalam sel B2 menentukan kadar komisen yang betul yang boleh dijangkakan oleh penjual. Sebaliknya, kadar yang terhasil digunakan dalam sel B3 untuk mengira jumlah komisen yang sepatutnya diterima oleh penjual (hanya mendarabkan sel B1 dan B2).
Bahagian jadual yang paling menarik terkandung dalam sel B2 - ini adalah formula untuk menentukan kadar komisen. Formula ini mengandungi fungsi Excel yang dipanggil IF (JIKA). Bagi pembaca yang tidak biasa dengan fungsi ini, saya akan menerangkan cara ia berfungsi:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Keadaan hartanah ialah hujah fungsi yang mengambil nilai sama ada KOD BENAR (BENAR), atau SALAH (SALAH). Dalam contoh di atas, ungkapan B1
Adakah benar B1 kurang daripada B5?
Atau anda boleh mengatakannya secara berbeza:
Adakah benar jumlah jualan bagi tahun tersebut adalah kurang daripada nilai ambang?
Jika kita menjawab soalan ini YA (BENAR), kemudian fungsi itu kembali nilai jika benar (nilai jika BENAR). Dalam kes kami, ini akan menjadi nilai sel B6, iaitu kadar komisen apabila jumlah jualan berada di bawah ambang. Jika kita menjawab soalan TIDAK (PALSU) kemudian kembali nilai jika palsu (nilai jika SALAH). Dalam kes kami, ini ialah nilai sel B7, iaitu kadar komisen apabila jumlah jualan melebihi ambang.
Seperti yang anda lihat, jika kami mengambil jumlah jualan sebanyak $20000, kami mendapat kadar komisen 2% dalam sel B20. Jika kita memasukkan nilai $40000, maka kadar komisen akan berubah sebanyak 30%:
Beginilah cara meja kami berfungsi.
Kami merumitkan tugas
Mari kita membuat perkara lebih sukar. Mari kita tetapkan ambang lain: jika penjual memperoleh lebih daripada $40000, maka kadar komisen meningkat kepada 40%:
Segala-galanya nampaknya mudah dan jelas, tetapi formula kami dalam sel B2 menjadi lebih rumit. Jika anda melihat dengan teliti pada formula, anda akan melihat bahawa hujah ketiga fungsi IF (JIKA) bertukar menjadi satu lagi fungsi penuh IF (JIKA). Pembinaan ini dipanggil bersarang fungsi antara satu sama lain. Excel dengan senang hati membenarkan binaan ini, malah ia berfungsi, tetapi ia lebih sukar untuk dibaca dan difahami.
Kami tidak akan menyelidiki butiran teknikal – mengapa dan bagaimana ia berfungsi, dan kami tidak akan membincangkan nuansa penulisan fungsi bersarang. Lagipun, ini adalah artikel khusus untuk fungsi tersebut VPR, bukan panduan lengkap untuk Excel.
Walau apa pun, formulanya menjadi lebih rumit! Bagaimana jika kami memperkenalkan pilihan lain untuk kadar komisen 50% untuk penjual yang membuat jualan lebih daripada $50000. Dan jika seseorang telah menjual lebih daripada $60000, adakah mereka akan membayar 60% komisen?
Kini formula dalam sel B2, walaupun ia ditulis tanpa ralat, telah menjadi tidak boleh dibaca sepenuhnya. Saya fikir terdapat segelintir orang yang ingin menggunakan formula dengan 4 tahap sarang dalam projek mereka. Mesti ada cara yang lebih mudah?!
Dan ada cara sedemikian! Fungsi ini akan membantu kita VPR.
Kami menggunakan fungsi VLOOKUP untuk menyelesaikan masalah
Jom ubah sikit design meja kita. Kami akan menyimpan semua medan dan data yang sama, tetapi menyusunnya dengan cara baharu yang lebih padat:
Luangkan masa dan pastikan jadual baharu Jadual Kadar termasuk data yang sama seperti jadual ambang sebelumnya.
Idea utama ialah menggunakan fungsi tersebut VPR untuk menentukan kadar tarif yang dikehendaki mengikut jadual Jadual Kadar bergantung kepada jumlah jualan. Sila ambil perhatian bahawa penjual boleh menjual barangan untuk jumlah yang tidak sama dengan satu daripada lima ambang dalam jadual. Sebagai contoh, dia boleh menjual dengan harga $34988, tetapi tidak ada jumlah sedemikian. Mari lihat bagaimana fungsinya VPR boleh menghadapi situasi sedemikian.
Memasukkan fungsi VLOOKUP
Pilih sel B2 (di mana kami ingin memasukkan formula kami) dan cari VLOOKUP (VLOOKUP) dalam Pustaka Fungsi Excel: Formula (rumus) > Fungsi Perpustakaan (Perpustakaan Fungsi) > Carian & Rujukan (Rujukan dan tatasusunan).
Kotak dialog muncul Hujah Fungsi (Hujah fungsi). Kami mengisi nilai hujah satu demi satu, bermula dengan Cari_nilai (Lookup_value). Dalam contoh ini, ini ialah jumlah jualan daripada sel B1. Letakkan kursor di medan Cari_nilai (Lookup_value) dan pilih sel B1.
Seterusnya, anda perlu menentukan fungsi VPRmana nak cari data. Dalam contoh kami, ini ialah jadual Jadual Kadar. Letakkan kursor di medan Susunan_jadual (Jadual) dan pilih keseluruhan jadual Jadual Kadarkecuali untuk tajuk.
Seterusnya, kami perlu menentukan lajur mana untuk mengekstrak data daripada menggunakan formula kami. Kami berminat dengan kadar komisen, yang terdapat di lajur kedua jadual. Oleh itu, untuk hujah Col_index_num (Column_nomber) masukkan nilai 2.
Dan akhirnya, kami memperkenalkan hujah terakhir - Range_lookup (Interval_lookup).
penting: ia adalah penggunaan hujah ini yang membuat perbezaan antara dua cara menggunakan fungsi VPR. Apabila bekerja dengan pangkalan data, hujah Range_lookup (range_lookup) mesti sentiasa mempunyai nilai SALAH (PALSU) untuk mencari padanan tepat. Dalam penggunaan fungsi kami VPR, kita mesti biarkan medan ini kosong atau masukkan nilai KOD BENAR (BENAR). Sangat penting untuk memilih pilihan ini dengan betul.
Untuk menjadikannya lebih jelas, kami akan memperkenalkan KOD BENAR (BENAR) di lapangan Range_lookup (Interval_lookup). Walaupun, jika anda membiarkan medan kosong, ini tidak akan menjadi ralat, kerana KOD BENAR ialah nilai lalainya:
Kami telah mengisi semua parameter. Sekarang kita tekan OK, dan Excel mencipta formula untuk kita dengan fungsi VPR.
Jika kami bereksperimen dengan beberapa nilai berbeza untuk jumlah jualan, maka kami akan memastikan bahawa formula berfungsi dengan betul.
Kesimpulan
Apabila fungsi VPR berfungsi dengan pangkalan data, hujah Range_lookup (range_lookup) mesti menerima SALAH (SALAH). Dan nilai dimasukkan sebagai Cari_nilai (Lookup_value) mesti wujud dalam pangkalan data. Dalam erti kata lain, ia sedang mencari padanan yang tepat.
Dalam contoh yang telah kita lihat dalam artikel ini, tidak perlu mendapatkan padanan tepat. Ini adalah kes apabila fungsi VPR mesti bertukar kepada mod anggaran untuk mengembalikan hasil yang diingini.
Sebagai contoh: Kami ingin menentukan kadar yang akan digunakan dalam pengiraan komisen untuk jurujual dengan volum jualan $34988. Fungsi VPR mengembalikan kita nilai 30%, yang betul-betul betul. Tetapi mengapa formula memilih baris yang mengandungi tepat 30% dan bukan 20% atau 40%? Apakah yang dimaksudkan dengan carian anggaran? Biar jelas.
Apabila hujah Range_lookup (interval_lookup) mempunyai nilai KOD BENAR (BENAR) atau ditinggalkan, fungsi VPR berulang melalui lajur pertama dan memilih nilai terbesar yang tidak melebihi nilai carian.
Titik penting: Untuk skema ini berfungsi, lajur pertama jadual mesti diisih dalam tertib menaik.