V dnešním videu si ukážeme, jak pracovat s oficiálními excelovými tabulkami, jak oficiální excelovou tabulku vytvořit a 10 důvodů, proč byste oficiální excelové tabulky měli používat. Na rozdíl od obyčejných tabulek v Excelu se totiž jedná o nástroj, který vám zrychlí práci, ušetří spoustu času a omezí chybovost. A co je nejlepší? Na rozdíl od nových funkcí, které jsou dostupné jen v nejnovějších Excelech, oficiální excelové tabulky jsou tady s námi již od verze Excelu 2007. Od této verze Excelu totiž máme možnost změnit obyčejnou excelovou tabulku neboli obyčejný list dat na tzv. oficiální excelovou tabulku. Oficiální excelové tabulky mají oproti obyčejným tabulkám několik zásadních výhod, a právě ty si představíme v dnešním videu.
Excelový soubor ke stažení
Oficiální excelové tabulky
Než z nějakých dat v Excelu vytvoříme oficiální excelovou tabulku, tak bychom měli dodržet několik zásad.
- Data by měla být v tabulce organizována do řádků a sloupců, přičemž každý sloupec by měl mít svůj vlastní nadpis.
- Tabulka by tedy měla mít vždy záhlaví, ve kterém se nevyskytují žádné mezery a prázdné buňky
- Každý sloupec tabulky by měl obsahovat pouze jeden datový typ. To znamená, že ve sloupci produkty by měly být pouze názvy produktů, ve sloupci tržby by se měly vyskytovat pouze číselné údaje, ve sloupci datum by naopak měly být pouze datumy atd.
- V datech by se neměly vyskytovat prázdné řádky ani sloupce
- Okolo dat by měl být prázdný minimálně jeden sloupec a řádek, to znamená, že se tabulky nedotýká žádná jiná tabulka nebo data
Když máme data připravená dle těchto zásad, tak můžeme z obyčejné tabulky vytvořit oficiální excelovou tabulku.
Jak vytvořit oficiální excelovou tabulku
Klikneme do tabulky a na kartě Vložení najdeme záložku Tabulka. Klikneme na Tabulku a Excel označí data a zeptá se, zda z nich chceme vytvořit oficiální tabulku. Jelikož naše tabulka obsahuje záhlaví, tak toto pole necháme zaškrtnuté a potvrdíme. Kdybychom toto pole odškrtli, tak by Excel přiřadil každému sloupci obecný název ve formátu Sloupec 1, Sloupec 2, atd. K vytvoření oficiální tabulky můžeme použít i klávesovou zkratku CTRL+T.
Výběr potvrdíme a z obyčejné excelové tabulky se stala oficiální excelová tabulka. To, že máte v Excelu oficiální excelovou tabulku poznáte většinou podle dvou znaků. Oficiální excelové tabulky mají toto charakteristické pruhované formátování, zároveň je konec tabulky označen tímto nenápadným trojúhelníkem v pravém dolním rohu. Druhým znakem je to, že po vytvoření oficiální excelové tabulky se na horní lištu v Excelu přidá nová záložka Návrh tabulky. Z této záložky můžete ovládat nejen zobrazení oficiální excelové tabulky, ale i její formátování a další vlastnosti tabulky.
Formátování oficiální excelové tabulky
Pokud by se vám toto pruhované formátování, které je pro oficiální excelové tabulky typické, nelíbilo, tak na kartě Návrh tabulky máte možnost vybrat Styl tabulky. Zde si můžete vybrat z několika předvolených stylů, můžete si rovněž vytvořit vlastní styl oficiální excelové tabulky a nebo můžete vybrat tuto první možnost bez stylu a tím se tento pruhovaný formát odstraní z tabulky.
Když máme oficiální excelovou tabulku vytvořenou, tak první, co bychom měli vždy udělat je tabulku pojmenovat. Tuto možnost najdeme rovněž na kartě Návrh tabulky. V poli Název tabulky můžeme tabulku pojmenovat, jediné pravidlo je, že název tabulky nesmí obsahovat mezeru. Buďto tedy použijte název bez mezer nebo můžete pro oddělení slov použít například podtržítko. Tuto cvičnou tabulku nazveme třeba ProdejníData.
A jaké jsou tedy výhody oficiálních excelových tabulek?
Záhlaví tabulky
Po vytvoření oficiální excelové tabulky se do záhlaví tabulky vloží automatický filtr. Každá oficiální excelová tabulka má v záhlaví automaticky filtr, který umožňuje tabulku rychle řadit nebo filtrovat, a to dle jednotlivých sloupců. Filtr nad tabulkou můžete vypnout na kartě Návrh tabulky, Možnosti stylů tabulek a odškrtnou toto pole Tlačítko filtru.
Další výhodou oficiálních excelových tabulek u záhlaví je to, že pokud kliknete na jakoukoliv buňku v tabulce, to znamená, že máte aktivně označenou alespoň jednu buňku v tabulce, tak při posunu dolů se záhlaví zmrazí a zůstane viditelné, i když se budete posouvat dolů. Záhlaví tabulky v tomto případě nahradí horní lištu s písmenky a zůstane tak viditelné, což se hodí zejména při práci s rozsáhlými excelovými tabulkami.
Strukturované odkazy
Jednou z výhod oficiálních excelových tabulek je i to, že ve chvíli, kdy se odkazujeme na jakékoliv pole v oficiální excelové tabulce například ve funkci, tak se ve funkci neobjeví klasický odkaz na buňky jako například B1, ale objeví se název sloupce, neboli strukturovaný odkaz. Hlavní výhoda spočívá zejména v lepší čitelnosti funkcí a vzorců. Na první pohled je patrné, s jakým sloupcem se ve funkci počítá. Kdybychom tedy vedle tabulky teď chtěli sečíst sloupec s tržbami, tak napíšeme funkci SUMA, kde označíme sloupec celý s tržbami. Ve chvíli, kdy sloupec označíme celý, tak se odkazy buněk změní na název ProdejníData se hranatou závorkou Tržba. Jedná se o strukturovaný odkaz oficiální excelové tabulky, který má vždy na začátku před hranatou závorkou název tabulky. My jsme tabulku pojmenovali ProdejníData, takže zde máme tento název v hranaté závorce máme uvedený název sloupce ze záhlaví, se kterým se ve funkci nebo vzorci počítá.
Výběr sloupců nebo řádků
S oficiální excelovou tabulkou se všeobecně rychleji pracuje. Použijeme stejný příklad jako v minulém případě. Chceme opět sečíst všechny položky ve sloupci Tržba. Místo toho abychom celý sloupec manuálně označovali, tak jako v předešlém příkladě, tak můžeme pouze kliknout na záhlaví příslušného sloupce, případně řádku. Napíšeme tedy funkci SUMA a najedeme myší na záhlaví sloupce Tržba a počkáme až se z myši stane tato černá šipka. Potom, co se z myši stane šipka, klikneme na záhlaví sloupce a automaticky se ve funkci SUMA objeví celý název sloupce ve formě strukturovaného odkazu. Funkci potvrdíme a máme označený celý sloupec Tržba bez nutnosti manuálního výběru všech buněk. Pokud byste chtěli naopak vybrat celý řádek, tak uděláte to samé, najedete na první buňku v řádku, počkáte až se z kurzoru myši stane šipka a výběr potvrdíte. Velmi rychle můžete rovněž vybrat celou tabulku. Stačí najet myší na první buňku v levém horním rohu tabulky a počkat až se z kurzoru myši stane šikmá šipka. Potvrdíme a tím se vybrala automaticky celá tabulka.
Automatická aktualizace velikosti tabulky
Oficiální excelová tabulka se automaticky rozšíří nebo naopak zmenší po přidání nebo odebrání řádků, případně sloupců. Pokud tedy na konec excelové tabulky přidáme data, tak se tato data automaticky přidají do tabulky. To samé platí i o nových sloupcích. Řekněme, že do tabulky přidáme nový sloupec Marže. Vedle sloupce Náklady tedy napíšeme do záhlaví Marže a potvrdíme. Nově přidaný sloupec Marže automaticky převezme formát a vlastnosti excelové tabulky. Pokud by se vám tabulka automaticky nerozšiřovala, tak je potřeba zkontrolovat nastavení pod záložkou Soubor, Možnosti. Nastavení je schované pod záložkou Kontrola pravopisu a mluvnice. Zde zvolíme Možnosti automatických oprav a Automatické úpravy formátu při psaní. Zde pod Formátování při vkládání musíte mít zaškrtnuté Do tabulky zahrnovat nové řádky a sloupce.
Počítané sloupce
Další výhodou excelových tabulek je práce se vzorci a funkcemi. Zadáním vzorce do jedné buňky ve sloupci oficiální excelové tabulky vytvoříte počítaný sloupec, kde se příslušný vzorec nebo funkce okamžitě doplní do ostatních buněk sloupce. Do první buňky nově přidaného sloupce Marže napíšeme jednoduchý vzorec, kdy mezi sebou odečteme tržby a náklady, abychom získali marži. Ve chvíli, kdy vzorec potvrdíme, tak se automaticky vzorec doplní do celého sloupce, bez nutnosti stahování vzorce dolů. To samé by platilo, pokud bychom ve sloupci uplatnili jakoukoliv funkci. Třeba podmínkovou funkci KDYŽ. Přidáme nový sloupec Podmínka a v něm napíšeme jednoduchou podmínku KDYŽ, a podmínka bude, pokud je marže vyšší než 2 000 Kč, tak chceme vrátit slovo „Nadprůměr“, v opačném případě chceme vrátit „OK“. Potvrdíme a funkce se automaticky propsala na všechny řádky v tabulce.
Automatická aktualizace
Největší výhodou používání oficiálních excelových tabulek je ovšem to, že se jakékoliv vzorce, které pracují s daty z oficiálních excelových tabulek okamžitě aktualizují a přepočítají, dojde-li ke změně v oficiální excelové tabulce. Dejme tomu, že vedle tabulky použijeme funkci SUMIF, kde chceme sečíst všechny tržby na obuvi. Vedle tabulky tedy použijeme funkci SUMIF, kde jako oblast označíme sloupec Druh produktu, opět nemusíme označovat celý sloupec, ale stačí najet na název sloupce, počkat až se z myši stane černá šipka a potvrdit. Jako oblast kritérií označíme slovo obuv a jako oblast součtu označíme sloupec s tržbami. Potvrdíme a máme výsledek.
Pokud bychom teď do tabulky přidali nová data, která máme schovaná na listu Nová data, tak se vzorec SUMIF automaticky přepočítá. A je to proto, že na rozdíl od obyčejných excelových tabulek, kde označujeme rozpětí buněk, oficiální excelové tabulky pracují s názvy sloupců. Takže jakmile do sloupce přibudou nová data, tak je Excel zahrne automaticky do souvisejících výpočtů.
Všimněte si, že nejen, že se automaticky přepočítaly funkce, ale do tabulky jsme přidali nová data pouze po sloupec náklady. V oficiální excelové tabulce se automaticky dopočítaly i sloupce pro Marži a pro podmínku.
Stejná automatická aktualizace funguje i na formát. Nejen, že se aktualizují vzorce a funkce, ale jakmile uplatníte v oficiální excelové tabulce podmíněné formátování na celý sloupec nebo řádek, tak se toto formátování automaticky přenese i na nově přidaná data. Musíte toto formátování ale uplatnit na celý sloupec nebo řádek. Na sloupec marže uplatníme jednoduché podmíněné formátování, a to tak, že označíme záhlaví sloupce a na kartě Data vybereme podmíněné formátování a zde vybereme barevné škály. Pokud teď do tabulky přidáme nová data, tak se podmíněný formát automaticky přenese i na nově přidaná data.
Stejně tak se na každá nová data aplikuje i formát, který jste nastavili pro daný sloupec. Řekněme, že sloupec Tržba naformátujeme jako měnu s třemi desetinnými místy. Klikneme na záhlaví tabulky, označíme sloupec a vybereme formát. Následně přidáme nová data a formát se automaticky přenesl i na nově přidaná data.
Fixace buněk
V oficiálních excelových tabulkách není potřeba fixovat buňky, a je to tím, že oficiální excelová tabulka pracuje s názvy sloupců a nikoliv přímo s odkazy na buňky. To ve spoustě situacích ušetří čas, nicméně to způsobuje i jeden problém. Jednou z mála nevýhod oficiálních excelových tabulek je to, že se funkce v oficiální excelové tabulce špatně kopírují přetahováním. Pokud v oficiální excelové tabulce nebo vedle tabulky vytvoříme funkci a následně bychom tuto funkci chtěli přetažením zkopírovat doprava, tak vzhledem k tomu, že v oficiální excelové tabulce nefunguje fixace, tak můžeme dostat nesprávný výsledek, jelikož se nám sloupce ve funkci posunou spolu s přetažením. Ukážeme si to na následujícím příkladu. Vedle tabulky máme vypsané některé klienty ze sloupce Klienti a chtěli bychom u nich sečíst příslušné marže. Začneme u prvního klienta funkcí SUMIF, kde jako oblast označíme sloupec Klienti, jako kritérium označíme prvního klienta a jako oblast součtu vybereme sloupec s Marží. Funkci potvrdíme a pro prvního klienta funkce funguje správně. Pokud ale funkci chytneme a zkopírujeme ji přetažením doprava pro druhého klienta, tak se vrátí nula. Když na funkci klikneme, zjistíme, že se sloupce ve funkci posunuli zároveň s přetažením funkce. Jak tedy tento problém obejít, když v oficiální excelové tabulce neexistuje fixace sloupců? Jednoduše označte první buňku, kde máte funkci a zároveň s ní označte i ostatní buňky, kde chcete mít stejný vzorec. Teď stačí jednoduše zmáčknout klávesovou kombinaci CTRL+R a funkce se doplní do všech označených buněk, bez toho, aby se sloupce posunuly. Klávesová zkratka CTRL+R totiž slouží ke kopírování obsahu první buňky do označených buněk, což v oficiální excelové tabulce funguje i na funkce.
Druhou možností je jednoduše obsah první buňky zkopírovat pomocí klávesové kombinace CTRL+C, označit buňky, kam chcete funkci vložit a zmáčknout kombinaci CTRL+V pro vložení.
Zdroj pro kontingenční tabulky
Tím, že se oficiální excelové tabulky v podstatě sami aktualizují po přidání nových dat, tak jsou skvělým zdrojem pro kontingenční tabulky. Pokud je zdrojem kontingenční tabulky oficiální excelová tabulka, tak není potřeba po rozšíření tabulky aktualizovat rozsah kontingenční tabulky. Nová data budou po aktualizaci kontingenční tabulky automaticky zahrnutá. Kdybychom teď z tabulky chtěli udělat kontingenční tabulku, tak do tabulky klikneme a na kartě Vložení vybereme Kontingenční tabulka. Jak vidíme zde ve zdroji, tak se díky oficiální excelové tabulce ve zdroji neobjeví rozsah tabulky, ale její jméno.
Možnost průřezů
Od verze Excelu 2013 máme možnost s oficiálními excelovými tabulkami používat i průřezy, na které jsme zvyklí od kontingenčních tabulek. Průřezy usnadňují filtrování, přičemž na jednu tabulku může být navázáno i několik průřezů. Průřez vložíme tak, že klikneme do tabulky a na záložce Návrh tabulky vybereme Vložit průřez. Ve vyskakovacím okně vybereme jaké filtry bychom chtěli vložit. Vložíme průřez na Klienta a Druh produktu. Do sešitu se vloží dva průřezy, které teď ovládají filtrování tabulky.
Řádek celkového součtu
Další výhodou oficiálních excelových tabulek je to, že na kartě Návrh tabulky můžeme pod tabulku lehce vkládat výpočty. Klikneme na kartu Návrh tabulky a vybereme Řádek souhrnů. Pod tabulku se přidal nový řádek Celkem. Pod tržbami se ukázal součet celého sloupce, to ovšem není vše. Na výpočet lze kliknout, což nám rozbalí menu s možnými výpočty. Můžeme si tak vybrat, jaké sumární číslo chceme pod sloupcem v celkovém řádku prezentovat. Na výběr máme z několika různých funkcí, od sumy po průměry až po minimální a maximální hodnoty. Tato možnost samozřejmě funguje zejména se sloupci, ve kterých máme číselné hodnoty. Pod sloupce náklady se na první pohled nepřidal žádný výpočet, když na pole ale najedeme myší, tak si rovněž můžeme vybrat výpočet. Výhodou je, že pro každý sloupec můžeme vybrat odlišný výpočet. Tato celková čísla samozřejmě reagují na filtry a průřezy, v podstatě se jedná o funkci SUBTOTAL. To, že se jedná o funkci SUBTOTAL si můžete ověřit i v příkazovém řádku. Klikneme na jedno z celkových čísel a v příkazovém řádku se objevila funkce SUBTOTAL s vybranou funkcí. V tomto případě jsme klikli na SUMU, takže se ve funkci SUBTOTAL objevila funkce 109. Pokud tedy pomocí průřezu vyfiltrujeme například nějakého klienta, tak se celková čísla v řádku celkem přepočítají dle zvolených filtrů.
Pokud jste pod tabulku přidali řádek celkového součtu a chcete do tabulky přidávat nová data, tak tento řádek nemusíte vypínat. Můžete jednoduše označit poslední buňku v tabulce a zmáčknout klávesu Tabulátoru, čímž se přidá nový řádek do tabulky a řádek celkového součtu se posune dolů. Nebo můžete nová data, která chcete vložit do tabulky vzít, zkopírovat a kliknout na tento název Celkem a data jednoduše vložit pomocí klávesové kombinace CTRL+V. Data se vloží do tabulky a řádek celkového součtu se posune dolů, přičemž se nová data samozřejmě automaticky promítnou ve výpočtech celkového součtového řádku.
Jak zrušit oficiální excelovou tabulku
Nakonec si ještě ukážeme, jak převrátit oficiální excelovou tabulku zpátky na obyčejnou tabulku. K tomu stačí kliknout do tabulky a na kartě Návrh tabulky, nástroje kliknout na Převést na oblast. Tím se z oficiální excelové tabulky stane zase normální oblast, což jednoduše poznáte podle toho, že zmizela záložka Návrh tabulky, zmizel automatický filtr z tabulky a rovněž zmizely strukturované odkazy z funkcí a vzorců a změnily se zase na klasické odkazy na buňky.
Jedna odpověď
Dobrý den, předem děkuji za super videa. Dle doporučení jsem si vytvořil oficiální excelovou tabulku, ale zjistil jsem, že v ní nelze používat zobrazení – vlastní zobrazení, kde jsem měl před nastavené vlastní zobrazení tabulky/uživatelské pohledy (z důvodů velkého množství sloupců). Jak lze prosím vlastní zobrazení řešit u oficiální excelové tabulky?
Příklad: ze 40 sloupců v tab., jsem si před nastavil vlastní zobrazení např. jen 5 ti sloupců.
Děkuji T. Santíno