Kademe Tabloları için Yaklaşık Eşleştirme

Bir kademe tablosu, sürekli değerleri aralıklara ayırır. Örnek olarak vergi dilimleri, ağırlığa göre kargo ücretleri, miktar indirimleri ve puana göre harf notları verilebilir.

Olası her değer için bir satırınız olmaz; yalnızca her aralığın başlangıç eşiğini tutarsınız. 87 puan için tam bir giriş yoktur, ancak 80'de başlayan aralığa girer.

Yaklaşık eşleşme tam da burada işe yarar: tam bir eşleşme aramak yerine doğru aralığı bulur.

Şimdiye kadar MATCH(value, range, 0) kullanarak tam eşleşme yaptık. Üçüncü bağımsız değişken olan 0, "bu değeri tam olarak bulamazsanız #N/A döndürün" anlamına gelir.

Kademeler için bunun yerine eşleşme türü olarak 1 kullanırız. Bu, arama değerinden küçük veya ona eşit olan en büyük değeri bulur. Bir aralık aramasının davranması gereken biçim tam olarak budur.

Kesin bir kural vardır: Eşleşme türü 1 kullanıldığında eşik listesi artan düzende sıralanmış olmalıdır.

Bir notlandırma tablosu düşünün. E sütununda alt eşikler artan düzende sıralanmış olarak bulunuyor: 0, 60, 70, 80, 90. F sütununda ise etiketler var: F, D, C, B, A.

0-59 arası puan F, 60-69 arası D ve bu şekilde devam eder. Her puanı değil, yalnızca her aralığın başlangıcını saklarız.

Amacımız, G1'deki bir puanı alıp harf notunu döndürmektir.

Bir puanın hangi aralığa girdiğini bulmak için yaklaşık MATCH kullanın. 87 puanla birlikte kullanılan MATCH(G1, E2:E6, 1), 87 veya daha küçük olan en büyük eşiği arar.

Eşikler 0, 60, 70, 80 ve 90'dır. 87'yi aşmayan en büyük eşik 80'dir ve 4. konumdadır. Bu nedenle MATCH 4 döndürür.

87 listede bulunmasa da bu konum doğru aralığı gösterir.

Şimdi konumu F2:F6 etiket sütununda INDEX'e aktarın.

INDEX(F2:F6, MATCH(G1, E2:E6, 1)), 4. konumu alır ve dördüncü etiket olan "B"yi döndürür.

Böylece 87 puanı doğru şekilde B notuna eşlenir. G1'i 95 yaparsanız MATCH 5 döndürür ve sonuç "A" olur; 55 yaparsanız MATCH 1 döndürür ve sonuç "F" olur.

Yaklaşık MATCH (tür 1), arama aralığının artan düzende sıralanmasını gerektirir. Verilerin yükseldiğini varsayar ve arama değerini geçtiği anda durur.

Eşikleriniz sıralı değilse MATCH çok erken durabilir ve sizi uyarmak için hata vermeden yanlış, sessizce hatalı bir konum döndürebilir. Kademe aramasına güvenmeden önce eşik sütununu her zaman küçükten büyüğe sıralayın.

XLOOKUP da yaklaşık eşleşme yapabilir. Eşleşme modu olan beşinci bağımsız değişkeni, kademe tabloları için ideal olan "tam eşleşme veya sonraki küçük öğe" seçeneği için -1 değerini kabul eder.

Bu işlem G1'e eşit veya ondan küçük olan en büyük eşiği bulur ve INDEX kullanmaya gerek kalmadan karşılık gelen etiketi döndürür. Aralık aramalarında genellikle INDEX-MATCH'ten daha kolay okunur.

Şimdi miktar indirimine bakalım. E sütunundaki eşikler (sipariş miktarı): 0, 10, 50, 100. F sütunundaki indirimler: %0, %5, %10, %15.

• 7 adetlik sipariş: 7'ye eşit veya daha küçük olan en büyük eşik 0'dır, konum 1'dir ve %0 döndürülür.
• 60 adetlik sipariş: 60'a eşit veya daha küçük olan en büyük eşik 50'dir, konum 3'tür ve %10 döndürülür.
• 200 adetlik sipariş: 200'e eşit veya daha küçük olan en büyük eşik 100'dür, konum 4'tür ve %15 döndürülür.

Tek bir formül her miktarı işler.

Bir değer tüm eşiklerden küçükse ne olur? Yaklaşık MATCH ile bu değere eşit veya ondan küçük bir değer bulunamadığı için MATCH #N/A döndürür.

Bunu önlemek için ilk eşiğin alt sınırı kapsadığından (genellikle 0) emin olun veya bir girdi aralık dışındayken anlaşılır bir ileti göstermek için formülü IFERROR ile sarın.

Bu kademe tablosu tuzaklarına dikkat edin:

• Sıralanmamış eşikler: Yanlış ama sessiz sonuçların bir numaralı nedenidir.
• Eşleşme türü olarak 0 kullanmak: Tam eşleşmeyi zorunlu kılar ve arada kalan herhangi bir değer için #N/A döndürür.
• Aralık başlangıçları yerine bitişlerini saklamak: Tür 1 MATCH, her aralığın üst sınırını değil alt sınırını bekler.
• Metin eşikleri: Metin olarak saklanan sayılar karşılaştırmayı bozar; değerleri sayısal tutun.

Yaklaşık eşleşmeyi iki yönlü teknikle birleştirebilirsiniz. Hem ağırlık aralığına (satırlar) hem de bölge aralığına (sütunlar) göre kargo ücretini gösteren bir tablo düşünün.

Ağırlık satırını bulmak için bir yaklaşık MATCH (tür 1), bölge sütununu bulmak için de başka bir MATCH kullanın; ardından her ikisini INDEX'e aktarın. Her iki eksen de sıralı eşikler içerdiğinden, her MATCH doğru aralığa ulaşır.

Bu yöntem, INDEX-MATCH-MATCH'i kademe mantığıyla birleştirerek kapsamlı ücret tabloları oluşturur.

Kademe ve aralık aramaları için:

• Her aralığın alt eşiğini saklayın ve bunları artan düzende sıralayın.
• Aralık konumunu bulmak için MATCH(value, thresholds, 1) kullanın (girdiye eşit veya ondan küçük olan en büyük değer).
• Aralığı döndürmek için bunu INDEX(labels, ...) içine alın veya aynı sonuç için XLOOKUP(..., -1) kullanın.

0 eşiğiyle alt sınırı kapsayın veya aralık dışı girdiler için IFERROR kullanın; eşikleri asla sıralanmamış bırakmayın.