Birden çok IF işlevini iç içe geçiren bir Excel formülünüz var mı? Çok fazla iç içe geçmiş EĞER ifadesinin olduğu noktaya geldiğinizde, basit bir DÜŞEYARA işleviyle her şeyin daha basit olup olmayacağını görmeniz gerekir. 1000 karakterlik formüllerin 30 karakterlik DÜŞEYARA formülüne basitleştirildiğini gördüm. Daha sonra yeni değerler eklemeniz gerektiğinde bakımı kolaydır.
Uzun zaman önce iş yerinde satış başkan yardımcılığı için çalıştım. Her zaman yeni bir bonus programı veya komisyon planı modelliyordum. Her türlü koşulda planları devreye almaya oldukça alıştım. Aşağıdakiler oldukça uysal.
Normal yaklaşım, iç içe geçmiş bir EĞER formülü oluşturmaya başlamaktır. Her zaman aralığın en yüksek veya en düşük ucundan başlarsınız. “Satışlar 500.000 $ 'ın üzerindeyse, indirim% 20'dir; aksi takdirde… ." EĞER işlevinin üçüncü argümanı, ikinci düzeyi test eden tamamen yeni bir EĞER işlevidir: Satışlar 250.000 $ 'ın üzerindeyse, indirim% 15'tir; aksi takdirde,… "
Seviye sayısı arttıkça bu formüller uzar ve uzar. Böyle bir formülün en zor kısmı, formülün sonuna kaç tane kapatma parantezi koyulacağını hatırlamaktır.
Mini Bonus İpucu
Parantezleri Eşleştirme
Excel, her yeni parantez düzeyi için çeşitli renkler arasında geçiş yapar. Excel renkleri yeniden kullanırken, siyahı yalnızca açılış parantezi ve eşleşen kapanış parantezi için kullanır. Aşağıdaki formülü bitirirken, siyah bir parantez yazana kadar kapatma parantezlerini yazmaya devam edin.
Yuvalanmış Formül İpucuna Geri Dön
Excel 2003 kullanıyorsanız, formülünüz zaten sınıra yaklaşmaktadır. O zamanlar 7'den fazla EĞER işlevi iç içe yerleştiremezdiniz. Komisyon planını değiştiren yetkilerin ve sekizinci bir EĞER işlevi eklemek için bir yola ihtiyaç duyduğunuz çirkin bir gündü. Bugün, 64 IF işlevini iç içe yerleştirebilirsiniz. Bunu asla yapmamalısınız, ancak 8 veya 9'u iç içe geçirmede sorun olmadığını bilmek güzel.
İç içe geçmiş EĞER işlevini kullanmak yerine, DÜŞEYARA işlevi için olağandışı kullanımı kullanmayı deneyin. DÜŞEYARA'nın dördüncü bağımsız değişkeni Yanlış'dan Doğru'ya değiştiğinde, işlev artık tam bir eşleşme aramıyor.
İlk önce DÜŞEYARA tam bir eşleşme bulmaya çalışır. Ancak tam bir eşleşme bulunmazsa, Excel, aradığınızdan biraz daha az satıra yerleşir.
Aşağıdaki tabloyu düşünün. Excel, C13 hücresinde tabloda 28.355 dolarlık bir eşleşme arayacaktır. Excel, 28355'i bulamadığında, daha düşük olan değerle ilişkili indirimi döndürür. Bu durumda, 10.000 $ seviyesi için% 1 indirim.
E13: F18'deki kuralları tabloya dönüştürdüğünüzde, en küçük seviyeden başlayıp en yüksek seviyeye ilerlemeniz gerekir. Kurallarda belirtilmemiş olmasına rağmen, biri 10.000 $ 'ın altındaysa indirim% 0 olacaktır. Bunu tablodaki ilk satır olarak eklemeniz gerekir.
Dikkat
DÜŞEYARA'nın "Doğru" sürümünü kullandığınızda, tablonuz artan şekilde sıralanmalıdır. Birçok insan tüm arama tablolarının sıralanması gerektiğine inanır. Ancak bir tablonun yalnızca yaklaşık bir eşleşme durumunda sıralanması gerekir.
Ya yöneticiniz tamamen kendi kendine yeten bir formül istiyorsa ve ikramiye tablosunu sağda görmek istemiyorsa? Formülü oluşturduktan sonra, tabloyu doğrudan formüle yerleştirebilirsiniz.
Hücreyi çift tıklayarak veya hücreyi seçip F2 tuşuna basarak formülü Düzenleme moduna getirin.
İmleci kullanarak ikinci bağımsız değişkenin tamamını seçin: $ E $ 13: $ F $ 18.
F9 tuşuna basın. Excel, arama tablosunu bir dizi sabiti olarak katıştırır. Dizi sabitinde, bir noktalı virgül yeni bir satırı ve bir virgül yeni bir sütunu belirtir.
Enter tuşuna basın. Formülü diğer hücrelere kopyalayın.
Artık tabloyu silebilirsiniz. Nihai formül aşağıda gösterilmiştir.
Eşleşen parantezleri bana öğrettiği için Mike Girvin'e teşekkürler. DÜŞEYARA tekniği Danny Mac, Boriana Petrova, Andreas Thehos ve @mvmcos tarafından önerildi.
Video izle
- Kademeli bir komisyon, bonus veya indirim programıyla, genellikle IF işlevlerinizi yerleştirmeniz gerekir
- Excel 2003 sınırı, 7 iç içe geçmiş IF deyimiydi.
- Şimdi 32'yi yuva yapabilirsin, ama hiçbir zaman 32 yuva yapmamalısın
- DÜŞEYARA'nın yaklaşık eşleşme sürümünü ne zaman kullanırsınız? Zaman bu zaman.
- İndirim programını bir arama tablosuna çevirin
- DÜŞEYARA çoğu durumda cevabı bulamayacaktır.
- Sonunda True koymak DÜŞEYARA'ya değeri daha az bulmasını söyleyecektir.
- DÜŞEYARA tablosunun sıralanması gereken tek zaman budur.
- DÜŞEYARA tablosunun yan tarafa gitmesini istemiyor musunuz? Formüle gömün.
- Formülü düzenlemek için F2. Arama tablosunu seçin. F9'a basın. Giriş.
Video Metni
Excel'i podcast, bölüm 2030'dan öğrenin - Yuvalanmış IF, DÜŞEYARA ile değiştirildi!
Bu kitabın tamamını podcast yayınlayacağım, oynatma listesine ulaşmak için sağ üst köşedeki "i" işaretine tıklayın!
Hey, İnternet'e tekrar hoş geldiniz, ben Bill Jelen. Pekala, bu, ya bir komisyon programında ya da bir indirim programında ya da katmanlara, farklı katmanlara sahip olduğumuz bir bonus programında gerçekten yaygındır, değil mi? 10000 $ 'ın üzerindeyseniz,% 1 indirim, 50000 $% 5 indirim alırsınız. Bu iç içe geçmiş EĞER ifadesini oluştururken dikkatli olmalısınız, daha büyük arıyorsanız en üstte, daha azını arıyorsanız en altta başlarsınız. Yani B10> 50000,% 20, aksi takdirde başka bir EĞER, B10> 250000,% 25 vb. Bu sadece uzun ve karmaşık bir formül ve eğer tablonuz daha büyükse, Excel 2003'e döndüğünüzde, 7'den fazla IF deyimi iç içe geçiremezseniz, burada neredeyse sınıra yaklaştık. Şimdi 32'ye gidebilirsin, 32'ye gitmen gerektiğini sanmıyorum ve "Hey bekle,IFS işleviyle birlikte Şubat 2016 sürümü olan Excel 2016'da çıkan yeni işlev ne olacak? " Evet tabii, burada daha az parantez var ve 97 karakter yerine 87 karakter var, yine de karışıklık var, hadi bundan kurtulalım ve DÜŞEYARA ile yapalım!
Pekala, işte adımlar, bu kuralları alıyorsun ve onları tersine çeviriyorsun. Söylememiz gereken ilk şey, eğer satışta 0 $ varsa,% 0 indirim alırsınız, 10000 $ satışınız varsa% 1 indirim alırsınız, 50000 $ satışınız varsa% 5 indirim alırsınız. . 100000 $,% 10 indirim, 250000 $,% 15 indirim ve son olarak, 500.000 $ 'dan fazla olan her şey% 20 indirim alır. Şimdi birçok kez, DÜŞEYARA'dan bahsettiğim her seferinde, oluşturduğunuz her DÜŞEYARA YANLIŞ ile bitecek diyorum ve insanlar "Bir saniye bekleyin, burada DOĞRU sürüm nedir?" Bu, bir aralık aradığımız tam da bu durumda, bu değeri arıyoruz, normal bir DÜŞEYARA, tabii ki, 2, YANLIŞ 550000 bulamaz, # N / A döndürür!Bu yüzden bu çok özel durumda, bu YANLIŞ'ı bir DOĞRU olarak değiştireceğiz ve bu, Excel'e "Gidip 550000'ü arayın, eğer bulamazsanız, bize sadece daha az olan değeri verin" diyeceğiz. Yani bize% 20 veriyor, yaptığı bu, tamam mı? Ve bu, DÜŞEYARA tablonuzun sıralanması gereken tek zamandır, diğer tüm zamanlarda, YANLIŞ ile, istediğiniz gibi olabilir. Ve evet, DOĞRU'yu devre dışı bırakabilirsiniz, ancak bunun o garip inanılmaz derecede tehlikeli DÜŞEYARA'lardan biri olduğunu kendime hatırlatmak için her zaman oraya koydum ve bu formülü başka bir yere kopyalamak istemiyorum. Pekala, özel Formülleri kopyalayıp Yapıştıracağız, tamam.Peki? Ve bu, DÜŞEYARA tablonuzun sıralanması gereken tek zamandır, diğer tüm zamanlarda, YANLIŞ ile, istediğiniz gibi olabilir. Ve evet, DOĞRU'yu devre dışı bırakabilirsiniz, ancak bunun o garip inanılmaz derecede tehlikeli DÜŞEYARA'lardan biri olduğunu kendime hatırlatmak için her zaman oraya koydum ve bu formülü başka bir yere kopyalamak istemiyorum. Pekala, özel Formülleri kopyalayıp Yapıştıracağız, pekala.Peki? Ve bu, DÜŞEYARA tablonuzun sıralanması gereken tek zamandır, diğer tüm zamanlarda, YANLIŞ ile, istediğiniz gibi olabilir. Ve evet, DOĞRU'yu devre dışı bırakabilirsiniz, ancak bunun o garip inanılmaz derecede tehlikeli DÜŞEYARA'lardan biri olduğunu kendime hatırlatmak için her zaman oraya koydum ve bu formülü başka bir yere kopyalamak istemiyorum. Pekala, özel Formülleri kopyalayıp Yapıştıracağız, pekala.
Sonraki şikayet: yöneticiniz arama tablosunu görmek istemiyor, "Sadece şu arama tablosundan kurtulun" istiyor. Pekala, bunu arama tablosunu yerleştirerek yapabiliriz, bunu kontrol edebiliriz, ExcelIsFun'da Mike Girvin'den aldığım harika numara. F2 ile formülü Düzenleme moduna getirin ve ardından çok dikkatli bir şekilde arama tablosunun aralığını seçin. F9'a basın ve bu tabloyu alıp dizi isimlendirmesine koyuyor ve sonra sadece bu şekilde Enter tuşuna basıyorum. Bunu kopyalayın, Özel Formülleri Yapıştırın ve sonra arama tablosundan kurtulmakta özgürüm, tüm bunlar satır boyunca çalışmaya devam ediyor. Geri gelip bunu düzenlemeniz gerekiyorsa, bu büyük bir güçlüktür, gerçekten çok net bir şekilde bakmanız gerekir. Virgül, bir sonraki sütuna gideceğimiz anlamına gelir, noktalı virgül, bir sonraki satıra gideceğimiz anlamına gelir, tamam,ama teoride oraya geri dönebilir ve zincir numaralarından biri değişirse bu formülü değiştirebilirsiniz.
Pekala, iç içe geçmiş EĞER ifadesi yerine DÜŞEYARA kullanmak 40 numaralı “Tüm Zamanların En Harika 40 Excel İpucu” na giderken ipucu # 32'dir, bu kitabın tamamına sahip olabilirsiniz. Sağ üst köşedeki "i" ye, bir e-kitap için 10 $, basılı kitap için 25 $ 'a tıklayın. Bu yüzden bugün kademeli bir komisyon bonusu veya indirim programını çözmeye çalışıyoruz. İç içe geçmiş IF'ler gerçekten yaygındır, dikkat edin, Excel 2003'te sahip olabileceğiniz tek şey 7'dir, bugün 32'ye sahip olabilirsiniz, ancak hiçbir zaman 32'ye sahip olmamanız gerekir. DÜŞEYARA'nın yaklaşık MATCH sürümünü ne zaman kullanacağınız, işte budur. Bu indirim programını alın, ters çevirin, en küçük sayıdan başlayıp en büyük sayıya giden bir arama tablosu haline getirin. DÜŞEYARA elbette cevabı bulamayacaktır, ancak DÜŞEYARA'yı sonunda TRUE olarak değiştirerek, ona değeri daha az bulmasını söyleyecektir.bu, tablonun sıralanması gereken tek zamandır. Bu tabloyu orada görmek istemiyorsanız, Mike Girvin numarasını, formülü düzenlemek için F2'yi kullanarak formüle gömebilirsiniz, arama tablosunu seçin, F9 tuşuna ve ardından Enter tuşuna basın.
Pekala hey, uğradığınız için teşekkür etmek istiyorum, bir dahaki sefere başka bir internet yayını için görüşürüz!
Dosyayı indir
Örnek dosyayı buradan indirin: Podcast2030.xlsx
