Dnes si ukážeme další užitečnou techniku, kterou využijete při práci s kontingenčními tabulkami. V minulém videu jsme si ukázali, jak zobrazit měsíce v kontingenční tabulce ve formátu textu a nikoliv číslice. V dnešním videu si ukážeme, jak v kontingenční tabulce zobrazit i řádky nebo sloupce, pro které nemáte ve zdrojové tabulce hodnoty. Představte si, že máte tabulku s prodejními hodnotami produktů za celý rok. Pro některé měsíce ovšem nemáte ve zdrojové tabulce prodejní data. Kontingenční tabulka taková data vezme a zobrazí pouze ty měsíce, pro které máme v kontingenční tabulce hodnoty, všechno ostatní kontingenční tabulka přeskočí. To ovšem může být nežádoucí a zavádějící. V dnešním videu si ukážeme, jak v kontingenční tabulce zobrazíte data i bez hodnot.
Excelový soubor ke stažení
Jak v kontingenční tabulce zobrazit data bez hodnot
V dnešní zdrojové tabulce máme pouze tři sloupce, a to datum, produkt a tržbu. Naším úkolem je zobrazit v kontingenční tabulce tržby po měsících. Problémem ovšem je, že pro některé měsíce ve zdrojové tabulce nemáme data.
Klikneme do zdrojové tabulky dat a vytvoříme kontingenční tabulku. Na kartě Vložení vybereme Kontingenční tabulka. Zkontrolujeme, že máme vybraný celý rozsah zdrojové tabulky a kontingenční tabulku pro jednoduchost vložíme na stejný list, kde máme i zdrojová data (E1).
Na řádky kontingenční tabulky vložíme sloupec Datum a do pole hodnot vložíme Tržby. Datumy se automaticky seskupily do měsíců.
Jak si ale můžeme všimnout, tak nám některé měsíce v tabulce chybí, a to proto, jelikož pro ně nemáme ve zdrojové tabulce hodnoty. V tabulce nám zcela chybí měsíce březen, červenec a září. To může být u prezentace dat zavádějící. Stejné by to bylo v grafu. Kdybychom chtěli tato data prezentovat v grafu, tak by se nám měsíce s nulovými prodeji neobjevily ani v grafu.
Naším cílem je ale naopak ukázat i ty měsíce, pro které žádné tržby nemáme.
Jak zobrazit hodnoty s chybějícími údaji
Pro zobrazení hodnot s nulovými údaji existuje v kontingenční tabulce jednoduchý trik. Nejprve klikněte do kontingenční tabulky na jakýkoliv měsíc na řádku. Na kartě Analýza kontingenční tabulky klikněte na Nastavení pole. Zde na záložce Rozložení a tisk vyberte Zobrazit položky neobsahující data.
Potvrďte a do kontingenční tabulky se přidají i chybějící měsíce.
Kromě chybějících měsíců se ovšem do tabulky přidala ještě tato dvě nová pole. Jsou jimi menší než první datum v tabulce a větší než poslední datum v tabulce. Tato pole se vložila, protože jsme zaškrtli pole Zobrazit pole i bez hodnot, a Excel se tím snaží ošetřit situaci, kdy by se ve zdrojových datech objevily hodnoty před prvním datumem a posledním datumem v tabulce.
Těchto polí se jednoduše zbavíme filtrací. Jelikož pro tyto dvě hodnoty stejně nemáme a nebudeme mít žádná data, tak jednoduše klikneme do záhlaví kontingenční tabulky a tato dvě pole odfiltrujeme. Potvrdíme a zbytečná pole z tabulky zmizela. Teď kontingenční tabulka ukazuje i měsíce, ve kterých jsme neměli žádné tržby. Co kdybyste ale chtěli místo těchto prázdných míst zobrazit něco jiného? Třeba nuly a nebo text?
Stačí kliknout do kontingenční tabulky pravým tlačítkem myši a vybrat Možnosti kontingenční tabulky. V záložce Rozložení formát najdeme Formát a vybereme Pro prázdné buňky zobrazit. Pokud toto pole zaškrtneme a necháme ho prázdné a potvrdíme, tak se v kontingenční tabulce zobrazí i hodnoty, pro které nemáme v tabulce údaje prázdným polem.
Vybereme možnosti kontingenční tabulky ještě jednou a do tohoto pole vyplníme tentokrát nulu. Potvrdíme. A místo prázdných buněk se zobrazila nula.
Co kdybychom ale chtěli v poli například text Nedostupná data? Vrátíme se k Možnosti kontingenční tabulky a do pole napíšeme náš text, třeba Nedostupná data. Potvrdíme a v kontingenční tabulce se pro měsíce, kde nemáme tržby zobrazil náš text.
To samé můžeme udělat i pro chybějící údaje například produktů. V nové zdrojové tabulce máme tři produkty, produkt A, B a C. Ne každý měsíc se ovšem prodávají všechny tři produkty. Z této zdrojové tabulky vytvoříme novou kontingenční tabulku, klikneme do tabulky a na kartě Vložení vybereme Kontingenční tabulka. Na řádky vložíme datum, a stačí nám měsíce, takže pole datum vyhodíme a necháme tam pouze měsíce. Pod měsíc vložíme ještě produkty a do pole hodnot vložíme tržby. Rozklikneme leden a vidíme, že v lednu se prodávaly všechny tři produkty, to samé platí i o únoru. Nicméně v březnu se neprodal ani jeden kus produktu A, takže nám z tabulky zcela vypadl.
Pro zobrazení Produktu A je postup stejný jako u předchozího příkladu s datumy. Musíme kliknout na pole, u kterého chceme zobrazit prázdná pole, tedy produkty. Na kartě Analýza kontingenční tabulky vybereme Nastavení pole, Rozložení a tisk a Zobrazit položky neobsahující data. Potvrdíme a rázem se nám v březnu objevil i produkt A.
Stejně jako v předchozím příkladu si můžeme vybrat, co místo prázdné buňky zobrazíme. Klikneme na tržby pravým tlačítkem myši a vybereme Možnosti kontingenční tabulky a zde v poli pro prázdné buňky nastavíme třeba nulu.
Jedna odpověď
😊