Roční kalendář – 4. díl: Kontingenční tabulky

Během posledního měsíce jsme si ukázali, jak vypadá šablona kalendáře, jak ji vytvořit a jak ji naplnit daty. Postup to byl možná trošku krkolomný a tak se teď podíváme na daleko jednodušší postup, který s sebou ale přináší určitá úskalí.

Základní kontingenční tabulka


Začneme tak, že si vytvoříme zdrojovou tabulku, která bude obsahovat sloupce:

• datum: bude to seznam jednotlivých dat, například data od 1. 1. 2015 do 31. 12. 2016,
• rok: pomocí funkce =ROK zjistíme rok daného data,
• měsíc: pomocí funkce =MĚSÍC zjistíme měsíc daného data,
• den týdne: funkcí =DENTÝDNE získáme pořadové číslo dne týdne (1 pro pondělí, 7 pro neděli),
• týden: funkce =WEEKNUM pomůže zjistit, do kterého týdne roku dané datum spadá.

Zdrojová tabulka

Vytvořenou tabulku použijeme jako zdroj pro vložení kontingenční tabulky, kterou sestavíme následovně:

• filtry: rok – můžeme se tak snadno přepnout z roku 2015 na 2016,
• řádky: měsíc a týden – jednotlivé měsíce budeme ještě členit po týdnech,
• sloupce: den týdne – abychom dosáhli dnů pondělí až neděle,
• hodnoty: součet ze sloupce datum – použijeme vlastní formát čísla d (zobrazí se tak pouze číslo dne).

Nezapomeňte se přepnout ve filtrech pouze na konkrétní rok. Pak dostanete velmi jednoduchý roční kalendář.

Základní kontingenční tabulka

Úpravy

V tabulce výše už jsme (na kartě Nástroje kontingenční tabulky: Návrh) odstranili Celkové součty pro řádky i sloupce, jelikož zde nedává smysl sčítat několik dat. Ze stejného důvodu jsme odstranili Souhrny.

Abychom dostali nějakou hezčí/smysluplnější tabulku, můžeme zdrojovou oblast pro tuto kontingenční tabulku rozšířit o název měsíce. K tomu využijeme funkci =ZVOLIT, která pak bude vypadat následovně:

=ZVOLIT([@měsíc];"leden";"únor";"březen";"duben";"květen";"červen";"červenec";"srpen";"září";"říjen";"listopad";"prosinec")

 (první argument odkazuje na sloupec měsíc v tabulce)

Nezapomeňte tento nově vytvořený sloupec přidat do řádků tabulky (objeví se tam tedy sloupec měsíc_název místo původního měsíc). Aby se změny projevily, musíte kontingenční tabulku aktualizovat (např. klávesovou zkratkou Alt + F5).

Aktualizace kontingenční tabulky

V záhlaví tabulky můžete přepsat čísla dnů týdnů na zkratky (po, út, st…), v řádcích pak bílou barvou "schovat" čísla týdnů. Nakonec skryjte přebytečné řádky, přidejte průřez na roky a dostanete profesionální výsledek.

Napojení a analýza dat

"Co když chci v šabloně kalendáře zobrazit nikoli dny, ale počet kusů prodaných pomerančů a jablek pro daný den?"

Zdrojovou tabulku budeme muset opět rozšířit o dva sloupce:

• ovoce: uvádíme buď pomeranče nebo jablka,
• prodáno: kolik kusů mrkve nebo jablek se v daný den prodalo.

Na konec tabulky pak přidáme samotná data.

V kontingenční tabulce nahradíme položku "Součet z datum" sloupcem Prodáno, do filtrů přidáme sloupec ovoce a máme na chvíli vystaráno. Brzy však narazíme na problém, když si vyfiltrujeme pouze jablka. Všimnete si, že jablka se v neděli vůbec neprodávají a tak ani kontingenční tabulka neděli nezobrazuje.

Tohle lze, byť poměrně krkolomě ošetřit ve zdrojových datech. Trik spočívá v tom, že do tabulky nebudete zadávat pouze dny, kdy se nějaké ovoce prodalo, ale všechny dny – ty bez prodejů s číslem 0 ve sloupci prodáno.

Po aktualizaci a opětovném "očištění" tabulky se již hodnoty zobrazují v pořádku. Pokud chcete, můžete opět zapnout Celkové součty a Souhrny, zde to už smysl dává. :-)

Stáhněte si zdrojový soubor.

Závěr

Vytváření kalendáře a analýza dat pomocí kontingenčních tabulek má své výhody i nevýhody. Pojďme se na ně tedy podívat.

Výhody

• velmi snadné vytvoření kalendáře (hotovo do 10 minut)
• snadná údržba (nejsou tu skoro žádné vzorce, pouze zdrojová a kontingenční tabulka)
• variabilita v napojení na data (snadné přepínání mezi pomeranči, jablky)
• snadné porovnávání více období (leden 2014 proti lednu 2015 apod.)
• profesionální vzhled (celkové součty, použití průřezů)

Nevýhody:

• složitejší příprava dat pro napojení
• nelze vytvořit matici 4×3 měsíce
• nutnost aktualizovat tabulku po změně zdrojových dat (platí ale pro kontingenční tabulky obecně, lze řešit pomocí maker)

Seznam dílů:

• 1. díl: Šablona (5. 2. 2015)
• Šablona pro roční kalendář s podmíněným formátováním na důležitá data
• 2. díl: Jak to to funguje? (12. 2. 2015)
• Jaké funkce jsou použité?
• Co vlastně buňky zobrazují?
• Jaké jsem použil podmíněné formátování?
• 3. díl: Analýza dat v kalendáři (19. 2. 2015)
• Jak na kalendář napojit jiná data?
• 4. díl: Kontingenční tabulky (26. 2. 2015)
• Jak vytvořit kalendář pomocí kontingenčních tabulek?
• Jak jej napojit na data?
• Jaké jsou výhody a nevýhody tohoto řešení?