Generisk formel
(=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0)))
Sammendrag
For å finne nærmeste samsvar i numeriske data, kan du bruke INDEX og MATCH, med hjelp fra ABS- og MIN-funksjonene. I eksemplet vist er formelen i F5, kopiert ned,:
=INDEX(trip,MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0))
der tur (B5: B14) og kostnad (C5: C14) er kalt områder.
I F5, F6 og F7 returnerer formelen turen nærmest i kostnad til henholdsvis 500, 1000 og 1500.
Merk: dette er en matriseformel og må angis med kontroll + skift + enter, unntatt i Excel 365.
Forklaring
I kjernen er dette en INDEX- og MATCH-formel: MATCH lokaliserer posisjonen til nærmeste samsvar, mater posisjonen til INDEX, og INDEX returnerer verdien på den posisjonen i Trip-kolonnen. Det harde arbeidet gjøres med MATCH-funksjonen, som er nøye konfigurert for å matche "minimumsforskjellen" slik:
MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0)
Tar ting trinnvis, blir oppslagsverdien beregnet med MIN og ABS slik:
MIN(ABS(cost-E5)
Først blir verdien i E5 subtrahert fra den navngitte området kostnaden (C5: C14). Dette er en arrayoperasjon, og siden det er 10 verdier i området, er resultatet en matrise med 10 verdier som dette:
(899;199;250;-201;495;1000;450;-101;500;795)
Disse tallene representerer forskjellen mellom hver kostnad i C5: C15 og kostnaden i celle E5, 700. Noen verdier er negative fordi en kostnad er lavere enn tallet i E5. For å konvertere negative verdier til positive verdier bruker vi ABS-funksjonen:
ABS((899;199;250;-201;495;1000;450;-101;500;795))
som returnerer:
(899;199;250;201;495;1000;450;101;500;795)
Vi ser etter den nærmeste kampen, så vi bruker MIN-funksjonen til å finne den minste forskjellen, som er 101:
MIN((899;199;250;201;495;1000;450;101;500;795)) // returns 101
Dette blir oppslagsverdien i MATCH. Oppslagsmatrisen genereres som før:
ABS(cost-E5) // generate lookup array
som returnerer den samme matrisen vi så tidligere:
(899;199;250;201;495;1000;450;101;500;795)
Vi har nå det vi trenger for å finne posisjonen til nærmeste samsvar (minste forskjell), og vi kan omskrive MATCH-delen av formelen slik:
MATCH(101,(899;199;250;201;495;1000;450;101;500;795),0) // returns 8
Med 101 som oppslagsverdi, returnerer MATCH 8, siden 101 er i 8. posisjon i matrisen. Til slutt blir denne stilling mates inn i INDEX som argument raden, med den navngitte området tur som oppstillingen:
=INDEX(trip,8)
og INDEX returnerer den 8. turen i området, "Spania". Når formelen kopieres ned til cellene F6 og F7, finner den nærmeste samsvar med 1000 og 1500, "Frankrike" og "Thailand" som vist.
Merk: hvis det er uavgjort, returnerer denne formelen den første kampen.
Med XLOOKUP
XLOOKUP-funksjonen gir en interessant måte å løse dette problemet på, fordi en samsvarstype på 1 (eksakt samsvar eller nest største) eller -1 (eksakt samsvar eller neste minste) ikke krever at data sorteres. Dette betyr at vi kan skrive en formel som denne:
=XLOOKUP(0,ABS(cost-E5),trip,,1)
Som ovenfor bruker vi den absolutte verdien av (kostnad-E5) for å lage et oppslagsmatrise:
(899;199;250;201;495;1000;450;101;500;795)
Deretter konfigurerer vi XLOOKUP for å se etter null, med samsvarstype satt til 1, for eksakt samsvar eller nest største. Vi leverer navngitt område turen som avkastningen array, så resultatet er "Spain" som før.
