1. Amaç
Daha önce göstermiş olduğum tek bir listede yer alan benzersiz değerlerden benzersiz olanları başka bir listeye dönüştürmek için kullanabileceğimiz formülleri göstermiştim. Şimdi ise bunu bir adım ileriye taşıyacağım. Yani birden fazla değerin yer aldığı sütunlarda, birden fazla kıstasa göre benzersiz olan değerleri farklı bir listeye nasıl dönüştürebiliriz bunun uygulamasını yapacağım. Her zaman olduğu gibi önce tablomuzu oluşturalım.
| İsimler | Kıstas 1 | Kıstas 2 |
|---|---|---|
| Ahmet | 1 | 1 |
| Elif | 2 | 3 |
| Nazife | 2 | 3 |
| Elif | 3 | 2 |
| Nazife | 3 | 3 |
| Doğan | 2 | 1 |
| Ceren | 1 | 2 |
| Mehmet | 1 | 1 |
| Nazife | 1 | 1 |
| Mehmet | 2 | 3 |
| Ahmet | 1 | 2 |
| Nazife | 2 | 2 |
| Ahmet | 1 | 2 |
| Hasan | 2 | 2 |
| Nazife | 1 | 2 |
| Mehmet | 2 | 1 |
Eğitimde bu örnek tabloyu kullanabileceğiniz gibi kendi değerlerinizin yer aldığı farklı bir liste de kullanabilirsiniz. Uygulamanın yer aldığı dosyaya buradan erişebilirsiniz.
Şimdi bu tabloda Kıstas 1 için 1, Kıstas 2 için 2 değeri olan benzersiz isimleri bulmak isteyelim. Bunun için de bir kaç formül göstereceğim. Sırası ile başlayalım.
2. İndis-Kaçıncı İle
a. Listeleri Karşılaştırma
Burada yapmak istediğim ilk şey isimler listesinde yeni listemde olmayan isimleri bulmak. Bunun için de yine EĞERSAY formülünü kullanacağım. Eşsiz değerleri bulma yazımda gösterdiğim formülü yazıyorum yine. Benim tablomda değerler B3’den başlıyor ve formülü F sütununa 2. satıra yazıyorum. Buna dikkat lütfen.
=EĞERSAY($F$2:$F2;$B$3:$B$18)
Bu formül ile karşılaştırma sonuçlarını elde edeceğim, aslında tek başına kullanılırsa eş değerlerin kaç tane olduğunu hesaplıyor ancak aslında formül ilk aşamada eşleşme sonucuna göre 1, 0 değerlerinden oluşan bir dizin çıkarıyor. Sonrasında buradaki 1’leri topluyor.
Diğer yazımda anlattığım yöntemden farklı olarak burada eşleşmeyen ilk değeri bulmak istiyorum. Bunun için de DEĞİL formülü ile elde ettiğim değerlerin tersini elde edeceğim, yani 1 ise 0, 0 ise 1 haline getireceğim:
=DEĞİL(İNDİS(EĞERSAY($F$2:$F2;$B$3:$B$18);;))
b. Kıstasları Bulma
Şimdi bu formüle diğer şartlarımızı eklememiz gerek. Temelde bunlar da bir kıyaslama olduğundan eşittir ifadesi ile kıyaslama yapacağız:
=($C$3:$C$18=1)
=($D$3:$D$18=2)
Bu iki formül bize kıyaslama sonuçlarına göre doğru ve yanlışlardan oluşan iki farklı dizi verir. Biz her iki şartın da doğru olduğu durumu bulmak istiyoruz. Yani iki değerin çarpımı 1’e eşit olmalı:,
=($C$3:$C$18=1)*($D$3:$D$18=2)
Bu formül sonucu iki karşılaştırma sonucu VE işlevinde olduğu gibi birleştirilerek her iki şartın da doğru olduğu satırların 1 olduğu bir dizi döndürür.
c. Tüm Kıstasları Birleştirme
Şimdi bütün bu formülleri birleştirmemiz lazım. Yine VE işlevi gibi sadece tüm karşılaştırmaların doğru olduğu durumda 1 değerini döndüren bir liste elde etmek istediğimden bu 3 formülü de çarpacağım.
=DEĞİL(İNDİS(EĞERSAY($F$2:$F2;$B$3:$B$18);;))*($C$3:$C$18=1)*($D$3:$D$18=2)
Dizi formülleri kullanmak istemediğimden ben bu formülün sonucu İNDİS işlevi ile birleştireceğim:
=İNDİS(DEĞİL(İNDİS(EĞERSAY($F$2:$F2;$B$3:$B$18);;))*($C$3:$C$18=1)*($D$3:$D$18=2);;)
EĞERSAY formülünün sonucunu DEĞİL ile tersine çevirmemizin sebebi de aslında bu. Eğer DEĞİL ile bu formülün sonucunu ters çevirmezsek bu durumda karşılaştırma tamamen 0’lardan oluşan bir dizi haline gelecek ve bu durumda çarpım sonrasında elde edeceğimiz dizi de maalesef sadece 0’lardan oluşacak ve formülümüz aradığımız değerleri döndürmeyecektir.
Bu işlevin sonunda elimde aşağıdaki gibi değerlerin olduğu bir dizi oluşacaktır.
d. Tüm Şartları Karşılayan Değeri Bulma
Elde ettiğimiz dizide 1 değerleri tüm şartların karşılandığı satırı ifade ediyor. Eğer ilk 1 değerini hangi sırada döndürdüğünü bulabilirsek bu sıradaki ana liste elemanını çekebiliriz. İlk 1 değerinin hangi sırada bulunduğunu bulmak ta KAÇINCI işlevinin görevi.
=KAÇINCI(1;İNDİS(DEĞİL(İNDİS(EĞERSAY($F$2:$F2;$B$3:$B$18);;))*($C$3:$C$18=1)*($D$3:$D$18=2);;);0)
Bu formülü yazdığımızda ilk 1 değerinin 7. sırada olduğunu elde ediyoruz. Şimdi ana listemizdeki 7. sıradaki elemanı çekmemiz gerek. Bunu da İNDİS işlevini kullanarak yapabiliriz:
=İNDİS($B$3:$B$18;KAÇINCI(1;İNDİS(DEĞİL(İNDİS(EĞERSAY($F$2:$F2;$B$3:$B$18);;))*($C$3:$C$18=1)*($D$3:$D$18=2);;);0);)
Formülü aşağıya doğru uzattığımızda tüm eşleşen değerleri yeni listemize aktarmış olacağız. Ancak Ana Listemizdeki tüm elemanlar aradığımız kıstasa uymadığından hali ile uyan değerlerden sonra #YOK hatası oluşacaktır.
e. Hataları Kaldırma
Bu listedeki #YOK hatalarını gizlemek için EĞERHATA işlevini kullanarak formülün hata döndürmesi durumunda boş değer göstermesini sağlayacağız.
=EĞERHATA(İNDİS($B$3:$B$18;KAÇINCI(1;İNDİS(DEĞİL(İNDİS(EĞERSAY($F$2:$F2;$B$3:$B$18);;))*($C$3:$C$18=1)*($D$3:$D$18=2);;);0););””)
Bu formülü alta doğru uzattığımızda elimizde aşağıdaki gibi sadece istediğimiz kıstaslara sahip eşsiz değerlerin olduğu bir liste olacaktır.
2. Ara İşlevi İle
a. Tüm Şartları Karşılayan Değeri Bulma
Bu yöntemde, bir önceki uygulamadaki ilk 3 adımı takip edeceğim. Bu adımlar sonrasında elimde sadece 1 ve 0’lardan oluşan liste oluşacak. Burada tekrar anlatma gereği duymuyorum.
=İNDİS(DEĞİL(İNDİS(EĞERSAY($F$2:$F2;$B$3:$B$18);;))*($C$3:$C$18=1)*($D$3:$D$18=2);;)
Sonrasında 1 değerini bu elde ettiğim listeye böleceğim. Liste 1 ve sıfırlardan oluştuğundan yeni elde edeceğim liste sıfıra bölüm hatası ve 1 değerlerinden oluşacak:
=1/İNDİS(DEĞİL(İNDİS(EĞERSAY($F$2:$F2;$B$3:$B$18);;))*($C$3:$C$18=1)*($D$3:$D$18=2);;)
b. Ara İşlevi İle Uyan Değerin Sırasını Bulma
Şimdi ARA işlevi bildiğiniz üzere aradığımız değeri ve eğer yok ise en yakın düşük değeri döndürdüğünü anlatmıştım. Benim bu dizinde aradığım değer 1, ancak listedeki en son sırada yer alan değeri bulmak istediğim için 2 değerini aratacağım. Listede 2 değeri olmadığından en son bulduğu 1 değerine ana listede denk gelen değeri çekeceğim:
=ARA(2;1/İNDİS(DEĞİL(İNDİS(EĞERSAY($F$2:$F2;$B$3:$B$18);;))*($C$3:$C$18=1)*($D$3:$D$18=2);;);$B$3:$B$18)
Bu formülü yazarak aşağıya doğru uzattığımda elimde aşağıdaki gibi sondan başa doğru sadece eşleşen değerlerin yer aldığı liste gelecektir. Ve tabii bir önceki adımda olduğu gibi bir de #YOK hatası da bu listede gözükecek. Bunu da EĞERHATA işlevi kullanarak kaldıracağım:
=EĞERHATA(ARA(2;1/İNDİS(DEĞİL(İNDİS(EĞERSAY($F$2:$F2;$B$3:$B$18);;))*($C$3:$C$18=1)*($D$3:$D$18=2);;);$B$3:$B$18);””)
Bu formülü uyguladığımda elimde aşağıdaki gibi bir liste oluşacaktır.
3. Ek Yöntemler
Bunların haricinde farklı yöntemler de var misal dizin formülü kullanma. Temelde dizin formülü ile ilk gösterdiğim yöntem hemen hemen aynı olduğundan burada göstermedim. Siz isterseniz formülün yazılımında ilk sırada kullandığım İNDİS (KAÇINCI’dan sonraki) formülünü yazmayarak bunun yerine CTRL+SHIFT+ENTER ile formülü çalıştırabilirsiniz.
Ya da Excel 365 ve 2021 sürümlerinde gelen FİLTRE, BENZERSİZ gibi işlevleri beraber kullanarak çok daha basit ve hızlı sonuç alabilirsiniz.