Genel formül
(=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0)))
Özet
Sayısal verilerdeki en yakın eşleşmeyi bulmak için, ABS ve MIN işlevlerinin yardımıyla INDEX ve MATCH kullanabilirsiniz. Gösterilen örnekte, F5'teki formül aşağıya kopyalanmıştır:
=INDEX(trip,MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0))
burada gezi (B5: B14) ve maliyet (C5: C14) adlandırılmış aralıklardır.
F5, F6 ve F7'de formül, maliyeti sırasıyla 500, 1000 ve 1500'e en yakın olan yolculuğu döndürür.
Not: Bu bir dizi formülüdür ve Excel 365 dışında control + shift + enter ile girilmelidir.
Açıklama
Temelde, bu bir INDEX ve MATCH formülüdür: MATCH, en yakın eşleşmenin konumunu bulur, konumu INDEX'e besler ve INDEX, Gezi sütunundaki o konumdaki değeri döndürür. Zor iş, aşağıdaki gibi "minimum fark" ile eşleşecek şekilde dikkatlice yapılandırılan MATCH işleviyle yapılır:
MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0)
İşleri adım adım ele alarak, arama değeri MIN ve ABS ile şu şekilde hesaplanır:
MIN(ABS(cost-E5)
İlk olarak, E5'teki değer, adlandırılmış aralık maliyetinden (C5: C14) çıkarılır . Bu bir dizi işlemidir ve aralıkta 10 değer olduğundan, sonuç şuna benzer 10 değer içeren bir dizidir:
(899;199;250;-201;495;1000;450;-101;500;795)
Bu sayılar, C5: C15'teki her maliyet ile E5, 700 hücresindeki maliyet arasındaki farkı temsil eder. Bazı değerler negatiftir, çünkü bir maliyet E5'teki sayıdan daha düşüktür. Negatif değerleri pozitif değerlere dönüştürmek için ABS işlevini kullanırız:
ABS((899;199;250;-201;495;1000;450;-101;500;795))
döndüren:
(899;199;250;201;495;1000;450;101;500;795)
En yakın eşleşmeyi arıyoruz, bu nedenle en küçük farkı bulmak için MIN işlevini kullanıyoruz, yani 101:
MIN((899;199;250;201;495;1000;450;101;500;795)) // returns 101
Bu, MATCH içindeki arama değeri olur. Arama dizisi daha önce olduğu gibi oluşturulur:
ABS(cost-E5) // generate lookup array
daha önce gördüğümüz diziyi döndürür:
(899;199;250;201;495;1000;450;101;500;795)
Artık en yakın eşleşmenin konumunu (en küçük fark) bulmamız için ihtiyacımız olan şey var ve formülün MATCH kısmını şu şekilde yeniden yazabiliriz:
MATCH(101,(899;199;250;201;495;1000;450;101;500;795),0) // returns 8
Arama değeri olarak 101 ile KAÇINCI, 8'i döndürür, çünkü 101 dizideki 8. konumdadır. Son olarak, bu konum dizin olarak adlandırılmış aralık gezisi ile satır bağımsız değişkeni olarak INDEX'e beslenir :
=INDEX(trip,8)
INDEX, "İspanya" aralığındaki 8. geziyi döndürür. Formül F6 ve F7 hücrelerine kopyalandığında, 1000 ve 1500'e en yakın eşleşmeyi, gösterildiği gibi "Fransa" ve "Tayland" ile bulur.
Not: Beraberlik varsa, bu formül ilk maçı döndürür.
XLOOKUP ile
XLOOKUP işlevi, bu sorunu çözmek için ilginç bir yol sağlar, çünkü 1 (tam eşleşme veya sonraki en büyük) veya -1 (tam eşleşme veya sonraki en küçük) eşleme türü verilerin sıralanmasını gerektirmez. Bu, şöyle bir formül yazabileceğimiz anlamına gelir:
=XLOOKUP(0,ABS(cost-E5),trip,,1)
Yukarıdaki gibi, bir arama dizisi oluşturmak için (maliyet-E5) mutlak değerini kullanırız:
(899;199;250;201;495;1000;450;101;500;795)
Ardından, tam eşleme veya sonraki en büyük eşleme türü 1 olarak ayarlanmış olarak, XLOOKUP'u sıfır arayacak şekilde yapılandırırız. Adlandırılmış aralık gezisini dönüş dizisi olarak sağlarız , böylece sonuç daha önce olduğu gibi "İspanya" olur.
