V tomto článku sa naučíme, ako vyhľadať najbližšiu zhodu pomocou funkcie INDEX & MATCH v programe Excel.
Scenár:
Napríklad v tabuľke musíme nájsť číselne najbližšiu zhodu a jej zodpovedajúce výsledky. Potrebujeme pevný vzorec, ktorý nám pomôže nájsť najbližšiu zhodu na získanie požadovanej hodnoty.
Ako vyriešiť problém?
Aby vzorec najskôr porozumel, musíme si trocha zrevidovať nasledujúce funkcie
- Funkcia INDEX
- Funkcia MATCH
Teraz vytvoríme vzorec pomocou vyššie uvedených funkcií. Funkcia MATCH vráti index najbližšej zhody z rozsahu. Funkcia INDEX berie ako argument index riadkov a vracia zodpovedajúce požadované výsledky.
Generický vzorec:
{= INDEX (údaje, MATCH (MIN (ABS (údaje - hodnota)), ABS (údaje - hodnota), 0))}
údaje : pole hodnôt dátumu
hodnotu : daná hodnota dátumu, kde je výsledok najbližšie
typ_zhody : 1 (presný alebo nasledujúci najmenší) alebo 0 (presná zhoda) alebo -1 (presný alebo najbližší najväčší)
NEDOTÝKAŤ SA ručne použite kučeravé zátvorky. Kučeravé zátvorky aplikované pomocou Ctrl + Shift + Enter namiesto spravodlivých Zadajte.
Príklad:
Pochopenie vyššie uvedených tvrdení môže byť komplikované. Poďme to teda pochopiť pomocou vzorca v príklade
Tu máme niekoľko náhodných hodnôt dátumu a danú hodnotu dátumu.
Pomenovaný rozsah používaný pre údaje (D4: D12).
Tu sme namiesto toho použili pomenovaný rozsah pre referenčné pole buniek, pretože je to ľahko zrozumiteľné. V bunke G6 musíme vyhľadať dátum, ktorý je najbližšie k danému dátumu.
Na získanie najbližšieho dátumu použite vyššie uvedený vzorec.
Použite vzorec:
{ = INDEX (údaje, ZÁPAS (MIN (ABS (údaje - G6)), ABS (údaje - G6), 0)) }
NEPOUŽÍVAJTE kučeravé zátvorky ručne. Kučeravé zátvorky aplikované pomocou Ctrl + Shift + Enter namiesto spravodlivých Zadajte.
Vysvetlenie:
- Funkcia ABS vráti kladné celé číslo po odčítaní hodnôt dátumu (G6).
- ABS (údaje - G6) vytvára pole odčítaním všetkých hodnôt dátumu od danej hodnoty dátumu v bunke G6. Vrátené pole je.
{ 160; 49 ; 105 ; 1048 ; 600 ; 6 ; 702 ; 822 ; 673 }
- MIN (ABS (údaje - G6)) vráti najbližšiu hodnotu z poľa vráteného z vyššie uvedeného kroku.
= INDEX (údaje, MATCH (6, {160; 49; 105; 1048; 600; 6; 702; 822; 673}, 0))
- Funkcia MATCH sa zhoduje s najbližšou zhodou minimálnej hodnoty vo vrátenom poli a vráti svoj index riadkov do funkcie INDEX.
- Funkcia INDEX nájde hodnotu s vráteným indexom ROW.
údaj pomenovaný rozsah použitý pre pole dátumov. Teraz použite Ctrl + Shift + Enter na mieste pre iba Enter top získate výsledok, pretože toto je vzorec poľa. Array formula je vzorec, kde pole hodnôt prechádza z jedného výsledku funkcie ako argumentu do vonkajšej funkcie.
Ako môžete vidieť z vyššie uvedenej snímky, v tabuľke sme získali najbližšie zhody z daného dátumu. Extrahujte podrobnosti z tabuľky pomocou vyššie uvedeného vzorca.
Tu je niekoľko pozorovacích poznámok pri použití vyššie uvedeného vzorca.
Poznámky:
- Funkcia vráti chybu #NA, ak argument vyhľadávacieho poľa pre funkciu MATCH nemá rovnakú dĺžku ako tabuľkové pole.
- Vzorec vráti chybu, ak sa hodnota lookup_value nezhoduje s hodnotou v tabuľke lookup_array.
- Funkcia zodpovedá presnej hodnote, pretože argument typu zhody pre funkciu MATCH je 0.
- Hodnoty vyhľadávania je možné zadať ako odkaz na bunku alebo priamo pomocou symbolu úvodzovky (") vo vzorci ako argumentov.
- NEPOUŽÍVAJTE kučeravé zátvorky ručne. Kučeravé zátvorky aplikované pomocou Ctrl + Shift + Enter namiesto spravodlivých Zadajte alebo vráti chybu.
Dúfam, že ste pochopili, ako vyhľadať najnižšiu alebo najmenšiu zhodu pomocou funkcie INDEX & MATCH v programe Excel. Tu nájdete ďalšie články o hodnotách vyhľadávania v Exceli a funkciách Excelu 2019. Neváhajte a uveďte svoje otázky nižšie v poli pre komentáre. Určite vám pomôžeme.
Ak sa vám páčili naše blogy, zdieľajte ich so svojimi priateľmi na Facebooku. A tiež nás môžete sledovať na Twitteri a Facebooku. Radi by sme vás počuli, dajte nám vedieť, ako môžeme zlepšiť, doplniť alebo inovovať našu prácu a zlepšiť ju pre vás. Napíšte nám na emailovú stránku
Na vyhľadanie hodnoty použite INDEX a MATCH : Funkcia INDEX & MATCH na vyhľadanie hodnoty podľa potreby.
Rozsah SUM s INDEX v Exceli : Pomocou funkcie INDEX nájdite SÚČET hodnôt podľa potreby.
Ako používať funkciu INDEX v programe Excel : Nájdite INDEX poľa pomocou funkcie INDEX vysvetlenej na príklade.
Ako používať funkciu MATCH v programe Excel : Nájdite MATCH v poli pomocou hodnoty INDEX vo funkcii MATCH vysvetlenej na príklade.
Ako používať funkciu LOOKUP v programe Excel : Nájdite hodnotu vyhľadávania v poli pomocou funkcie LOOKUP vysvetlenej na príklade.
Ako používať funkciu VLOOKUP v programe Excel : Nájdite hodnotu vyhľadávania v poli pomocou funkcie VLOOKUP vysvetlenej na príklade.
Populárne články
50 Skratka pre Excel, ktorá zvýši vašu produktivitu
Upravte rozbaľovací zoznam
Absolútna referencia v Exceli
Ak s podmieneným formátovaním
Ak so zástupnými znakmi
Funkcia Excel 2019 CONCAT
Funkcia Excel 2019 MAXIFS