SVYHLEDAT, KDYŽ, INDEX a některé další funkce Excelu, které musíte znát

Článek přináší přehled praktických a často používaných funkcí, na které se nás na školeních Excelu pravidelně dotazujete. Znalost většiny zde uváděných funkcí by měla být samozřejmostí.

SVYHLEDAT

Funkce SVYHLEDAT (anglicky VLOOKUP) je funkce, která dokáže dohledat k určité hodnotě doplňující informace ze souhrnné tabulky.

Syntaxe funkce: SVYHLEDAT(hledat;tabulka;sloupec;[typ])

Příklad požadavku

Z tabulky, kde evidujeme SPZ vlastněných aut, potřebujeme zobrazit počet ujetých km auta s konkrétní SPZ. Potřebujeme znát:

• Hledanou SPZ (argument Hledat)
• Tabulku, která se má prohledávat (argument Tabulka)
• Pořadové číslo sloupce, z kterého potřebujeme zobrazit výsledek (argument Sloupec)
• Typ hledání: SVYHLEDAT umí hledat přesnou hodnotu, nebo nejbližší nižší. Pro hledání přesné hodnoty musíme zadat do argumentu Typ hodnotu 0 (nula), nebo slovně NEPRAVDA.

Ve výše uvedené tabulce se v prvním sloupci dohledá požadovaná SPZ (v našem případě hledáme ADA 28-13). Po nalezení této SPZ funkce SVYHLEDAT posune svoji „pozornost“ vodorovně doprava o námi zadaný počet sloupců (v tomto případě 4) a zobrazí počet ujetých km auta s SPZ ADA 28-13, tedy hodnotu 3623 km, která je v našem případě výsledkem funkce SVYHLEDAT.

Argument Sloupec je možné automatizovat funkcemi SLOUPEC nebo POZVYHLEDAT. Uživatel pak nemusí otrocky zapisovat do velkého množství sloupců pokaždé pořadové číslo sloupce, výpočet v dalších sloupcích je proveden automaticky.

Ve většině případů je potřeba zadat argument Typ jako NEPRAVDU, resp. 0 (nulu). Vstup 0 (nebo NEPRAVDA) znamená, že funkce SVYHLEDAT bude pracovat v režimu přesném, nikoli přibližném.

Jestliže hledaná hodnota nebude v prvním sloupci nalezena, funkce SVYHLEDAT vrátí vždy chybu. Pokud nám formát chyby nevyhovuje, lze funkci SVYHLEDAT vnořit do funkce IFERROR.

Funkce SVYHLEDAT a přibližné dohledání

V marketingu, účetnictví, nákupu, controllingu a dalších oblastech potřebujete v naprosté většině situací dohledat funkcí SVYHLEDAT vždy konkrétní hodnotu. Tedy pokud budete hledat podrobnosti k číslu faktury, potřebujete podrobnosti k onomu konkrétnímu číslu, nikoli k jinému. O přesné dohledání podrobností se postará argument Typ, do kterého uvedete 0 (nulu), nebo nepravda.

Nicméně funkce SVYHLEDAT dokáže pracovat v režimu, kdy pokud požadovaná hledaná hodnota není nalezena, je dohledána nejbližší nižší, na základě které se zobrazí výsledky. Příkladem by mohla být situace, kdy odběratel při vyšším množství odebraného zboží má určitou slevu. Tedy při odběru zboží v množství 1 ks je sleva 0%, v množství do 10 ks je sleva 3% atd.

Ve sloupci A jsou uvedené limity na odebrané zboží, ve sloupci B pak slevy odpovídající odběru určitého množství zboží.

Do buňky E1 zadáváme množství aktuálně objednaného zboží a v buňce E2 je funkcí SVYHLEDAT zobrazená sleva. Pokud bude např. zboží objednáno v množství mezi 11 a 100 ks, sleva bude 3%.

Funkce SVYHLEDAT zde hledá aktuálně objednané množství zboží v prvním sloupci tabulky. Pokud hodnotu najde, zobrazí slevu (např. hodnota 100 a sleva 6%). Pokud hodnotu nenajde, hledá nejbližší nižší hodnotu k námi zadané hodnotě a k ní zobrazí slevu (např. zadáme množství 88 ks, přičemž nejbližší hodnota k 88 ks je 10 a sleva pro 10 kusů jsou 3%).

Pro použití funkce SVYHLEDAT pro dohledání nejbližší nižší hodnoty je potřeba souhrnnou tabulku seřadit podle prvního sloupce vzestupně a v argumentech nastavit Typ na 1 (jedničku), nebo Pravdu, nebo tento argument zcela vynechat.

