Formülleri Kullanarak Excel'de En Yakın Eşleşmeyi Bulun

Farklı formülleri birleştirmeye alışırsanız, Excel işlevleri son derece güçlü olabilir. İmkansız gibi görünen şeyler birdenbire çocuk oyuncağı gibi görünmeye başlardı.

Böyle bir örnek, Excel'deki bir veri kümesindeki bir arama değerinin en yakın eşleşmesini bulmaktır.

Excel'de birkaç basit durumda en yakın eşleşmeyi bulabilen (aşağıdaki örneklerle göstereceğim gibi) birkaç yararlı arama işlevi vardır (DÜŞEYARA & INDEX MATCH gibi).

Ancak en iyi yanı, çok daha fazlasını yapmak için bu arama işlevlerini diğer Excel işlevleriyle birleştirebilmenizdir (sıralanmamış bir listede bir arama değerinin en yakın eşleşmesini bulmak dahil).

Bu derste, Excel'de bir arama değerinin en yakın eşleşmesini arama formülleriyle nasıl bulacağınızı göstereceğim.

Excel'de En Yakın Eşleşmeyi Bulun

En yakın eşleşmeyi (veya en yakın eşleşen değeri) aramanız gereken birçok farklı senaryo olabilir.

Bu yazıda ele alacağım örnekler aşağıdadır:

1. Satışlara göre komisyon oranını bulun
2. En iyi adayı bulun (en yakın deneyime göre)
3. Bir sonraki etkinlik tarihini bulma

Başlayalım!

Örnek dosyayı indirmek için buraya tıklayın

Komisyon Oranını Bul (En Yakın Satış Değerini Arayarak)

Tüm satış personelinin komisyon oranlarını bulmak istediğiniz aşağıda gösterildiği gibi bir veri kümeniz olduğunu varsayalım.

Komisyon, satış değerine göre atanır. Bu da sağdaki tablo kullanılarak hesaplanır.

Örneğin, bir satış elemanı toplam 5000 satış yaparsa komisyon %0 ve toplam 15000 satış yaparsa komisyon %5 olur.

Komisyon oranını elde etmek için, satış değerinden biraz daha düşük olan en yakın satış aralığını bulmanız gerekir. Örneğin, 15000 satış değeri için komisyon 10.000 (%5) ve 25000 satış değeri için komisyon oranı 20.000 (%7) olacaktır.

En yakın satış değerini bulmak ve komisyon oranını almak için DÜŞEYARA'daki yaklaşık eşleşmeyi kullanabilirsiniz.

Aşağıdaki formül bunu yapacaktır:

=DÜŞEYARA(B2,$E$2:$F$6,2,1)

Bu formülde, son bağımsız değişkenin formüle yaklaşık bir arama kullanmasını söyleyen 1 olduğuna dikkat edin. Bu, formülün E sütunundaki satış değerlerini gözden geçireceği ve arama değerinden biraz daha düşük olan değeri bulacağı anlamına gelir.

Daha sonra DÜŞEYARA formülü bu değer için komisyon oranını verecektir.

Not: Bunun çalışması için verilerin artan düzende sıralanması gerekir.

Örnek dosyayı indirmek için buraya tıklayın

En iyi adayı bulun (en yakın deneyime göre)

Yukarıdaki örnekte, verilerin artan düzende sıralanması gerekiyordu. Ancak verilerin sıralanmadığı durumlar olabilir.

Öyleyse bir örneği ele alalım ve bir formül kombinasyonu kullanarak Excel'de en yakın eşleşmeyi nasıl bulabileceğimizi görelim.

Aşağıda, istenen değere en yakın iş deneyimine sahip çalışan adını bulmam gereken örnek bir veri seti bulunmaktadır. Bu durumda 2,5 yılda istenen değer.

Verilerin sıralanmadığını unutmayın. Ayrıca, en yakın deneyim, verilen deneyimden daha az veya daha fazla olabilir. Örneğin, 2 yıl ve 3 yıl eşit derecede yakındır (0,5 yıllık fark).

Bize sonucu verecek formül aşağıdadır:

=INDEX($A$2:$A$15,MATCH(MIN(ABS(D2-B2:B15)),ABS(D2-$B$2:$B$15),0))

Bu formüldeki hile, gerekli ve gerçek değerlerdeki minimum deneyim farkını bulmak için arama dizisini ve arama değerini değiştirmektir.

Önce manuel olarak nasıl yapacağınızı anlayalım (sonra bu formülün nasıl çalıştığını açıklayacağım).

