Vispārēja formula
(=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0)))
Kopsavilkums
Lai atrastu tuvāko skaitlisko datu atbilstību, varat izmantot INDEX un MATCH, izmantojot ABS un MIN funkcijas. Parādītajā piemērā F5 formula, kas nokopēta uz leju, ir:
=INDEX(trip,MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0))
kur brauciens (B5: B14) un izmaksas (C5: C14) ir nosaukti diapazoni.
F5, F6 un F7 formula atgriež braucienam vistuvāko braucienu attiecīgi līdz 500, 1000 un 1500.
Piezīme: šī ir masīva formula, un tā jāievada ar vadības taustiņu + Shift + Enter, izņemot Excel 365.
Paskaidrojums
Būtībā šī ir INDEX un MATCH formula: MATCH atrod tuvākās atbilstības pozīciju, padod pozīciju INDEX un INDEX atgriež vērtību šajā pozīcijā kolonnā Ceļojums. Smagais darbs tiek veikts ar funkciju MATCH, kas ir rūpīgi konfigurēta, lai tā atbilstu "minimālajai starpībai" šādi:
MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0)
Veicot darbības pa solim, uzmeklēšanas vērtība tiek aprēķināta ar MIN un ABS šādi:
MIN(ABS(cost-E5)
Pirmkārt, vērtība E5 tiek atņemta no nosauktajām diapazona izmaksām (C5: C14). Šī ir masīva darbība, un, tā kā diapazonā ir 10 vērtības, rezultāts ir masīvs ar 10 šādām vērtībām:
(899;199;250;-201;495;1000;450;-101;500;795)
Šie skaitļi atspoguļo starpību starp katrām izmaksām C5: C15 un izmaksām šūnā E5, 700. Dažas vērtības ir negatīvas, jo izmaksas ir zemākas par skaitli E5. Lai negatīvās vērtības pārveidotu par pozitīvām, mēs izmantojam ABS funkciju:
ABS((899;199;250;-201;495;1000;450;-101;500;795))
kas atgriež:
(899;199;250;201;495;1000;450;101;500;795)
Mēs meklējam tuvāko atbilstību, tāpēc mēs izmantojam MIN funkciju, lai atrastu mazāko atšķirību, kas ir 101:
MIN((899;199;250;201;495;1000;450;101;500;795)) // returns 101
Tas kļūst par meklēšanas vērtību MATCH iekšpusē. Uzmeklēšanas masīvs tiek ģenerēts tāpat kā iepriekš:
ABS(cost-E5) // generate lookup array
kas atgriež to pašu masīvu, kuru redzējām iepriekš:
(899;199;250;201;495;1000;450;101;500;795)
Tagad mums ir tas, kas mums nepieciešams, lai atrastu tuvākās spēles pozīciju (mazākā starpība), un mēs varam pārrakstīt formulas MATCH daļu šādi:
MATCH(101,(899;199;250;201;495;1000;450;101;500;795),0) // returns 8
Ar 101 kā uzmeklēšanas vērtību MATCH atgriež 8, jo 101 masīvā atrodas 8. pozīcijā. Visbeidzot, šī pozīcija tiek ievadīta INDEX kā rindas arguments, kā masīvs ir nosauktais diapazona ceļojums :
=INDEX(trip,8)
un INDEX atgriež diapazona 8. braucienu "Spānija". Kad formula tiek nokopēta līdz šūnām F6 un F7, tā atrod vistuvāko atbilstību 1000 un 1500, "Francija" un "Taizeme", kā parādīts.
Piezīme: ja neizšķirts, šī formula atgriezīs pirmo maču.
Ar XLOOKUP
Funkcija XLOOKUP nodrošina interesantu veidu, kā atrisināt šo problēmu, jo atbilstības tipam 1 (precīza atbilstība vai nākamā lielākā) vai -1 (precīzā atbilstība vai nākamā mazākā) dati nav jāšķiro. Tas nozīmē, ka mēs varam uzrakstīt šādu formulu:
=XLOOKUP(0,ABS(cost-E5),trip,,1)
Kā iepriekš, mēs izmantojam (izmaksu E5) absolūto vērtību, lai izveidotu uzmeklēšanas masīvu:
(899;199;250;201;495;1000;450;101;500;795)
Tad mēs konfigurējam XLOOKUP, lai meklētu nulli ar atbilstības veidu iestatītu uz 1, precīzai atbilstībai vai nākamajai lielākajai. Mēs piegādājam nosaukto diapazona braucienu kā atgriešanās masīvu, tāpēc rezultāts ir "Spānija" tāpat kā iepriekš.
