Elimizde birbirine yakın değerlere sahip bir liste olduğunu varsayalım ve bu listede belli bir sapma ile en yakın değeri bulmak isteyelim. Burada ARA formülünü kullanmak isteyebilirsiniz. Ancak Ara formülü sadece sıralanmış listede arama yapar ve eğer aranan değer bulunamaz ise en yakın düşük değeri döndürür. Ben burada eğer aradığım değer tam olarak yoksa belli bir sapma oranına göre en yakın değeri bulmak istiyorum ve yerine göre düşük, büyük ve en yakın değeri döndürmek amacındayım. Örnek olarak aşağıdaki tabloyu ele alalım:
Şimdi bu tabloda sırası ile verdiğimiz tolerans değerine göre tabloda gösterdiğim en yakın değerleri bulmak isteyelim. Ben tabloyu B2 hücresinden itibaren yapıştırdım. Siz farklı bir yere aldı iseniz formüllerde başvuru adreslerini buna göre değiştirin lütfen.
1.En Yakın Değeri Bulma
En yakın değeri bulma işlemi göreceli olarak kolay bir işlem. İlk olarak aranan değer ile verilmiş değerler arasındaki farkı bulacağım:
=$B$3:$B$7-$C$3
Bu formül ile tüm verilen değerler ile aradığımız değer arasındaki farkları çıkarak {0,01; 0,03; -0,03;…} bir dizin oluşturacağız. Bizim aradığımız küçük veya büyük olmasına bakmadan en düşük bulmak. Bunun için – olan değerleri + olacak şekilde döndürmemiz lazım. Bunun için MUTLAK formülünü kullanacağım:
=MUTLAK($B$3:$B$7-$C$3)
Bu formül ile elde ettiğimiz değerleri bir dizin olarak işleyecek formülü yazalım. Bildiğiniz gibi bunu İNDİS ile yapıyoruz:
=İNDİS(MUTLAK($B$3:$B$7-$C$3);;)
Artık elimde verilen değerlerle aranan değer arasındaki farkları sadece + sayılardan oluşan {0,01; 0,03; 0,03;…} şeklinde bir dizin var. Bu dizin içinde en düşük değeri arayacağız. Çünkü en düşük fark aradığımız değere en yakın değerdir. Bunun için MİN formülünü kullanacağım:
=MİN(İNDİS(MUTLAK($B$3:$B$7-$C$3);;))
Evet artık elimizde en düşük fark değeri var. Formülü buraya kadar eksiksiz yazdı iseniz formül 0,01 değerini döndürecektir. Şimdi elde ettiğimiz bu değer acaba dizinin hangi elemanı. Bunu bulacak formül de KAÇINCI:
=KAÇINCI(MİN(İNDİS(MUTLAK($B$3:$B$7-$C$3);;));İNDİS(MUTLAK($B$3:$B$7-$C$3);;);0)
Bu değer bulduğumuz dizinin 1. elemanında. Zaten bizim elde ettiğimiz dizin de elimizdeki rakamlar ile aynı sıralamaya sahip. Bu da aradığımız değerin aslında verilen değerlerin ilki olduğunu ifade ediyor. Hemen bu değeri çekelim:
=İNDİS($B$3:$B$7;KAÇINCI(MİN(İNDİS(MUTLAK($B$3:$B$7-$C$3);;));İNDİS(MUTLAK($B$3:$B$7-$C$3);;); 0);)
Bu sayede B2:B7 aralığındaki ilk değeri geri döndürüyoruz. Bu da 100,01. Ancak burada bir eksikliğimiz var bu da bulduğumuz en küçük fark acaba bizim tanımladığımız tolerans değerinden küçük mü? Hemen bir kontrol ekleyerek bunu denetleyelim ve eğer küçük ise formülümüzün sonucunu geri döndürelim, yok değil ise bu sefer “Değer Bulunamadı!” yazalım:
=EĞER(MİN(İNDİS(MUTLAK($B$3:$B$7-$C$3);;))<=$D$3;İNDİS($B$3:$B$7;KAÇINCI(MİN(İNDİS(MUTLAK( $B$3:$B$7-$C$3);;));İNDİS(MUTLAK($B$3:$B$7-$C$3);;);0););”Değer Bulunamadı!”)
2.En Yakın Büyük Değeri Bulma
Şimdi işleri biraz daha zor bir hale getireceğiz. Yine işe önce aradığımız değer ile verilen değerler arasındaki farkı bulmakla başlayacağız:
=$C$3-$B$3:$B$7
Ancak bu sefer elde ettiğimiz değer sıfırdan büyük mü bunu kontrol etmeliyiz.
=$C$3-$B$3:$B$7>0
Bu formül ile sadece farkın + olduğu değerler yani aradığımız değerden küçük olan değerlerin “DOĞRU” olduğu bir dizin elde ediyoruz. Ben sonraki aşamada en düşük farkı bulacağımdan bu değerlerin en düşük değer olarak çıkmaması için elde ettiğim değerleri toleransın 10 katı ile çarpıyorum:
=($C$3-$B$3:$B$7>0)*10*$D$3
Şimdi bu formülün sonucunda {0,0,0,5,0,0] gibi bir dizin elde edeceğim. Aradığım değer verilen değere en yakın büyük değer olduğu için verilen değerler ile aranan değer arasındaki farkı mutlak olarak bulacağım ve bu dizine ekleyeceğim. Bu sayede aradığım değerden küçük olan değerlerin formül tarafından %100 elenmesini sağlayabilirim. Tabii bir de bu tüm formülleri dizin olarak ele alacak İNDİS formülü ile de birleştirmem gerek:
=İNDİS(($C$3-$B$3:$B$7>0)*10*$D$3+MUTLAK($C$3-$B$3:$B$7);;)
Evet artık elimde {0,01;0,03;0,53;0,09;0,06} şeklinde bir dizin var. Artık bu dizindeki en küçük değeri arayabilirim:
=MİN(İNDİS(($C$3-$B$3:$B$7>0)*10*$D$3+MUTLAK($C$3-$B$3:$B$7);;))
Formülün sonucu bir önceki örnek gibi 0,01 olacaktır. Hemen bu elemanın dizinin kaçıncı elemanı olduğunu bulalım:
=KAÇINCI(MİN(İNDİS(($C$3-$B$3:$B$7>0)*10*$D$3+MUTLAK($C$3-$B$3:$B$7);;));İNDİS(($C$3-$B$3: $B$7>0)*10*$D$3+MUTLAK($C$3-$B$3:$B$7);;);0)
Gördüğünüz gibi yine ilk elemanda bulduk. Hemen bu elemanı çekelim:
=İNDİS($B$3:$B$7;KAÇINCI(MİN(İNDİS(($C$3-$B$3:$B$7>0)*10*$D$3+MUTLAK($C$3-$B$3:$B$7);;)); İNDİS(($C$3-$B$3: $B$7>0)*10*$D$3+MUTLAK($C$3-$B$3:$B$7);;);0);)
Tabii bulduğumuz değerin girdiğimiz tolerans değerinden büyük olup olmadığını da kontrol etmemiz gerekiyor. Eğer küçük ise bu değeri geri döndürsün ama değilse hata mesajı verelim:
=EĞER(MİN(İNDİS(($C$3-$B$3:$B$7>0)*10*$D$3+MUTLAK($C$3-$B$3:$B$7);;))<=$D$3;İNDİS($B$3:$B$7; KAÇINCI(MİN(İNDİS(($C$3-$B$3:$B$7>0)*10*$D$3+MUTLAK($C$3-$B$3:$B$7);;));İNDİS(($C$3-$B$3: $B$7 >0)*10*$D$3+MUTLAK($C$3-$B$3:$B$7);;);0););”Değer Bulunamadı!”)
3.En Yakın Küçük Değeri Bulma
En yakın değeri bulma işlevi bir önceki başlıkta anlattığım En Yakın Büyük Değeri Bulma formülü ile benzer. Tek farkı en başta yaptığım çıkarma işlemini ters yapmak. Yani aradığımız değerden verilen değerleri değil, verilen değerlerden aranan değeri çıkarmak. Bu formülü yazma işini size bırakıyorum. Hem iyi bir uygulama olur hem de kafanızda daha rahat canlandırabilirsiniz.
Kaynak: http://excelileharikalar.com/index.php/2018/11/18/sirasiz-listede-yakin-degeri-bulma/