Dahulu kala, saya bekerja untuk wakil presiden penjualan di sebuah perusahaan. Saya selalu memodelkan beberapa program bonus atau rencana komisi baru. Saya menjadi cukup terbiasa dengan rencana komisi dengan segala macam kondisi. Yang ditunjukkan di tip ini cukup jinak.
Pendekatan normal adalah mulai membangun rumus IF bertingkat. Anda selalu mulai dari ujung atas atau bawah dari kisaran. “Jika penjualan lebih dari $ 500K, maka diskonnya adalah 20%; jika tidak,… ." Argumen ketiga dari fungsi IF adalah fungsi IF yang benar-benar baru yang menguji tingkat kedua: "Jika penjualan melebihi $ 250K, maka diskonnya adalah 15%; jika tidak,….”
Rumus ini menjadi lebih lama dan lebih lama karena ada lebih banyak level. Bagian tersulit dari rumus semacam itu adalah mengingat berapa banyak tanda kurung penutup yang harus diletakkan di akhir rumus.
Jika Anda menggunakan Excel 2003, rumus Anda sudah mendekati batas. Dengan versi tersebut, Anda tidak dapat menumpuk lebih dari 7 fungsi IF. Itu adalah hari yang buruk ketika kekuatan yang diubah rencana komisi dan Anda membutuhkan cara untuk menambahkan fungsi IF kedelapan. Hari ini, Anda dapat menumpuk 64 fungsi IF. Anda tidak boleh membuat sarang sebanyak itu, tetapi senang mengetahui tidak ada masalah membuat sarang 8 atau 9.
Daripada menggunakan fungsi IF bertumpuk, coba gunakan fungsi VLOOKUP. Saat argumen keempat VLOOKUP berubah dari False menjadi True, fungsinya tidak lagi mencari kecocokan persis. Nah, VLOOKUP pertama kali mencoba menemukan kecocokan persis. Namun jika pencocokan persis tidak ditemukan, Excel berada di baris yang kurang dari yang Anda cari.
Perhatikan tabel di bawah ini. Di sel C13, Excel akan mencari kecocokan untuk $ 28.355 di tabel. Jika tidak dapat menemukan 28355, Excel akan mengembalikan diskon yang terkait dengan nilai yang kurang-dalam hal ini, diskon 1% untuk level $ 10K.
Saat Anda mengubah aturan ke tabel di E13: F18, Anda harus memulai dari level terkecil dan melanjutkan ke level tertinggi. Meskipun tidak disebutkan dalam aturan, jika penjualan seseorang di bawah $ 10.000, diskonnya akan menjadi 0%. Anda perlu menambahkan ini sebagai baris pertama pada tabel.
Peringatan
Saat Anda menggunakan versi VLOOKUP "True", tabel Anda harus diurutkan dari atas ke bawah. Banyak orang percaya bahwa semua tabel pencarian harus diurutkan. Tapi tabel perlu diurutkan hanya untuk perkiraan kecocokan.
Bagaimana jika manajer Anda menginginkan formula yang lengkap dan tidak ingin melihat tabel bonus di sebelah kanan? Setelah menyusun rumus, Anda dapat menyematkan tabel langsung ke dalam rumus. Letakkan rumus dalam mode Edit dengan mengklik ganda sel atau dengan memilih sel dan menekan F2. Gunakan kursor untuk memilih seluruh argumen kedua: $ E $ 13: $ F $ 18.
Tekan tombol F9. Excel menyematkan tabel pencarian sebagai konstanta array. Dalam konstanta array, titik koma menunjukkan baris baru, dan koma menunjukkan kolom baru. Lihat Memahami Konstanta Array.
Tekan enter. Salin rumus ke sel lain.
Sekarang Anda dapat menghapus tabel. Rumus terakhir ditunjukkan di bawah ini.
Terima kasih kepada Mike Girvin karena telah mengajari saya tentang pencocokan tanda kurung. Teknik VLOOKUP disarankan oleh Danny Mac, Boriana Petrova, Andreas Thehos, dan @mvmcos.
