V tomto článku se naučíme, jak vyhledat nejbližší shodu pomocí funkce INDEX & MATCH v aplikaci Excel.
Scénář:
Například z tabulky potřebujeme najít číselně nejbližší shodu a jí odpovídající výsledky. Potřebujeme nějaký pevný vzorec, který pomůže při hledání nejbližší shody, jak získat požadovanou hodnotu.
Jak problém vyřešit?
Aby vzorec nejprve porozuměl, musíme si trochu zrevidovat následující funkce
- Funkce INDEX
- Funkce MATCH
Nyní vytvoříme vzorec pomocí výše uvedených funkcí. Funkce MATCH vrátí index nejbližší shody z rozsahu. Funkce INDEX bere jako argument index řádku a vrací jeho odpovídající požadované výsledky.
Obecný vzorec:
{= INDEX (data, MATCH (MIN (ABS (data - hodnota)), ABS (data - hodnota), 0))}
data : řada hodnot data
hodnota : daná hodnota data, kde je výsledek nejbližší
typ_ shody : 1 (přesné nebo nejbližší nejmenší) nebo 0 (přesná shoda) nebo -1 (přesné nebo příští největší)
NE ručně použijte složené závorky. Kudrnaté závorky aplikované pomocí Ctrl + Shift + Enter místo spravedlivého Vstupte.
Příklad:
Výše uvedená tvrzení mohou být komplikovaná k pochopení. Pojďme to tedy pochopit pomocí vzorce v příkladu
Zde máme nějaké náhodné hodnoty data a danou hodnotu data.
Pojmenovaný rozsah používaný pro data (D4: D12).
Zde jsme místo toho pro referenční pole buněk použili pojmenovaný rozsah, protože je snadno pochopitelný. V buňce G6 musíme vyhledat datum, které je nejbližší danému datu.
Pomocí výše uvedeného vzorce získáte nejbližší datum.
Použijte vzorec:
{ = INDEX (data, MATCH (MIN (ABS (data - G6)), ABS (data - G6), 0)) }
NEPOUŽÍVEJTE kudrnaté závorky ručně. Kudrnaté závorky aplikované pomocí Ctrl + Shift + Enter místo spravedlivého Vstupte.
Vysvětlení:
- Funkce ABS vrací kladné celé číslo po odečtení provedených nad hodnotami data (data - G6).
- ABS (data - G6) vytvoří pole odečtením všech hodnot data od dané hodnoty data v buňce G6. Vrácené pole je.
{ 160; 49 ; 105 ; 1048 ; 600 ; 6 ; 702 ; 822 ; 673 }
- MIN (ABS (data - G6)) vrací nejbližší hodnotu z pole vráceného z výše uvedeného kroku.
= INDEX (data, MATCH (6, {160; 49; 105; 1048; 600; 6; 702; 822; 673}, 0))
- Funkce MATCH odpovídá nejbližší shodě s minimální hodnotou ve vráceném poli a vrací její řádkový index funkci INDEX.
- Funkce INDEX najde hodnotu s vráceným indexem ROW.
datový pojmenovaný rozsah použitý pro pole dat. Nyní použijte Ctrl + Shift + Enter na místě pro pouze Enter top získáte výsledek, protože toto je maticový vzorec. Maticový vzorec je vzorec, ve kterém pole hodnot přechází z jednoho výsledku funkce jako argumentu do vnější funkce.
Jak vidíte z výše uvedeného snímku, získali jsme nejbližší shodu z daného data v tabulce. Extrahujte detaily z tabulky pomocí výše uvedeného vzorce.
Zde je několik pozorovacích poznámek při použití výše uvedeného vzorce.
Poznámky:
- Funkce vrací chybu #NA, pokud argument vyhledávacího pole funkce MATCH nemá stejnou délku pole tabulky.
- Vzorec vrací chybu, pokud lookup_value neodpovídá hodnotě v tabulce lookup_array.
- Funkce odpovídá přesné hodnotě, protože argument typu shody pro funkci MATCH je 0.
- Hodnoty vyhledávání lze zadat jako odkaz na buňku nebo přímo pomocí symbolu uvozovky (") ve vzorci jako argumentů.
- NEPOUŽÍVEJTE kudrnaté závorky ručně. Kudrnaté závorky aplikované pomocí Ctrl + Shift + Enter místo spravedlivého Vstupte jinak vrátí chybu.
Doufám, že jste pochopili, jak vyhledat nejnižší nebo nejmenší shodu pomocí funkce INDEX & MATCH v aplikaci Excel. Zde najdete další články o hodnotách vyhledávání v Excelu a funkcích Excelu 2019. Neváhejte a uveďte své dotazy níže v poli pro komentáře. Určitě vám pomůžeme.
Pokud se vám naše blogy líbily, sdílejte je se svými přáteli na Facebooku. A také nás můžete sledovat na Twitteru a Facebooku. Rádi bychom od vás slyšeli, dejte nám vědět, jak můžeme zlepšit, doplnit nebo inovovat naši práci a zlepšit ji pro vás. Napište nám na e -mail
K vyhledání hodnoty použijte INDEX a MATCH : Funkce INDEX & MATCH k vyhledání hodnoty podle potřeby.
SUM rozsah s INDEX v Excelu : Pomocí funkce INDEX najděte součet hodnot podle potřeby.
Jak používat funkci INDEX v Excelu : Najděte INDEX pole pomocí funkce INDEX vysvětlené na příkladu.
Jak používat funkci MATCH v Excelu : Najděte MATCH v poli pomocí hodnoty INDEX uvnitř funkce MATCH vysvětlené na příkladu.
Jak používat funkci LOOKUP v Excelu : Najděte vyhledávací hodnotu v poli pomocí funkce LOOKUP vysvětlené na příkladu.
Jak používat funkci VLOOKUP v Excelu : Najděte vyhledávací hodnotu v poli pomocí funkce VLOOKUP vysvětlené na příkladu.
Populární články
50 Zkratka aplikace Excel pro zvýšení produktivity
Upravte rozevírací seznam
Absolutní reference v Excelu
Pokud s podmíněným formátováním
Pokud se zástupnými znaky
Funkce Excel 2019 CONCAT
Funkce MAXIFS aplikace Excel 2019