Kaçıncı formülü Excel’in pek değeri bilinmeyen formüllerinden birisidir. Ancak buna rağmen oldukça fayda sağlayan ve özellikle daha önce gördüğümüz İNDİS (INDEX) ve daha sonra göreceğimiz KAYDIR (OFFSET) formülleri ile beraber kullanıldığında harikalar yaratan bir formüldür kendileri. Özellikle “Hazırla ve Unut” tarzı tablolar hazırlıyorsanız vazgeçemeyeceğiniz bir formüldür diyebilirim.

Bu kadar övdükten sonra gelelim ne işe yaradığına. Teknik olarak bu formül bir aralıkta aradığınız değerin hangi sırada bulunduğunu bulmaya yarayan bir formüldür. Sıra diyorum çünkü yatay ve dikey aralıklarda kullanılabilir ve aralık istenilen yerde başlayabilir. Gelelim formülümüzün yazılımına.

=KAÇINCI(Aranan Değer; Aranan Dizi; Eşleşme Türü)

Formülün açıklamasına gelince “Aranan Değer” bir aralıkta bulmak istediğimiz değeri ifade eder. Metin, Tarih, Rakam gibi farklı değerler girebiliriz. Aranan Dizi ise bu değeri bulmak istediğimiz aralığı ifade eder. Eşleşme türü ise daha önce arama işlevlerinde anlattığım gibi bire bir uyan değeri veya yakın değeri bulmak için kullanılır. Buradan ilgili konuya erişebilirsiniz. Şimdi bir önceki derste kullandığımız dosyayı tekrar açalım. Elinde olmayanlar için dosya linkini aşağıda verdim.

Uygulama Dosyası

Hemen formülümüzü deneyelim. Örnek olarak Onur adlı müşterinin ilk kaçıncı satırda yer aldığını bulalım. Formülümüz:

=KAÇINCI(“Onur”;$A$2:$A$41;0)

Bu formül ne yapıyor; A2:A41 arasındaki tüm hücreleri bir dizin olarak alıyor ve bu dizinin içeriğini “Onur” ile kıyaslanıyor.  Ve ilk bulduğu eşleşmedeki numarayı geri döndürülüyor. Yani aralıkta birden fazla “Onur” değeri var ise sadece ilk karşılaştığı numara geri döndürülür. Burada dikkat edilecek bir başka konu da saymaya her zaman 1’den başlanması. Aşağıdaki resimde formülün işleme mantığını göstermeye çalıştım.

Kaçıncı Formülü Çalışma Mantığı

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.

Kaçıncı Formülü Dizin Şeklinde Kullanımı

Bu tarz dizin formüllerini alt alta satır başlıklarında arama yapmak için de kullanabilirsiniz. Örnek olarak yıllar ve aylar içeren bir tablomuz olsun. 2016 yılının Ocak ayının hangi sütunda olduğunu bulmak için bu formülü kullanabilirsiniz. Ancak burada dikkat etmeniz gereken husus özellikle yıl gibi 12 ayı kapsayan aralıklarda her bir ay için yıl değerinin de girilmesi zorunluluğu. Aşağıdaki gibi bir tablo olduğunu düşünün. 2016 ve 2017 yılları gördüğünüz gibi “Birleştir ve Ortala” komutu ile 12 sütuna yayılmış. Bu durumda sadece ilk hücrede “2016” değeri olacak yani “OCAK” yazan hücrenin üstündeki hücrede, diğerleri boş olacaktır. Bunu bir dizin formülünde kullandığınızda oluşacak dizin (2016, 0, 0, 0….) şeklinde olacaktır. Bu durumda “Yıl” ve “Ay” kıyaslaması için oluşturulan her çarpım, eğer Ay değeri için Ocak kullanılmıyor ise, “0” değerini döndürür. Bunu aşmanın yolu, her bir ay değerinin üstüne yıl değerini de eklemektir. Bu da çirkin bir görünüm oluşturur, görünümü düzeltmek için o satırı gizleyebilirsiniz. Başka ve satır gizlemenizi gerektirmeyen bir yöntem daha var. Ancak onu farklı bir konuda anlatayım.

 
Birleştirilmiş Hücrelerde Kaçıncı Formülünün Dizin Olarak Kullanılması

Biraz kafa karıştırıcı değil mi? Gayet normal. İlk başta anlamak zaman alıyor. En güzel yolu formülün nasıl çalıştığını görmek için Formül Değerlendir seçeneğini kullanın. Ve kendiniz inceleyin. İnceleyerek nasıl bir mantıkla çalıştığını daha rahat çözebilirsiniz. Her ne kadar videolarda gösterilse de en güzeli uygulayarak öğrenmektir.

Size bir sır daha vereyim mi? Yukarıda yazdığımız formülü İNDİS (veya KAYDIR) formülü ile birleştirerek ne kadarlık satış yaptığınızı da bulabilirsiniz. Harika değil mi? Bununla ilgili çok güzel uygulamaların yer aldığı bir yazı da yazmayı düşünüyorum. Şimdilik siz bu gösterdiklerimi iyice sindirin. Bu yazının sonuna geldik. Lütfen öneri ve taleplerinizi bana iletmeyi unutmayın.

İlginizi Çekebilecek Diğer Yazılar
Etiketler: , ,
    • macro içinde kelime arama yapmak istiyorsanız Range(Aralık).Find(“Metin”) işlevini kullanabilirsiniz. eğer aranan metin bulunur ise bir hücre geri döndürülür. Find işlevinin bir çok parametresi var. onları kullanarak formüller dahil olmak üzere farklı aramalar yapabilirsiniz.

  1. Set FoundCell = Sayfa1.Range(“B:B”).Find(What:=TextBox1.Text) b sutunundaki aranan değeri bul işe yarıyor. umarım başkalarınında işine yarar

  2. BİR ÇALIŞMA SAYFASINA DİNAMİK OLARAK SÜREKLİ VERİ GİRİLİYORSA VE BU SAYFANIN BİR SÜTUNUNDA SÜREKLİ OLARAK TEKRAR EDEN (ID, İSİM, vs) BİLGİLER MEVCUT İSE, TÜM TEKRAR EDEN VERİLERE AİT, SADECE EN SON GİRİLMİŞ OLAN SATIRLARINI FİLTRELEMEK İÇİN NASIL BİR YOL İZLEMEK GEREKİR?
    öRNEK:(MÜŞTERİLERİNİN HER BİR ALIŞVERİŞİNİ BİR ÇİZELGEDE ALT ALTA DİNAMİK OLARAK YAZAN BİR BAKKAL OLDUĞUNU VAR SAYALIM, HER MÜŞTERİNİN SADECE EN SON ALIŞVERİŞİNE AİT SATIRLARI AYNI ANDA GÖRMEK İSTERSE NASIL BİR YOL İZLEMELİ?) ŞİMDİDEN TEŞEKKÜRLER.

Leave a Reply