Farklı formülleri birleştirmeye alışırsanız, Excel işlevleri çok güçlü olabilir.Bir zamanlar imkansız görünen şey, birdenbire çocuk oyununa dönüşebilir.
Böyle bir örnek, bir Excel veri kümesindeki en yakın değeri bulmaktır.
Excel'de (DÜŞEYARA ve INDEX MATCH gibi) size en yakın olanı bulabilen birkaç yararlı arama işlevi vardır.值(aşağıdaki örnekte gösterildiği gibi).
Ancak en iyi yanı, çok daha fazlasını yapmak için (sıralanmamış bir listedeki bir arama değerine en yakın eşleşmeyi bulmak dahil) bu arama işlevlerini diğer Excel işlevleriyle birleştirebilmenizdir.
Bu öğreticide, Excel'de bir arama değerine en yakın eşleşmeyi bulmak için bir arama formülünün nasıl kullanılacağını göstereceğim.
En yakın eşleşmeyi (veya en yakın eşleşme değerini) bulmanız gereken birçok farklı senaryo vardır.
İşte bu makalede ele alacağım bir örnek:
- Satışlara dayalı komisyon oranlarını bulun
- En iyi adayı bulun (en yakın deneyime göre)
- Bir sonraki etkinlik tarihini bulun
Hadi başlayalım!
İçerik
Komisyon oranlarını bulun (en yakın satış değerini arayın)
Tüm satış görevlileri için komisyon oranlarını bulmak istediğiniz aşağıdaki gibi bir veri kümeniz olduğunu varsayalım.
Komisyonlar satış değerine göre dağıtılır.Bu, sağdaki tablo kullanılarak hesaplanır.
Örneğin, satış elemanının toplam satışları 5000 ise komisyonu %0, toplam satışları 15000 ise komisyonu %5'tir.
Komisyon oranı almak için satış değerinin hemen altındaki en yakın satış aralığını bulmanız gerekir.Örneğin, 15000 satış için komisyon 10,000'dir (veya %5), 25000 satış için komisyon oranı 20,000'dir (veya %7).
En yakın satış değerini bulmak ve komisyon oranı almak için DÜŞEYARA'da yaklaşık eşleştirmeyi kullanabilirsiniz.
Aşağıdaki formül bunu yapabilir:
= DÜŞEYARA (B2, $ E $ 2: $ F $ 6,2,1)
Bu formülde, formüle yaklaşık aramayı kullanmasını söyleyen son parametrenin 1 olduğuna dikkat edin.Bu, formülün E sütunundaki satış değerinden geçeceği ve arama değerinin hemen altındaki değeri bulacağı anlamına gelir.
DÜŞEYARA formülü daha sonra bu değer için komisyon oranını verecektir.
注意: Bunu yapmak için verileri artan düzende sıralamanız gerekir.
En iyi adayı bulun (en yakın deneyime göre)
Yukarıdaki örnekte, verilerin artan düzende sıralanması gerekir.Ancak verilerin sıralanmadığı durumlar olabilir.
Şimdi bir örnek alalım ve Excel'de en yakın eşleşmeyi bulmak için formül kombinasyonlarını nasıl kullanabileceğimizi görelim.
Aşağıda, iş tecrübesi istenen değere en yakın çalışanların isimlerini bulmam gereken örnek bir veri seti var.Bu durumda beklenen değer 2.5 yıldır.
Verilerin sıralanmadığını unutmayın.Ek olarak, en yakın deneyimler, belirli bir 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ıl fark).
İşte bize sonucu veren formül:
=INDEX($A$2:$A$15,MATCH(MIN(ABS(D2-B2:B15)),ABS(D2-$B$2:$B$15),0))
Bu formüldeki hile, istenen ve gerçek değerler arasındaki en küçük ampirik farkı bulmak için arama dizisini ve arama değerlerini 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 yaptığınızda, B sütunundaki her hücreyi gözden geçirir ve istediğiniz deneyim ile sahip olunan deneyim arasındaki farkı bulursunuz.Tüm farklılıklara sahip olduğunuzda, en küçüğünü bulup o kişinin adını alırsı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 (yani 2.5 yıl) ile diğer tüm deneyimler arasındaki minimum farkı verir.Bu durumda 0.3 döndürür
En yakını aradığımdan emin olmak için ABS kullandığımı unutmayın (muhtemelen verilen deneyimden daha fazla veya daha az).
Ş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 (deneyim gerekli) çıkarıldığı bir dizi sayı verir.
所以現在我們有一個查找值 (0.3) 和一個查找數組 ({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, en yakın deneyime sahip kişinin adının konumu olan bu dizide 0.3'ün konumunu bulur.
INDEX işlevi daha sonra kişinin adını döndürmek için iş numarasını kullanır.
Not: Aynı asgari deneyime sahip birden fazla aday varsa, yukarıdaki formül eşleşen ilk çalışanın adını verecektir.
Bir sonraki etkinlik tarihini bulun
Geçerli tarihe dayalı olarak bir etkinliğin sonraki tarihini bulmak için bir arama formülü kullanabileceğiniz başka bir örnek.
Aşağıda, olay adlarına ve olay tarihlerine sahip olduğum veri kümesi bulunmaktadır.
İstediğim şey bir sonraki etkinliğin adı ve bu yaklaşan etkinliğin etkinlik tarihi.
Yaklaşan etkinliğin adını veren formül:
=INDEX($A$2:$A$11,MATCH(E1,$B$2:$B$11,1)+1)
Aşağıdaki formül yaklaşan etkinlik tarihlerini 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 MATCH işlevi, geçerli tarih için B sütununa bakar.Bu durumda, tam bir eşleşme değil, yaklaşık bir eşleşme arıyoruz.Bu nedenle, KAÇINCI işlevinin son parametresi 1'dir (arama değerinden küçük veya ona eşit en büyük değeri bulur).
Böylece KAÇINCI işlevi, tarihi geçerli tarihten küçük veya ona eşit olan hücrenin konumunu döndürür.Dolayısıyla bu durumda bir sonraki olay bir sonraki hücrede olacaktır (çünkü liste artan düzende sıralanmıştır).
Bu nedenle, yaklaşan etkinlik 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 etkinlik 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ın ve INDEX işlevindeki aralığı B sütunundan A sütununa değiştirin.
Bir arkadaşım istekte bulununca aklıma bu örnek geldi.Tüm arkadaşlarının/akrabalarının doğum günlerini bir sütunda listeler ve bir sonraki doğum gününün (ve kişinin adının) geleceğini bilmek ister.
Yukarıdaki üç örnek, Excel'de en yakın değeri bulmak için arama formülünün nasıl kullanılacağını gösterir.
