Üldine valem
(=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0)))
Kokkuvõte
Numbriandmetest lähima vaste leidmiseks võite kasutada funktsioone ABS ja MIN abiga INDEX ja MATCH. Näidatud näites on F5 valem, kopeeritud allpool:
=INDEX(trip,MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0))
kus reis (B5: B14) ja maksumus (C5: C14) nimetatakse vahemikeks.
Funktsioonides F5, F6 ja F7 tagastab valem kululähedaseima reisi vastavalt väärtusele 500, 1000 ja 1500.
Märkus: see on massiivivalem ja see tuleb sisestada juhtklahviga + shift + enter, välja arvatud Excelis 365.
Selgitus
Põhimõtteliselt on see valem INDEX ja MATCH: MATCH otsib lähima vaste asukoha, sisestab positsiooni INDEX-ile ja INDEX tagastab veerus Trip selle väärtuse. Kõva töö tehakse funktsiooniga MATCH, mis on hoolikalt konfigureeritud järgima "minimaalset erinevust":
MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0)
Asjade sammhaaval arvutamisel otsingu väärtus arvutatakse MIN-i ja ABS-iga järgmiselt:
MIN(ABS(cost-E5)
Esiteks lahutatakse E5 väärtus nimetatud vahemiku maksumusest (C5: C14). See on massiivioperatsioon ja kuna vahemikus on 10 väärtust, on tulemuseks 10 sellise väärtusega massiiv:
(899;199;250;-201;495;1000;450;-101;500;795)
Need numbrid tähistavad C5: C15 iga kulu ja lahtris E5, 700 oleva hinna erinevust. Mõned väärtused on negatiivsed, kuna kulu on väiksem kui arv E5-s. Negatiivsete väärtuste teisendamiseks positiivseteks kasutame funktsiooni ABS:
ABS((899;199;250;-201;495;1000;450;-101;500;795))
mis tagastab:
(899;199;250;201;495;1000;450;101;500;795)
Otsime lähimat vastet, seega kasutame väikseima erinevuse leidmiseks funktsiooni MIN, mis on 101:
MIN((899;199;250;201;495;1000;450;101;500;795)) // returns 101
Sellest saab otsingu väärtus MATCH-is. Otsingumassiiv genereeritakse nagu varem:
ABS(cost-E5) // generate lookup array
mis tagastab sama massiivi, mida nägime varem:
(899;199;250;201;495;1000;450;101;500;795)
Nüüd on meil olemas see, mida vajame lähima vaste positsiooni leidmiseks (väikseim erinevus), ja saame valemi MATCH osa ümber kirjutada järgmiselt:
MATCH(101,(899;199;250;201;495;1000;450;101;500;795),0) // returns 8
Kui otsingu väärtus on 101, tagastab MATCH 8, kuna 101 on massiivi 8. positsioonil. Lõpuks on see positsioon juhitakse INDEX kui rida argument, mille nimega vahemik reisi nagu massiivi:
=INDEX(trip,8)
ja INDEX tagastab vahemikus 8. reisi "Hispaania". Kui valem kopeeritakse lahtritesse F6 ja F7, leiab see lähima vaste arvudele 1000 ja 1500, "Prantsusmaa" ja "Tai", nagu näidatud.
Märkus: võrdse tulemuse korral tagastab see valem esimese vaste.
XLOOKUPiga
Funktsioon XLOOKUP pakub huvitavat viisi selle probleemi lahendamiseks, kuna vastetüüp 1 (täpne vaste või järgmine suurim) või -1 (täpne vaste või järgmine väikseim) ei vaja andmete sortimist. See tähendab, et saame kirjutada järgmise valemi:
=XLOOKUP(0,ABS(cost-E5),trip,,1)
Nagu ülalpool, kasutame otsimassiivi loomiseks absoluutväärtust (kulu-E5):
(899;199;250;201;495;1000;450;101;500;795)
Seejärel konfigureerime XLOOKUP otsima täpse vaste või järgmise suurusega nulli, kui vaste tüüp on 1. Pakume nimetatud vahemiku reisi tagasimassiivina, nii et tulemuseks on "Hispaania" nagu varem.
