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. 

İsimlerKıstas 1Kıstas 2
Ahmet11
Elif23
Nazife23
Elif32
Nazife33
Doğan21
Ceren12
Mehmet11
Nazife11
Mehmet23
Ahmet12
Nazife22
Ahmet12
Hasan22
Nazife12
Mehmet21

Ş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.

  • İndis-Kaçıncı İle
  • Ara Formülü İle

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 B2'den başlıyor ve formülü F sütununa 2. satıra yazıyorum. Buna dikkat lütfen.

=EĞERSAY($F$1:$F1;$B$2:$B$17)

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. Ancak ben sonradan kullanacağım İNDİS ile bu listeyi bir dizin olarak ele alacağım.

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(EĞERSAY($F$1:$F1;$B$2:$B$17))

Ş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$2:$C$C17=1)*($D$2:$D$C17=2)

Şmdi bütün bu formülleri birleştirmemiz lazım.

=DEĞİL(EĞERSAY($F$1:$F1;$B$2:$B$17))*($C$2:$C$17=1)*($D$2:$D$17=2)

Neden "*" kullandık? Çünkü burada elde edilen sonuçlar doğru veya yanlış şeklinde 1 ve 0'lardan oluşuyor. Bu dizinlerin çarpımı ise aslında "VE" mantıksal karşılaştırmanın sonucuna benzer bir sonuç ede etmemizi sağlıyor. Yani iki dizinde eşleşen değerlerin çarpımı ancak ikisi 1 değerine sahip ise 1 olabilir. Aksi takdirde elde edilecek değer 0 olacaktır.

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 dizin haline gelecek ve bu durumda çarpım sonrasında elde edeceğimiz dizin de maalesef sadece 0'lardan oluşacak ve formülümüz hatalı sonuç verecektir.

Şimdi sırada bu formülün sonucunu bir dizin olarak ele almamızı sağlayacak İNDİS formülü ile birleşim var:

=İNDİS(DEĞİL(EĞERSAY($F$1:$F1;$B$2:$B$17))*($C$2:$C$17=1)*($D$2:$D$17=2);0;0)

Bu formülü yazıp Formül İnceleme ile incelediğimizde {1,1,1,1,....)*(1,0,0,0...)*(0,0,0,1...) gibi üç dizinin çarpımı sonucu (0,0,0,0...) gibi bir dizin elde edildiğini görebilirsiniz.

Bu açıklamalardan sonra formülümüzün ilk 1 değerini hangi sırada döndürdüğünü bulmamız gerek. Bu da "KAÇINCI" formülünün görevi.

=KAÇINCI(1;İNDİS(DEĞİL(EĞERSAY($F$1:$F1;$B$2:$B$17))*($C$2:$C$17=1)*($D$2:$D$17=2);0;0);0)

Ve tabii ana tablomuzda bu değerin ilk bulunduğu hücrenin değerine ulaşmak ve tabii ki bunun için de İNDİS kullanacağız:

=İNDİS($B$2:$B$18;KAÇINCI(1;İNDİS(DEĞİL(EĞERSAY($F$1:$F1;$B$2:$B$17))*($C$2:$C$17=1)*($D$2:$D$17=2);0;0);0);0)

Şimdi bu formül işimizi görecektir ancak formülü alt hücrelere doğru uzattığımızda eşleşen değer bulma ihtimali düşecek ve en nihayetinde bulunamayacaktır. bu durumda formül #YOK hatası verecektir. Bu hatanın gözükmesini istemiyorsak bir formül daha kullanmamız lazım:

=EĞERHATA(İNDİS($B$2:$B$17;KAÇINCI(1;İNDİS(DEĞİL(EĞERSAY($F$1:$F1;$B$2:$B$17))*($C$2:$C$17=1)*($D$2:$D$17=2);0;0);0);0);"")

Oldukça uzun bir formül oldu. Formülü yazarak aşağıya doğru uzatırsanız girdiğimiz kıstaslara göre listede eşsiz olan değerlerin bulunduğunu görebilirsiniz.

Bu sefer daha farklı bir mantık üzerinden gideceğiz. İlk önce bir önceki formülde olduğu gibi kıyaslama formüllerimizi yazalım:

=EĞERSAY($F$2:$F2;$B$2:$B$17)

Burada da diğer formülde olduğu gibi DEĞİL ile elde ettiğimiz listeyi tersine çevireceğiz:

=DEĞİL(EĞERSAY($F$2:$F2;$B$2:$B$17))

Hemen ardından da diğer kıstaslar için karşılaştırma sonuçlarını bulmak için kullandığımız formülleri yazacağız:

=($C$2:$C$17=1)
=($D$2:$D$17=2)

Şimdi burada diğer formülden farklı olarak 1 değerini bu formüllerin sonuçlarına böleceğim:

=1/DEĞİL(EĞERSAY($F$2:$F2;$B$2:$B$17))/($C$2:$C$17=1)/($D$2:$D$17=2)

Bu formül sonucunda 1/{1,1,1,1,0,.....)/{1,0,0,0,0,...}/{0,0,0,1,0,...} bir dizin elde ederiz. Bu bölümün sonunda da sadece tüm kıstasların doğru olduğu sırada 1 diğerlerinde #SAYI/0! hatasının olduğu { #SAYI/0!,1,#SAYI/0!,#SAYI/0!,#SAYI/0!,....} bir dizin elde ederiz.

Şimdi ARA formülü 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 en son değeri bulmak istediğim için 2 değerini aratacağım bu sayede sondan başa doğru tüm değerleri listeleyeceğim ve isimlerin olduğu aralıktaki değeri de geri döndüreceğim:

=ARA(2;1/DEĞİL(EĞERSAY($F$1:$F1;$B$2:$B$17))/($C$2:$C$17=1)/($D$2:$D$17=2);$B$2:$B$17)

Bu formül ile ilk aralığımızda, yani kıyaslamanın yapıldığı dizinde 1 değeri en sondan itibaren kaçıncı sırada olduğunu buluyorum ve B2:B17 aralığında aynı sıradaki elemanı geri döndürüyorum. Tabii eğer istenen değer bulunamaz ise formül #YOK! hatası verecektir. Bu hatayı görmek istemiyorsak da yine diğer örnekte olduğu gibi EĞERHATA formülü ile birleştireceğiz:

=EĞERHATA(ARA(2;1/DEĞİL(EĞERSAY($F$1:$F1;$B$2:$B$17))/($C$2:$C$17=1)/($D$2:$D$17=2);$B$2:$B$17);"")

Formülümüzü aşağıya doğru uzattığımızda tüm değerlerin diğer formülden farklı olarak ters sırada listelendiğini göreceksiniz. Bunun nedeni bahsettiğim gibi ARA formülünde kullandığımız 2 değeri. Bunu 1 yaparak ta formülü çalıştırabiliriz.

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.

İlginizi çekebilecek Diğer Yazılar
Etiketler: , , , , , , ,

Leave a Reply