Excelové tabulky patří mezi nejpraktičtější nástroje v Excelu, a přesto je mnoho uživatelů Excelu vůbec nepoužívá. V tomto videu si ukážeme, proč byste měli s excelovými tabulkami pracovat a jaké výhody vám to přinese. A abychom byli objektivní, tak se podíváme i na příklady, kdy naopak použití excelové tabulky vhodné není.
Excelový soubor ke stažení:
Představte si, že máte v Excelu následující hodnoty. Datum, produkt a tržbu. Tento seznam hodnot může v Excelu existovat v podstatě ve dvou podobách. První možnost je obyčejný rozsah dat. To je situace, kterou máme teď. Excel teď tyto buňky v podstatě vnímá jako samostatné buňky, které jsou vyplněné nějakými hodnotami. Když nad sloupec tržba vložíme součet tržeb, tak se vloží suma hodnot. Když teď ale přidáme další řádek hodnot, tak se SUMA nezmění, jelikož Excel nepoznal, že tento nový řádek se má zahrnout do součtu. U výpočtu se nám maximálně zobrazí zelený trojúhelník, čímž se nás Excel snaží upozornit na nekonzistentní data, tedy, že nemáme některé hodnoty zahrnuté v součtu, ale to je celé. Nově přidaná hodnota se do součtu sama od sebe nezahrne. Excel bere tuto hodnotu prostě jako další řádek, který nemusí mít nic společného s původním rozsahem hodnot.
A pak je druhý způsob, jak tuto tabulku zobrazit. A to je použít speciální objekt, kterému se v Excelu říká excelová tabulka. Excelová tabulka vznikne tak, že klikneme do rozsahu dat a buď použijeme klávesovou kombinaci CTRL+T nebo na kartě Vložení vybereme Tabulka. U vzniku tabulku musíme Excelu říct, zda naše tabulka obsahuje záhlaví. Potvrdíme, že tabulka záhlaví obsahuje, a to je celé. Tabulka se změnila z obyčejného rozsahu dat na excelovou tabulku. Excelová tabulka má svůj speciální formát, kterým je toto typické proužkování. Na první pohled, až na toto formátování vypadá rozsah dat stejně. Ale jedná se teď spíše o databázi než o rozsah dat. Excel tyto buňky teď chápe jako spojené do excelové tabulky a ví, že tyto hodnoty všechny patří k sobě. Kromě tohoto typického proužkování se do záhlaví tabulky rovněž automaticky vloží filtr. Opět nad sloupcem tržba sečteme tržby. A teď přidáme nový produkt a hned po přidání se hodnota zahrne do součtu nad sloupcem. Proč? Protože Excel teď tato data chápe jako data, která patří k sobě, jinými slovy tvoří excelovou tabulku.
Konec excelové tabulky poznáme podle tohoto trojúhelníku na pravém spodním rohu. Ten značí, kde končí excelová tabulka. To že se data zahrnou do tabulky poznáte podle toho, že se trojúhelník posouvá dolů a pokud máte na excelové tabulce formátování, tak se na nový řádek přenese i formátování. Pokud do excelové tabulky zahrneme nový řádek nebo sloupec, tak se tento sloupec nebo řádek okamžitě zahrnou do tabulky a do všech souvisejících výpočtů.
Pokud si někdy nebudete jistí, zda pracujete s excelovou tabulkou, tak to poznáte nejjednodušeji podle toho, že do rozsahu dat kliknete a pokud se vám na horní liště zobrazí záložka Návrh tabulky, tak víte, že pracujete s excelovou tabulkou.
Když už jsme u lišty návrh tabulky, tak na rozdíl od obyčejného rozsahu dat můžete excelovou tabulku pojmenovat, což vřele doporučuji, jelikož název tabulky se následně zobrazuje ve vzorcích, což činní vzorce a výpočty přehlednější. V základu se tabulka vždy pojmenuje obecně jako Tabulka 1, Tabulka 2 atd. Excelovou tabulku pojmenovávejte jednoduchými názvy, název nesmí obsahovat mezeru, ale můžete používat podtržítka nebo kombinaci malých a velkých písmen. Nazveme naši tabulku třeba jako ZdrojDat.
Ještě, než se dostaneme k důvodům, kdy a proč používat excelové tabulky. Tak se vrátíme k zápisu ve funkci SUMA. Excelové tabulky mají totiž speciální zápis, na který si začátečníci v Excelu musejí většinou zvyknout.
Ve funkci SUMA nemáme klasický rozsah buněk od do, jako to máme u první tabulky, ale u excelové tabulky se vždy nejprve zobrazí název tabulky a pak v hranatých závorkách název sloupce, se kterým se ve funkci pracuje. Právě kvůli tomuto syntaxu se nová data zahrnou automaticky do výpočtů. Jelikož se zde nepracuje s přesně daným rozsahem dat, jako je A1 až A10, ale pracuje se s celým sloupce Tržba. K zápisu ve vzorcích se ještě za chvíli vrátíme.
A jaké jsou tedy hlavní výhody používání excelových tabulek?
Přepneme se na list Výpočty a ukážeme si první výhodu. První výhodou je, že se excelové tabulky automaticky rozšiřují. Na listu Výpočty máme obyčejný rozsah dat, který nejprve změníme na excelovou tabulku. A tabulku nezapomene pojmenovat.
Řekněme, že chceme spočítat, jaké byly celkové tržby pro kalhoty. K výpočtu použijeme funkci SUMIFS. Další výhodu excelových tabulek poznáte právě při práci s funkcemi. Jelikož se vždy ve výpočtech pracuje s celým sloupcem, tak ve výpočtech nemusíte označovat celý sloupec ručně, ale stačí kliknout na záhlaví a tím se automaticky celý sloupec zahrne do výpočtu. Stačí najet na záhlaví sloupce, počkat až se z kurzoru myši stane černá šipka směřující dolů a potvrdit. Tím se vybere celý sloupec. Takže pokud vytváříte hodně funkcí u dlouhých tabulek, tak je práce s excelovou tabulkou určitě rychlejší. Potvrdíme funkci a funkce doručila správný výsledek. Zkusíme do tabulky přidat nový záznam pro kalhoty a jelikož se tento řádek automaticky zahrne do excelové tabulky, tak automaticky dojde i k přepočítání součtu. To znamená, že když navážeme vzorce a funkce na excelovou tabulku, tak se již nemusíme bát, že nová data zapomeneme zahrnout do výpočtů.
Další výhodou, ale někdy i nevýhodou excelové tabulky je to, že sloupce v tabulce fungují jako celek. Řekněme, že v naší tabulce chceme dopočítat cenu produktu, což není nic jiného než vydělená tržba počtem kusů. Da tabulky přidáme sloupec a to tak, že prostě napíšeme název sloupce a jelikož sloupec sousedí s excelovou tabulkou, tak bude automaticky zahrnutý do tabulky. Klikneme do první buňky a napíšeme vzorec, všimněte si opět speciálního zápisu. Po potvrzení se vzorec okamžitě propíše na všechny řádky. Nemusíte vzorec kopírovat ani stahovat dolů. Jelikož sloupce v excelové tabulce u vzorců fungují jako celek, tak se vzorec, který napíšete do první buňky automaticky přenese do dalších řádků. Zkusíme přidat další řádek a vidíme, že se vzorec ze sloupce počet okamžitě dopočítal.
Samozřejmě někdy se jedná ale o nevýhodu. A to zejména ve chvíli, kdy ve sloupci potřebujete udělat ad hoc úpravy. Řekněme, že z nějakého důvodu potřebujete u jednoho produktu změnit výpočet ceny a ponížit cenu o 5 %. Samozřejmě by vás mohlo napadnout kliknout do buňky a vzorec upravit. Vaším záměrem je upravit jen tuto jednu buňku, ale když to potvrdíte, tak se upraví celý sloupec. A je to právě proto, že sloupec funguje jako celek. Tento příklad byl samozřejmě spíše ilustrační, jelikož byste takovéto úpravy, kdy ve sloupci upravíte vzorec jen na některých řádcích, nikdy neměli dělat.
Za mě je největší výhodou to, kromě automatického zahrnutí dat, to, že excelové tabulky jsou nejlepším zdrojem pro kontingenční tabulky, jako zdroj pro Power Query, excelové grafy nebo rozbalovací seznamy. Klikneme do naší excelové tabulky a vytvoříme z ní jednoduchou kontingenční tabulku. Vytvoříme kontingenční tabulku. Stejně jako výpočty funkcemi i kontingenční tabulka se aktualizuje o nová data, která do tabulky přidáme nebo je změníme, případně smažeme.
Samozřejmě, že kontingenční
tabulku můžete vytvořit i z obyčejného rozsahu dat, ale ztratíte tu
vlastnost, že se nově přidané hodnoty přidají do kontingenční tabulky.
Pokud bychom tuto tabulku chtěli
nahrát do Power Query a dělat na ní úpravy, tak by rovněž musela být ve formátu
excelové tabulky.
Skvělé jsou excelové tabulky třeba i s grafy. Tím, že se tabulka rozšiřuje, tak se samozřejmě nová data zahrnou i do excelového grafu, který máme třeba napojený na excelovou tabulku.
Kdy tedy excelovou tabulku používat? Kdykoliv kdy v Excelu vytváříte třeba databáze. Je mi jasné, že se teď snese vlna kritiky, kdy mi v komentářích lidé budou psát, že Excel není vhodný produkt pro databáze. To sice může být pravda, ale reality je, že lidé Excel jako databázi používají. Evidují v ní klienty, tržby, ceníky atd. Kdykoliv tedy pracujete s takovouto tabulkou, je lepší ji změnit na excelovou tabulku. Zajistíte si, že se nově přidané řádky zahrnou do výpočtů, kontingenčních tabulek a zdroje Power Query.
Skvělé u práce s dynamickými
funkcemi.
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.
Samozřejmě excelové tabulky mohou mít i nevýhody.
Excelová tabulka není vhodná, pokud potřebujete vytvořit nějaký speciální, netabulkový formát dat. Třeba z nějakého důvodu potřebujete mít v tabulce mezisoučty. Nebo v tabulce kvůli prezentaci potřebujete prázdné řádky nebo sloupce. Nebo potřebujete mít měsíce v tabulce vedle sebe. Ani jedna z těchto ukázek není správný formát pro tabulku, jelikož ve správném tabulkovém formátu by byly měsíce v jednom samostatném sloupci a tabulka by byla spíše delší než širší. Stejně tak mezisoučty nejsou formát, pro který je vhodná excelová tabulka. Jelikož mezisoučty narušují strukturu tabulky. Pokud takovýto typ tabulky potřebujete vytvořit třeba pro účely reportingu, tak je lepší použít buď kontingenční tabulky nebo tuto tabulku vytvořit jako obyčejný rozsah dat.
Pro začátečníky a lidi, kteří nejsou zvyklí pracovat s excelovými tabulkami, může být nevýhoda speciální syntax u excelových tabulek, na který si musíte zvyknout. Tento speciální syntax obsahuje třeba křížky nebo zavináče, což značí vždy něco jiného. Když označujeme sloupce, je sloupec uvedený v hranatých závorkách. Když chceme počítat pouze s jedním řádkem, tak je zase před sloupcem zavináč. Když budeme označovat i záhlaví, tak se zase v zápisu objeví křížek atd. Stačí si porovnat následující
zápisy. Řekněme, že chceme označit sloupce Produkt až Tržba včetně záhlaví. Zatímco,
když v obyčejném rozsahu dat označíme data a zápis je buňka od do. To je
zápis, na který je každý, kdo jen jednou dělal v Excelu zvyklý. Ale když
toto uděláme v excelové tabulce, tak bude zápis o něco složitější, což
samozřejmě může někoho odrazovat od používání excelových tabulek.
Možnost tohoto zápisu s názvy tabulek můžeme vypnout. Stačí vybrat Soubor, Možnosti, Vzorce a zde vypnout Používat názvy tabulek ve vzorcích. Pak se i u označování excelových tabulek bude zobrazovat klasický zápis místo speciálního syntaxu.
Excelové tabulky stejně tak nepracují dobře s novými dynamickými funkcemi. Respektive, fungují a nefungují. Pokud navážete dynamické funkce na excelovou tabulku, tak vše funguje skvěle. To je příklad, který jsme si ukazovali před chvílí s funkcemi UNIQUE a FILTER. To funguje skvěle. Ale, pokud byste ale chtěli použít některou z nových dynamických funkcí v excelové tabulce, tak s velkou pravděpodobností narazíte na chybovou hlášku #Přesah_dat. A je to proto, protože dynamické funkce se neumí rozlít uvnitř excelové tabulky. Velmi jednoduše řečeno, v podstatě se jedná o dva dynamické nástroje, které se oba rozpínají podle potřeby a tím se navzájem ruší.
Ale zase záleží, jak dynamickou funkci použijete. Řekněme, že chceme vyfiltrovat do řádku všechny hodnoty k produktu. Pokud použijeme funkci FILTER, tak se vrátí chybová hláška Přesah, jelikož funkce FILTER se chce rozlít na více řádků a zároveň excelová tabulka chce funkci FILTER posunout na všechny další řádky a tím se vlastně funkce ruší. Ale když funkci FILTER zabalíme do funkce TEXTJOIN, tak se následně do jedné buňky vrátí všechny hodnoty. Takže samozřejmě záleží, co je vaším cílem a jak s excelovou tabulkou pracujete.
Stejně tak nepoužívejte excelovou tabulku, pokud potřebujete dělat ad hoc výpočty, kdy na každém řádku je jiný výpočet nebo koeficient atd. Jak jsme si již ukázali, sloupce v excelové tabulce fungují jako celek a pokud uděláte úpravy a nevšimnete si toho, tak se vám tento výpočet automaticky propíše do celého sloupce.
Excelové tabulky rovněž nefungují dobře na zamčených listech, a to i za předpokladu, že odemknete buňky pod tabulkou. Řekněme, že nechcete, aby někdo zasahoval do vaší tabulky, ale zároveň chcete, aby do tabulky doplnil nová data. Takže zamkneme list, ale ještě předtím odemkneme buňky pod tabulkou. Možná se domníváte, že teď budou moci lidé přidat data do tabulky. Ale bohužel… Sice data do odemčených buněk můžete zapsat, ale jak si můžete všimnout, na data se nepřenesl ani formát ani se neposunul trojúhelník, takže data nejsou oficiálně zahrnutá do excelové tabulky.
