To nejlepší z Excelu

Excel je velmi mocný nástroj, který usnadní každodenní kancelářskou práci. Když jej uživatel ovládá, ušetří si drahocenný čas, který pak může věnovat důležitějším věcem. Jak jsem za svou mnohaletou praxi na pozici lektora Excelu zjistil, účastníci kurzů mnohdy nevyužijí ani 10% toho, co Excel nabízí. Nejčastěji proto, že o dané funkcionalitě nevědí, a tedy je ani nenapadne, že je něco takového možné. Druhý důvod je ten, že si pro svou práci vystačí s tím, co znají a nemají potřebu se posunout dále.

V online kurzech na UmimExcel.cz se věnuji ucelenému výkladu od začátečnických postupů až po expertní nástroje. V každém kurzu je téma, které se v praxi hodí více a téma, které se zase až tak moc nevyužije. V tomto článku naleznete výběr témat napříč různými online kurzy, které jsou z mého pohledu to nejlepší, co v Excelu máme a co se do praxe opravdu hodí. U každého tématu najdete krátký popis a odkaz na ten online kurz(y) Excelu, ve kterém se téma probírá.

Podmíněné formátování

Nástroj podmíněné formátování pomůže v Excelu automaticky zabarvit buňky s čísly, texty nebo datumy dle zvoleného kritéria. Nemusíte je tak složitě hledat ani filtrovat, ale barevné buňky hned uvidíte. Kromě toho při změně hodnot podmíněné formátování ihned zareaguje přebarvením buněk. Zabarvit jdou jak jednotlivé buňky, tak i celé řádky. Podle barev lze dokonce i filtrovat.

Kurzy obsahující toto téma: Excel pro středně pokročilé, Excel – Formátování tabulek, Excel – Podmíněné nástroje a funkce

Funkce v Excelu

Excel má více než 350 funkcí z různých kategorií. Jsou zde k usnadnění výpočtů v různých ekonomických či jiných situacích a umí kouzla, o kterých se většině uživatelů ani nezdá. To, co bychom pracně počítali, za nás funkce vypočítá v okamžiku.

Kurz obsahující toto téma: Excel vzorce a funkce

Funkce SVYHLEDAT – nejlepší funkce z Excelu

Pokud máte dvě tabulky, které mají nějaký společný údaj (ID objednávky, číslo zákazníka, kód výrobku apod.), k propojení těchto tabulek a dohledání souvislostí je funkce SVYHLEDAT (angl. VLOOKUP). Jedná o jednu z nejpraktičtějších funkcí Excelu vůbec, ale bohužel je zprvu složitá na pochopení a nápověda u ní uvedená příliš nepomůže. Jakmile ji ale pochopíte, už si bez ní nedovedete život v Excelu představit.

=SVYHLEDAT(hledat;tabulka;sloupec;typ)

• hledat … klíčová buňka, pro kterou párujeme informace
• tabulka … druhá tabulka, ze které chceme dohledat údaje
• sloupec … pořadí sloupce v oblasti tabulky, ve kterém je výsledky hledaný údaj
• typ … shoda párovaných údajů, 0 = přesná, 1 = nejbližší menší

Kurzy obsahující toto téma: Excel pro pokročilé, Excel vzorce a funkce

Funkce KDYŽ – pro podmínky

Když pracujeme s hodnotou, která ovlivňuje další rozhodování, a tedy mění obsah buňky dle podmínky, použijeme funkci KDYŽ (angl. IF). Ta vyhodnotí platnost námi definované podmínky a na tomto základě do cílové buňky napíše jednu či druhou výslednou informaci. Např. “když je buňka větší než nula, zobraz slovo kladné, jinak slovo záporné“. Funkce KDYŽ se dá tzv. větvit dalšími KDYŽ pro potřeby většího počtu možností a v nové verzi ji na další úroveň posouvá funkce IFS. Pro kontrolu více podmínek současně používáme funkci A či NEBO, které se vnoří do funkce KDYŽ.

=KDYŽ(podmínka;ano;ne)

• podmínka … to co se má kontrolovat; může zde být porovnání hodnot, vzorec nebo jiná funkce, odkaz na buňku a tato podmínka musí mít logický výsledek PRAVDA nebo NEPRAVDA; tento argument je nutné vyplnit
• ano … co se má jako výsledek této funkce zobrazit v případě, že bude podmínka splněná (tedy když podmínka vychází jako PRAVDA); opět zde může být vzorec, jiná funkce, odkaz na hodnotu buňky nebo textová / číselná hodnota
• ne … stejně jako pro argument ano, ale pro případ nesplnění podmínky (tedy když podmínka vychází jako NEPRAVDA)

Kurzy obsahující toto téma: Excel pro pokročilé, Excel vzorce a funkce, Excel – Podmíněné nástroje a funkce

