Uzun zaman önce bir şirkette satış başkan yardımcılığı yaptım. Her zaman yeni bir bonus programı veya komisyon planı modelliyordum. Her türlü koşulda planları devreye almaya oldukça alıştım. Bu ipucunda gösterilen, 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 ABD Dolarının üzerindeyse, indirim% 15; 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.
Excel 2003 kullanıyorsanız, formülünüz zaten sınıra yaklaşmaktadır. Bu sürümle, 7'den fazla IF işlevini yerleştiremezsiniz. 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. Asla bu kadar çok yuva 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şlevini 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, yalnızca daha az olan değerle ilişkili indirimi döndürür - bu durumda, 10.000 ABD doları düzeyi 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 için 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. İkinci bağımsız değişkenin tamamını seçmek için imleci kullanın: $ E $ 13: $ F $ 18.
F9 tuşuna basın. Excel, arama tablosunu bir dizi sabiti olarak gömer. Dizi sabitinde, bir noktalı virgül yeni bir satırı ve bir virgül yeni bir sütunu belirtir. Bkz. Dizi Sabitlerini Anlama.
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.
