Excel není jen „tabulka“, je to nástroj, který pomáhá k lepšímu získávání informací z dat a ke zvyšování produktivity. Pokročilé funkce se hodí, abyste mohli pracovat efektivněji. Zajímají také personalisty*ky, když zjišťují, jak dobře ovládáte Excel.
Základy jsme probrali v článku Excel pro začátečníky: 10 základních funkcí. Pojďme si představit funkce, které vám otevřou dveře k pokročilému využití Excelu. Ukážeme si, jak se používá VLOOKUP (SVYHLEDAT), INDEX, MATCH (POZVYHLEDAT), COUNTIF a XLOOKUP.
Užitečná je nejen znalost jednotlivých funkcí a jejich využití, ale i obvyklých chyb spojených s některými z nich. Třeba funkce SVYHLEDAT vypadá na první pohled jednoduše, ale má hned několik úskalí, na která se zapomíná.
VLOOKUP (Vertikální vyhledávání)
Funkce VLOOKUP hledá zadanou hodnotu v prvním sloupci tabulky a dosazuje hodnotu buňky ve stejném řádku jiného sloupce.
Syntax v anglické verzi: =VLOOKUP (hledaná hodnota, tabulka, číslo sloupce [shoda])
Syntax v české verzi: =SVYHLEDAT (hledaná hodnota; tabulka; číslo sloupce; [shoda])
Příklad: Představte si, že máte tabulku se seznamem zaměstnanců*kyň a jejich platy. Chcete zjistit plat konkrétního člověka. Použijete funkci VLOOKUP následovně:
Syntax: =SVYHLEDAT ("Novák"; A2:C10; 3; FALSE)
=SVYHLEDAT (A2; A2:C10; 3; FALSE)
Tento vzorec vyhledá „Novák“ ve sloupci A a vrátí hodnotu ze třetího sloupce (sloupec C), což je jeho plat. Odkazovat můžete také přímo na buňku (například A2). Hodnota, která se vrací, je v prvním příkladu vyznačena tučně.
Prvním parametrem funkce je slovo „Novák“, tedy co hledáme. Ve druhém parametru upřesňujeme oblast buněk, ve kterých se má dané slovo vyhledávat. Třetí parametr označuje pořadí sloupce, ve kterém se vyskytuje hledaná odpověď. Poslední parametr je nepovinný, což znamená, že ho nemusíte zadávat. Potřebujete však vědět, co se stane, když parametr nevyplníte.
Jak je to s nepovinným parametrem „shoda“
Poslední parametr funkce SVYHLEDAT je označován jako „shoda“. Je nepovinný, což znamená, že funkce vám i při jeho nevyplnění dodá výsledky. Hodnoty se však mohou doplnit nesprávně. Jedná se o chybu, která se dá velmi snadno přehlédnout.
Parametr shoda vám totiž umožňuje zvolit, zda chcete hledat přesnou nebo jen přibližnou shodu. Pokud jej nevyplníte, funkce vyhledá přibližnou shodu. Stejně jako v případě, kdy zadáte TRUE.
3 časté chyby při práci s funkcí SVYHLEDAT
Funkce SVYHLEDAT je vhodným příkladem, na kterém si můžeme ukázat další chyby.
1. Označte správnou oblast
Dejte si pozor, aby oblast byla ukotvená pomocí „$“, jinak může nastat situace, že dojde k posunu buněk a pak se neodkazuje na to, co chcete. Takže si například překopírujete sloupeček dolů a už to není A2:C10, ale A3:C11.
Když používáte funkci SVYHLEDAT v Excelu, je důležité si uvědomit, že pořadí sloupců ve funkci závisí na oblasti, kterou jste vybrali, nikoliv na pořadí sloupců ve zdrojové tabulce.
Pokud označíte jen část zdrojové tabulky, počítejte se změnou pořadí sloupce a vyplňte číslo sloupce v označené oblasti. Jinak vám funkce vrátí chybu, protože jste zadali sloupec mimo označené buňky.
2. Hleďte vlevo
Při použití funkce SVYHLEDAT musí být hodnota, podle které vyhledáváte, vždy nalevo od hodnot, které funkce hledá. Hodnota se však nemusí nutně nacházet v prvním sloupci tabulky. Jak máte postupovat, pokud je sloupec s hodnotami uprostřed tabulky, kterou nemůžete měnit? Proveďte označení tak, aby hodnoty, dle kterých chcete vyhledávat byly ve vybrané oblasti jako první.
Pokud vám výše uvedená jednoduchá rada nepomůže, má Excel v záloze kombinaci funkcí INDEX a POZVYHLEDAT, v angličtině známých jako INDEX a MATCH. Jsou řešením v případech, kdy funkce SVYHLEDAT nestačí.
Jejich hlavní výhodou je, že sloupec, podle kterého vyhledáváte, se nemusí nacházet vlevo od hledaných hodnot. Kombinace funkcí INDEX a MATCH navíc umožňuje nejen vertikální, ale i horizontální vyhledávání. Další možností je funkce HLOOKUP. Jak vidíte, Excel nabízí různé alternativy a možnosti, které vám mohou usnadnit práci, s jejich efektivním využitím vám pomůže online kurz Pokročilý Excel.
Veškerý obsah v užitečném formátu. Rozhovory, články, life hacky a tipy ze světa businessu i korporátů na našem Instagramu.
Pojďte se připojit!
INDEX
Funkce INDEX vrací hodnotu z konkrétního umístění (řádků nebo sloupců).
Syntax: =INDEX (pole; řádek; [sloupec])
Funkce INDEX má dva povinné parametry (pole, řádek) a jeden nepovinný (sloupec).
Příklad: Pokud chcete zjistit plat zaměstnance „Novák“ pomocí funkce INDEX, můžete použít následující vzorec:
Syntax: =INDEX (C2:C5; 1)
Vzorec vrátí hodnotu z oblasti C2:C5, která odpovídá hledanému platu.
MATCH
Funkce MATCH (POZVYHLEDAT) vrací pozici hledané položky v rámci určitého seznamu nebo oblasti. Připomíná funkci SVYHLEDAT.
Syntax: =MATCH (hledaná hodnota; prohledávané pole; [shoda])
Pracuje podobně jako funkce SVYHLEDAT. Má dva povinné parametry (hodnota, pole) a jeden nepovinný (shoda). Typ shody umožňuje tři možnosti: hledání přesné shody, shody menší než, shody větší než.
Příklad: Když hledáte pozici „Novák“ ve sloupci A, pak vzorec s funkcí MATCH vypadá následovně.
Syntax: =MATCH ("Novák"; A2:A5; 0)
Vzorec hledá přesnou shodu hodnoty „Novák“ v oblasti A2:A5. Pokud funkce vrátí hodnotu 1, znamená to, že „Novák“ je na prvním místě v zadané oblasti.
Když změníte hledané jméno na „Procházka“, funkce vám vrátí hodnotu 4, protože jméno je na čtvrtém místě v zadané oblasti:
Jméno „Němec“ je mimo prohledávanou oblast:
Příklad kombinace funkcí INDEX a MATCH: Představte si, že chcete najít plat zaměstnance „Novák“ podobně jako u funkce SVYHLEDAT, ale pomocí kombinace funkcí INDEX a MATCH:
Syntax: =INDEX(C2:C10; MATCH("Novák"; A2:A10; 0))
MATCH vyhledá pozici „Novák“ ve sloupci A a INDEX použije tuto pozici k získání odpovídající hodnoty ve sloupci C.
XLOOKUP
V nejnovějších verzích Excelu (buď 365 nebo poslední verze) najdete ještě funkci XLOOKUP. Zajímavá je tím, že zvládá vyřešit neduhy funkce VLOOKUP (SVYHLEDAT) a je jednodušší na pochopení než INDEX/MATCH. Pokud máte starší verzi Excelu, můžete tuto funkci využívat v Google Sheets.
Představte si, že máte seznam zaměstnanců a jejich platů a chcete najít plat konkrétního zaměstnance pomocí funkce XLOOKUP.
Syntax: =XLOOKUP ("Novák"; A2:A5; C2:C5)
=XLOOKUP (hledaná hodnota; prohledávané pole; oblast, ze které chceme vrátit hodnotu)
Funkce XLOOKUP je flexibilnější, protože není omezena na vyhledávání pouze v prvním sloupci. Níže je pěkně vidět rozdíl oproti funkci SVYHLEDAT a obrázku, který je na začátku článku jako třetí příklad s funkcí SVYHLEDAT. Musíte ovšem upravit prohledávanou oblast.
3. Pozor na duplicitní záznamy
Funkce SVYHLEDAT v Excelu vždy vrací první hodnotu, kterou najde. V praxi se pak stává, že pokud máte v tabulce duplicitní záznamy, funkce vám vrátí pouze první nalezenou hodnotu splňující zadaná kritéria.
Tento problém se týká duplicitních záznamů ve zdrojové tabulce. Existují jiné možnosti, které se dají použít, když potřebujete vrátit hodnotu, která je vícekrát. Funkce COUNTIF pomůže odhalit možné duplicitní hodnoty.
Funkce COUNTIF v Excelu je užitečným nástrojem pro hledání duplicitních záznamů. Snadno díky ní zjistíte, kolikrát se určitá hodnota vyskytuje v seznamu nebo oblasti buněk.
Syntax: =COUNTIF (rozsah; kritérium)
Rozsah znamená oblast buněk, ve které chcete hledat duplicitní záznamy. Kritérium je hodnota, kterou hledáte. Nejprve označte oblast, pak použijte funkci COUNTIF pro každou buňku v oblasti, abyste zjistili, kolikrát se zde daná hodnota vyskytuje.
Příklad: Zjišťujete, kolikrát se hodnota v buňce A2 objevuje v celém rozsahu A2:A10. Přetáhněte vzorec dolů, abyste zkontrolovali každou buňku ve sloupci.
Syntax: =COUNTIF ($A$2:$A$10; A2)
Když přidáte do tabulky jméno „Novák“ a upravíte prohledávanou oblast o nový řádek, změní se hodnota, kterou vám funkce vrací.
Pokud hodnota, která se vrací, je větší než 1, znamená to, že daná položka je duplicitní. Číslo označuje, kolikrát se objevuje v prohledávané oblasti. Samotné odstranění řeší možnost Remove Duplicates na kartě Data.
Zjednodušte si práci
Použití pokročilých funkcí jako jsou VLOOKUP, INDEX, MATCH, COUNTIF a XLOOKUP výrazně zvýší vaši produktivitu a schopnost analyzovat data v Excelu. Pokročilé funkce v Excelu vám nejen zlepší technické dovednosti, ale také usnadní každodenní práci. Chcete je úspěšně zvládnout?
Online kurz Pokročilý Excel je plný praktických informací od opakování funkcí v širším kontextu, přes principy připojení k interním i externím datovým zdrojům až k vizualizacím interaktivních grafů a tabulek a prezentaci dat v reportu. Lektor Jan Kudělka vše předvede online v živém vysílání, vy se můžete ptát a zkoušet si nové věci pod jeho vedením. Online kurz vám pomůže naplno využívat potenciál Excelu.
Chcete se přihlásit k odběru novinek?
