Obecný vzorec
(=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0)))
souhrn
Chcete-li najít nejbližší shodu v číselných datech, můžete použít INDEX a MATCH s pomocí funkcí ABS a MIN. V zobrazeném příkladu je vzorec v F5 zkopírovaný dolů:
=INDEX(trip,MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0))
kde cesta (B5: B14) a náklady (C5: C14) jsou pojmenovány rozsahy.
V F5, F6 a F7 vrátí vzorec cestu nejbližší z hlediska nákladů na 500, 1000 a 1500.
Poznámka: Toto je maticový vzorec a musí být zadán pomocí kláves Control + Shift + Enter, kromě Excel 365.
Vysvětlení
V jádru se jedná o vzorec INDEX a MATCH: MATCH vyhledá pozici nejbližší shody, posune pozici na INDEX a INDEX vrátí hodnotu na této pozici ve sloupci Trip. Tvrdá práce se provádí pomocí funkce MATCH, která je pečlivě nakonfigurována tak, aby odpovídala „minimálnímu rozdílu“ takto:
MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0)
Pokud vezmeme věci krok za krokem, vyhledávací hodnota se vypočítá s MIN a ABS takto:
MIN(ABS(cost-E5)
Za prvé, je hodnota v E5 je odečtena od pojmenované oblasti nákladů (C 5: C14). Jedná se o operaci pole a protože v rozsahu je 10 hodnot, výsledkem je pole s 10 hodnotami, jako je tato:
(899;199;250;-201;495;1000;450;-101;500;795)
Tato čísla představují rozdíl mezi každou cenou v C5: C15 a cenou v buňce E5, 700. Některé hodnoty jsou záporné, protože cena je nižší než cena v E5. K převodu záporných hodnot na kladné hodnoty používáme funkci ABS:
ABS((899;199;250;-201;495;1000;450;-101;500;795))
který vrací:
(899;199;250;201;495;1000;450;101;500;795)
Hledáme nejbližší shodu, takže pomocí funkce MIN najdeme nejmenší rozdíl, který je 101:
MIN((899;199;250;201;495;1000;450;101;500;795)) // returns 101
Toto se stane vyhledávací hodnotou uvnitř MATCH. Vyhledávací pole je generováno jako dříve:
ABS(cost-E5) // generate lookup array
který vrací stejné pole, které jsme viděli dříve:
(899;199;250;201;495;1000;450;101;500;795)
Nyní máme to, co potřebujeme k nalezení pozice nejbližší shody (nejmenší rozdíl), a můžeme MATCH část vzorce přepsat takto:
MATCH(101,(899;199;250;201;495;1000;450;101;500;795),0) // returns 8
Se 101 jako vyhledávací hodnotou vrátí MATCH 8, protože 101 je na 8. pozici v poli. Konečně, tato pozice se přivádí do index jako řádek argumentu, s názvem rozsahu cesty jako pole:
=INDEX(trip,8)
a INDEX vrátí 8. cestu v rozsahu „Španělsko“. Když je vzorec zkopírován dolů do buněk F6 a F7, najde nejbližší shodu s 1000 a 1500, „Francie“ a „Thajsko“, jak je znázorněno.
Poznámka: Pokud dojde k nerozhodnému výsledku, vrátí tento vzorec první zápas.
S XLOOKUPEM
Funkce XLOOKUP poskytuje zajímavý způsob řešení tohoto problému, protože typ shody 1 (přesná shoda nebo další největší) nebo -1 (přesná shoda nebo další nejmenší) nevyžaduje třídění dat. To znamená, že můžeme napsat vzorec takto:
=XLOOKUP(0,ABS(cost-E5),trip,,1)
Jak je uvedeno výše, k vytvoření vyhledávacího pole používáme absolutní hodnotu (cost-E5):
(899;199;250;201;495;1000;450;101;500;795)
Potom nakonfigurujeme XLOOKUP tak, aby hledal nulu s typem shody nastaveným na 1, pro přesnou shodu nebo další největší. Dodáváme s názvem rozsah výlet jako vratné pole, takže výsledkem je „Španělsko“ jako předtím.
