Generička formula
(=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0)))
Sažetak
Da biste pronašli najbliže podudaranje u numeričkim podacima, možete koristiti INDEX i MATCH, uz pomoć funkcija ABS i MIN. U prikazanom primjeru, formula u F5, kopirana dolje, je:
=INDEX(trip,MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0))
gdje su putovanje (B5: B14) i trošak (C5: C14) imenovani rasponi.
U F5, F6 i F7, formula vraća putovanje najbliže po cijeni na 500, 1000 i 1500, respektivno.
Napomena: ovo je formula niza i mora se unijeti sa control + shift + enter, osim u programu Excel 365.
Obrazloženje
U osnovi je ovo INDEX i MATCH formula: MATCH locira položaj najbližeg podudaranja, dovodi položaj u INDEX i INDEX vraća vrijednost na tom položaju u stupcu Trip. Težak posao obavlja se s funkcijom MATCH, koja je pažljivo konfigurirana tako da odgovara "minimalnoj razlici" poput ove:
MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0)
Uzimajući stvari korak po korak, vrijednost pretraživanja izračunava se s MIN i ABS ovako:
MIN(ABS(cost-E5)
Prvo se vrijednost u E5 oduzima od imenovanog troška raspona (C5: C14). Ovo je operacija niza, a budući da u rasponu ima 10 vrijednosti, rezultat je niz s 10 vrijednosti poput ove:
(899;199;250;-201;495;1000;450;-101;500;795)
Ti brojevi predstavljaju razliku između svakog troška u C5: C15 i troška u ćeliji E5, 700. Neke su vrijednosti negativne jer je trošak niži od broja u E5. Za pretvaranje negativnih vrijednosti u pozitivne vrijednosti koristimo funkciju ABS:
ABS((899;199;250;-201;495;1000;450;-101;500;795))
koji se vraća:
(899;199;250;201;495;1000;450;101;500;795)
Tražimo najbliže podudaranje, pa koristimo funkciju MIN da pronađemo najmanju razliku, a to je 101:
MIN((899;199;250;201;495;1000;450;101;500;795)) // returns 101
To postaje vrijednost pretraživanja unutar MATCH-a. Polje za pretraživanje generira se kao i prije:
ABS(cost-E5) // generate lookup array
koji vraća isti niz koji smo vidjeli ranije:
(899;199;250;201;495;1000;450;101;500;795)
Sada imamo ono što nam treba za pronalaženje položaja najbližeg podudaranja (najmanja razlika), a dio MATCH formule možemo prepisati ovako:
MATCH(101,(899;199;250;201;495;1000;450;101;500;795),0) // returns 8
Uz 101 kao vrijednost pretraživanja, MATCH vraća 8, jer je 101 na 8. mjestu u polju. Konačno, ovaj se položaj uvodi u INDEX kao argument retka, a imenovano putovanje raspona kao niz:
=INDEX(trip,8)
i INDEX vraća osmo putovanje u nizu, "Španjolska". Kada se formula kopira dolje u ćelije F6 i F7, pronalazi najbliže podudaranje s 1000 i 1500, "Francuska" i "Tajland", kao što je prikazano.
Napomena: ako postoji izjednačenje, ova će formula vratiti prvo podudaranje.
Uz XLOOKUP
Funkcija XLOOKUP pruža zanimljiv način za rješavanje ovog problema, jer vrsta podudaranja 1 (točno podudaranje ili sljedeće najveće) ili -1 (točno podudaranje ili sljedeće najmanje) ne zahtijeva sortiranje podataka. To znači da možemo napisati formulu poput ove:
=XLOOKUP(0,ABS(cost-E5),trip,,1)
Kao i gore, koristimo apsolutnu vrijednost (cost-E5) za stvaranje niza pretraživanja:
(899;199;250;201;495;1000;450;101;500;795)
Zatim konfiguriramo XLOOKUP tako da traži nulu, s vrstom podudaranja postavljenom na 1, za točno podudaranje ili sljedeće najveće. Imenovano putovanje isporučujemo kao povratni niz, tako da je rezultat "Španjolska" kao i prije.
