Bir listede yer alan benzersiz değerleri bulamak için birden fazla yöntem mevcut. En basit yöntemi tabii ki Veri sekmesindeki Sırala ve Filtre Uygula kısmında Gelişmiş seçeneğini kullanmaktır. Ancak benim anlatmak istediğim bu işi formül ile nasıl yapabiliriz. Öyle bir listemiz olsun ki başka bir listedeki benzersiz değerleri çıkarsın. Ancak bunu otomatik olarak yapsın.

Öncelikle bu konuda imkanlarımız kısıtlı. Bunu otomatik olarak yapabilecek yeni tür formüller geliştirilme aşamasında ve ne yazık ki Excel 2010-13-16-19 sürümlerinde bu formüller henüz eklenmedi. Ancak bu sürümlerde tamamen çaresiz de değiliz. Bunu yapabilmek için farklı formüller kullanabiliriz. Ben burada iki farklı yöntem kullanacağım. Ancak bu formüller el ile yazıldığından güncelleme konusunda zaafları var. Yani ana listemize değerler ekledikçe eşsiz değerlerin yer aldığı listemizdeki formüllerin de tüm ana listeyi ele alacak şekilde güncellendiğinden emin olmamız gerekecektir.

Bu kısa uyarıdan sonra gelelim bu işi formülle nasıl yapabileceğimize. Aşağıdaki gibi bir isimler listemiz olduğunu varsayalım.

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

Şimdi bu isimlerden sadece benzersiz olanları çıkarmak istediğimizi varsayalım. Öncelikle elimizde iki liste olmalı. İkinci liste benzersiz isimleri tutmalı. Bu iki listeyi karşılaştırmalı ve ikinci listede olmayanları eklemeliyiz. Ancak burada eşsiz isimlerin yer aldığı tablo aşağıya doğru uzatıldıkça üstteki satırları da içerecek şekilde oluşturulmalı. Karşılaştırma da iki dizin arasında olduğundan bunu bir dizin formülü ile yapabiliriz. Önce formülümüzü yazalım isterseniz, sonra nasıl çalıştığına bakarız. Resimdeki örnek üzerinden gittiğim için formülü E3 hücresine yazacağım, siz listenizin konumuna göre farklı adresler kullanabilirsiniz:

={EĞERHATA(İNDİS($B$3:$B$13;KAÇINCI(0;EĞERSAY($D$2:$D2;$B$3:$B$13);0));"")}

Formülü braket olmadan yazarak SHIFT+CTRL+ENTER tuşuna basarak çalıştıralım. Ve D13 hücresine kadar sürükleyerek çoğaltalım. Göreceğiniz gibi ilk listemizde yer alan tüm isimlerden sadece birer tane olacak şekilde yeni bir listemiz oluştu. Formül nasıl çalışıyor bir bakalım isterseniz.

Yapmak istediğimiz şey ikinci tabloda yer alan değerleri ilk tablodaki değerler ile kıyaslamak. Eşleşen ve eşleşmeyen değerleri 1 ve 0 olarak dizine yerleştirmek. Karşılaştırmayı yapacak formülü aşağıda ayırdım. Karşılaştırma yaparken de ikinci tablonun başlangıç hücresini sabitleyerek ancak son hücresini formülün yer aldığı hücrenin bir üstünü alacak şekilde değişken olacak şekilde tanımlayarak bu tablonun sürekli değişmesini sağlıyorum. Formüldeki $E$2:$E2 ifadesi tam da bunu yapıyor.

=EĞERSAY($E$2:$E2;$C$3:$C$13)

