Formula generică
(=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0)))
rezumat
Pentru a găsi cea mai apropiată potrivire în datele numerice, puteți utiliza INDEX și MATCH, cu ajutorul funcțiilor ABS și MIN. În exemplul prezentat, formula din F5, copiată, este:
=INDEX(trip,MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0))
unde călătoria (B5: B14) și costul (C5: C14) sunt denumite intervale.
În F5, F6 și F7, formula returnează costul cel mai apropiat de călătorie la 500, 1000 și, respectiv, 1500.
Notă: aceasta este o formulă matrice și trebuie introdusă cu control + shift + enter, cu excepția Excel 365.
Explicaţie
La bază, aceasta este o formulă INDEX și MATCH: MATCH localizează poziția celui mai apropiat meci, alimentează poziția la INDEX și INDEX returnează valoarea la acea poziție în coloana Trip. Munca grea se face cu funcția MATCH, care este configurată cu atenție pentru a se potrivi cu „diferența minimă” astfel:
MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0)
Luând lucrurile pas cu pas, valoarea de căutare este calculată cu MIN și ABS astfel:
MIN(ABS(cost-E5)
În primul rând, valoarea în E5 este scăzută din costul intervalului numit (C5: C14). Aceasta este o operațiune de matrice și, deoarece există 10 valori în interval, rezultatul este o matrice cu 10 valori de genul acesta:
(899;199;250;-201;495;1000;450;-101;500;795)
Aceste numere reprezintă diferența dintre fiecare cost din C5: C15 și costul din celula E5, 700. Unele valori sunt negative, deoarece un cost este mai mic decât numărul din E5. Pentru a converti valorile negative în valori pozitive, folosim funcția ABS:
ABS((899;199;250;-201;495;1000;450;-101;500;795))
care returnează:
(899;199;250;201;495;1000;450;101;500;795)
Căutăm cea mai apropiată potrivire, așa că folosim funcția MIN pentru a găsi cea mai mică diferență, care este 101:
MIN((899;199;250;201;495;1000;450;101;500;795)) // returns 101
Aceasta devine valoarea de căutare din MATCH. Matricea de căutare este generată ca înainte:
ABS(cost-E5) // generate lookup array
care returnează aceeași matrice pe care am văzut-o mai devreme:
(899;199;250;201;495;1000;450;101;500;795)
Acum avem ceea ce ne trebuie pentru a găsi poziția celei mai apropiate potriviri (cea mai mică diferență) și putem rescrie porțiunea MATCH a formulei astfel:
MATCH(101,(899;199;250;201;495;1000;450;101;500;795),0) // returns 8
Cu 101 ca valoare de căutare, MATCH returnează 8, deoarece 101 se află în poziția a 8-a în matrice. În cele din urmă, această poziție este introdusă în INDEX ca argument rând, cu distanța numită trip ca matrice:
=INDEX(trip,8)
iar INDEX returnează cea de-a opta călătorie din gamă, „Spania”. Când formula este copiată în celulele F6 și F7, se găsește cea mai apropiată potrivire cu 1000 și 1500, „Franța” și „Thailanda” așa cum se arată.
Notă: dacă există o egalitate, această formulă va returna primul meci.
Cu XLOOKUP
Funcția XLOOKUP oferă o modalitate interesantă de a rezolva această problemă, deoarece un tip de potrivire de 1 (potrivire exactă sau următorul cel mai mare) sau -1 (potrivire exactă sau următorul cel mai mic) nu necesită sortarea datelor. Aceasta înseamnă că putem scrie o formulă ca aceasta:
=XLOOKUP(0,ABS(cost-E5),trip,,1)
Ca mai sus, folosim valoarea absolută a (cost-E5) pentru a crea o matrice de căutare:
(899;199;250;201;495;1000;450;101;500;795)
Apoi configurăm XLOOKUP pentru a căuta zero, cu tipul de potrivire setat la 1, pentru potrivirea exactă sau următoarea cea mai mare. Furnizăm călătoria de distanță numită ca matrice de întoarcere, astfel încât rezultatul este „Spania” ca și până acum.
