V dnešním videu si ukážeme, co byste nikdy neměli dělat v excelové tabulce. Excelová tabulka je jedním z nejlepších excelových nástrojů, který v Excelu můžete používat. Nicméně u práce s excelovými tabulkami si musíte dát pozor na několik věcí, které si právě dnes projdeme v tomto videu. V předešlém videu na Akademii Excelu jsme si již ukázali deset důvodů, proč excelové tabulky používat, dnes si ukážeme, co byste u práce s excelovými tabulkami dělat nikdy neměli.
Excelový soubor ke stažení:
V příkladu máme tabulku, na které si chyby ukážeme. V současné době zdrojová tabulka není ve formátu excelové tabulky. Nejprve excelovou tabulku musíme vytvořit. Nejjednodušším způsobem, jak excelovou tabulku vytvořit je, že klikneme do tabulky a použijeme klávesovou kombinaci CTRL+T. Potvrdíme, že tabulka má záhlaví a z obyčejného rozsahu dat se stala excelová tabulka. Dobrou praxí je, že excelovou tabulku pojmenujeme. To uděláme tak, že klikneme do tabulky, tím se aktivuje karta Návrh tabulky, kde máme možnost tabulku pojmenovat. Nazveme ji jako Data.
Kombinace datových typů v jednom sloupci
V excelové tabulce platí, že co sloupec, to jedinečný datový typ a jeden výpočet. Nikdy byste v excelové tabulce tedy neměli v rámci jednoho sloupce kombinovat různé datové typy, jako třeba textové, datumové a číselné hodnoty. Co sloupec v tabulce, to jeden datový typ. V tabulce v příkladu máme jeden sloupec pro datumy, jeden pro produkty, jeden sloupec pro typ produktu, další sloupec pro počet kusů a další sloupec pro tržbu. Pokud máme data správě strukturovaná, tak můžeme plně využívat výhod záhlaví excelové tabulky. Můžeme data pomocí záhlaví filtrovat nebo řadit.
V případě, že bychom ve sloupcích měli mix hodnot, tak záhlaví excelové tabulky zobrazí filtr a řazení dle typu hodnot, které ve sloupci převládají. U sloupce v příkladu, kde máme jak datumy, tak text, tak čísla se v řazení zobrazí řazení dle textu, jelikož ten převažuje a ve filtraci vidíme mix hodnot, které nedávají smysl.
Nekonzistentní vzorce
S předchozím bodem souvisí i další chyba a to je používání nekonzistentních vzorců v rámci jednoho sloupce. Příkladem může být výpočet kumulativní sumy ve sloupci. Do tabulky přidáme nový sloupec, ve kterém chceme kumulativně nasčítat tržby. Když v první buňce v excelové tabulce propojíme první tržbu, tak excelová tabulka po potvrzení nakopíruje stejný vzorec na každý řádek.
Chybou by bylo nechat jen první hodnotu, ostatní smazat a na druhém řádku použít jiný vzorec. Pokud to uděláme, takž na druhém řádku sečteme první a druhou hodnotu, tak si všimněte, že funkce už se na ostatní řádky nenakopíruje, jelikož je narušená integrita sloupce. Stáhneme funkci dolů a zdánlivě se zdá, že vše funguje. Nicméně, když do tabulky přidáme nový záznam, tak kumulativní suma nefunguje. A je to proto, že jsme narušili konzistentnost vzorců v jednom sloupci.
Fixace ve sloupci
S předchozím bodem souvisí správná fixace sloupce. Vrátíme se k výpočtu kumulativní sumy a ukážeme si, jak v excelové tabulce provést správně výpočty, kde je potřeba fixace. V excelové tabulce totiž nelze klasicky fixovat buňky. Kumulativní sumu tedy spočítáme tak, že napíšeme funkci SUMA, kde nemůžeme zafixovat první buňku, takže místo toho označíme buňku záhlaví, což je jediná buňka ve excelové tabulce, která je zafixovaná a po označení se nepohne. Dvojtečka a první tržba. Potvrdíme funkci a máme kumulativní sumu v celém sloupci, která bude fungovat i na nově přidaná data do tabulky.
Stejné je to třeba s označením duplicit v excelové tabulce. Pro identifikaci duplicit používáme funkci COUNTIF. Napíšeme funkci COUNTIF, kde označíme buňku v záhlaví, což je jediná buňka, kterou v excelové tabulce můžeme zafixovat. Následuje dvojtečka, první produkt a jako kritérium opět první produkt. Ukončíme funkci a potvrdíme. Teď funkce vrátila správný výsledek.
Výpočty nad tabulkou
Jednou z výhod excelových tabulek je to, že se snadno filtrují. U filtrování tabulek si ale musíte dát pozor na to, že některé funkce jako SUMA, PRŮMĚR, ale i funkce jako SUMIF a COUNTIF, nereagují na filtr v tabulce. Řekněme, že nad excelovou tabulkou chceme spočítat celkový součet tržeb. Obrovskou chybou by bylo použít funkci SUMA. Pokud totiž sloupec Tržba sečteme pomocí funkce SUMA, tak se sice vrátí správný výsledek, ale ve chvíli, kdy tabulku vyfiltrujeme například pro oblečení, tak součet pomocí funkce SUMA zůstane stejný a nepřizpůsobí se filtru v tabulce.
Daleko lepším řešením je použít funkci SUBTOTAL nebo souhrnný řádek. Takže funkci SUMA nahradíme funkci SUBTOTAL, ve které vybereme funkci SUMA a označíme sloupec tržba. Funkce SUBTOTAL bude po vyfiltrování reagovat na filtr v tabulce a bude vracet správné součty. Druhou možností je použít souhrnný řádek v excelové tabulce. V takovém případě klikneme do tabulky a na kartě Návrh tabulky vybereme Řádek souhrnu. Tím se vloží do excelové tabulky řádek souhrnu, ve kterém můžeme zvolit výpočet, který pro daný sloupec chceme provést. Pro sloupec tržba vybereme součet, tím se do spodního řádku vloží celkový součet. Když teď vyfiltrujeme tabulku pro oblečení, tak se výpočet v buňce přizpůsobí filtru.
Odkazy ve vzorcích na excelovou tabulku
Excelová tabulka je skvělým zdrojem pro výpočty. Jakékoliv výpočty, které jsou na excelovou tabulku navázané se totiž sami aktualizují, když přidáte nová data do zdrojové tabulky. Nicméně pokud neznáte trik s kopírováním vzorců navázaných na excelovou tabulku, tak můžete narazit na následující problém. Z excelové tabulky chceme spočítat celkové tržby pro oblečení a obuv. Celkové tržby spočítáme pomocí funkce SUMIFS. Začneme s funkcí SUMIFS pro oblečení. Napíšeme funkci SUMIFS, kde nejprve označíme oblast součtu, což je sloupec s tržbami. Následuje sloupec s kritériem, což je typ produktu, takže označíme sloupec s typem produktu. A následuje kritérium, což je oblečení. Funkci ukončíme a potvrdíme a funkce správně vrátila celkový součet pro oblečení. Když teď ale vzorec chytíme a přetáhneme ho doprava i pro obuv, tak se vrátí nula. A důvodem je to, že se přetažením posunuly sloupce v excelové tabulce. V excelové tabulce ale neexistuje způsob, jak sloupce zafixovat. Takže jak vyřešíme tento problém? Místo přetahování vzorce, který odkazuje na excelovou tabulku, vzorec buď nakopírujeme a nebo použijeme excelovou zkratku. Takže místo přetažení vzorec zkopírujeme a vložíme ho do pole pro obuv. V tomto případě se sloupce neposunou a funkce vrátí správný výsledek. Druhou možností je označit první výpočet, označit zbylé buňky, kam chceme vložit vzorec a použít klávesovou kombinaci CTRL+R. Tím se vzorec rovněž nakopíruje do buňky bez toho, aby došlo k posunutí odkazů na sloupce.
Dynamické funkce
Nové dynamické funkce, které jsou ve většině případů dostupné v Office 2021 a Microsoft 365 nefungují v excelových tabulkách. Pokud tedy v excelové tabulce chcete použít například funkci SEQUENCE, FILTER nebo jakoukoliv jinou dynamickou funkci, tak narazíte na chybovou hlášku. Důvodem je to, že se ve své podstatě jedná o dva dynamické nástroje, které se navzájem bijí. Excelová tabulka automaticky kopíruje vzorec z první do buňky do ostatních buněk v tabulce, což v podstatě blokuje rozlití dynamických funkcí a dojde ke vzniku chybové hlášky. V excelové tabulce proto nikdy tyto nové dynamické funkce nepoužívejte. Daleko lepší je tyto funkce napojit na excelovou tabulku, jelikož pak máte zajištěno, že se jakákoliv nová dat automaticky zahrnou do výpočtů.
Jedna odpověď
😊