Konten
Kami baru-baru ini mendedikasikan sebuah artikel ke salah satu fungsi Excel paling berguna yang disebut VPR dan menunjukkan bagaimana itu dapat digunakan untuk mengekstrak informasi yang diperlukan dari database ke dalam sel lembar kerja. Kami juga menyebutkan bahwa ada dua kasus penggunaan untuk fungsi tersebut VPR dan hanya satu dari mereka yang berurusan dengan kueri basis data. Dalam artikel ini, Anda akan mempelajari cara lain yang kurang dikenal untuk menggunakan fungsi tersebut VPR di Excel
Jika Anda belum melakukannya, pastikan untuk membaca artikel terakhir tentang fungsinya VPR, karena semua informasi di bawah ini mengasumsikan bahwa Anda sudah terbiasa dengan prinsip-prinsip yang dijelaskan dalam artikel pertama.
Saat bekerja dengan database, fungsi VPR pengidentifikasi unik dilewatkan, yang digunakan untuk mengidentifikasi informasi yang ingin kami temukan (misalnya, kode produk atau nomor identifikasi pelanggan). Kode unik ini harus ada di database, jika tidak VPR akan melaporkan kesalahan. Pada artikel ini, kita akan melihat cara menggunakan fungsi ini VPRketika id tidak ada di database sama sekali. Seolah-olah fungsinya VPR beralih ke mode perkiraan, dan memilih data apa yang akan diberikan kepada kami ketika kami ingin menemukan sesuatu. Dalam keadaan tertentu, inilah yang dibutuhkan.
Contoh dari kehidupan. Kami mengatur tugas
Mari kita ilustrasikan artikel ini dengan contoh nyata – menghitung komisi berdasarkan berbagai metrik penjualan. Kami akan mulai dengan opsi yang sangat sederhana, dan kemudian kami akan secara bertahap memperumitnya sampai satu-satunya solusi rasional untuk masalah ini adalah menggunakan fungsi VPR. Skenario awal untuk tugas fiktif kami adalah sebagai berikut: jika seorang tenaga penjualan menghasilkan lebih dari $30000 dalam penjualan dalam setahun, maka komisinya adalah 30%. Jika tidak, komisi hanya 20%. Mari kita taruh dalam bentuk tabel:
Penjual memasukkan data penjualan mereka di sel B1, dan rumus di sel B2 menentukan tingkat komisi yang benar yang dapat diharapkan penjual. Pada gilirannya, tarif yang dihasilkan digunakan di sel B3 untuk menghitung total komisi yang harus diterima penjual (cukup dengan mengalikan sel B1 dan B2).
Bagian paling menarik dari tabel terdapat di sel B2 – ini adalah rumus untuk menentukan tarif komisi. Rumus ini berisi fungsi Excel yang disebut IF (JIKA). Bagi pembaca yang belum mengetahui fungsi ini, saya akan menjelaskan cara kerjanya:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Kondisi adalah argumen fungsi yang mengambil nilai salah satu dari KODE BENAR (Benar atau SALAH (SALAH). Dalam contoh di atas, ekspresi B1
Benarkah B1 lebih kecil dari B5?
Atau Anda dapat mengatakannya secara berbeda:
Benarkah jumlah total penjualan selama setahun lebih kecil dari nilai ambang batas?
Jika kita menjawab pertanyaan ini YES (TRUE), maka fungsi kembali nilai jika benar (nilai jika TRUE). Dalam kasus kami, ini akan menjadi nilai sel B6, yaitu tingkat komisi ketika total penjualan di bawah ambang batas. Jika kita menjawab pertanyaan TIDAK (FALSE) lalu kembali nilai jika salah (nilai jika SALAH). Dalam kasus kami, ini adalah nilai sel B7, yaitu tingkat komisi ketika total penjualan di atas ambang batas.
Seperti yang Anda lihat, jika kita mengambil total penjualan $20000, kita mendapatkan tingkat komisi 2% di sel B20. Jika kita memasukkan nilai $40000, maka tarif komisi akan berubah sebesar 30%:
Beginilah cara meja kami bekerja.
Kami memperumit tugas
Mari kita membuat hal-hal sedikit lebih sulit. Mari kita tetapkan ambang batas lain: jika penjual menghasilkan lebih dari $40000, maka tingkat komisi meningkat menjadi 40%:
Semuanya tampak sederhana dan jelas, tetapi rumus kita di sel B2 menjadi lebih rumit. Jika Anda melihat lebih dekat pada rumus, Anda akan melihat bahwa argumen ketiga dari fungsi IF (JIKA) berubah menjadi fungsi lengkap lainnya IF (JIKA). Konstruksi ini disebut fungsi bersarang satu sama lain. Excel dengan senang hati mengizinkan konstruksi ini, dan mereka bahkan berfungsi, tetapi mereka jauh lebih sulit untuk dibaca dan dipahami.
Kami tidak akan menyelidiki detail teknis – mengapa dan bagaimana cara kerjanya, dan kami tidak akan membahas nuansa penulisan fungsi bersarang. Bagaimanapun, ini adalah artikel yang didedikasikan untuk fungsi VPR, bukan panduan lengkap untuk Excel.
Apapun masalahnya, rumusnya menjadi lebih rumit! Bagaimana jika kami memperkenalkan opsi lain untuk tingkat komisi 50% untuk penjual yang menghasilkan lebih dari $ 50000 dalam penjualan. Dan jika seseorang telah menjual lebih dari $60000, apakah mereka akan membayar komisi 60%?
Sekarang rumus di sel B2, meskipun ditulis tanpa kesalahan, sama sekali tidak dapat dibaca. Saya pikir ada beberapa yang ingin menggunakan formula dengan 4 tingkat bersarang dalam proyek mereka. Pasti ada cara yang lebih mudah?!
Dan ada cara seperti itu! Fungsinya akan membantu kita VPR.
Kami menerapkan fungsi VLOOKUP untuk menyelesaikan masalah
Mari kita ubah sedikit desain meja kita. Kami akan menyimpan semua bidang dan data yang sama, tetapi mengaturnya dengan cara baru yang lebih ringkas:
Luangkan waktu sejenak dan pastikan meja baru Tabel Tarif menyertakan data yang sama dengan tabel ambang sebelumnya.
Ide utamanya adalah menggunakan fungsi VPR untuk menentukan tarif tarif yang diinginkan sesuai tabel Tabel Tarif tergantung pada volume penjualan. Harap dicatat bahwa penjual dapat menjual barang dengan jumlah yang tidak sama dengan salah satu dari lima ambang batas dalam tabel. Misalnya, dia bisa menjual seharga $34988, tetapi tidak ada jumlah seperti itu. Mari kita lihat bagaimana fungsinya VPR dapat menghadapi situasi seperti itu.
Memasukkan fungsi VLOOKUP
Pilih sel B2 (tempat kita ingin memasukkan rumus kita) dan temukan VLOOKUP (VLOOKUP) di Pustaka Fungsi Excel: Rumus (rumus) > Fungsi perpustakaan (Perpustakaan Fungsi) > Pencarian & Referensi (Referensi dan array).
Sebuah kotak dialog muncul Argumen Fungsi (Argumen fungsi). Kami mengisi nilai argumen satu per satu, dimulai dengan Nilai lookup (Nilai lookup). Dalam contoh ini, ini adalah jumlah total penjualan dari sel B1. Letakkan kursor di bidang Nilai lookup (Lookup_value) dan pilih sel B1.
Selanjutnya, Anda perlu menentukan fungsinya VPRtempat untuk mencari data. Dalam contoh kita, ini adalah tabel Tabel Tarif. Letakkan kursor di bidang Tabel_array (Tabel) dan pilih seluruh tabel Tabel Tarifkecuali header.
Selanjutnya, kita perlu menentukan kolom mana yang akan mengekstrak data menggunakan rumus kita. Kami tertarik pada tingkat komisi, yang ada di kolom kedua tabel. Oleh karena itu, untuk argumen col_index_number (Column_number) masukkan nilai 2.
Dan akhirnya, kami memperkenalkan argumen terakhir – Rentang_pencarian (Pencarian_Interval).
Penting: penggunaan argumen inilah yang membuat perbedaan antara dua cara penerapan fungsi VPR. Saat bekerja dengan database, argumennya Rentang_pencarian (range_lookup) harus selalu memiliki nilai SALAH (FALSE) untuk mencari kecocokan yang sama persis. Dalam penggunaan fungsi kami VPR, kita harus mengosongkan bidang ini, atau memasukkan nilai KODE BENAR (BENAR). Sangat penting untuk memilih opsi ini dengan benar.
Untuk membuatnya lebih jelas, kami akan memperkenalkan KODE BENAR (BENAR) di lapangan Rentang_pencarian (Interval_lookup). Meskipun, jika Anda mengosongkan bidang, ini tidak akan menjadi kesalahan, karena KODE BENAR adalah nilai defaultnya:
Kami telah mengisi semua parameter. Sekarang kita tekan OK, dan Excel membuat rumus untuk kita dengan fungsi VPR.
Jika kita bereksperimen dengan beberapa nilai yang berbeda untuk jumlah total penjualan, maka kita akan memastikan bahwa rumus tersebut bekerja dengan benar.
Kesimpulan
Ketika fungsinya VPR bekerja dengan database, argumen Rentang_pencarian (range_lookup) harus menerima SALAH (SALAH). Dan nilai yang dimasukkan sebagai Nilai lookup (Lookup_value) harus ada di database. Dengan kata lain, ini mencari kecocokan yang tepat.
Dalam contoh yang telah kita lihat di artikel ini, tidak perlu mendapatkan kecocokan persis. Ini adalah kasus ketika fungsi VPR harus beralih ke mode perkiraan untuk mengembalikan hasil yang diinginkan.
Sebagai contoh: Kami ingin menentukan tarif apa yang akan digunakan dalam perhitungan komisi untuk tenaga penjualan dengan volume penjualan $34988. Fungsi VPR mengembalikan nilai 30% kepada kami, yang sepenuhnya benar. Tetapi mengapa rumus memilih baris yang mengandung persis 30% dan bukan 20% atau 40%? Apa yang dimaksud dengan pencarian perkiraan? Mari kita menjadi jelas.
Ketika argumen Rentang_pencarian (interval_lookup) memiliki nilai KODE BENAR (TRUE) atau dihilangkan, fungsi VPR iterasi melalui kolom pertama dan memilih nilai terbesar yang tidak melebihi nilai pencarian.
Poin penting: Agar skema ini berfungsi, kolom pertama tabel harus diurutkan dalam urutan menaik.