1. Hazırlık
Bu yazımda site ziyaretçilerinin birinden gelen bir sorunun çözümünü paylaşmak istiyorum. Dağınık ve benzer değerlerin yer aldığı bir listede en küçük 3. değeri bulmak istediğimizi varsayalım. Burada BÜYÜK veya KÜÇÜK formüllerini kullandığımızda, en küçük 2. Değerden 2 adet var ise KÜÇÜK formülü ile çektiğimi 2. Ve 3. Küçük değerler aynı olacaktır. Bu da bizim için hatalı bir sonuçtur çünkü biz farklı olan en küçük 3. Değeri arıyoruz. Peki biz benzersiz küçük veya büyük değeri nasıl bulabiliriz bu yazımda bu konuya değineceğim. Başlamadan önce uygulamada kullandığım örnek dosyaya buradan erişebilirsiniz.
Aşağıdaki resimde ne elde etmek istediğimi gösterdim.
2. Dizin Formülü İle
Şimdi aralığımıza hemen bizim güzel KÜÇÜK formülünü C3 hücresinden başlayarak uygulayalım:
=KÜÇÜK($B$3:$B$32;SATIR()-SATIR($C$2))
Bu formülü aşağıya doğru çoğaltırsanız B sütununda yer alan tüm değerlerin büyükten küçüğe sıralanmasını elde edersiniz. Gördüğünüz gibi burada birden fazla olan değerler alt alta yer alıyor. Yani biz 3. En büyük rakama baktığımızda üstten 3. Satırda yer alan 11 değerini görüyoruz çünkü en küçük değer olan 10’dan 2 tane var. Ancak ben burada 12 değerini görmek istiyorum.
Ne yapmamız gerek? İlk olarak ben bu dizindeki her bir elemanı, dizinin kendisi ile karşılaştırırsam, hangi elemanın ilk olarak hangi sırada ortaya çıktığını bulabilirim. Başka bir ifade ile eğer bir değerden 2 tane var ise, 2. kopyanın ilk hangi sırada ortaya çıktığını bu formülle bulabilirim. Bunu ise KAÇINCI formülü ile yapacağım:
=KAÇINCI($B$3:$B$32;$B$3:$B$32;0)
Bu formülü incelerseniz dizindeki tüm elemanları, dizin ile karşılaştırıyor. Ve bunun sonucunda dizindeki her bir elemanın ilk hangi satırda yer aldığını bize gösteriyor. Bu formüle Formül Değerlendir ile bakarsanız sonucu göremezsiniz çünkü formül geriye bir dizin döndürmesine rağmen Excel bu dizinin ilk elemanını size gösterecektir. Eğer dizinin tüm elemanlarını görmek istiyorsanız bu formülü İNDİS formülü ile birleştirmeniz gerek. Sadece formül sonucunu görmek için ben bunu yapacağım, sonrasında sileceğim:
=İNDİS(KAÇINCI($B$3:$B$32;$B$3:$B$32;0);;)
Bu formülün Formüller sekmesindeki Formül Değerlendir seçeneği ile incelediğinizde {1,2,3,4,5,6,3,8,5…} şeklinde bir dizin göreceksiniz. Aşağıda ekran görüntüsünü görebilirsiniz
Bu dizinde dikkat ederseniz 6’ya kadar düzenli artan bir rakamlar dizisi varken ardından 3,8,5 gibi düzensiz rakamlar yer alıyor. Bunun nedeni dizindeki 7 elamanın, yani 28 rakamının ilk olarak 3. Sırada yani 5. Satırda yer alıyor olması.
Burada dikkatimizi çeken bir diğer husus ta satır numarası ile eş güdümlü bir dizin oluşmuş olması. Değerlerin bulunduğu satırları da {3,4,5,6,7,8,9,10…} gibi düşünürsek bu dizin ile KAÇINCI formülünün döndürdüğü dizin arasında bir bağlantı olduğunu görüyorsunuz. Benim örneğimde ilk iki satırda değer olmadığından bu dizinden 2 çıkarırsam dizin {1,2,3,4,5,6,7,8… } haline gelecektir ve bu ilişki daha açık bir hal alacaktır. Hücrenin satırını döndüren formülü yazalım:
=SATIR($B$3:$B$32)-2
Eğer formül sonucunu bulmak isterseniz yine İNDİS formülü ekleyerek dönen dizini görebilirsiniz. Ben aşağıda sonucu veriyorum.
Eğer KAÇINCI ile bu formülün dizinlerini karşılaştırdığımızda, aynı sıradaki elemanlar birbiri ile aynı ise o sıradaki rakam dizinde ilk kez ortaya çıkmış demektir. Örnek olarak ilk dizindeki ilk 6 eleman dizinde ilk kez yer alıyor ama 7. eleman olan 28 rakamı ilk 3. satırda ortaya çıkmış. Biz bu iki dizini karşılaştırırsak elimizde tüm değerlerin ilk ortaya çıktığı sırayı gösteren bir dizin elde ederiz.
=SATIR($B$3:$B$32)-2=KAÇINCI($B$3:$B$32;$B$3:$B$32;0))
Formülü İNDİS ile birleştirerek formül değerlendirme sonucunu aşağıdaki resimde görebilirsiniz.
Eğer biz buradaki “DOĞRU” ifadeleri yerine aralıktaki değerlerimizi döndürebilirsek elimizde sadece eşsiz değerlerin olduğu bir dizin oluşacak demektir. Bunun için kullanacağımız formül EĞER formülü:
=EĞER((SATIR($B$3:$B$32)-2=KAÇINCI($B$3:$B$32;$B$3:$B$32;0));$A$2:$A$31)
Artık elimizde sadece benzersiz değerlerin yer aldığı bir dizin var. Bu dizinde KÜÇÜK formülünü kullanırsak:
=KÜÇÜK(EĞER((SATIR($B$3:$B$32)-2=KAÇINCI($B$3:$B$32;$B$3:$B$32;0));$A$2:$A$31);SATIR()-2)
Bu formülü CTRL+SHIFT+ENTER ile dizin formülü olarak hesaplatın ve aşağıya doğru çoğaltın. Artık her satırda farklı bir değerin yer aldığı en küçük değerler yer alacaktır. Eğer daha fazla küçük değeri bulamaz ise formül #SAYI! hatası verecektir. İstersenin EĞERHATA formülünü de ekleyerek bu hatayı kaldırabilirsiniz.
3. TOPLAMA Formülü İle
Bu örnekte biraz farklı bir yöntem izleyeceğim.Bir önceki örnekte kullandığım kıyaslama formülünü alıyorum
=SATIR($B$3:$B$32)-2=KAÇINCI($B$3:$B$32;$B$3:$B$32;0)
Bu kıyaslama sonrasında DOĞRU ve YANLIŞ değerlerinden oluşan bir dizin oluşuyordu. Bu değerleri çarpma ve bölme işlemlerinde kullanırken sırası ile 1 ve 0 olarak ele alınacağı zaten biliyoruz. Ben aralıktaki tüm değerleri bu dizine bölersem:
=($B$3:$B$32)/(SATIR($B$3:$B$32)-2=KAÇINCI($B$3:$B$32;$B$3:$B$32;0))
Elimde 23, 29, 28, 26, 18, 30, #SAYI/0,22, #SAYI/0….} şeklide bir dizin oluşacaktır. Bu dizinde hata olan değerleri dikkate almadan içindeki küçük değerleri bulabileceğimiz bir formüle ihtiyacımız var bu da TOPLAMA formülü.
=TOPLAMA(15;6;($B$3:$B$32)/(SATIR($B$3:$B$32)-2=KAÇINCI($B$3:$B$32;$B$3:$B$32;0));SATIR()-2)
Bu formülde 15 rakamı KÜÇÜK değeri bul anlamına gelir, 6 değeri ise formüle dizin içindeki hataları dikkate almamasını söyler. Bu formülü de aşağıya doğru uzattığımızda benzersiz küçük değerleri elde ederiz.
4. Neler Yapılabilir?
Formüllerde SATIR()-2 değeri, formülün yer aldığı satıra göre 1’den başlayarak artan bir değer verecektir. Bu da her satırda bir sonraki küçük sayıyı bulmanızı sağlar. Bunun yerine sabit bir değer yazarak o sıradaki küçük değeri bulabilirsiniz.
Eğer büyük değerleri bulmak isterseniz KÜÇÜK yerine BÜYÜK formülü kullanmalısınız. TOPLAMA formülünde de ilk değişkeni 15 yerine 14 kullanmalısınız.
Eğer sizin değerleriniz satırlarda değilde sütunlarda yer alıyor ise aralığı buna göre belirledikten sonra SATIR gördüğünüz yerlerde SÜTUN formülünü kullanabilirsiniz.