Dulu, saya bekerja sebagai naib presiden penjualan di sebuah syarikat. Saya selalu memodelkan beberapa program bonus atau rancangan komisen baru. Saya menjadi biasa digunakan untuk membuat rancangan dengan pelbagai keadaan. Yang ditunjukkan dalam petua ini cukup jinak.
Pendekatan biasa adalah mula membina formula IF bersarang. Anda selalu bermula di hujung tinggi atau rendah. "Sekiranya penjualan melebihi $ 500K, maka diskaun adalah 20%; jika tidak,…. ” Argumen ketiga fungsi IF adalah fungsi IF baru yang menguji tahap kedua: "Sekiranya penjualan melebihi $ 250K, maka diskaun adalah 15%; jika tidak, …."
Formula ini semakin lama dan lama kerana terdapat lebih banyak tahap. Bahagian paling sukar dari formula tersebut adalah mengingat berapa banyak tanda kurung penutup yang akan diletakkan di akhir formula.
Sekiranya anda menggunakan Excel 2003, formula anda sudah hampir mencapai had. Dengan versi itu, anda tidak boleh meletakkan lebih dari 7 fungsi IF. Itu adalah hari yang jelek ketika kuasa yang diubah rancangan komisen dan anda memerlukan cara untuk menambahkan fungsi IF kelapan. Hari ini, anda boleh menggunakan 64 fungsi IF. Anda tidak boleh bersarang begitu banyak, tetapi senang mengetahui bahawa tidak ada masalah bersarang 8 atau 9.
Daripada menggunakan fungsi IF bersarang, cuba gunakan fungsi VLOOKUP. Apabila argumen keempat VLOOKUP berubah dari False menjadi True, fungsi tersebut tidak lagi mencari padanan yang tepat. Nah, VLOOKUP pertama cuba mencari padanan yang tepat. Tetapi jika pencocokan tepat tidak dijumpai, Excel akan berada di baris lebih sedikit daripada yang anda cari.
Pertimbangkan jadual di bawah. Di sel C13, Excel akan mencari padanan dengan harga $ 28,355 dalam jadual. Apabila tidak dapat menemui 28355, Excel akan mengembalikan potongan yang berkaitan dengan nilai yang hanya kurang - dalam kes ini, diskaun 1% untuk tahap $ 10K.
Apabila anda menukar peraturan ke jadual di E13: F18, anda perlu bermula dari tahap terkecil dan terus ke tahap tertinggi. Walaupun tidak dinyatakan dalam peraturan, jika seseorang menjual di bawah $ 10,000, potongan harga akan menjadi 0%. Anda perlu menambahkannya sebagai baris pertama dalam jadual.
Awas
Apabila anda menggunakan versi "Benar" VLOOKUP, jadual anda harus disusun menaik. Ramai orang percaya bahawa semua jadual carian mesti disusun. Tetapi jadual perlu disusun hanya untuk pertandingan yang hampir.
Bagaimana jika pengurus anda mahukan formula lengkap dan tidak mahu melihat jadual bonus di sebelah kanan? Setelah membina formula, anda boleh memasukkan jadual ke dalam formula. Masukkan formula dalam mod Edit dengan mengklik dua kali sel atau dengan memilih sel dan menekan F2. Gunakan kursor untuk memilih keseluruhan argumen kedua: $ E $ 13: $ F $ 18.
Tekan kekunci F9. Excel membenamkan jadual carian sebagai pemalar larik. Dalam pemalar larik, titik koma menunjukkan baris baru, dan koma menunjukkan lajur baru. Lihat Memahami Pemalar Array.
Tekan enter. Salin formula ke sel lain.
Anda kini boleh memadamkan jadual. Formula terakhir ditunjukkan di bawah.
Terima kasih kepada Mike Girvin kerana mengajar saya mengenai kurungan yang sepadan. Teknik VLOOKUP disarankan oleh Danny Mac, Boriana Petrova, Andreas Thehos, dan @mvmcos.
