Kaçıncı Formülü

Burada dikkat edilmesi gereken en önemli konu saymaya 1’den başlanması. Bu yüzden  başlığı olan tablolarda bulunan satır başlık dahil satırdır. Buna çok dikkat edin. Bu nedenle özellikle “KAYDIR” formülü ile beraber satırlarda kullanılırsa bir alt satırdaki veya sütunda arama yapıldı ise bir sağ sütundaki değeri bulmanız muhtemeldir. Evet bu formülle sütunlarda da arama yapabilirsiniz.

=KAÇINCI(“Toplam”; $A$2:$G$2;0)

Burada ise A2:G2 aralığında, “Toplam” değerine sahip ilk hücreyi buluyor. Örnekte “Toplam” sütunu F sütunu, A Sütununa 1 dersek bu sütun 7. sütun. Formül de zaten 7 değerini döndürüyor. Burada dikkat etmeniz gereken diğer husus ta hücre referansı değil direk numara geri döndürmesi. Bu nedenle eğer hücre referansı bulmak istiyorsanız Kaydır veya İndis gibi bir formülle beraber kullanmak durumundasınız.

Şimdi bu kadar temel bilgiden sonra biraz eğlenmeye başlayalım. Evet işler şimdi biraz ilginç bir hale gelecek. Bundan sonra anlatacaklarım biraz karmaşık gelebilir ancak dikkatinizi verirseniz çözeceğinize eminim.

Şimdi bir tabloda farklı birden fazla satırda veya sütunda yer alan verilerin değerine göre bir arama yapmak isteyebilirsiniz . Misal “Onur” adlı müşterinin 12/10/2016 tarihinde yaptığı alış verişin hangi satırda olduğunu bulmanız gerekebilir. Bu durumda “KAÇINCI” formülünü kullanabilir miyiz? Cevap evet, ancak biraz farklı bir yöntem ile. Nasıl yapıyoruz, önce formülümüzü yazalım:

={KAÇINCI(1;($A$2:$A$42=”Onur”)*($C$2:$c$42=TARİH(2016;10;12));0)}

ve formülü (braketler olmadan) yazdıktan sonra SHIFT+CTRL+ENTER tuşlarına beraber basalım ve… Sonuç 33. Nasıl yani dediğinizi duyar gibiyim. İlk başta oldukça alakasız gelen bir formül, tuhaf bir tuş kombinasyonu ile sihir yapılmış gibi çalıştı değil mi? Şahsen ben bu formülü ilk kullandığımda bu hisleri yaşamıştım. Bir projede buna benzer bir sorun ile karşı karşıya kaldım. internette araştırma yaparken bir yerlerde “{….}” şeklinde bir formül gördüm. Braket, formül çubuğunda. Anlam veremedim tabii. Formülü yazdım ama tabii braketlerle birlikte ve hata. Sayfaya tekrar girdim, defalarca kontrol ettim ama bir hata yok. Tabii o sayfayı yazan eleman braketin yazılmaması gerektiğinden ve yazdığım tuş kombinasyonundan bahsetmediği gibi “Dizin” formülü hakkında da bir şey yazmamıştı. 3 gün süren sancılı bir araştırma sonrasında cevabı buldum. Dedim ya biraz inatçı bir insanım. (Siz 3 gün araştırma yapmayın diye ben peşin peşin söyledim).

Neyse şimdi gelelim formülün nasıl çalıştığına. Öncelikle aradığımız ifade A sütununda “Onur” olan satırlar, ve tarih sütununda da 12/10/2016 olan satırlar. Dikkat ederseniz aradaki ifade tam da bunu yapıyor. Daha detaylı olarak formül önce A2:A42 aralığını alıyor ve bunu bir dizine dönüştürüyor, (Ahmet, Mehmet, Mustafa, Hasan, Berk, Onur,…) sonrasında bu dizindeki elemanları teker teker “Onur” değeri ile karşılaştırıyor ve (0, 0, 0, 0, 0, 1, …) şeklinde bir dizin elde ediyor. Daha sonra aynı işlemi tarih sütunu için tekrarlıyor ve yine (0,0,0,0,0,…) bir dizin elde ediyor. Bir sonraki adımda ise bu iki dizini eleman eleman çarparak yeni bir dizin elde ediyor. İşte bizim aradığımız değer bu son dizin içinde. Aradığımız değer ne peki? Her ikisinin de 1 olduğu durum, yani 1. Ve bu eşleşme dizinlerin 33. elemanında gerçekleşmiş. Tabloya bakarsanız 33. satırda Onur ve 12/10/2016 tarihini görebilirsiniz. Ve hatta bu çarpımı daha fazla sütundaki verileri de kıyaslayacak şekilde genişletebilirsiniz.

Peki CTRL+SHİFT+ENTER ne işe yarıyor? Daha önce dizin formülü dediğimi hatırlarsınız. İşte bu kombinasyon formülü bir dizin formülüne dönüştürüyor. Neden? Excel’deki pek çok formül tek bir hücre referans alınarak çalışır. Yani girdiğiniz değeri hücrenin içeriği ile kıyaslar. Ancak aradığımız değer bir aralıkta yer alıyor ve aralıktaki her bir hücre değerini aradığımız değer ile kıyaslamak istersek ne olur? Arama yapılan aralık bir dizin haline getirilir. Örnek olarak (“Onur”=”$A$2:$A$41”) ifadesindeki aralık (Ahmet, Mehmet, Mustafa, Hasan, Berk, Onur,…)  gibi. Normal şartlar altında Excel bu karşılaştırmayı yaparken dizinin tümünü alır ve bu dizini “Onur” ifadesi ile kıyaslar ancak “Onur” ifadesi bir metin değeri iken karşılaştırma yapılan aralık birden fazla değer içeren bir dizindir ve Excel bu durumda metin ile dizin kıyaslaması yapamaz ve “#YOK” (#NA) hatası verir, programlama bilenler zaten bu tarz ifadelere alışkındır ancak “METIN”?=(A, B, C,…) gibi bir kıyaslama programlama açısından mümkün değildir. Ve fakat SHIFT+CTRL+ENTER tuşuna basıldığında Excel’e karşılaştırmanın eleman eleman yapılması talimatı verilir. Dizinin her bir elemanını “Onur” ile kıyasla ve sonucu 0 veya 1 olarak bir dizine yerleştir. Yani artık kıyaslama Metin?=(A, B, C,…) değil (Metin?=A, Metin?=B, Metin=?C, …) haline gelir, Zaten bu yüzden buna dizin formülü deniyor ya. İşin güzel tarafı bu taktiği tek hücrenin değerini kıyaslayan tüm formüllere uygulayabilirsiniz. Daha iyi anlaşılması için aşağıdaki videoda formülün nasıl çalıştığını gösterdim. Dikkatle inceleyin lütfen.