Generisk formel
(=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0)))
Sammanfattning
För att hitta den närmaste matchningen i numeriska data kan du använda INDEX och MATCH, med hjälp av ABS- och MIN-funktionerna. I exemplet som visas är formeln i F5, kopierad ned:
=INDEX(trip,MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0))
där resa (B5: B14) och kostnad (C5: C14) heter områden.
I F5, F6 och F7 returnerar formeln resan närmast i kostnad till 500, 1000 respektive 1500.
Obs: detta är en matrisformel och måste anges med kontroll + shift + enter, utom i Excel 365.
Förklaring
Kärnan är detta en INDEX- och MATCH-formel: MATCH lokaliserar positionen för närmaste matchning, matar positionen till INDEX och INDEX returnerar värdet vid den positionen i Trip-kolumnen. Det hårda arbetet görs med MATCH-funktionen, som är noggrant konfigurerad för att matcha "minsta skillnad" så här:
MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0)
Tar saker steg för steg beräknas uppslagsvärdet med MIN och ABS så här:
MIN(ABS(cost-E5)
Först värdet i E5 subtraheras från det namngivna området kostnad (C5: C14). Detta är en arrayoperation, och eftersom det finns 10 värden i intervallet är resultatet en array med 10 värden så här:
(899;199;250;-201;495;1000;450;-101;500;795)
Dessa siffror representerar skillnaden mellan varje kostnad i C5: C15 och kostnaden i cell E5, 700. Vissa värden är negativa eftersom en kostnad är lägre än antalet i E5. För att konvertera negativa värden till positiva värden använder vi ABS-funktionen:
ABS((899;199;250;-201;495;1000;450;-101;500;795))
som returnerar:
(899;199;250;201;495;1000;450;101;500;795)
Vi letar efter den närmaste matchningen, så vi använder MIN-funktionen för att hitta den minsta skillnaden, som är 101:
MIN((899;199;250;201;495;1000;450;101;500;795)) // returns 101
Detta blir uppslagsvärdet i MATCH. Uppslagsmatrisen genereras som tidigare:
ABS(cost-E5) // generate lookup array
som returnerar samma array som vi såg tidigare:
(899;199;250;201;495;1000;450;101;500;795)
Vi har nu vad vi behöver för att hitta positionen för närmaste matchning (minsta skillnad), och vi kan skriva om MATCH-delen av formeln så här:
MATCH(101,(899;199;250;201;495;1000;450;101;500;795),0) // returns 8
Med 101 som uppslagsvärde returnerar MATCH 8, eftersom 101 ligger i 8: e positionen i arrayen. Slutligen är denna position matas in INDEX som raden argumentet, med det namngivna området resan som arrayen:
=INDEX(trip,8)
och INDEX returnerar den åttonde resan i intervallet "Spanien". När formeln kopieras ner till cellerna F6 och F7 hittar den den närmaste matchningen till 1000 och 1500, "Frankrike" och "Thailand" som visas.
Obs: om det är oavgjort kommer denna formel att returnera den första matchen.
Med XLOOKUP
XLOOKUP-funktionen ger ett intressant sätt att lösa detta problem, eftersom en matchningstyp 1 (exakt matchning eller näst största) eller -1 (exakt matchning eller nästa minsta) inte kräver att data sorteras. Det betyder att vi kan skriva en formel så här:
=XLOOKUP(0,ABS(cost-E5),trip,,1)
Som ovan använder vi det absoluta värdet på (kostnad-E5) för att skapa en uppslagsmatris:
(899;199;250;201;495;1000;450;101;500;795)
Sedan konfigurerar vi XLOOKUP för att leta efter noll, med matchningstyp inställd på 1, för exakt matchning eller näst största. Vi levererar det namngivna området resan som return array, så resultatet är "Spanien" som tidigare.
