Splošna formula
(=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0)))
Povzetek
Če želite najti najbližje ujemanje v številskih podatkih, lahko s pomočjo funkcij ABS in MIN uporabite INDEX in MATCH. V prikazanem primeru je formula v F5, kopirana navzdol,:
=INDEX(trip,MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0))
kjer so potovanje (B5: B14) in stroški (C5: C14) imenovani obsegi.
V F5, F6 in F7 formula vrne potovanje, ki je po ceni najbližje na 500, 1000 oziroma 1500.
Opomba: to je formula matrike, ki jo je treba vnesti s tipko control + shift + enter, razen v Excelu 365.
Pojasnilo
V bistvu je to formula INDEX in MATCH: MATCH poišče položaj najbližjega ujemanja, poda položaj v INDEX in INDEX vrne vrednost na tem mestu v stolpcu Trip. Težko delo opravimo s funkcijo MATCH, ki je skrbno konfigurirana tako, da ustreza "minimalni razliki", kot je ta:
MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0)
Če upoštevamo stvari po korakih, se vrednost iskanja izračuna z MIN in ABS tako:
MIN(ABS(cost-E5)
Najprej se vrednost v E5 odšteje od imenovanih stroškov obsega (C5: C14). To je operacija matrike in ker je v obsegu 10 vrednosti, je rezultat matrika z 10 vrednostmi, kot je ta:
(899;199;250;-201;495;1000;450;-101;500;795)
Te številke predstavljajo razliko med posameznimi stroški v C5: C15 in stroški v celici E5, 700. Nekatere vrednosti so negativne, ker je strošek nižji od števila v E5. Za pretvorbo negativnih vrednosti v pozitivne vrednosti uporabimo funkcijo ABS:
ABS((899;199;250;-201;495;1000;450;-101;500;795))
ki vrne:
(899;199;250;201;495;1000;450;101;500;795)
Iščemo najbližje ujemanje, zato s funkcijo MIN poiščemo najmanjšo razliko, ki je 101:
MIN((899;199;250;201;495;1000;450;101;500;795)) // returns 101
To postane iskalna vrednost znotraj MATCH. Iskalno polje se ustvari kot prej:
ABS(cost-E5) // generate lookup array
ki vrne isto matriko, ki smo jo videli prej:
(899;199;250;201;495;1000;450;101;500;795)
Zdaj imamo, kar potrebujemo, da najdemo položaj najbližjega ujemanja (najmanjša razlika), in del formule MATCH lahko prepišemo takole:
MATCH(101,(899;199;250;201;495;1000;450;101;500;795),0) // returns 8
Z 101 kot iskalno vrednostjo MATCH vrne 8, saj je 101 na 8. mestu v matriki. Nazadnje je ta položaj doveden v INDEX kot argument vrstice, z imenovanim obsegom potovanja kot matriko:
=INDEX(trip,8)
in INDEX vrne 8. potovanje v območju "Španija". Ko formulo kopiramo v celici F6 in F7, poiščemo najbližje ujemanje 1000 in 1500, "Francija" in "Tajska", kot je prikazano.
Opomba: če je izenačen rezultat, bo ta formula vrnila prvo ujemanje.
Z XLOOKUP
Funkcija XLOOKUP ponuja zanimiv način za rešitev te težave, ker vrsta ujemanja 1 (natančno ujemanje ali naslednje največje) ali -1 (natančno ujemanje ali naslednje najmanjše) ne zahteva razvrščanja podatkov. To pomeni, da lahko napišemo takšno formulo:
=XLOOKUP(0,ABS(cost-E5),trip,,1)
Kot zgoraj, za ustvarjanje iskalnega polja uporabimo absolutno vrednost (cost-E5):
(899;199;250;201;495;1000;450;101;500;795)
Nato nastavimo XLOOKUP tako, da išče nič, pri čemer je vrsta ujemanja nastavljena na 1, za natančno ujemanje ali naslednje največje. Imenovano potovanje ponudimo kot povratno matriko, zato je rezultat "Španija" kot prej.
