Stalo se vám někdy, že jste potřebovali vyhledávat v Excelu, ale funkce SVYHLEDAT nefungovala? Právě pro takové případy je tady kombinace funkcí INDEX a POZVYHLEDAT, v angličtině INDEX a MATCH. Budete překvapeni, co tato kombinace funkcí dokáže v Excelu za parádu. Kombinace funkcí INDEX a POZVYHLEDAT je mnohem flexibilnější ve vyhledávání než například funkce SVYHLEDAT. Na rozdíl od funkce SVYHLEDAT nemusíte mít u kombinace funkcí INDEX a POZVYHLEDAT sloupec, dle kterého hledáte, v tabulce vždy vlevo od hledaných hodnot. Stejně tak si můžete tuto kombinaci funkcí použít jak na vertikální vyhledávání, tak na horizontální vyhledávání.
Excelový soubor ke stažení
INDEX a POZVYHLEDAT
Nejprve se na každou z těchto funkcí podíváme zvlášť.
Funkce INDEX
Funkce INDEX vrátí hodnotu nebo odkaz na buňku v určitém řádku nebo sloupci v dané oblasti. Funkce INDEX má tři parametry, přičemž povinné jsou první dva, a to parametr pole a řádek. Třetí parametr sloupec je nepovinný.
= INDEX (pole; řádek; [sloupec])
Na následujícím příkladu si ukážeme funkci INDEX ve své nejjednodušší podobě. Máme zde sloupec s názvy společností (A3:A11). Do pole C3 napíšeme funkci INDEX a jako první parametr pole funkce INDEX označíme seznam společností (A3:A11). Jako druhý parametr řádek vyplníme třeba číslo 5. Poslední třetí parametr je nepovinný, takže ho zatím nevyplníme a funkci potvrdíme. Funkce INDEX vrátila název společnosti, která je uvedená na pátém místě v seznamu. Funkce INDEX tak ve své nejjednodušší podobě vrátí hodnoty z řádků nebo sloupců, které ji zadáte.
Funkce INDEX dokáže pracovat i se sloupci. V dalším příkladu máme zdrojovou tabulku a řekněme, že bychom teď chtěli v buňce F15 vrátit hodnotu ve třetím sloupci v záhlaví. Do buňky F15 tedy napíšeme funkci INDEX, kde jako první parametr pole vyplníme pole záhlaví (A14:D14), a jako řádek napíšeme jedničku, jelikož jsme označili pouze jeden řádek. Chceme vrátit hodnotu ze třetího sloupce, tak napíšeme do třetího nepovinného parametru sloupce číslo 3. Funkce INDEX vrátila hodnotu ve třetím sloupci v záhlaví, tedy slovo Únor.
Funkce INDEX si poradí i s maticovým vyhledáváním. Na ukázku použijeme stejnou tabulku. Do buňky F17 napíšeme funkci INDEX a jako pole označíme celou tabulku (A14:D23). Jako řádek vyplníme například číslo 3 a tentokrát vyplníme i nepovinný parametr sloupec, třeba čísl 2. Víte, jakou hodnotu teď funkce INDEX vrátí?
Funkce INDEX vrátila hodnotu 148 z buňky B16. Jedná se o hodnotu, která se v tabulce nachází na třetím řádku a ve druhém sloupci. Což je přesně to, co jsme vyplnili ve funkci INDEX.
V posledním příkladu na funkci INDEX máme stejnou zdrojovou tabulku, akorát chceme vrátit hodnotu z tabulky v závislosti na tom, jaký řádek a sloupec napíšeme do buněk G20 a G21. Do buňky G22 tedy napíšeme funkci INDEX a jako pole označíme hodnoty v tabulce (A14:D23) a jako parametr řádek označíme buňku G20 a jako sloupec označíme buňku G21. Potvrdíme a máme ta díky funkci INDEX jednoduché maticové vyhledávání ve zdrojové tabulce.
Funkce POZVYHLEDAT
Funkce POZVYHLEDAT, anglicky funkce MATCH, vrátí polohu položky, pro které určíme souřadnice. Funkce POZVYHLEDAT funguje trochu jako funkce SVYHLEDAT. Funkce POZVYHLEDAT má dva povinné parametry a jeden parametr nepovinný. Povinné parametry jsou co hledáme a kde to hledáme a nepovinný parametr je stejně jako u funkce SVYHEDAT shoda.
= POZVYHLEDAT (co; prohledat; [shoda])
Na stejnou zdrojovou tabulku zkusíme použít funkci POZVYHLEDAT. V buňce F30 máme uvedenou společnost Coca Cola a v buňce G30 chceme zjistit její polohu v naší zdrojové tabulce (A29:D38). Do buňky G30 tedy napíšeme funkci POZVYHLEDAT a jako parametr co označíme to, co hledáme, tedy společnost Coca Cola (F30). Druhý parametr funkce POZVYHLEDAT je prohledat, tedy kde hledanou hodnotu hledáme. Na rozdíl od funkce SVYHLEDAT neoznačujeme ve funkci POZVYHLEDAT celou tabulku, ale pouze sloupec, kde se hledaná hodnota nachází. Označíme proto sloupec Společnost (A30:A38). Poslední parametr funkce POZVYHLEDAT je nepovinný a jedná se o typ shody, podobně jako ve funkci SVYHLEDAT. Máme na výběr ze tří možností – můžeme hledat přesnou shodu, a nebo můžeme hledat hodnotu menší než a nebo větší než. V tomto základním příkladu vybereme přesnou shodu a potvrdíme. Funkce POZVYHLEDAT vrátila hodnotu 5. Tato hodnota znamená, že ve sloupci se společnostmi je Coca Cola na pátém řádku v tabulce.
Pokud v buňce G32 budeme hledat koordinace společnosti Avast, tak v buňce G32 napíšeme funkci POZVYHLEDAT, jako parametr co funkce POZVYHLEDAT označíme hodnotu v buňce F32, jako parametr prohledat označíme sloupec se společnostmi (A30:A38) a jako shodu vyplníme přesnou shodu. Funkce vrátí hodnotu 8, což znamená, že se společnost Avast nachází na osmém řádku v tabulce.
Funkce INDEX a POZVYHLEDAT pro vyhledávání v Excelu
Kombinace funkcí INDEX a POZVYHLEDAT je skvělá pro vyhledávání v Excelu. Zatímco funkce POZVYHLEDAT určí polohu hledané hodnoty, tak funkce INDEX následně podle této koordinace přiřadí hledanou hodnotu.
Ukážeme si to na klasickém příkladu. Na ukázku použijeme zdrojovou tabulku se společnostmi a jejich tržbami (A44:B53). Do cílové tabulky (D44:E53) chceme podle společností doplnit tržby do sloupce E45:E53.
Začneme postupně a nejdříve do první buňky E4 napíšeme funkci POZVYHLEDAT. Jako parametr co hledáme, označíme společnost, kterou hledáme v buňce D45. Tuto buňku nemusíme fixovat, jelikož potřebujeme, aby se vzorec posouval s funkcí směrem dolů. Druhý parametr je prohledat, tedy kde tuto společnost hledáme. Hledáme ji ve sloupci se společnostmi (A45:A53). Tentokrát musíme zafixovat buňky jak pro sloupce, tak řádky, a to klávesou F4. V posledním parametru vyplníme přesnou shodu. Potvrdíme a funkce POZVYHLEDAT vrátila polohu správně odpovědi. Správná odpověď se tedy nachází na pátém řádku. A jak nám k tomu pomůže funkce INDEX?
Víme, že funkce INDEX vrací hodnotu podle určeného řádku nebo sloupce. Funkce POZVYHLEDAT tak poslouží jako ukazatel koordinace pro funkci INDEX. Celou funkci POZVYHLEDAT tedy zabalíme do funkce INDEX a ve funkci INDEX jako pole označíme hodnoty, kde jsou naše odpovědi, což jsou tržby (B45:B53). Nezapomeneme tento sloupec zafixovat klávesou F4 jak pro sloupce, tak řádky. A jako parametr řádky poslouží celá funkce POZVYHLEDAT. Nepovinný parametr sloupce nepotřebujeme. Potvrdíme, pošleme vzorec dolů a zkontrolujeme.
Stejná kombinace funkcí INDEX a POZVYHLEDAT by fungovala i na horizontálně orientovanou tabulku. V dalším příkladu máme stejná zdrojová data, akorát horizontálně (A57:J58). Chceme doplnit cílovou tabulku (A61:B69) stejně jako v předcházejícím příkladu.
Tentokrát to zkusíme napsat celé dohromady. V buňce B61 tedy nejprve napíšeme funkci INDEX a jako pole označíme hodnoty, které hledáme, což jsou tržby v řádku B58:J58. Nezapomeneme řádek zafixovat klávesou F4. Místo parametru řádek napíšeme funkci POZVYHLEDAT, kde jako parametr co označíme první společnost v cílové tabulce (A61). Jako parametr prohledat označíme řádek se společnostmi (B57:J57) a opět hledáme přesnou shodu. Ukončíme závorky a potvrdíme a stáhneme pro ostatní buňky dolů. Tržby se doplnily správně.
Obrovskou výhodou kombinace funkcí INDEX a POZVYHLEDAT pro vyhledávání je to, že na rozdíl od funkce SVYHLEDAT nemusí být hledané hodnoty ve zdrojové tabulce vždy jako první sloupec. Na pořadí sloupců u INDEX a POZVYHLEDAT vůbec nezáleží.
V dalším příkladu máme ve zdrojové tabulce nejdříve uvedené tržby (A74:A82) a až následně společnosti (B74:B82). Do cílové tabulky (D74:F82) chceme opět doplnit tržby. Pokud bychom na takto strukturovaná data použili funkci SVYHLEDAT, tak se nám vrátí chyba, jelikož sloupec se společnostmi není ve zdrojové tabulce jako první (buňka E74). Použijeme tedy kombinaci funkcí INDEX a POZVYHLEDAT. Do buňky F74 napíšeme funkci INDEX, kde jako pole označíme sloupec s tržbami (A74:A82), tento sloupec rovněž zafixujeme klávesou F4 jak pro řádky, tak sloupce. Jako parametr řádky napíšeme funkci POZVYHLEDAT, kde jako parametr co označíme první společnost (D74) a jako parametr prohledat označíme sloupec se společnostmi (B74:B82), opět sloupec zafixujeme klávesou F4. Hledáme přesnou shodu, ukončíme závorky a potvrdíme a pošleme vzorec dolů.
Způsob, jak spolu funkce INDEX a POZVYHLEDAT u vyhledávání fungují je ten, že funkce POZVYHLEDAT nejprve lokalizuje řádek, na kterém se nachází to, co hledáte. Výsledkem funkce POZVYHLEDAT je tedy jen číslo. Funkce POZVYHLEDAT je ale součástí funkce INDEX, kde máte v parametru pole označené buňky, kde se nachází odpovědi, které hledáte. Jen nevíte na jakém řádku nebo v jakém sloupci se odpověď nachází. Toto číslo vám dodá právě funkce POZVYHLEDAT. Funkce INDEX tak už jen vezme hodnotu z řádku, kterou ji určí funkce POZVYHLEDAT.
Hlavní výhody kombinace funkcí INDEX a POZVYHLEDAT:
- Na rozdíl od funkce SVYHLEDAT nemusí být sloupec, dle kterého vyhledáváme v tabulce vždy vlevo.
- Na rozdíl od SVYHLEDAT a VVYHLEDAT stačí pouze tato kombinace funkcí, a to jak pro vertikální, tak pro horizontální vyhledávání.
Co byste si u kombinace funkcí INDEX a POZVYHLEDAT měli pamatovat:
U funkce INDEX musíte v parametru pole vždy vyplnit celou oblast, kde předpokládáte, že se budou vyskytovat hledané hodnoty. V buňce F15 se objevila chybová hláška u funkce INDEX z toho důvodu, že jsme jako parametr pole označili pouze jeden řádek A14:D14, ale jako parametr řádek jsme vyplnili číslo 2. Funkce INDEX se tak v řádku posunula mimo hledané pole a vrátila chybu. Pokud bychom chtěli chybu napravit, museli bychom rozšířit pole o řádek 15. Parametr pole ve funkci INDEX by tedy byl A14:D15.
U funkce POZVYHLEDAT musíte v parametru prohledat vždy označit pouze jeden sloupec nebo řádek. Kdybyste označili větší pole, tak funkce POZVYHLEDAT nebude vědět, zda má hledanou hodnotu hledat směrem dolů nebo do stran. Výsledkem by byla tedy chybová hláška NENÍ_K_DISPOZICI.
Rozsah označených buněk jak pro funkci INDEX, tak POZVYHLEDAT musí být stejný. Nemůžeme označit dvě různě veliké oblasti jako na příkladu níže. Výsledkem takové funkce by byla chyba, jelikož by některé hledané společnosti nebo tržby byly mimo označené pole.
