Listede aynı değerde birden fazla olduğunda bunların kaç adet olduğunu ve sıralanmış ise sadece bu değerleri bir alt tabloya ayırmayı göstermiştim. Peki biz buradaki tüm değerleri sıralama yapmadan başka bir tabloya nasıl çekebiliriz? Bu yazımda bunu nasıl yapabileceğimizi göstereceğim.

1. Hazırlık

Bu uygulamaya başlamadan önce indis, toplama, kaydır formüllerini bildiğinizi varsayıyorum. Eğer bu formüllerin yazılımı ve nasıl çalıştığını bilmiyorsanız önce ilgili yazılarımı okuyun lütfen.

Ben aşağıda verdiğim tablo üzerinden uygulama yapacağım. Siz isterseniz bu tabloyu isterseniz elinizdeki farklı bir tabloyu kullanabilirsiniz. Eğer kendi tablonuzu kullanacaksanız formüllerde kullandığım hücre adreslerini kendi tablonuza göre uyarlayın.

Çoklu Eşleşme Bulma Ana Tablo

Aradığım değerleri tutmak üzere bana ikinci bir tablo lazım. Bu ikinci tabloyu ben F2 hücresinden itibaren aşağıdaki gibi hazırladım. Siz istediğiniz sayfada istediğiniz yerde bu tabloyu oluşturabilirsiniz. Tabii formül yazarken ilgili referanslara dikkat etmeniz gerek.

Çoklu Eşleşme Bulma Yardımcı Tablo
2. Uygulama
a. Satır ve Eşleşen Satırları Bulma

İlk aşamada yapmak istediğim şey  aradığım değerin verdiğim bir aralıktaki değerlerle karşılaştırmak ve eşleşen satırların numarasını döndürmek. Hücrenin satırını bulmak için kullandığımız formülü yazmakla başlayalım:

=SATIR($B$2:$B$12)

Bu formülün sonucu {2, 3, 4, … } şekliden bir dizin olacaktır. Aradığım değeri bir liste içindeki değerler ile kıyaslama formülü de diğer yazılarımda da olduğu gibi oldukça basit:

=($F$3=$B$2:$B$12)

Bu formül de { YANLIŞ, DOĞRU, YANLIŞ,….} şeklinde bir dizin döndürecektir. Bu iki dizini bölersek:

=(SATIR($B$2:$B$12))/($F$3=$B$2:$B$12)

bu formül ile satır numarası, karşılaştırma sonucu YANLIŞ ise 0, DOĞRU ise 1 değeri ile bölünür ve elimizde {#SAYI/0!, 4, #SAYI/0!, #SAYI/0!, #SAYI/0!…} gibi bir dizin geçer. Dikkat ederseniz bu dizin bizim istediğimiz satır numaralarını ve hata değerlerini barındırıyor.

b. Dizin Oluşturma ve Değeri Çekme

Hata değerini dikkate almadan rakam değerlerini nasıl çekebiliriz? Önce bu formülü dizin olarak işlenebilecek hale getirmemiz gerek. Bunu da İNDİS formülü ile yapıyoruz.

=İNDİS((SATIR($B$2:$B$12))/($F$3=$B$2:$B$12);;)

Artık bu dizinden eşleşme olan satır numaralarını çekebiliriz. Dikkat ederseniz eşleşmenin gerçekleştiği satır değerleri küçükten büyüğe doğru artıyor. Bu desenden küçük olan değerleri, hata değerlerini ele almadan çekmemiz gerek. Bunun için de TOPLAMA  formülünü kullanabiliriz. Hangi küçük değerini alacağımız ise tabii ki formülün yazıldığı satırdan gelmeli.

Şimdi isterseniz ilk olarak hangi en küçük değeri çekeceğimizi, formülün içinde bulunduğu satıra göre hesaplayan formülü yazalım:

=SATIR()-SATIR($G$2)

Eğer bu formülü G3 hücresine yazarsak 1, G4 hücresine yazarsak 2 değerini elde ederiz. Bu sayede listemizdeki en küçük 1,2,… değerlerini alt alta olacak şekilde çekebiliriz. Şimdi tüm bu formülleri TOPLAMA formülünde birleştirelim:

=TOPLAMA(15;3İNDİS((SATIR($B$2:$B$12))/($F$3=$B$2:$B$12);;);SATIR()-SATIR($G$2))

Bu formülde 15 rakamı en küçük değerleri bulmak istediğimizi, 3 ise hatalı değerleri yok sayılması istediğimizi ifade ediyor. Formülü aşağı doğru çoğaltınca sırası ile 4, 9, 12 döndürüyor. Eğer istediğimiz rakam yok ise #SAYI! hatası veriyor. Bununla sonradan ilgileneceğiz. 

c. Kaydır Formülü İle Ana Tablodan Değer Çekme

Şimdi önce KAYDIR ile ana tablodan, alt tabloya almak istediğimiz değerleri çekelim:

=KAYDIR($B$1;TOPLAMA(15;3İNDİS((SATIR($B$2:$B$12))/($F$3=$B$2:$B$12);;);SATIR()-SATIR($G$2))-1;1;1;1)

Bu formülü ben G3 hücresine yazarak aşağıya doğru uzattığımda sırası ile Hasan 1, Hasan 2 ve Hasan 3 değerlerini çektim. formül Sonrasında #SAYI! hatası verdi. Bu hatayı da EĞERHATA formülü ile engelleyebiliriz. Formülümüze bunu da ekleyelim:

=EĞERHATA(KAYDIR($B$1;TOPLAMA(15;3;İNDİS((SATIR($B$2:$B$12))/($F$3=$B$2:$B$12);;);SATIR()-SATIR($G$2))-1;1;1;1);””)

Bu formülü aşağıya doğru uzattığımızda artık hata olan değerler yerine bir şey gösterilmeyecek. Formülü bir yan sütunu çekecek şekilde güncelleyerek H sütununa da yazdığımda seçtiğim kişiye ait diğer verileri de yeni tabloya çekebiliyorum.

=EĞERHATA(KAYDIR($B$1;TOPLAMA(15;3;İNDİS((SATIR($B$2:$B$12))/($F$3=$B$2:$B$12);;);SATIR()-SATIR($G$2))-1;2;1;1);””)

Aşağıdaki videoda formüllerin çalışmasını gösterdim.

Çoklu Eşleşme Bulma Formül Çalışması
3. Neler Yapılabilir

Bu örnekte tablo kullanmadım. Sizler tablo üzerinde bu uygulamayı yapabilirsiniz. Ek olarak KAYDIR formülündeki sütun sayısı elle yazdım. Buraya da bir KAÇINCI formülü yazarak yardımcı tablonun başlıklarına göre ana tablodaki sütunu bulan bir formül yazılabilir.

Etiketler: ,

Leave a Reply