Pro správné použití funkce SVYHLEDAT nikdy nesmí být v klíčovém sloupci souhrnné tabulky duplicity. Duplicity lze identifikovat podmíněným formátováním nebo funkcemi.

Doporučení k argumentu Tabulka: Souhrnnou tabulku je rozumné pojmenovat. Výhoda pojmenování je ve snazším zadávání argumentu Tabulka a především ve zkrácení a zpřehlednění zápisu funkce SVYHLEDAT. Pojmenování oblasti buněk je užitečné i pro většinu ostatních funkcí Excelu.

Pro pojmenování tabulky postupujte následovně:

Varianta A

Tabulku označte. Do Pole názvů napište název tabulky. Název musí být jedinečný (jiná tabulka nesmí mít stejný název a v názvu nesmí být mezery).

Pokud byste název chtěli změnit nebo zrušit, na kartě Vzorce najdete Správce názvů (lze vyvolat zkratkou CTRL+F3).

Varianta B

Z tabulky vytvořte „objektovou“ tabulku. Na kartě Vložení použijte příkaz Tabulka (zkratka CTRL + T) a v Nástrojích tabulky ji pojmenujte.

Pokud jsou oblasti buněk pojmenovány, samy se nabízí při zadávání funkcí v jejich argumentech.

V okně Argumenty funkce je možné si pomoci funkční klávesou F3.

Další informace k funkci SVYHLEDAT naleznete v nápovědě.

SLOUPEC

Funkce SLOUPEC (anglicky COLUMN) vrací pořadové číslo sloupce od začátku tabulky. Lze ji zadat bez argumentu, potom určí pořadové číslo sloupce aktivní buňky, nebo odkazem na buňku a pak tato funkce určí pořadové číslo sloupce nikoli aktivní buňky, ale buňky, na kterou odkazujeme.

Funkce SLOUPEC sama o sobě většinou nemá příliš význam, lze ji ale použít jako pomocnou funkci např. pro automatizaci funkce SVYHLEDAT. Funkce SVYHLEDAT má argument Sloupec, do kterého se většinou ručně zapisuje pořadové číslo sloupce, ze kterého je třeba vrátit výsledek. Pokud uživatel potřebuje vracet výsledky z více vedlejších na sebe navazujících sloupců, je možné si situaci usnadnit vnořením funkce SLOUPEC.

Výše uvedený postup lze snadno rozkopírovat najednou do více sloupců např. přes CTRL + Enter.

POZVYHLEDAT

Funkce POZVYHLEDAT (angl. MATCH) umožňuje dohledat pozici hledané hodnoty v oblasti hodnot.

Na obrázku výše tato funkce zjišťuje pozici fráze David (vstupní buňka B5) v oblasti B2:I2. Výsledkem je hodnota 5, protože David se v oblasti B2:I2 nachází na páté pozici od začátku označené oblasti. Funkce POZVYHLEDAT dokáže hledat přesnou frázi nebo přibližnou hodnotu. Pokud hledáme číslo a toto číslo v oblasti není, můžeme argumentem Shoda dohledat pozici nejbližšího nižšího, nebo naopak nejbližšího vyššího čísla, nicméně při tomto požití funkce POZVYHLEDAT je třeba dbát na správné řazení prohledávané oblasti.

Funkce POZVYHLEDAT je podobně jako funkce SLOUPEC pomocnou funkcí a lze ji velmi dobře využít např. ve funkci SVYHLEDAT.

Ve funkci SVYHLEDAT ji můžeme vložit do argumentu Sloupec, kde se postará o dodání pořadového čísla sloupce, ze kterého je třeba zobrazit výsledek. Funkce POZVYHLEDAT zde dokáže ušetřit velké množství času a práce.

Výhodou výše uvedeného postupu je možnost zapsat do buněk B1 až I1 jakýkoli existující název sloupečku tabulky. Funkce POZVYHLEDAT zjistí jeho pořadí v originální tabulce a funkce SVYHLEDAT vrátí odpovídající hodnotu. Výše uvedený postup stačí jednoduše ukončit kombinací CTRL + Enter, čímž se funkce rozloží do požadovaného množství řádků a sloupců.

IFERROR

Funkce IFERROR je užitečná v případě, že nám některý výpočet občas zobrazuje chybové hlášení. Pokud nám systémový kód chyby nevyhovuje, funkce IFERROR se postará o zobrazení jiného textu nebo i o provedení jiného výpočtu, pokud to bude v dané situaci vyhovující.

Syntaxe funkce IFERROR: IFERROR(hodnota, hodnota_při_chybě)

