İleri Ders 1: ÇAPRAZARA ile Esnek Arama

ÇAPRAZARA neyi çözüyor?

ÇAPRAZARA, aradığınız değeri bir sütunda bulup başka bir sütundaki karşılığını döndüren arama fonksiyonudur. DÜŞEYARA ile aynı işi yapar, ama sütun numarası istemez ve aranan sütunun solundaki verileri de getirebilir. Microsoft 365, Excel 2021 ve sonraki sürümlerde bulunur. Dosyayı Excel 2019 kullanan biriyle paylaşacaksanız bu formül orada #AD? hatası verir.

Söz dizimi şöyledir: =ÇAPRAZARA(arama_değeri; arama_dizisi; döndürülen_dizi; [bulunamıyorsa]; [eşleştirme_modu]; [arama_modu]). İlk üç argüman zorunludur. Kalan üçü, aramayı DÜŞEYARA'da ancak ek formüllerle kurabileceğiniz durumlara uyarlar.

Sola doğru arama

ÇAPRAZARA'da arama sütunu ile sonuç sütunu birbirinden bağımsızdır. Ürün adlarının B sütununda, kodların A sütununda olduğu bir listede Silgi'nin kodunu bulmak için =ÇAPRAZARA("Silgi";B2:B5;A2:A5) yazarsınız. Sonuç A300 olur.

DÜŞEYARA bu soruyu çözemez, çünkü her zaman tablonun ilk sütununda arar ve sağa doğru sayar. Eskiden bunun için İNDİS ile KAÇINCI birlikte kullanılırdı. ÇAPRAZARA aynı sonucu tek fonksiyonla verir. Tabloya sonradan sütun eklediğinizde de formül bozulmaz, çünkü sütun numarası yerine aralık tutar.

Bulunamayan değeri yönetmek

Dördüncü argüman, aranan değer listede yoksa ne gösterileceğini belirler. Boş bırakırsanız sonuç #YOK olur. Bir sipariş listesinde 40 satırın üçünde #YOK görmek, raporu okuyan kişinin dikkatini asıl rakamlardan dağıtır.

=ÇAPRAZARA(E2;A2:A6;C2:C6;"Yok") yazdığınızda listede olmayan kodun yanında "Yok" görünür. Bu, formülü EĞERHATA ile sarmaktan daha güvenlidir. EĞERHATA, yanlış yazılmış bir aralıktan doğan hatayı da gizlerdi. Dördüncü argüman ise yalnızca "bulunamadı" durumunu yakalar.

Barem tablosunda yaklaşık eşleşme

Eşleştirme modu, tam değer bulunamadığında ne yapılacağını söyler. 0 tam eşleşmedir ve varsayılandır. -1 ise tam eşleşme yoksa bir küçük değeri alır, prim veya vergi dilimi gibi baremlerde tam olarak bu gerekir.

Satış eşiklerinin 0, 10.000, 25.000 ve 50.000 olduğu bir prim tablosunda 27.500 TL satış yapan bir temsilcinin oranı için =ÇAPRAZARA(27500;A2:A5;B2:B5;;-1) yazarsınız. Formül 27.500'den küçük en büyük eşik olan 25.000'i bulur ve %3,5 döndürür. Temsilcinin primi böylece 27.500 x 0,035 = 962,50 TL olarak hesaplanır. Bulunamıyorsa argümanını atladığınız için iki noktalı virgül yan yanadır.

Son eşleşmeyi bulmak

Arama modu, listenin hangi uçtan taranacağını belirler. Varsayılan 1 baştan sona arar. -1 ise sondan başa arar, bu sayede aynı müşterinin en son kaydını bulursunuz.

Ali Yılmaz'ın iki siparişi olan bir listede =ÇAPRAZARA("Ali Yılmaz";A2:A5;B2:B5;"Yok";0;-1) sondaki 950 TL'lik siparişi döndürür. Liste tarih sırasına göre tutuluyorsa bu, müşterinin son sipariş tutarı demektir. DÜŞEYARA her zaman ilk eşleşmede durduğu için 1.200 TL'yi getirirdi.

İki yönlü arama: satır ve sütun birlikte

ÇAPRAZARA bir aralık döndürebildiği için iki aramayı iç içe yazarak tablonun hem satırını hem sütununu seçebilirsiniz. Ürünlerin satırlarda, ayların sütunlarda olduğu bir satış tablosunda Defter'in mart satışını bulmak istediğinizi düşünün. İçteki formül önce "Mart" başlığını arayıp o sütunun tamamını döndürür, dıştaki formül de bu sütunda Defter satırını bulur.

Formül şu yapıdadır: =ÇAPRAZARA("Defter";A2:A5;ÇAPRAZARA("Mart";B1:D1;B2:D5)). İçteki ÇAPRAZARA, B1:D1 başlıklarında Mart'ı bulup B2:D5'in o sütununu verir. Dıştaki ise A2:A5'te Defter'i arayıp bu sütundaki değeri döndürür. Eskiden aynı iş İNDİS, KAÇINCI ve ikinci bir KAÇINCI ile yapılırdı.

Eşleştirme modu 2 joker karakterleri açar. =ÇAPRAZARA("Def*";B2:B5;C2:C5;"Yok";2) adı Def ile başlayan ilk ürünün fiyatını getirir. Yıldız herhangi sayıda karakter, soru işareti tek karakter yerine geçer.

Sık yapılan hatalar

En yaygın hata, arama dizisi ile döndürülen dizinin farklı uzunlukta olmasıdır. A2:A5 ile C2:C6 yazarsanız Excel #DEĞER! verir. İki aralığın aynı satırda başlayıp aynı satırda bitmesine dikkat edin.

Döndürülen dizi birden fazla sütun içeriyorsa sonuç sağa doğru yayılır. =ÇAPRAZARA("A200";A2:A5;B2:C5) ürün adını ve fiyatı yan yana iki hücreye yazar. Sağdaki hücre doluysa Excel sonucu yazamaz ve #TAŞMA! hatası verir; komşu hücreyi boşaltmanız yeterlidir.

Formülü aşağı kopyalayacaksanız aralıkları $A$2:$A$6 biçiminde sabitleyin. Barem aramalarında ise eşik sütununun küçükten büyüğe sıralı olması gerekmez, ama sıralı tutmak tabloyu okuyan kişinin işini kolaylaştırır.

Sıradaki adım

Alıştırmada bir ürün listesinden üç kodun fiyatını getireceksiniz; kodlardan biri listede yok. Formülü yazdıktan sonra dördüncü argümanı silip sonucun nasıl #YOK'a döndüğünü de görün. Ardından E3'teki kodu A400 yapın ve "Yok" yazan hücrenin fiyata dönüştüğünü izleyin.

=ÇAPRAZARA("Silgi";B2:B5;A2:A5)

=XLOOKUP("Silgi",B2:B5,A2:A5)

=ÇAPRAZARA(27500;A2:A5;B2:B5;;-1)

=XLOOKUP(27500,A2:A5,B2:B5,,-1)

=ÇAPRAZARA("Ali Yılmaz";A2:A5;B2:B5;"Yok";0;-1)

=XLOOKUP("Ali Yılmaz",A2:A5,B2:B5,"Yok",0,-1)