Bunu manuel olarak yaparken, B sütunundaki her hücreyi gözden geçirecek ve gerekli olan ile bir kişinin sahip olduğu deneyim arasındaki farkı bulacaksınız. Tüm farklılıklara sahip olduğunuzda, minimum olanı bulacaksınız ve o kişinin adını alacaksınız.

Bu formülle tam olarak bunu yapıyoruz.

Açıklamama izin ver.

KAÇINCI formülündeki arama değeri MIN(ABS(D2-B2:B15)).

Bu bölüm size verilen deneyim (2,5 yıl) ile diğer tüm deneyimler arasındaki minimum farkı verir. Bu örnekte, 0,3 döndürür

En yakını aradığımdan emin olmak için ABS kullandığımı unutmayın (bu, verilen deneyimden daha fazla veya daha az olabilir).

Şimdi, bu minimum değer bizim arama değerimiz olur.

KAÇINCI işlevindeki arama dizisi ABS'dir(D2-$B$2:$B$15).

Bu bize, 2.5'in (gerekli deneyim) çıkarıldığı bir sayı dizisini verir.

Şimdi bir arama değerimiz (0.3) ve bir arama dizimiz var ({6.8;0.8;19.5;21.8;14.5;11.2;0.3;9.2;2;9.8;14.8;0.4;23.8;2.9})

KAÇINCI işlevi, aynı zamanda en yakın deneyime sahip kişinin adının konumu olan bu dizide 0.3'ün konumunu bulur.

Bu pozisyon numarası daha sonra kişinin adını döndürmek için INDEX işlevi tarafından kullanılır.

Not: Aynı asgari deneyime sahip birden fazla aday olması durumunda, yukarıdaki formül eşleşen ilk çalışanın adını verecektir.

Sonraki Etkinlik Tarihini Bulun

Bu, geçerli tarihe dayalı olarak bir etkinliğin sonraki tarihini bulmak için arama formüllerini kullanabileceğiniz başka bir örnektir.

Etkinlik adları ve etkinlik tarihlerine sahip olduğum veri kümesi aşağıdadır.

İstediğim şey, bir sonraki etkinliğin adı ve bu yaklaşan etkinliğin etkinlik tarihi.

Yaklaşan etkinlik adını verecek formül aşağıdadır:

=INDEX($A$2:$A$11,MATCH(E1,$B$2:$B$11,1)+1)

Ve aşağıdaki formül yaklaşan etkinlik tarihini verecektir:

=INDEX($B$2:$B$11,MATCH(E1,$B$2:$B$11,1)+1)

Bu formülün nasıl çalıştığını açıklayayım.

Etkinlik tarihini almak için, KAÇINCI işlevi B sütununda geçerli tarihi arar. Bu durumda, tam bir eşleşme değil, yaklaşık bir eşleşme arıyoruz. Bu nedenle, KAÇINCI işlevinin son argümanı 1'dir (bu, arama değerinden küçük veya ona eşit olan en büyük değeri bulur).

Böylece KAÇINCI işlevi, geçerli tarihe eşit veya ondan küçük olan tarihe sahip hücrenin konumunu döndürür. Dolayısıyla, bu durumda bir sonraki olay, bir sonraki hücrede olacaktır (liste artan düzende sıralandığından).

Yaklaşan olay tarihini almak için, MATCH işlevi tarafından döndürülen hücre konumuna bir tane eklemeniz yeterlidir ve bu size bir sonraki olay tarihinin hücre konumunu verecektir.

Bu değer daha sonra INDEX işlevi tarafından verilir.

Olay adını almak için aynı formül kullanılır ve INDEX işlevindeki aralık B sütunundan A sütununa değiştirilir.

Örnek dosyayı indirmek için buraya tıklayın

Bu örneğin fikri, bir arkadaşım bir istekle yaklaştığında aklıma geldi. Bir sütunda tüm arkadaşlarının/akrabalarının doğum günlerinin bir listesi vardı ve bir sonraki doğum gününü (ve kişinin adını) bilmek istedi.

Bunlar, arama formüllerini kullanarak Excel'de en yakın eşleşen değerin nasıl bulunacağını gösteren üç örnektir.

Aşağıdaki Excel İpuçlarını/Öğreticilerini de Beğenebilirsiniz

• DÜŞEYARA İşlevini kullanarak Listeden Son Numarayı Alın.
• Tek Bir Hücrede Tekrarlamadan Birden Çok Arama Değeri Alın.
• DÜŞEYARA Vs. İNDEKS/MAÇ
• Excel İNDEKS MAÇI
• Excel'de Bir Listede Arama Değerinin Son Oluşumunu Bulun
• Excel'de Yinelenenleri Bul ve Kaldır
• Koşullu biçimlendirme.