Hodnotou je většinou výpočet. Pokud výpočet proběhne v pořádku a jeho výsledkem nebude chyba, funkce IFERROR zobrazí správný výsledek. Jestliže ovšem výsledek bude chybový, provede se hodnota_při_chybě. Příklad: při používání funkce SVYHLEDAT se občas stane, že funkcí SVYHLEDAT hledaná hodnota nebyla nalezena. V tomto případě funkce SVYHLEDAT vrátí chybové hlášení #NENÍ_K_DISPOZICI. Pokud nám tento výraz nevyhovuje, je možné funkci SVYHLEDAT vnořit do IFERROR a pokud hledaná hodnota nebude nalezena, zobrazí to námi zadaný text. Např. nenalezeno, nebo nic (""). Výstupem v případě chyby nemusí být pouze text, ale např. jiný vzorec.

INDEX

Funkce INDEX dokáže zobrazit hodnotu, která se nachází v určité oblasti na určitém místě. Místo hodnoty může být pořadové číslo buňky ve sloupci, řádku nebo v obojí.

Syntaxe: INDEX (pole, řádek; [sloupec])

Pole je oblast, z které má funkce index vrátit výsledek. Oblastí může být řádky (např. A1:A10), sloupce (např. A1:H1) nebo oblast (např. A1:H10). Řádek, případně sloupec jsou zacílením do buněk v poli, odkud se má vzít výsledná hodnota.

Funkci INDEX lze v určitých situacích použít místo funkce SVYHLEDAT. Kromě své základní podoby (maticové) má podobu odkazovou, která umožňuje zobrazovat výsledek z různých polí. Funkce index je dále popsána na praktickém příkladu Dohledání nejnižší ceny a jejího dodavatele.

ZVOLIT

Funkce ZVOLIT (anglicky CHOOSE) lze chápat jako jakousi paměť na buňky nebo oblasti buněk.

Syntaxe: ZVOLIT(index, hodnota1; [hodnota2];...)

Hodnot může být od 1 do 254, přičemž hodnota je paměť na číslo nebo rozsah. Rozsahem může být např. A1:A10. Index je odkaz do hodnoty. První hodnota má index 1, druhá 2, třetí 3 atd.

Funkci ZVOLIT lze použít pro výpočty, kdy se hodí vybírat z různých oblastí buněk tu aktuální. Např. =SUMA(ZVOLIT(G17;B16:B21;C16:C21;D16:D21;E16:E21)) bude podle indexu (hodnoty v buňce G17) sčítat jednu z oblastí B16:B21, C16:C21 atd.

KDYŽ

Funkce KDYŽ (anglicky IF) je základní funkcí, která umožňuje provádět test a na základě výsledu testu provést větvení. Tedy pokud je test ověřen, funkce KDYŽ spouští větev ANO, pokud test není ověřen, spouští se větev NE.

Syntaxe: KDYŽ(podmínka;ano;ne)

Funkce KDYŽ pracuje s relačními operátory:

Operátory lze na standardní české klávesnici zadat přes pravý ALT a čárku, resp. tečku.

Příklad: Evidujeme u zaměstnanců čerpání dovolené a je potřeba být upozorněni, pokud zaměstnanec vyčerpal např. více než 25 dnů dovolené.

Pokročilé větvení funkce KDYŽ

Uvnitř jedné funkce KDYŽ lze provést až 64 testů. Toho je dosaženo tzv. vnořováním. Základní funkce KDYŽ provede v podmínce test a podle situace lze do argumentu Pravda nebo Nepravda vložit další funkci KDYŽ, která bude mít svoji další podmínku a své vlastní větvení Pravda a Nepravda, přičemž každá následující Pravda i Nepravda lze dále větvit vnořenou funkcí KDYŽ a takto to lze provést až do maximální 64. úrovně. Příklad na testování číselné hodnoty Pokud do buňky A1 zapíšeme jakékoli záporné číslo, funkce KDYŽ nás upozorní textem Záporná hodnota, pokud do buňky A1 zapíšeme 0, funkce nás upozorní na nulovou hodnotu a pokud do buňky A1 uvedeme jakékoli kladné číslo, funkce KDYŽ nás upozorní výstupem Kladná hodnota. =KDYŽ(A1<0;“Záporná hodnota“;KDYŽ(A1=0;“Je nula“;“Kladná hodnota“))

COUNTIFS

Funkce COUNTIFS zjistí četnost výskytů určitého kritéria v určité oblasti buněk. Například můžeme zjistit, kolikrát se v tabulce nachází určitý typ zboží, dodavatele, zaměstnance atp.

