Apakah Anda memiliki rumus Excel yang memiliki banyak fungsi IF? Saat Anda sampai pada poin dengan terlalu banyak pernyataan IF bersarang, Anda perlu melihat apakah semuanya akan menjadi lebih sederhana dengan fungsi VLOOKUP sederhana. Saya telah melihat rumus 1000 karakter disederhanakan menjadi rumus VLOOKUP 30 karakter. Mudah dipelihara saat Anda harus menambahkan nilai baru nanti.
Dahulu kala, saya bekerja untuk wakil presiden penjualan di tempat kerja. Saya selalu memodelkan beberapa program bonus atau rencana komisi baru. Saya menjadi cukup terbiasa dengan rencana komisi dengan segala macam kondisi. Yang di bawah 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 baru yang menguji level 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.
Tip Bonus Mini
Mencocokkan Tanda Kurung
Excel berputar melalui berbagai warna untuk setiap tingkat baru tanda kurung. Saat Excel menggunakan kembali warna, Excel hanya menggunakan warna hitam untuk kurung buka dan tanda kurung tutup yang cocok. Saat Anda menyelesaikan rumus di bawah ini, teruslah mengetikkan tanda kurung tutup sampai Anda mengetikkan tanda kurung hitam.
Kembali ke Tip Formula Bersarang
Jika Anda menggunakan Excel 2003, rumus Anda sudah mendekati batas. Saat itu, 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 seharusnya tidak pernah melakukan ini, tetapi senang mengetahui tidak ada masalah bersarang 8 atau 9.
Daripada menggunakan fungsi IF bertingkat, coba gunakan penggunaan yang tidak biasa untuk 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 kecocokan persis tidak ditemukan, maka 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 lebih kecil. 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. Tetapi tabel perlu diurutkan hanya dalam kasus melakukan 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.
Dengan menggunakan kursor, pilih seluruh argumen kedua: $ E $ 13: $ F $ 18.
Tekan tombol F9. Excel akan menyematkan tabel pencarian sebagai konstanta array. Dalam konstanta array, titik koma menunjukkan baris baru, dan koma menunjukkan kolom baru.
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.
Menonton video
- Dengan program komisi, bonus, atau diskon berjenjang, Anda sering kali harus menyusun fungsi IF Anda
- Batas Excel 2003 adalah 7 pernyataan IF bertumpuk.
- Anda sekarang dapat membuat sarang 32, tetapi menurut saya Anda tidak perlu membuat sarang 32
- Kapan Anda pernah menggunakan versi perkiraan pertandingan VLOOKUP? Inilah waktunya.
- Terjemahkan program diskon ke dalam tabel pencarian
- VLOOKUP tidak akan menemukan jawabannya dalam banyak kasus.
- Dengan kata lain, True pada akhirnya akan memberi tahu VLOOKUP untuk menemukan nilainya lebih sedikit.
- Ini adalah satu-satunya saat tabel VLOOKUP harus diurutkan.
- Tidak ingin tabel VLOOKUP miring? Sematkan dalam rumus.
- F2 untuk mengedit rumus. Pilih tabel pencarian. Tekan F9. Memasukkan.
Transkrip Video
Pelajari Excel dari podcast, episode 2030 - Replaced Nested IF dengan VLOOKUP!
Saya akan membuat podcasting seluruh buku ini, klik "i" di pojok kanan atas untuk membuka playlist!
Hai, selamat datang kembali di netcast, saya Bill Jelen. Baiklah, ini benar-benar umum dengan program komisi, atau program diskon, atau program bonus, di mana kita memiliki tingkatan, tingkatan yang berbeda, bukan? Jika Anda di atas $ 10.000, Anda mendapatkan diskon 1%, diskon $ 50000 5%. Anda harus berhati-hati saat membuat pernyataan IF bertingkat ini, Anda memulainya dari ujung atas jika Anda mencari lebih besar dari, atau di ujung bawah jika Anda mencari kurang dari. Jadi B10> 50000, 20%, sebaliknya IF lain, B10> 250000, 25%, dan seterusnya. Ini hanyalah rumus yang panjang dan rumit, dan jika tabel Anda lebih besar, di Excel 2003, Anda tidak dapat membuat lebih dari 7 pernyataan IF, kita hampir mendekati batasnya di sini. Anda bisa mencapai usia 32 sekarang, saya rasa Anda tidak harus pernah berusia 32 tahun, dan bahkan jika Anda berteriak "Hei tunggu,bagaimana dengan fungsi baru yang baru saja keluar di Excel 2016, rilis Februari 2016, dengan fungsi IFS? ” Ya tentu, ada lebih sedikit tanda kurung di sini, dan 87 karakter bukannya 97 karakter, itu masih berantakan, mari kita singkirkan ini dan lakukan dengan VLOOKUP!
Baiklah, ini langkah-langkahnya, Anda mengambil aturan itu, dan Anda mengubahnya di atas kepala mereka. Hal pertama yang harus kami katakan adalah, jika Anda memiliki penjualan $ 0, Anda mendapatkan diskon 0%, jika Anda memiliki penjualan $ 10.000, Anda mendapatkan diskon 1%, jika Anda memiliki penjualan $ 50.000, Anda mendapatkan diskon 5% . $ 100000, diskon 10%, $ 250000, diskon 15%, dan akhirnya, apapun yang> $ 500000 mendapatkan diskon 20%, oke. Sering kali, setiap kali saya berbicara tentang VLOOKUP, saya mengatakan setiap VLOOKUP yang pernah Anda buat akan berakhir dengan FALSE, dan orang-orang akan berkata "Tunggu sebentar, untuk apa versi BENAR di sana?" Ini untuk kasus ini di mana kami mencari rentang, jadi kami mencari nilai ini, VLOOKUP biasa, tentu saja, 2, FALSE tidak akan menemukan 550000, akan mengembalikan # N / A!Jadi dalam kasus yang sangat khusus ini kita akan mengubah FALSE itu menjadi TRUE, dan itu akan memberi tahu Excel "Cari 550000, jika Anda tidak dapat menemukannya, beri kami nilai yang kurang." Jadi itu memberi kita 20%, itulah fungsinya, oke? Dan ini adalah satu-satunya saat tabel VLOOKUP Anda harus diurutkan, di lain waktu dengan, FALSE, bisa sesuka Anda. Dan ya, Anda dapat membiarkan, TRUE off, tetapi saya selalu menaruhnya di sana hanya untuk mengingatkan diri saya sendiri bahwa ini adalah salah satu VLOOKUP yang sangat berbahaya, dan saya tidak ingin menyalin formula ini di tempat lain. Baiklah, jadi kita akan menyalin dan Menempelkan Rumus Khusus.baik? Dan ini adalah satu-satunya saat tabel VLOOKUP Anda harus diurutkan, di lain waktu dengan, FALSE, bisa sesuka Anda. Dan ya, Anda dapat membiarkan, TRUE off, tetapi saya selalu menaruhnya di sana hanya untuk mengingatkan diri saya sendiri bahwa ini adalah salah satu VLOOKUP yang sangat berbahaya, dan saya tidak ingin menyalin formula ini di tempat lain. Baiklah, jadi kita akan menyalin dan Menempelkan Rumus Khusus.baik? Dan ini adalah satu-satunya saat tabel VLOOKUP Anda harus diurutkan, di lain waktu dengan, FALSE, bisa sesuka Anda. Dan ya, Anda dapat membiarkan, TRUE off, tetapi saya selalu menaruhnya di sana hanya untuk mengingatkan diri saya sendiri bahwa ini adalah salah satu VLOOKUP yang sangat berbahaya, dan saya tidak ingin menyalin formula ini di tempat lain. Baiklah, jadi kita akan menyalin dan Menempelkan Rumus Khusus.
Keluhan berikutnya: manajer Anda tidak ingin melihat tabel pemeta, dia menginginkannya "Singkirkan saja tabel pemeta itu". Baiklah, kita bisa melakukannya dengan menyematkan tabel pencarian, lihat ini, trik hebat yang saya dapatkan dari Mike Girvin di ExcelIsFun. F2 untuk meletakkan rumus ke mode Edit, dan kemudian dengan sangat hati-hati pilih hanya kisaran untuk tabel pencarian. Tekan F9, dan ini mengambil tabel ini dan meletakkannya dalam nomenklatur array, lalu saya tekan Enter seperti itu. Salin itu ke bawah, Tempel Rumus Khusus, dan kemudian saya bebas untuk menyingkirkan tabel pencarian, yang semuanya terus bekerja di baris. Ini sangat merepotkan jika Anda harus masuk kembali dan mengedit ini, Anda harus benar-benar melihatnya dengan sangat jelas. Koma berarti kita akan ke kolom berikutnya, titik koma berarti ke baris berikutnya, oke,tetapi Anda bisa secara teori kembali ke sana dan mengubah rumus itu jika salah satu nomor rantai berubah.
Baiklah, jadi menggunakan VLOOKUP alih-alih pernyataan IF bertumpuk adalah tip # 32 dalam perjalanan ke 40, "40 Tips Excel Terbaik Sepanjang Masa", Anda dapat memiliki keseluruhan buku ini. Klik "i" di pojok kanan atas, $ 10 untuk e-book, $ 25 untuk buku cetak, oke. Jadi hari ini kami mencoba menyelesaikan bonus komisi berjenjang, atau program diskon. IF bersarang sangat umum, hati-hati, 7 adalah semua yang dapat Anda miliki di Excel 2003, hari ini Anda dapat memiliki 32, tetapi Anda tidak boleh memiliki 32. Jadi, kapan harus menggunakan versi MATCH dari VLOOKUP, ini dia. Ambil program diskon itu, balikkan, buat menjadi tabel pencarian yang dimulai dengan angka terkecil, dan terus ke angka terbesar. VLOOKUP, tentu saja, tidak akan menemukan jawabannya, tetapi dengan mengubah VLOOKUP menjadi, TRUE di bagian akhir, VLOOKUP akan memberitahu untuk menemukan nilainya sedikit,ini adalah satu-satunya saat tabel harus diurutkan. Jika Anda tidak ingin melihat tabel tersebut di luar sana, Anda bisa menyematkannya dalam rumus menggunakan trik Mike Girvin, F2 untuk mengedit rumus, pilih tabel pencarian, tekan F9, lalu Enter.
Baiklah, saya ingin berterima kasih telah mampir, sampai jumpa di lain waktu untuk netcast lain dari!
Unduh berkas
Unduh file contoh di sini: Podcast2030.xlsx
