Dnešní video Wildcards v Excelu vám změní život. Alespoň mě se změnil potom, co jsem objevila wildcards. V Excelu totiž většina funkcí potřebuje přesné shody názvů, ať už u vyhledávání, SUMIF nebo COUNTIF. Což může být často problém. A pak jsem objevila wildcards v Excelu. Tedy znaky, které vám dovolují provádět ve funkcích pouze částečné shody. A to nejlepší? Stačí vám deset minut a naučíte se, jak ušetřit hodiny práce v Excelu.
Excelový soubor ke stažení
Wildcards v Excelu
Zástupné speciální znaky neboli wildcards vám dovolují v Excelu provádět pouze částečnou textovou shodu. V angličtině je pro tyto speciální znaky zažitý pojen wildcards, což by v doslovném překladu znamenalo divoké znaky nebo karty. V češtině pro ně není žádný speciální název, daly by se možná klasifikovat jako zástupné znaky. A oč že to vlastně jde? Jedná se o speciální znaky jako je ?, * nebo vlnovka, které vám v Excelu pomohou s částečnou textovou shodou.
Ne všechny funkce použití wildcards v Excelu podporují. Naštěstí ty nejvíce používané funkce jako jsou SVYHLEDAT, POZVYHLEDAT, SUMIF, COUNTIF nebo AVERAGEIF použití wildcards v Excelu podporují.
Použití wildcards v Excelu si ukážeme na následujících příkladech.
Hvězdička (*)
Začneme s nejjednodušším znakem, kterým je hvězdička. Hvězdička zastupuje jakýkoliv počet znaků, tedy nula znaků až nekonečně mnoho znaků. A může se jednat jak o text, tak o číselné znaky.
Řekněme, že v našem prvním příkladu chceme spočítat počet produktů, které začínají slovem polo. Jak vidíme z tabulky, tak takové produkty máme v tabulce celkem dvakrát a vždy se jedná o polobotky. Zkusíme tedy produkty spočítat za pomoci funkce COUNTIF, kde jako oblast označíme oblast, kde slovo polo hledáme (A2:A10). Kritérium je slovo, které hledáme, což je slovo polo. Důvod, proč potřebujeme použít wildcard hvězdičku je to, že si třeba nejsme jistí, jak jsou polobotky v tabulce uvedené, nebo kolik různých názvů produktů s textem polo v tabulce je.
Víme ale, že nás zajímají produkty, které slovem polo začínají. Pokud bychom jako kritérium použili pouze slovo polo a funkci potvrdili, tak se vrátí nula. Jelikož funkce COUNTIF i SUMIF hledají vždy přesnou shodu.
Parametr kritéria tedy musíme upravit. K tomuto účelu využijeme wildcard hvězdičku, která značí jakýkoliv počet znaků. Napíšeme tedy slovo polo a za něj napíšeme hvězdičku, celé kritérium i s touto hvězdičkou uvedeme do uvozovek, jelikož se jedná o textovou hodnotu. Celý tento zápis s hvězdičkou znamená, že chceme, aby funkce COUNTIF použila jako kritérium jakýkoliv text, který začíná slovem polo, nicméně za slovem polo může následovat nekonečně mnoho znaků. Potvrdíme a vrátila se hodnota 2. Což je správně, jelikož v tabulce máme dvě slova začínající slovem polo.
Všimněte si, že díky hvězdičce našla funkce jako slovo polobotky, tak polobotka.
Stejně bychom hledali slovo, které končí slovem polo. Použijeme funkci COUNTIF, otevřeme závorku, označíme oblast hodnot, kde slovo polo hledáme (A2:A10) a jako kritérium hledáme znovu slovo polo, nicméně teď může být libovolný počet znaků před slovem polo, takže hvězdičku umístíme přes slovo polo a nezapomene to celé uvést do uvozovek. Konec závorky, potvrdíme a vrátila se hodnota 1, jelikož v tabulce máme pouze tričko polo, které vyhovuje zadání.
Poslední variantou je, že hledáte slovo polo a nevíte, zda tímto slovem produkt začíná nebo končí. V takovém případě využijete dvě hvězdičky. Zápis by vypadal takto, COUNTIF, závorka, oblast hodnot (A2:A10) a jako kritérium slovo polo, a jelikož toto slovo může být kdekoliv, tak hvězdičku napíšeme před i po slovo polo a zase nezapomeneme na uvozovky. Potvrdíme a vrátila se hodnota 3. Funkce tak spočítala každé slovo, kde se polo objevilo.
Otazník (?)
Dalším znakem wildcard v Excelu je otazník, který zastupuje právě jeden znak. Hlavním rozdílem od hvězdičky je to, že otazník zastupuje právě jeden znak, kdežto hvězdička značí jakýkoliv počet znaků. Dva otazníky pak značí dva znaky atd.
V následujícím příkladu chceme spočítat počet faktur, které mají před pomlčkou pouze jeden znak, a to buď číslo nebo text. Použijeme opět funkci COUNTIF a jako oblast označíme hodnoty (A14:A22) a jako kritérium tentokrát v uvozovkách napíšeme “?-*”. Otazník značí jeden znak před pomlčkou, následuje pomlčka a hvězdička je zde pro, protože za pomlčkou může následovat nekonečně mnoho znaků.
V dalším příkladu potřebujeme spočítat faktury, které mají dva znaky před pomlčkou, pomlčku a tři znaky za pomlčkou. Použijeme tedy znovu funkci COUNTIF, označíme oblast buněk (A14:A22) a jako kritérium tentokrát použijeme otazníky, jelikož specificky hledáme faktury, které mají dva a tři znaky před a po pomlčce. Kritérium tedy bude „??-???“. Faktury s takovouto strukturou máme v tabulce dvě.
Pokud bychom otazníky nahradili hvězdičkou, tak by funkce vypadala COUNTIF, oblast buněk, „*-*“. Konec závorky, enter. Vrátila se hodnota devět, což je počet faktur v tabulce, jelikož *-* znamená, že funkce COUNTIF sečte jakékoliv faktury, které mají před a za pomlčkou jakýkoliv počet znaků, což jsou vlastně všechny faktury v tabulce.
Co když budeme chtít spočítat faktury, které začínají číslem deset? Napíšeme funkci COUNTIF, označíme oblast buněk (A14:A22) a jako kritérium v uvozovkách napíšeme 10, hvězdičku a konec uvozovek. Potvrdíme a vrátil se počet faktur, které začínají číslem 10.
Kombinace wildcards v Excelu
Wildcards hvězdičku a otazník můžeme rovněž kombinovat. V dalším příkladu máme v tabulce několik produktů a rádi bychom spočítali, kolik produktů zde máme uvedeno. A jelikož potřebujeme sečíst počet textových hodnot, tak k tomu využijeme funkci POČET2, což je funkce, která umí sčítat textové hodnoty.
Napíšeme tedy funkci POČET2 a označíme tabulku (A26:A35). Funkce POČET2 vrátila hodnotu 10, v tabulce by tedy mělo být deset produktů. Pokud to ale zkontrolujeme, tak zjistíme, že v tabulce je uvedeno pouze 9 produktů. V desátém řádku (A35) sice není uvedený produkt, ale máme zde znak pro nic. V Excelu se jako znak nic označuje dvěma uvozovkami. I přesto, že to vizuálně vypadá, že je buňka prázdná, tak Excel považuje tento znak pro nic za neprázdnou buňku. Pro splnění úkolu tedy využijeme místo funkce POČET2 funkci COUNTIF.
Napíšeme funkci COUNTIF a označíme celou oblast buněk (A26:A35), jako kritérium použijeme wildcards, jelikož se jedná o textovou hodnotu, tak i wildcards uvádíme do uvozovek a napíšeme “?*”.
Wildcard, kterou jsme použili “?*” znamená jakýkoliv text. Otazník pro jeden znak znamená, že v buňce by měl být alespoň jeden znak, a hvězdička znamená, jakýkoliv počet znaků, tedy, že v buňce může být od jednoho do nekonečně mnoho znaků.
Řekněme, že ve druhém příkladu máte ve sloupci kombinaci textu a čísel. Potřebujete spočítat, kolik neprázdných buněk zde máte vyplněno. Tedy potřebujete, aby nějaká funkce spočítala jak textové hodnoty, tak číselné. Pokud byste opět použili pouze funkci POČET2, jako v buňce E38, tak se vám může stát, že započítáte i prázdné buňky (A47), kde se vyskytuje nějaký podobný znak, což by vám dalo nesprávný výsledek.
Použijeme tedy zase výpočet s wildcards. Začneme stejně jako v prvním příkladu. Funkce COUNTIF, označíme oblast buněk (A38:A47), a jako kritérium uvozovky a v nich wildcards znaky pro jakýkoliv text, tedy “?*”. Teď nám funkce COUNTIF počítá pouze textové hodnoty. Následně k této funkci jednoduše ještě přičteme funkci POČET, kde označíme celou oblast. Funkce POČET spočítá pouze počet číselných hodnot a ignoruje hodnoty textové.
Vlnovka (~)
Posledním znakem wildcard v Excelu je vlnovka neboli znak tilde. Vlnovka mění druhé dva znaky wildcard hvězdičku a otazník na normální znaky. Vlnovka se vám tedy bude hodit ve chvíli, kdy potřebujete vyhledat jako součást textu samotnou hvězdičku nebo otazník. Excel by totiž nepochopil, že najednou tyto symboly nepředstavují wildcards, ale že jsou to ve skutečnosti symboly, které chcete vyhledat jako součást textu. Pokaždé, když tedy nechceme, aby byla hvězdička nebo otazník brána jako wildcards, tak před ně jednoduše umístíme vlnovku.
Vlnovka se na klávesnici napíše pomocí klávesové zkratky pravý ALT+1.
V prvním příkladu hledáme čísla faktur, která končí hvězdičkou. Zápis provedeme takto. Funkce COUNTIF, oblast hodnot (A51:A59) a jako kritérium uvozovky a v nich vlnovku a hvězdičku, vlnovka v tomto případě dělá z hvězdičky prostou hvězdičku a jelikož před hvězdičkou může být libovolný počet znaků tak ještě před vlnovku musíme napsat hvězdičku “*~*”.
Tento zápis tedy znamená, že hledáme fakturu, která končí znakem hvězdička, ale před hvězdičkou může být libovolný počet znaků. Potvrdíme a vrátila se hodnota 1, jelikož takovou fakturu máme v tabulce pouze jednou.
V posledním příkladu hledáme faktury, které mají kdekoliv ve svém čísle otazník. Opět využijeme funkci COUNTIF a jako oblast označíme hodnoty (A51:A59) a jako kritérium uvozovky, hvězdičku, vlnovku, otazník a další hvězdičku, konec uvozovek “*~?*”. Tento zápis vyjadřuje, že chceme spočítat faktury, které mohou mít před otazníkem, ale i za otazníkem neznámý počet znaků. A vlnovka je zde proto, aby změnila otazník na prostý znak.
Různé zápisy wildcards v Excelu
Existují dva zápisy wildcards:
- První způsob je, že do wildcard napíšete přímo část textu, nebudete se tedy odkazovat na buňku, ale část slova napíšeme přímo do vzorce.
- Druhý způsob zápisu je s odkazem na buňku. V takovém případě je zápis trochu odlišný. Porovnání zápisů si ukážeme na příkladu.
Řekněme, že chceme spočítat faktury, které začínají písmeny AAA.
V prvním zápisu použijeme funkci COUNTIF, označíme oblast hodnot (A63:A71) a jako kritérium napíšeme hledaný text přímo do kritéria, tedy “AAA*”. Ve druhém zápisu napíšeme funkci COUNTIF, označíme oblast hodnot (A63:A71) a jako kritérium označíme buňku, kde máme uvedené slovo AAA (D64), ampersand (&) a v uvozovkách hvězdičku, konec uvozovek a konec závorku. Oba dva způsoby zápisu jsou správně, takže je jen na vás, který si vyberete.
Pokud vás práce s wildcards zajímají, tak se podívejte na navazující bonusové video – Wildcards v Excelu v praktických příkladech.