Syntaxe: COUNTIFS(oblast_kritérií1, kritérium1, [oblast kritérií2, kritéria2]…)

Funkce COUNTIFS je novější verzí funkce COUNTIF, která dokázala pracovat pouze s jedním kritériem. Funkce COUNTIFS nabízí k výběru až 127 kritérií.

SUMIFS

Funkce SUMIFS je sumou s podmínkou: dokáže v tabulce sečíst hodnoty odpovídající požadované podmínce. Díky této funkci lze v tabulce odběratelů sečíst obrat konkrétního odběratele nebo např. sečíst obraty vyšší než určitý limit.

Syntaxe: SUMIFS(oblast_součet, oblast_kritérií1, kritéria 1, oblast_kritérií2, kritéria2], ...)

Funkce SUMIFS je novější verzí funkce SUMIF. Funkce SUMIF dokázala pracovat pouze s jedním kritériem, funkce SUMIFS zvládne kritérií až 127.

OBSAHUJE

Excel nenabízí funkci OBSAHUJE. Pokud ale potřebujeme ověřit, zda určitá buňka na začátku, na konci nebo kdekoli uprostřed obsahuje určitý řetězec, lze toho docílit funkcí HLEDAT. Tato funkce vrátí pozici hledaného řetězce v buňce. Např. hledáme, zda v buňce je kořen slova klad (z pokladní).

Pokud bude v některé buňce ve sloupci A kdekoli kořen klad, funkce vrátí číselnou pozici začátku řetězce. A jestli tam nebude, funkce vrátí chybu.

Konstrukci lze „učesat“ tak, že napíšeme funkci KDYŽ, do které vnoříme funkci HLEDAT. Pokud bude výsledek HLEDAT vyšší nebo roven 1, znamená to, že kořen v buňce je. Pokud ne, funkce vrátí stále chybu. Chybu ošetříme např. funkcí JE.CHYBHODN.

Výše uvedeného triku lze využít velmi účinně např. v pokročilém podmíněném formátování. Příklad: pokud bude určitá buňka obsahovat určitý kořen, zvýrazní se celý řádek.

IDENTIFIKACE DUPLICIT

V excelových tabulkách můžete mít podezření na duplicitní hodnoty v oblastech, kde nemají být. Pokud bude vaše excelová tabulka příliš velká, můžete duplicity identifikovat:

• Podmíněným formátováním
• Funkcí COUNTIF nebo COUNTIFS

Identifikované duplicity lze poté snadno vyfiltrovat, opravit nebo odstranit.

Identifikace duplicit v Excelu za pomoci podmíněného formátování

Jednoduchý postup

Označíme oblast buněk, v které máme podezření na duplicity. Po označení volíme na kartě Domů ikonu Podmíněné formátování, kde v části Pravidla zvýraznění buněk volíme Duplicitní hodnoty. Zvolíme, zda nás více zajímají duplicity, či jedinečné hodnoty, a dále barvu duplicit.

Zvýraznění celého řádku s duplicitní hodnotou

Často může být užitečné zvýraznit nikoli jen buňku obsahující duplicitní hodnotu, ale celý řádek s duplicitou. Toho lze dostáhnout např. tak, že za pomoci podmíněného formátování budeme testovat, kolikrát se hodnota v určité buňce nachází v určité oblasti. Pokud tam bude více než jednou, hodnota bude duplicitní.

Jestliže duplicitní údaje za pomoci podmíněného formátování zvýrazníme, je možné je za účelem další kontroly a oprav přesunout do horní části obrazovky, případně je možné je filtrovat podle barvy buňky.

Pokud bude zcela jasné, že duplicitní hodnoty jsou opravdu nadbytečné a třeba je odstranit, lze použít nástroj Odebrat duplicity. Tento nástroj bez dalšího potvrzování v zadaných sloupcích všechny řádky s duplicitními hodnotami odstraní a ponechá jen první záznam.

Nástroj Odebrat duplicity se nachází na kartě Data, případně je k dispozici ještě v Nástrojích tabulky nebo nápovědním poli Řekněte mi, co chcete udělat (Excel 2016).

Další funkce Microsoft Excel 2016 nabízí uživateli téměř 500 uživatelských funkcí, přičemž další si může uživatel vytvořit ve Visual Basicu. Aktuální přehled funkcí Excelu s jejich popisem můžete najít na stránce Funkce aplikace.

Tento text je doplňujícím materiálem pro naše kurzy MS Excel pro pokročilé uživatele a MS Excel pro manažery a ekonomy.