Adakah anda mempunyai formula Excel yang menempatkan banyak fungsi IF? Apabila anda sampai ke titik dengan terlalu banyak penyataan IF bersarang, anda perlu melihat apakah keseluruhannya akan menjadi lebih mudah dengan fungsi VLOOKUP yang sederhana. Saya telah melihat formula 1000 watak dipermudah kepada formula VLOOKUP 30 watak. Ia mudah dijaga apabila anda perlu menambah nilai baru kemudian.
Dulu, saya bekerja sebagai naib presiden penjualan di tempat kerja. Saya selalu memodelkan beberapa program bonus atau rancangan komisen baru. Saya menjadi biasa digunakan untuk membuat rancangan dengan pelbagai keadaan. Yang di bawah 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.
Petua Bonus Mini
Memadankan Parentheses
Excel menggunakan pelbagai warna untuk setiap tanda kurung baru. Walaupun Excel menggunakan semula warna, ia menggunakan warna hitam hanya untuk kurungan pembukaan dan untuk kurungan penutup yang sepadan. Semasa anda menyelesaikan formula di bawah, teruskan menaip tanda kurung penutup sehingga anda menaip kurungan hitam.
Kembali ke Petua Formula Bersarang
Sekiranya anda menggunakan Excel 2003, formula anda sudah hampir mencapai had. Pada masa itu, anda tidak dapat menempatkan lebih dari 7 fungsi IF. Ini adalah hari yang jelek ketika kuasa yang mengubah rancangan komisen dan anda memerlukan cara untuk menambahkan fungsi IF kelapan. Hari ini, anda boleh menggunakan 64 fungsi IF. Anda tidak boleh melakukan ini, tetapi senang mengetahui bahawa tidak ada masalah bersarang 8 atau 9.
Daripada menggunakan fungsi IF bersarang, cuba gunakan penggunaan yang tidak biasa untuk 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 barisan kurang dari apa 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 diskaun yang berkaitan dengan nilai yang lebih sedikit. 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 sekiranya melakukan 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.
Dengan menggunakan kursor, pilih keseluruhan argumen kedua: $ E $ 13: $ F $ 18.
Tekan kekunci F9. Excel akan menyematkan jadual carian sebagai pemalar array. Dalam pemalar larik, titik koma menunjukkan baris baru, dan koma menunjukkan lajur baru.
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.
Tonton Video
- Dengan program komisen, bonus, atau diskaun berjenjang, anda sering harus meletakkan fungsi IF anda
- Had Excel 2003 adalah 7 penyataan IF bersarang.
- Anda sekarang boleh bersarang 32, tetapi saya rasa anda tidak seharusnya bersarang 32
- Bilakah anda akan menggunakan versi pencocokan anggaran VLOOKUP? Inilah masanya.
- Terjemahkan program diskaun ke dalam jadual carian
- Dalam kebanyakan kes, VLOOKUP tidak akan menemui jawapannya.
- Meletakkan, Benar pada akhirnya akan memberitahu VLOOKUP untuk mencari nilainya kurang.
- Ini adalah satu-satunya masa jadual VLOOKUP disusun.
- Tidak mahu meja VLOOKUP di sebelah? Benamkannya dalam formula.
- F2 untuk menyunting formula. Pilih jadual carian. Tekan F9. Masukkan.
Transkrip Video
Belajar Excel dari podcast, episod 2030 - Diganti Nested IF dengan VLOOKUP!
Saya akan menyiarkan keseluruhan buku ini, klik “i” di sudut kanan atas untuk masuk ke senarai main!
Hei, selamat datang kembali ke netcast, saya Bill Jelen. Baiklah, ini sangat biasa dengan program komisen, atau program diskaun, atau program bonus, di mana kita mempunyai tingkatan, tingkatan yang berbeza, bukan? Sekiranya anda melebihi $ 10000, anda mendapat diskaun 1%, $ 50000 diskaun 5%. Anda harus berhati-hati semasa membina pernyataan IF bersarang ini, anda memulakannya di hujung atas jika anda mencari yang lebih besar daripada, atau di hujung bawah jika anda mencari kurang dari. Jadi B10> 50000, 20%, sebaliknya JIKA lain, B10> 250000, 25%, dan seterusnya dan seterusnya. Ini hanyalah formula yang panjang dan rumit, dan jika jadual anda lebih besar, kembali di Excel 2003, anda tidak dapat meletakkan lebih dari 7 penyataan JIKA, kita hampir mencapai had di sini. Anda boleh pergi ke 32 sekarang, saya rasa anda tidak boleh pergi ke 32, dan walaupun anda menjerit "Hai tunggu,bagaimana dengan fungsi baru yang baru sahaja keluar di Excel 2016, keluaran Februari 2016, dengan fungsi IFS? " Ya pasti, kurang tanda kurung di sini, dan 87 aksara daripada 97 aksara, masih berantakan, mari kita hilangkan ini dan lakukan dengan VLOOKUP!
Baiklah, berikut adalah langkah-langkahnya, anda mengambil peraturan tersebut, dan memusingkannya. Perkara pertama yang harus kami katakan adalah, jika anda mempunyai penjualan $ 0, anda mendapat diskaun 0%, jika anda menjual $ 10000, anda mendapat diskaun 1%, jika anda mempunyai penjualan $ 50000, anda akan mendapat diskaun 5% . $ 100000, diskaun 10%, $ 250000, diskaun 15%, dan akhirnya, apa sahaja> $ 500000 mendapat diskaun 20%, baiklah. Sekarang berkali-kali, setiap kali saya bercakap mengenai VLOOKUP, saya katakan setiap VLOOKUP yang anda buat akan berakhir dengan PALSU, dan orang akan berkata "Tunggu sebentar, apa versi BENAR di sana?" Ini adalah untuk kes ini di mana kita mencari julat, jadi kita mencari nilai ini, VLOOKUP biasa, tentu saja, 2, FALSE tidak akan menemui 550000, akan mengembalikan # N / A!Oleh itu, dalam kes yang sangat istimewa ini kita akan menukar PALSU itu menjadi BENAR, dan itu akan memberitahu Excel "Pergi mencari 550000, jika anda tidak dapat menemuinya, beri kami nilai yang lebih sedikit." Oleh itu, ia memberi kita 20%, itulah yang dilakukannya, baiklah? Dan ini adalah satu-satunya masa jadual VLOOKUP anda disusun, sepanjang masa dengan, SALAH, ia sesuka hati. Dan ya, anda boleh meninggalkan, BENAR, tetapi saya selalu meletakkannya di sana hanya untuk mengingatkan diri saya bahawa ini adalah salah satu VLOOKUP pelik yang sangat berbahaya, dan saya tidak mahu menyalin formula ini di tempat lain. Baiklah, jadi kami akan menyalin dan Tampalkan Formula Khas, baiklah.baiklah? Dan ini adalah satu-satunya masa jadual VLOOKUP anda disusun, sepanjang masa dengan, SALAH, ia sesuka hati. Dan ya, anda boleh meninggalkan, BENAR, tetapi saya selalu meletakkannya di sana hanya untuk mengingatkan diri saya bahawa ini adalah salah satu VLOOKUP pelik yang sangat berbahaya, dan saya tidak mahu menyalin formula ini di tempat lain. Baiklah, jadi kami akan menyalin dan Tampalkan Formula Khas, baiklah.baiklah? Dan ini adalah satu-satunya masa jadual VLOOKUP anda disusun, sepanjang masa dengan, SALAH, ia sesuka hati. Dan ya, anda boleh meninggalkan, BENAR, tetapi saya selalu meletakkannya di sana hanya untuk mengingatkan diri saya bahawa ini adalah salah satu VLOOKUP pelik yang sangat berbahaya, dan saya tidak mahu menyalin formula ini di tempat lain. Baiklah, jadi kami akan menyalin dan Tampalkan Formula Khas, baiklah.
Keluhan seterusnya: pengurus anda tidak mahu melihat jadual pencarian, dia menginginkannya "Singkirkan jadual carian itu." Baiklah, kita boleh melakukannya dengan memasukkan jadual carian, lihat ini, helah hebat yang saya dapat dari Mike Girvin di ExcelIsFun. F2 untuk memasukkan formula ke dalam mod Edit, dan kemudian dengan berhati-hati pilih julat untuk jadual carian. Tekan F9, dan ia mengambil jadual ini dan memasukkannya ke dalam nomenklatur array, dan kemudian saya tekan Enter seperti itu. Salin ke bawah, Tampalkan Rumus Khas, dan kemudian saya bebas untuk menyingkirkan jadual carian, yang semuanya terus berjalan lancar. Ini adalah kerumitan besar jika anda harus masuk semula dan menyuntingnya, anda harus benar-benar menatapnya dengan banyak kejelasan. Tanda koma bermaksud kita menuju ke lajur seterusnya, titik koma akan menuju ke baris seterusnya, baiklah,tetapi secara teori anda boleh masuk ke sana dan mengubah formula itu jika salah satu nombor rantai berubah.
Baiklah, jadi menggunakan VLOOKUP dan bukannya penyataan IF bersarang adalah tip # 32 dalam perjalanan ke 40, "40 Petua Excel Terbaik Sepanjang Masa", anda boleh memiliki keseluruhan buku ini. Klik “i” di sudut kanan atas, $ 10 untuk e-buku, $ 25 untuk buku cetak, baiklah. Jadi hari ini kami berusaha menyelesaikan bonus komisen berjenama, atau program diskaun. JIKA bersarang sangat biasa, awas, 7 adalah semua yang dapat anda miliki di Excel 2003, hari ini anda boleh memiliki 32, tetapi anda tidak semestinya mempunyai 32. Oleh itu, apabila menggunakan versi VLOOKUP PERLAWANAN perkiraan, inilah dia. Ikuti program diskaun itu, putar terbalik, jadikannya sebagai jadual pencarian bermula dengan nombor terkecil, dan pergi ke nombor terbesar. Tentu saja, VLOOKUP tidak akan menemui jawapannya, tetapi dengan menukar VLOOKUP menjadi, BENAR pada akhirnya, ia akan memberitahunya untuk mencari nilainya kurang,ini adalah satu-satunya masa jadual disusun. Sekiranya anda tidak mahu melihat jadual itu di luar sana, anda boleh memasukkannya ke dalam formula menggunakan helah Mike Girvin, F2 untuk mengedit formula, pilih jadual pencarian, tekan F9, dan kemudian Enter.
Baiklah hei, saya ingin mengucapkan terima kasih kerana mampir, kami akan berjumpa anda di lain kali untuk siaran lain
Muat turun fail
Muat turun fail contoh di sini: Podcast2030.xlsx
