Všeobecný vzorec
(=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0)))
Zhrnutie
Ak chcete nájsť najbližšiu zhodu v číselných údajoch, môžete použiť INDEX a MATCH s pomocou funkcií ABS a MIN. V zobrazenom príklade je vzorec v F5, skopírovaný nadol,:
=INDEX(trip,MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0))
kde cesta (B5: B14) a náklady (C5: C14) sú pomenované rozsahmi.
V prípade F5, F6 a F7 vráti vzorec cestu najbližšiu z hľadiska nákladov na 500, 1 000 a 1 500.
Poznámka: toto je vzorec poľa a musí sa zadávať pomocou klávesov Control + Shift + Enter, s výnimkou Excel 365.
Vysvetlenie
V jadre ide o vzorec INDEX a MATCH: MATCH vyhľadá pozíciu najbližšej zhody, posunie pozíciu na INDEX a INDEX vráti hodnotu na tejto pozícii v stĺpci Trip. Tvrdá práca sa deje s funkciou MATCH, ktorá je starostlivo nakonfigurovaná tak, aby zodpovedala „minimálnemu rozdielu“, ako je tento:
MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0)
Ak vezmeme veci krok za krokom, vyhľadávacia hodnota sa počíta s MIN a ABS takto:
MIN(ABS(cost-E5)
Najskôr sa hodnota v E5 odpočíta od pomenovaných nákladov rozsahu (C5: C14). Toto je operácia s poľom a keďže v rozsahu je 10 hodnôt, výsledkom je pole s 10 hodnotami, ako je táto:
(899;199;250;-201;495;1000;450;-101;500;795)
Tieto čísla predstavujú rozdiel medzi každou cenou v C5: C15 a nákladmi v bunke E5, 700. Niektoré hodnoty sú záporné, pretože cena je nižšia ako cena v E5. Na prevod záporných hodnôt na kladné hodnoty používame funkciu ABS:
ABS((899;199;250;-201;495;1000;450;-101;500;795))
ktorý vracia:
(899;199;250;201;495;1000;450;101;500;795)
Hľadáme najbližšiu zhodu, takže pomocou funkcie MIN nájdeme najmenší rozdiel, ktorý je 101:
MIN((899;199;250;201;495;1000;450;101;500;795)) // returns 101
Toto sa stane vyhľadávacou hodnotou vo vnútri MATCH. Vyhľadávacie pole sa generuje ako predtým:
ABS(cost-E5) // generate lookup array
ktorý vráti to isté pole, ktoré sme videli predtým:
(899;199;250;201;495;1000;450;101;500;795)
Teraz máme to, čo potrebujeme na nájdenie pozície najbližšej zhody (najmenší rozdiel), a môžeme takto prepísať časť MATCH vzorca:
MATCH(101,(899;199;250;201;495;1000;450;101;500;795),0) // returns 8
So 101 ako vyhľadávacou hodnotou vráti MATCH 8, pretože 101 je na 8. pozícii v poli. Napokon, táto pozícia sa privádza do index ako riadok argumentu, s názvom rozsahu cesty ako pole:
=INDEX(trip,8)
a INDEX vracia 8. cestu v rozmedzí „Španielsko“. Keď sa vzorec skopíruje do buniek F6 a F7, vyhľadá najbližšiu zhodu s 1 000 a 1 500, „Francúzskom“ a „Thajskom“, ako je to znázornené.
Poznámka: ak dôjde k remíze, tento vzorec vráti prvý zápas.
S XLOOKUPOM
Funkcia XLOOKUP poskytuje zaujímavý spôsob riešenia tohto problému, pretože typ zhody 1 (presná zhoda alebo najbližšia najväčšia) alebo -1 (presná zhoda alebo najbližšia najmenšia) nevyžaduje triedenie údajov. To znamená, že môžeme napísať nasledujúci vzorec:
=XLOOKUP(0,ABS(cost-E5),trip,,1)
Ako je uvedené vyššie, na vytvorenie vyhľadávacieho poľa používame absolútnu hodnotu (cost-E5):
(899;199;250;201;495;1000;450;101;500;795)
Potom nakonfigurujeme XLOOKUP tak, aby hľadal nulu s typom zhody nastaveným na 1, pre presnú zhodu alebo nasledujúcu najväčšiu. Dodávame s názvom rozsah výlet ako vratné pole, takže výsledkom je "Španielsko" ako predtým.