Kontingenční tabulky – to nejlepší z Excelu

Pro získávání souhrnných údajů z velkých databází je kontingenční tabulka v Excelu to nejlepší. Přehlednou formou sumarizuje hodnoty a umožňuje analyzovat souvislosti mezi daty. Např. pro jednotlivé klienty a období zobrazí celkové tržby. Kromě sumarizací nabízí filtrování hodnot s využitím nových Průřezů, spojení více tabulek dohromady nebo tvorbu kontingenčního grafu. Pokud máte velké tabulky, kontingenční tabulka je jasná volba.

Kurz obsahující toto téma: Excel kontingenční tabulky

Dynamické doplňování – Excel nám čte myšlenky

Pokud neznáte funkce a chcete hromadně upravit texty – spojit, rozdělit, změnit velikost písma, doplnit něco na začátek, vytáhnout část slova apod., stačí využí nástroj Dynamické doplňování (angl. Flash fill), který je v Excelu od verze 2013.  Stačí nastínit, co chcete dělat a Excel za vás dokončí práci. Nemusíte tak již používat některé funkce, jejichž tvorba dříve zabrala spoustu času. Jednoduše, rychle a inteligentně.

Kurz obsahující toto téma: Excel pro středně pokročilé

Ukotvení příček – stále vidíte nadpisy

U větších tabulek v Excelu často dochází k tomu, že se při posouvání myší dolů nebo vpravo skrývají nadpisové řádky či sloupce a my tak ztrácíme přehled o významu zobrazených údajů. Aby k tomu nedocházelo, je možné využít nástroj ukotvení příček.

Kurzy obsahující toto téma: Excel pro středně pokročilé, Excel velké tabulky

Kopírování formátu – dvojklik pro více použití

Také se vám stává, že musíte opakovaně měnit barvu, typ písma nebo ohraničení buněk? Konec otročiny…pro zkopírování vzhledu jedné buňky na druhou použijte nástroj Kopie formátu. Stejný postup lze navíc použít i pro přenesení vzhledu písma ve Wordu, formát objektů v PowerPointu a dalších aplikacích Office. Jedno tlačítko na všechno!

1. Označíme oblast s připraveným formátem.
2. Klikneme na ikonu Domů > Kopírovat formát.
   • pokud klikneme jednou, kopírování se provede jen jednou
   • pokud použijeme dvojklik, aktivuje se trvalé kopírování formátu i na více míst, dokud jej neukončíme klávesou Escape
3. Označíme cílovou oblast, na kterou chceme formát zkopírovat.

Kurz obsahující toto téma: Excel pro začátečníky

Chytrá tabulka – nápomocná v mnoha směrech

Chytrou tabulku vytvoříme na kartě Vloženi > Tabulka nebo Domů > Formátovat jako tabulku

Pokud v tabulce máme myš aktivuje se na konci Pásu karet karta Návrh. Obsahuje ulehčující pomůcky pro efektivní práci s tabulkou. Můžeme si popsat některé z nich:

• Styly tabulky – umožňuje rychle měnit grafický styl (formát) celé tabulky najednou a navíc hezky střídá barvy v lichých a sudých řádcích, což lze měnit volbou Pruhované řádky resp. Pruhované sloupce.
• Řádek souhrnů – aktivuje na konci tabulky řádek s názvem Celkem. V každé buňce tohoto řádku si uživatelé mohou vybrat typ souhrnu, který bude počítán z daného sloupce hodnot. Tento souhrn je počítán jen z vyfiltrovaných položek, které si snadno zvolíme rovněž aktivovaným automatickým filtrem.
• Odebrat duplicity – odstraní duplicitní (opakující se) řádky v tabulce, konec s pracným ručním mazáním!
• Převést na rozsah – převede tabulku zpět do běžné formy, ale zachová formátování.

Dále chytrá tabulka automaticky zmrazí nadpisový řádek spolu s filtrem v horní části tabulky, vidíme jej tedy i když se posouváme níže.

Kurz obsahující toto téma: Excel pro začátečníky

PowerQuery – nejlepší pro transformaci dat

V nejnovějších verzích Excelu se setkáváme s nástroji pracujícími s velkými objemy dat, říkáme je Power nástroje. Slouží pro analýzu dat, jejich souvislostí, transformaci a vizualizaci. Někdy je také označujeme jako nástroje pro Business Intelligence (viz Power BI). Z mého pohledu je PowerQuery to nejlepší z Excelu, co mezi těmito nástroji je. Určitě by zaslouží zvláštní kurz. Jeho stručné představení a využití v jednom konkrétním případě (tzv. metoda Unpivot) je v online kurzu Excel pro experty spolu s dalšími Power nástroji.