Orta Ders 4: İNDİS ve KAÇINCI ile Esnek Arama

Neden iki fonksiyon birden?

İNDİS ve KAÇINCI, aramayı iki ayrı adıma böldüğü için DÜŞEYARA'dan daha esnektir. KAÇINCI "aradığım değer kaçıncı sırada?" sorusunu, İNDİS ise "şu sıradaki değer ne?" sorusunu cevaplar. İkisi birleşince aranan sütunun konumu ve döndürülecek sütunun yeri birbirinden bağımsız hale gelir.

Bu bağımsızlık, bir önceki derste gördüğünüz iki sınırı ortadan kaldırır. Aranan sütun solda olmak zorunda kalmaz. Tabloya yeni sütun eklendiğinde de sabit bir sütun numarası bozulmaz.

KAÇINCI ile sırayı bulmak

KAÇINCI, bir değerin tek satırlık ya da tek sütunluk bir aralıktaki sırasını döndürür. Söz dizimi =KAÇINCI(aranan_değer; aranan_dizi; [eşleştir_tür]) biçimindedir. Son argümana 0 yazmak tam eşleşme demektir ve kod ya da ad aramalarında hep 0 kullanılır.

Örnek kargo tablosunda bölgeler A2:A4 aralığında, paket ağırlıkları B1:D1 başlıklarında duruyor.

=KAÇINCI("Ege";A2:A4;0)

=MATCH("Ege",A2:A4,0)

Ege, bölge listesinin ikinci elemanıdır. Sonuç bir sayfa satır numarası değil, aralık içindeki sıradır. Aralık A2'den başladığı için Ege'nin bulunduğu 3. satır, aralık içinde 2. sıraya karşılık gelir.

İNDİS ile değeri almak

İNDİS, verdiğiniz aralıkta belirtilen satır ve sütundaki değeri döndürür: =İNDİS(dizi; satır_sayısı; [sütun_sayısı]). =İNDİS(B2:D4;2;3) aralığın 2. satırı ile 3. sütununun kesişimini, yani Ege'nin 10 kg ücretini getirir: 160.

Tek başına İNDİS pek kullanışlı değildir, çünkü sıra numarasını elle yazmanız gerekir. Gücü, bu numarayı KAÇINCI'dan almasından gelir.

İki yönlü arama

Satır numarasını bir KAÇINCI'dan, sütun numarasını başka bir KAÇINCI'dan aldığınızda iki yönlü arama kurmuş olursunuz. Bu yapı, satır başlığı ile sütun başlığının kesiştiği hücreyi bulur. Fiyat matrisleri, vardiya çizelgeleri ve ücret baremleri bu tür tablolardır.

=İNDİS(B2:D4;KAÇINCI("Doğu";A2:A4;0);KAÇINCI("5 kg";B1:D1;0))

=INDEX(B2:D4,MATCH("Doğu",A2:A4,0),MATCH("5 kg",B1:D1,0))

İlk KAÇINCI Doğu'nun 3. sırada olduğunu, ikincisi "5 kg" başlığının 2. sırada olduğunu bulur. İNDİS bu iki sayıyla B2:D4 aralığındaki 140'ı döndürür. Bölgeyi ya da ağırlığı bir hücreden okursanız aynı formül bir hesap aracına dönüşür; kullanıcı yalnız iki hücreyi değiştirir.

DÜŞEYARA'nın yapamadığı: sola bakmak

DÜŞEYARA aranan sütunun sağına bakabilir, İNDİS ve KAÇINCI ise her iki yöne de bakar. Personel listesinde ad A sütununda, sicil numarası B sütunundaysa sicilden ada ulaşmak DÜŞEYARA ile mümkün değildir.

=İNDİS(A2:A4;KAÇINCI(1077;B2:B4;0))

=INDEX(A2:A4,MATCH(1077,B2:B4,0))

KAÇINCI 1077'nin B2:B4 aralığında 2. sırada olduğunu bulur, İNDİS de A2:A4'ün 2. elemanını getirir: Murat. İki aralığın aynı satırdan başlayıp aynı satırda bitmesi gerekir. A2:A4 ile B3:B5 karıştırılırsa formül hata vermez ama bir alt satırdaki kişiyi döndürür.

Sık yapılan hatalar

#YOK sonucu, KAÇINCI'nın değeri bulamadığını gösterir. Yazım farkı, sondaki boşluk ya da "5kg" ile "5 kg" gibi küçük farklar en yaygın nedenlerdir. Başlıklarla arama yapıyorsanız aranan değeri başlık hücresinden kopyalamak bu riski azaltır.

#BAŞV! ise İNDİS'e aralığın dışında bir numara gittiğinde çıkar. Üç satırlık bir aralıkta 4. satırı istemek buna örnektir. Bu da genellikle KAÇINCI'nın baktığı aralık ile İNDİS'in aralığının farklı boyda seçilmesinden kaynaklanır.

Formülü kopyalayacaksanız arama aralıklarını sabitleyin. Satır ve sütun ölçütlerini göreli bırakın ki her satır kendi bölgesini ve paketini okusun.

Prim tablosunda iki yönlü arama

Bir satış ekibinde prim oranının hem kıdeme hem hedef gerçekleşme dilimine göre belirlendiğini düşünün. Satırlarda "0-2 yıl", "3-5 yıl", "6+ yıl", sütunlarda "%80 altı", "%80-100", "%100 üstü" başlıkları olan bir tablo, iki yönlü aramanın tipik örneğidir.

Personel listesinde her çalışanın kıdem dilimi ve gerçekleşme dilimi ayrı sütunlarda yazılıysa prim oranını tek formül getirir: =İNDİS($B$2:$D$4;KAÇINCI(F2;$A$2:$A$4;0);KAÇINCI(G2;$B$1:$D$1;0)). Yönetim oranları değiştirdiğinde yalnız tabloyu güncellersiniz, personel listesindeki formüller aynen kalır.

Tablo yeni bir kıdem dilimiyle büyürse İNDİS ve KAÇINCI aralıklarını birlikte genişletmeniz gerekir. Tabloyu Excel tablosuna çevirmek bu güncellemeyi de kendiliğinden yapar.

Aynı yapı yalnız prim tablolarında değil, sigorta primi baremlerinde, kargo firmalarının desi ve bölge tablolarında ya da okulların not dönüşüm çizelgelerinde de karşınıza çıkar. Satır ve sütun başlıkları olan her tablo, İNDİS ve iki KAÇINCI ile sorgulanabilir. Başlıkların yazımını arama değerleriyle birebir aynı tutmak, bu formüllerin sorunsuz çalışmasının tek şartıdır.

Sıradaki adım

Alıştırmada dört siparişin kargo ücretini, bölge ve paket ağırlığına göre aynı matristen bulacaksınız. Formülü I2'ye yazıp aşağı kopyalayın ve sonuçları tabloya bakarak tek tek kontrol edin.