V dnešním videu navážeme na předchozí video o nejčastějších problémech s kontingenčními tabulkami. V předešlém videu jsme si ukázali, jak vyřešit problémy s aktualizací dat, seskupením hodnot, ztrátou zdrojových dat nebo překrytím kontingenčních tabulek. V dnešním videu navážeme na toto video a ukážeme si další problémy s kontingenčními tabulkami a jak je vyřešit.
Více videí o kontingenčních tabulkách – Vše co potřebujete vědět o kontingenčních tabulkách, zobrazení hodnot v kontingenční tabulce, kontingenční tabulky k pohovoru, 10 triků s kontingenčními tabulkami.
Excelový soubor ke stažení:
Ztráta formátování
V kontingenční tabulce můžete bojovat s neustálou ztrátou formátu čísel. Většinou se jedná o případ, kdy v tabulce označíme hodnoty a k formátování použijeme horní lištu na kartě Domů. V tabulce máme tržby, takže vybereme českou měnu bez desetinných míst. Když teď vyhodíme z řádků pobočky a dáme na řádky produkt, tak se formát tržeb vrátil opět k obecnému číslu. Abyste neztráceli neustále formát, tak použijte formát čísla. Klikneme pravým tlačítkem myši a vybereme místo formátu buněk formát čísla. Vybereme českou měnu bez desetinných míst a potvrdíme. Zkusíme změnit položky na řádku a vyhodíme produkt a dáme tam zpátky pobočky. A teď již formát tržeb zůstane.
Neustálá změna šířky sloupců
Jendou z otravných vlastností kontingenční tabulky je to, že se po aktualizaci v základním nastavení šířka sloupců neustále přizpůsobuje obsahu ve sloupci. Toto nastavení můžeme vypnout, když na kontingenční tabulku klikneme pravým tlačítkem a vybereme Možnosti kontingenční tabulky. Zde odškrtneme Při aktualizaci automaticky upravit šířky sloupců. Potvrdíme nastavení a teď po aktualizaci již sloupce zůstanou v šířce, v jaké jsme je nastavili.
Zaokrouhlování času
V dalším příkladu máme ve zdrojové tabulce závodní časy pro jednotlivé kategorie. V kontingenční tabulce chceme zobrazit pro každou kategorii nejlepší čas. Vytvoříme kontingenční tabulku a vložíme ji na nový list. Na řádky vložíme kategorie a do hodnot časy, kontingenční tabulka automaticky zobrazí počet časů. Klikneme na časy pravým tlačítkem myši a vybereme Nastavení polí hodnot, kde vybereme Minimum. A zde nastává problém. Časy se za desetinnou čárkou zaokrouhlily na nuly. Tento zaokrouhlovací problém lze obejít pomocí pomocného sloupce ve zdrojové tabulce. Do zdrojové tabulky přidáme nový sloupec, který nazveme Čas KT jako kontingenční tabulka. V tomto sloupci se pouze odkážeme na čas v původním sloupci a formát buněk necháme v obecném formátu. To je klíčové, jelikož chybu v zaokrouhlování způsobuje právě formát času, který máme na původním sloupci. Aktualizujeme propojení, aby se nový sloupec načetl do tabulky. Nový čas vložíme do tabulky a opět vybereme, že chceme zobrazit Minimální hodnotu. Teď na čas uplatníme vybraný formát času. A problém je vyřešený.
Jedinečné hodnoty
Mezi základními výpočty, které v kontingenční tabulce dostaneme na výběr je počet hodnot, nikoliv ale počet jedinečných hodnot. Co když ale pomocí kontingenční tabulky chceme například zjistit, kolik produktů máme v jednotlivých kategoriích produktů? První řešení je, že si jedinečné položky dopočítáte v pomocném sloupci ve zdrojové tabulce a nebo použijete trik s datovým modelem. Klikneme do tabulky a vytvoříme kontingenční tabulku, než ji ale potvrdíme, tak dole zaškrtneme, přidat do datového modelu. Potvrdíme a pole se zobrazila trochu jinak, jelikož se tabulka vložila do datového modelu.
Nicméně pracovat s kontingenční tabulkou můžeme pořád stejně. Na řádky vložíme druh produktu a do pole hodnot vložíme pole produkt. Podle očekávání došlo k sečtení všech produktů ve zdrojové tabulce. Klikneme pravým tlačítkem myši a vybereme Nastavení polí hodnot, kde vybereme Jednoznačný počet. Potvrdíme a teď máme jedinečný počet produktů pro jednotlivé kategorie.
Duplicity
Někdy vložíte pole na řádky v kontingenční tabulce, očekáváte jedinečný seznam hodnot a najednou se vám na řádcích objeví zdánlivé duplicity. V tomto případě je vždy chyba ve zdrojových datech. Nejčastější chybou je, že máte u hodnot nadbytečné mezery, které se chovají jako nový znak a tím pádem vytvářejí zdánlivou duplicitu. Jediným řešením je vrátit se ke zdrojovým datům, chybu odhalit a data vyčistit. Následně se vraťte ke kontingenční tabulce a obnovte spojení.
Nelze seskupit datumy
V příkladu máme kontingenční tabulky, kde na řádcích máme datumy a ve sloupci tržby. Tabulku chceme seskupit do kvartálů. Když ovšem na datumy klikneme pravým tlačítkem myši a vybereme Seskupit, tak na nás vyskočí upozornění, že dané pole seskupit nelze. Důvodem je to, že kontingenční tabulka vnímá datumy jako text, nikoliv jako číslo. To znamená, že buď máte zdrojová data datumů naformátovaná jako text nebo je mezi datumy chybná hodnota, která způsobuje, že celý sloupec datumů považuje kontingenční tabulka za text. Řešením je vyčistit zdrojová data a najít chybu. V příkladu je zdrojem pro kontingenční tabulku malá tabulka, aby se nám chybné hodnoty lépe hledali. Můžeme do tabulky přidat funkci, která ověří, zda jsou datumy číslem nebo textem. Takže funkce JE.ČÍSLO, kde označíme datum. Tam, kde se vrátí nepravda je datum naformátované jako text, takže tyto datumy musíme opravit. Po opravení se vrátíme ke kontingenční tabulce, aktualizujeme propojení, vyhodíme datum z tabulky a zase ho tam vrátíme a zkusíme seskupit datumy a teď seskupení již funguje.
Stejná chyba se může stát, pokud máte mezi datumy nesmyslné datum, i to způsobí, že se datumy nebudou chtít seskupit. Chybu odhalíte stejně, pomocí funkce JE.ČÍSLO.
Nelze seskupit text
Nejde vám v kontingenční tabulce seskupit text? Textové hodnoty se v kontingenční tabulce totiž seskupují jinak než číselné hodnoty. Zatímco na číselné a datumové hodnoty stačí kliknout pravým tlačítkem myši a následně vybrat seskupení, tak u textových hodnot musíte nejprve označit hodnoty, které spolu chcete seskupit. Řekněme, že chceme města seskupit do skupin a teď je jedno jak. Označíme města, která chceme mít v jedné skupině a vybereme Seskupit. Tím se města seskupila a ostatní nevybraná města jsou ve vlastních skupinách. Označíme další města, která chceme ve druhé skupině a vybereme seskupit. A teď stačí označit zbylá města a seskupit je do třetí skupiny.
Jedna odpověď
😊