V dnešním videu si ukážeme několik nejčastějších problémů s kontingenčními tabulkami a jak je vyřešit. Kontingenční tabulky patří mezi nejoblíbenější nástroje v Excelu, nicméně práce s nimi má svá specifika. A někdy se můžete setkat s problémy, se kterými se u klasických excelových tabulek nepotkáte. Několik z nich, včetně řešení, si ukážeme v tomto videu.
Nezapomeňte se podívat rovněž na video Triky s kontingenčními tabulkami.
Excelový soubor ke stažení
Problémy s kontingenčními tabulkami a jak je vyřešit
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.
Nová data se nepřidávají do tabulky
Prvním problémem, se kterým se u práce s kontingenčními tabulkami můžete setkat je, že se vám po aktualizaci kontingenční tabulky neobjeví nová data v tabulce. Důvodem je to, že máte nejspíše jako zdroj kontingenční tabulky fixně zadané rozpětí buněk. To se stává, pokud jako zdroj kontingenční tabulky nepoužijete excelovou tabulku. Problém vyřešíme, když klikneme do kontingenční tabulky a na kartě Analýza kontingenční tabulky vybereme Změnit zdroj dat. V poli můžeme zkontrolovat, jaká data máme zahrnutá v kontingenční tabulce. V případě, že nějaká chybí, tak je zde můžeme označit a po potvrzení se nová data zahrnou do kontingenční tabulky. Ideálním řešením, abyste se tomuto problému vyhnuli je ale používat jako zdroj kontingenční tabulky excelovou tabulku, nová data, která do excelové tabulky přidáte se totiž automaticky zahrnout do zdroje kontingenční tabulky potom, co aktualizujete připojení.
Kontingenční tabulky se překrývají
Možná jste se někdy setkali s podobnou výstrahou, která na vás vyskočí při aktualizaci kontingenčních tabulek. Tato výstraha jednoduše znamená, že nemůže dojít k aktualizaci propojení, jelikož by se některé kontingenční tabulky překrývaly. Do zdrojové tabulky totiž přibyl nový produkt, takže se první kontingenční tabulka potřebuje o jeden řádek rozšířit, ale nemá kam, jelikož hned pod ní je druhá kontingenční tabulka. Řešením je potvrdit chybovou hlášku, vložit dostatek mezer mezi kontingenční tabulky a znovu tabulky aktualizovat. Ideální je, pokud máte více kontingenčních tabulek na jednom listu, skládat je vedle sebe a nebo mezi nimi nechávat dostatek volných buněk, aby nic nebránilo jejich aktualizaci.
Nesmyslné názvy v záhlaví
Když máme členitou kontingenční tabulku, tak se v záhlaví řádku a sloupce objeví tyto nic neříkající názvy Popisky řádků a Popisky sloupců. To jsou názvy, které někoho, kdo není s kontingenčními tabulkami zvyklý pracovat mohou mást, jelikož nepřinášejí do tabulky žádnou přidanou hodnotu. Pokud se pokusíte název smazat, tak na vás vyskočí hláška, že to nelze. Jedním ze způsobů, jak se jich zbavit je změnit rozložení kontingenční tabulky z kompaktního standardního rozložení na tabulkové. V tabulkovém rozložení se záhlaví přejmenuje podle polí, které máme na řádcích a ve sloupcích. Druhou možností je, použít trik s mezerou. Vrátíme se ke kompaktnímu rozložení a klikneme na název v záhlaví řádků a název nahradíme jednou mezerou. Potvrdíme a název zmizel. To samé uděláme pro sloupce a záhlaví tabulky je vyčištěné.
Shodné názvy polí
Pokud chcete přejmenovat celkový součet řádku podle pole, které je v něm, tak narazíte na problém, že žádné pole v kontingenční tabulce nesmí mít shodný název. Pokud bychom tedy pole Součet z tržba chtěli přejmenovat jen na Tržba, tak se nám to nepovede, jelikož takový název už v kontingenční tabulce máme. Pomůžeme si opět trikem s mezerou. Přepíšeme název na Tržba a dáme za něj mezeru. Excel považuje mezeru za další znak, takže se již nejedná o shodný název.
Z čeho se skládá číslo v tabulce
Při používání kontingenčních tabulek důvěřujeme tomu, že kontingenční tabulka za nás správně agreguje zdrojová data. Co když ale potřebujeme ověřit, že je číslo v kontingenční tabulce správně nebo potřebujeme zkontrolovat z jakých zdrojových dat se číslo skládá? Samozřejmě se můžeme vrátit ke zdrojové tabulce a tabulku filtrovat podle polí, které máme v kontingenční tabulce. Rychlejší způsob ale je, pokud na číslo dvojitě poklepáme nebo stiskneme pravé tlačítko myši a vybereme Zobrazit podrobnosti. Tím se na nový list vloží zdrojová data, ze kterých číslo v kontingenční tabulce vzniklo.
Smazaná zdrojová data
Smazali jste si po vytvoření kontingenčních tabulek omylem list se zdrojovými daty? V tomto případě nemusíte zoufat, jelikož kontingenční tabulka používá mezi paměť, ve které je kopie zdrojových dat uchovaná, takže i potom, co smažete zdrojová data, můžete s kontingenční tabulkou nadále pracovat. Co víc? Pokud potřebujete zdrojová data zpátky, tak stačí dvakrát poklepat na buňku celkového součtu a zdrojová data se vloží na nový list.
Zobrazení hodnot místo počtu a součtu
Potřebovali jste někdy v kontingenční tabulce zobrazit skutečné hodnoty ze zdrojové tabulky místo počtu a součtu? Existuje pro to jednoduchý trik. V kontingenční tabulce chceme zobrazit v poli hodnoty datumy, kdy se zaměstnanci zúčastnili kterého kurzu. Účastníky máme na řádcích a kurzy máme ve sloupcích. Když přesuneme datum do pole hodnot, tak kontingenční tabulka automaticky zobrazí počet hodnot. Abychom zobrazili datumy v poli hodnot, tak stačí jako výpočet vybrat buď minimum nebo maximum. Takže v Nastavení polí hodnot vybere Minimum a po potvrzení kontingenční tabulka v poli hodnot zobrazí skutečné hodnoty místo počtu a součtu. Jen musíme upravit formátování. Pravé tlačítko, formát čísla a datum.
Seskupování dat
U seskupování dat v kontingenční tabulce se můžete setkat s následujícím problémem. V kontingenční tabulce chceme na řádcích zobrazit datumy seskupené do kvartálů. Klikneme na datumy a vybereme Seskupit a vybereme seskupení po kvartálech. Datumy se seskupí a kontingenční tabulka ukazuje tržby pro čtvrtletí. Teď tuto tabulku zkopírujeme, vložíme ji vedle a tuto tabulku budeme chtít zobrazit bez seskupení, tedy po dnech. Když vybereme Oddělit, tak se oddělí i první tabulce. A je to proto, že kontingenční tabulky sdílejí jednu mezi paměť a změna v seskupení jedné tabulky ovlivní i druhou tabulku.
Řešení existuje několik. Jedním ze způsobů, jak donutit novou kontingenční tabulku použít jinou mezi paměť než původní tabulka je, že použijeme původního průvodce kontingenční tabulkou. Toho najdeme v nabídce Panelu nástrojů Rychlý přístup. Klikneme na horní lištu a vybereme Rychlý přístup. Vybereme Všechny příkazy a najdeme Průvodce kontingenční tabulkou. Když ho najdeme, tak vybereme Přidat. Tím se zobrazí průvodce v pásu rychlého přístupu. Klikneme do zdroje. A vytvoříme kontingenční tabulku pomocí průvodce.
Rozdíl je v tom, že pomocí průvodce bude mít každá tabulka jinou mezi paměť. Potvrdíme vytvoření tabulky a teď vložíme datumy na řádky, tržby do hodnot a zkusíme seskupení a vidíme, že jsou teď na sobě tabulky nezávislé.
Jedna odpověď
😊👍