Általános képlet
(=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0)))
Összegzés
A numerikus adatokban a legközelebbi egyezés megtalálásához használhatja az INDEX és a MATCH funkciókat, az ABS és a MIN funkciók segítségével. A bemutatott példában az F5 képlete le van másolva:
=INDEX(trip,MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0))
ahol az utazás (B5: B14) és a költség (C5: C14) tartományokat neveznek meg.
Az F5, F6 és F7 esetében a képlet 500, 1000 és 1500 értékre adja vissza a költséghez legközelebb eső utazást.
Megjegyzés: ez egy tömbképlet, amelyet a Control + Shift + Enter billentyűkombinációval kell megadni, az Excel 365 kivételével.
Magyarázat
Lényegében ez egy INDEX és MATCH képlet: A MATCH megkeresi a legközelebbi egyezés helyzetét, betáplálja a pozíciót az INDEX-be, és az INDEX visszaadja az utazás oszlopban található értéket. A kemény munkát a MATCH funkcióval végzik, amelyet gondosan konfigurálnak, hogy megfeleljen a "minimális különbségnek":
MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0)
Lépésről lépésre haladva a keresési értéket a MIN és az ABS segítségével számítják ki:
MIN(ABS(cost-E5)
Először is, az értéket E5 levonjuk a megnevezett tartományban költség (C5: C14). Ez egy tömbművelet, és mivel 10 érték van a tartományban, az eredmény egy 10 ilyen értékű tömb:
(899;199;250;-201;495;1000;450;-101;500;795)
Ezek a számok az egyes C5: C15 költségek és az E5, 700 cellák költségeinek különbségét jelentik. Egyes értékek negatívak, mert a költségek alacsonyabbak, mint az E5 számai. A negatív értékek pozitívra konvertálásához használjuk az ABS funkciót:
ABS((899;199;250;-201;495;1000;450;-101;500;795))
amely visszatér:
(899;199;250;201;495;1000;450;101;500;795)
A legközelebbi mérkőzést keressük, ezért a MIN funkcióval keressük meg a legkisebb különbséget, ami 101:
MIN((899;199;250;201;495;1000;450;101;500;795)) // returns 101
Ez lesz a keresési érték a MATCH belsejében. A keresési tömb a korábbiak szerint jön létre:
ABS(cost-E5) // generate lookup array
amely ugyanazt a tömböt adja vissza, amelyet korábban láttunk:
(899;199;250;201;495;1000;450;101;500;795)
Most megvan, amire szükségünk van, hogy megtaláljuk a legközelebbi egyezés helyzetét (a legkisebb különbség), és így írhatjuk át a képlet MATCH részét:
MATCH(101,(899;199;250;201;495;1000;450;101;500;795),0) // returns 8
Ha a keresési érték 101, a MATCH értéke 8, mivel a 101 a tömbben a 8. pozícióban van. Végül, ebben a helyzetben vezetjük be INDEX, mint a sorban érv, a megnevezett tartományban utat , mint a tömb:
=INDEX(trip,8)
és az INDEX visszatér a tartomány 8. útjára, "Spanyolországra". Amikor a képletet lefelé másoljuk az F6 és F7 cellákba, megtalálja az 1000 és 1500, a "Franciaország" és a "Thaiföld" legközelebbi egyezést, ahogy az látható.
Megjegyzés: ha döntetlen van, ez a képlet adja vissza az első mérkőzést.
XLOOKUP-tal
Az XLOOKUP funkció érdekes módon megoldja ezt a problémát, mivel az 1 (pontos egyezés vagy a következő legnagyobb) vagy a -1 (pontos egyezés vagy a következő legkisebb) egyezési típus nem igényli az adatok rendezését. Ez azt jelenti, hogy írhatunk egy ilyen képletet:
=XLOOKUP(0,ABS(cost-E5),trip,,1)
Mint fent, az (cost-E5) abszolút értékét használjuk a keresési tömb létrehozásához:
(899;199;250;201;495;1000;450;101;500;795)
Ezután úgy konfiguráljuk az XLOOKUP-ot, hogy nulla értéket keressen, 1-re beállított egyezéstípussal, a pontos egyezéshez vagy a következő legnagyobbhoz. Mi a kínálat a megnevezett tartományban út a visszatérés tömb, így az eredmény „Spanyolország”, mint korábban.
