1. KAÇINCI İşlevi
KAÇINCI işlevi aranan değerin, arama yapılacak değerlerin yer aldığı yatay veya dikey listede tam olarak hangi konumda, kaçıncı sırada olduğunu bulmaya yarayan bir işlevdir. Bu sayede özellikle İNDİS ve KAYDIR gibi işlevler ile birleştirerek çok işlevli formüller yazabilirsiniz.
2. Yazılımı ve Parametreleri
KAÇINCI işlevinin yazılımı:
=KAÇINCI(aranan_değer; aranan_dizi; [eşleşme_türü])
aranan_değer: Bulmak istediğimiz değeri ifade eder. Metin, Tarih, Rakam gibi farklı değerler veya bu değerlerin yer aldığı bir hücre adresi girilebilir.
aranan_izi: bu değeri bulmak istediğimiz aralığı ifade eder. Buraya da yine statik bir dizi veya hücre aralığı girilebilir. Aralık yatay veya dikey olabilir.
[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. Buraya girilebilecek değerler:
1: aranan değere eşit veya en yakın küçük değeri arar. Varsayılan değerdir.
0: birebir aynı değer aranır
-1: aranan değere eşit veya en yakın büyük değeri arar
1 ve -1 değerlerinin düzgün çalışması için arama yapılan aralığın sırası ile artan ve azalan şekilde sıralanmış olması gerekmektedir. Sıralanmamış bir liste içinde arama yaparsanız formül yanlış bir değer geri döndürecektir.
3. Kullanımı
KAÇINCI işlevi aranan değeri bir sayfada veya tabloda yer alan satırda veya sütunda kaçıncı sırada olduğunu bulmak için kullanılan bir işlevdir. Eğer aranan değer bulunur ise işlev bu değerin hangi sırada bulunduğunu ifade eden bir rakam geri döndürür. KAÇINCI işlevi ile joker karakter kullanımı mümkündür. Genel olarak İNDİS ve KAYDIR işlevleri ile kullanılarak esnek aramalar yapılabilir.
Uygulama dosyasında Müşteri isimleri listesinde belirli bir ismi aradığımızı varsayalım bu durumda aşağıdaki formülü kullanabiliriz:
=KAÇINCI(“Onur”;$A$2:$A$41;0)
Bu formül 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.
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.
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.
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.
macro olarak kaçıncı satırda olduğunu nasıl buluruz aranan kelimenin
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.
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
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.