Bu formül dizin formülü olarak çalışacağından karşılaştırma sonucu {1,1,1,0,1,1... gibi bir dizin oluşturacaktır. Sonrasında bu kıyaslamada eşleşmeyen ilk girdiyi bulmak bunun için de Kaçıncı formülünü kullanıyoruz. Aradığımız değer eşleşmeme durumu yani 0 durumu:

=KAÇINCI(0;EĞERSAY($E$2:$E2;$C$3:$C$13);0)

Bir üstte gösterdiğim örnek dizine göre bu değer 4. elemanda bulunmuş. Sonrasında ise INDİS formülü ile eşleşmenin ana listemizdeki 4. satırdaki değeri okuyoruz.

=INDIS($C$3:$C$13;KAÇINCI(0;EĞERSAY($E$2:$E2;$C$3:$C$13);0);)

Bu işlem formül aşağıya doğru çoğaltıldığında eşleşmeyen değer bulunamayıncaya kadar devam edecektir. Ve bulunamadığı zaman da #YOK hatası verecektir. Eğer bu hatanın gözükmesini istemiyor isek bu sefer başka bir formül ile hata durumunda farklı bir değer gösterilmesini sağlamamız gerek. Bunu da EĞERHATA formülü ile yapıyoruz.

={EĞERHATA(İNDİS($B$3:$B$13;KAÇINCI(0;EĞERSAY($D$2:$D2;$B$3:$B$13);0));"")}

Formül sonucunda bir hata oluşursa hata yerine boş ifade gösterilmesini sağlıyoruz. Aşağıdaki videoları izleyerek formülün yazılması, sonucu ve tabii ki değerlendirmesini görebilirsiniz. Daha büyük hallerini görmek için lütfen üzerlerine tıklayın.

Eşsiz Değerleri Listeleme Formülü
Eşsiz Değerleri Bulma Formülü Değerlendirme

Burada kullanacağım formül dizin formülü için kullandığım formül ile hemen hemen aynı. Ancak dizin formülü kullanmayacağım için karşılaştırma sonucunu dizin olarak değerlendirebilecek bir formüle ihtiyacım var. Bu formül de bizim mucizevi formülümüz İNDİS. Yine karşılaştırma formülümüzü D3 hücresine yazarak başlayalım:

=EĞERSAY($D$2:$D2;$B$2:$B$13)

Şimdi bu formülü bir dizine çevirmek için İNDİS formülünü kullanalım:

=İNDİS(EĞERSAY($D$3:$D3;$B$4:$B$14);;)

Bu formülü incelediğimizde karşlılaştırma işleminin D2 hücresinden başlayıp formülün yazıldığı hücre olan D3 hücresinin bir üstünde son bulan aralıktaki değerlerle B3:B13 arasındaki değerler ile yapıldığını görüyoruz ve bu karşılaştırmanın sonucunda eğer karşılaştırma doğru ise 1, yanlış ise 0 değerinin bulunduğu {1;0;1....;0) gibi bir dizi elde ediyoruz. Eğer burada İNDİS formülünü kullanmaz ise bu değerler dizisi tek bir değere indirgenir. Ancak İNDİS ile kullanılır ise dizinin tamamı alınır.

Şimdi elde ettiğimiz bu dizinde bizim ikinci listemizde yer almayan ilk girdiyi aramamız lazım. Yani eşleşmeyen ilk girdinin hangi sırada gerçekleştiğini bulmamız gerek. Bunu da KAÇINCI formülü ile yapıyoruz.

=KAÇINCI(0;İNDİS(EĞERSAY($D$3:$D3;$B$4:$B$14);;);0)

Bu formül ile de elde ettiğimiz {0;1;0;...;0} dizisinde karşılaşmama durumu olan 0 değerinin ilk hangi elemanda bulunduğunu buluyoruz.

Ardından artık ilk listemizde bulduğumuz sıradaki elemanı çekmemiz gerekiyor. Bunu da yine İNDİS formülü ile yapıyoruz

=İNDİS($B$4:$B$14;KAÇINCI(0;İNDİS(EĞERSAY($D$3:$D3;$B$4:$B$14);;);0);)

Bu formülü aşağıya doğru çoğalttığımızda karşılaştırma aralığı formülün yazılı olduğu satırın bir üstün göre tekrar oluşacağından karşılaştırma dizinimiz her satırda farklı sıralamada 1 ve 0 lardan oluşan bir dizin haline gelecektir. Ve en nihayetinde hiç bir 0 değerinin yer almadığı sadece 1'lerden oluşan bir dizin haline gelecektir. İşte bu aşamada KAÇINCI formülü 0 değerini bulamadığı için #YOK hatası verecektir. Bunu engellemek için ise bir başka formüle ihtiyacımız var.

=EĞERHATA(İNDİS($B$4:$B$14;KAÇINCI(0;İNDİS(EĞERSAY($D$3:$D3;$B$4:$B$14);;);0););"")

Bu formül ile sornaki formülde bir hata oluşması durumunda hata mesajı yerine hiç bir şey gösterilmemesi sağlanır.

Evet gördüğünüz gibi dizin formülüne benzeyen ancak dizin formülü gerektirmeyen ve standart bir şekilde çalışan bir formül ile birden fazla benzer girdiler içeren listemizi sadece benzersiz değerler içeren bir listeye çevirdik.

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

Leave a Reply