Generisk formel
(=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0)))
Resumé
For at finde det tætteste match i numeriske data kan du bruge INDEX og MATCH med hjælp fra ABS- og MIN-funktionerne. I det viste eksempel er formlen i F5, kopieret ned,:
=INDEX(trip,MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0))
hvor tur (B5: B14) og pris (C5: C14) er navngivet områder.
I F5, F6 og F7 returnerer formlen den tur, der er tættest i pris til henholdsvis 500, 1000 og 1500.
Bemærk: dette er en matrixformel og skal indtastes med kontrol + skift + enter, undtagen i Excel 365.
Forklaring
I kernen er dette en INDEX- og MATCH-formel: MATCH lokaliserer positionen for det nærmeste match, føder positionen til INDEX, og INDEX returnerer værdien på den position i Trip-kolonnen. Det hårde arbejde udføres med MATCH-funktionen, som er omhyggeligt konfigureret til at matche "minimumsforskellen" som denne:
MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0)
Ved at tage tingene trin for trin beregnes opslagsværdien med MIN og ABS således:
MIN(ABS(cost-E5)
For det første er værdien i E5 trækkes fra det navngivne område omkostninger (C5: C14). Dette er en matrixhandling, og da der er 10 værdier i området, er resultatet en matrix med 10 værdier som denne:
(899;199;250;-201;495;1000;450;-101;500;795)
Disse tal repræsenterer forskellen mellem hver pris i C5: C15 og prisen i celle E5, 700. Nogle værdier er negative, fordi en pris er lavere end antallet i E5. For at konvertere negative værdier til positive værdier bruger vi ABS-funktionen:
ABS((899;199;250;-201;495;1000;450;-101;500;795))
som vender tilbage:
(899;199;250;201;495;1000;450;101;500;795)
Vi leder efter det tætteste match, så vi bruger MIN-funktionen til at finde den mindste forskel, som er 101:
MIN((899;199;250;201;495;1000;450;101;500;795)) // returns 101
Dette bliver opslagsværdien inden for MATCH. Opslagsarrayet genereres som før:
ABS(cost-E5) // generate lookup array
som returnerer det samme array, som vi så tidligere:
(899;199;250;201;495;1000;450;101;500;795)
Vi har nu det, vi har brug for for at finde placeringen af det nærmeste match (mindste forskel), og vi kan omskrive MATCH-delen af formlen sådan:
MATCH(101,(899;199;250;201;495;1000;450;101;500;795),0) // returns 8
Med 101 som opslagsværdi returnerer MATCH 8, da 101 er i 8. position i arrayet. Endelig er denne stilling føres ind INDEX som rækken argument, med den navngivne område tur som arrayet:
=INDEX(trip,8)
og INDEX returnerer den 8. tur i området, "Spanien". Når formlen kopieres ned til cellerne F6 og F7, finder den den nærmeste match til 1000 og 1500, "Frankrig" og "Thailand" som vist.
Bemærk: hvis der er uafgjort, returnerer denne formel den første kamp.
Med XLOOKUP
XLOOKUP-funktionen giver en interessant måde at løse dette problem på, fordi en matchtype på 1 (nøjagtigt match eller næststørst) eller -1 (eksakt match eller næste mindste) ikke kræver, at data sorteres. Dette betyder, at vi kan skrive en formel som denne:
=XLOOKUP(0,ABS(cost-E5),trip,,1)
Som ovenfor bruger vi den absolutte værdi af (cost-E5) til at oprette et opslagsarray:
(899;199;250;201;495;1000;450;101;500;795)
Derefter konfigurerer vi XLOOKUP til at se efter nul, med matchtype indstillet til 1, for nøjagtig matchning eller næststørste. Vi leverer det navngivne område turen idet afkastet array, så resultatet er "Spanien" som før.
