Praktické použití skupinových polí v kontingenční tabulce

Příklad - slučování datumů

V této tabulce jsou jednotlivé prodeje mé firmy. Potřebuji zjistit, kolik jsem utržil za jednotlivé měsíce. Problém samozřejmě je, že znám sice datum, ale neznám konkrétní měsíc.

Dříve jsem tento problém obcházel tak, že jsem v původních datech přidal další sloupec, do kterého jsem pomocí funkce MONTH (MĚSÍC) odvodil z data číslo měsíce.

Dá se to ale dělat i elegantněji.

Návod

Vytvořím základní kontingenční tabulku, kde mám v řádkových polích data a v polích hodnot tržby.

Kliknu myší do některého z dat v kontingenční tabulce a v kartě Možnosti kliknu na Skupinové pole.

Vyberu Měsíce (a třeba ještě Čtvrtletí) a kliknu na OK. A je hotovo. 

Příklad - histogram

Nemusím ale slučovat jen data - mohu slučovat i běžná čísla. A z toho může vzniknout (kromě jiných možností) např. histogram.

Vrátím se k předchozímu případu. Dejme tomu, že bych chtěl v tabulce zjistit, jak vysoké byly tržby - a to tak, že bych chtěl vidět, kolik tržeb bylo v různých pásmech od-do.

Návod

Vytvořím kontingenční tabulku, kde mám v řádkových polích tržby a v polích hodnot např. Den (protože sleduji počet, je vlastně jedno, které pole do hodnot dám.

Kliknu do některé z hodnoty v Popisky řádku a pak zase na Skupinové pole.

Vyberu, jak velká mají být pásma - já např. nechám 1000.

A hotovo - vidím, že např. tržeb v objemu mezi jedním a dvěma tisíci bylo 2295. Mohu si vytvořit i přehledný kontingenční graf.

Histogram lze v Excelu dělat i přes analytické nástroje - ale je to dost za trest a tady uvedený způsob je výrazně šikovnější.

Kontingenční tabulky konečně pohromadě

V horní liště přibylo tlačítko, které odkazuje na všechny návody týkající se kontingenčních tabulek. Je to proto, že se podle návštěvnosti jedná o velmi důležité téma. Doufám, že je to takto pro návštěvníky stránek přehlednější.

Návody na kontingenční tabulky

Zobrazení procent nebo přírůstků v kontingenční tabulce

Příklad

Ve své firmě sleduji tržby. V každém měsíci (1-12) mám několik tržeb. Základní data vypadají takto:

Udělal jsem si kontingenční tabulku, ve které vidím celkové tržby za jednotlivé měsíce:

Sleduji tedy, kolik jsem v jednotlivých měsících utržil celkově.
Mně ale zajímá, kolik procent jsem utržil ve kterém měsíci (ne kolik celkově, ale jde mi o to, jak se který měsíc podílel na celoročních tržbách).
Jdu na Nástroje kontingenční tabulky / Možnosti / Zobrazit hodnoty jako / % z celkového součtu

Takto vypadá výsledek:

Mohlo by mě ale také zajímat, jaké přírůstky jsem v jednotlivých měsících realizoval proti předchozím měsícům.
Pak jdu na Nástroje kontingenční tabulky / Možnosti / Zobrazit hodnoty jako / Rozdíl mezi...

Jako základní položku vyberu "Předchozí". Takto vypadá výsledek:

Také bych ale mohl chtít vidět v jedné kontingenční tabulce všechno najednou - tedy celkové hodnoty, procenta i přírůstky.
Pak si prostě naskládám do Pole hodnot položku Tržba třikrát, jednomu sloupečku nenastavím nic, druhému procenta a třetímu přírůstky.
Výsledek vypadá takto:

Příklad k vyzkoušení je tady:
Stáhnout příklad k vyzkoušení procent nebo přírůstků v kontingenční tabulce

Zobrazení procent nebo přírůstků v kontingenční tabulce

Příklad

Ve své firmě sleduji tržby. V každém měsíci (1-12) mám několik tržeb. Základní data vypadají takto:

Udělal jsem si kontingenční tabulku, ve které vidím celkové tržby za jednotlivé měsíce:

Sleduji tedy, kolik jsem v jednotlivých měsících utržil celkově.
Mně ale zajímá, kolik procent jsem utržil ve kterém měsíci (ne kolik celkově, ale jde mi o to, jak se který měsíc podílel na celoročních tržbách).
Jdu na Nástroje kontingenční tabulky / Možnosti / Zobrazit hodnoty jako / % z celkového součtu

Takto vypadá výsledek:

Mohlo by mě ale také zajímat, jaké přírůstky jsem v jednotlivých měsících realizoval proti předchozím měsícům.
Pak jdu na Nástroje kontingenční tabulky / Možnosti / Zobrazit hodnoty jako / Rozdíl mezi...

Jako základní položku vyberu "Předchozí". Takto vypadá výsledek:

Také bych ale mohl chtít vidět v jedné kontingenční tabulce všechno najednou - tedy celkové hodnoty, procenta i přírůstky.
Pak si prostě naskládám do Pole hodnot položku Tržba třikrát, jednomu sloupečku nenastavím nic, druhému procenta a třetímu přírůstky.
Výsledek vypadá takto:

Příklad k vyzkoušení je tady:
Stáhnout příklad k vyzkoušení procent nebo přírůstků v kontingenční tabulce

Kontingenční tabulka nad více datovými oblastmi

Příklad

Potřebuji vytvořit kontingenční tabulku, která bude používat data z více oblastí.. Mohou být rozložena takto:

nebo mohou tyto oblasti být i na více listech.

Návod

Pro vytvoření kontingenční tabulky nad více oblastmi potřebuji Průvodce kontingenční tabulkou. Prvním krokem tedy bude jeho přidání.
V Excelu 2010 jdu na Soubor / Možnosti / Přizpůsobit pás karet.
Vpravo kliknu (např.) na Vložení a Nová skupina.

Přibyla mi Nová skupina (Vlastní). Zůstanu na ní kurzorem a vpravo vyberu Příkazy mimo pás karet a dále Průvodce kontingenční tabulkou a... Kliknu na Přidat a tlačítko se mi přesunulo zleva do skupiny vpravo.

Dám OK. Teď vidím, že v kartě Vložit přibylo vpravo nové tlačítko Průvodce kontingenční tabulkou a grafem.

Teď jej použiji. V dialogu vyberu Násobné oblasti sloučení. Dále Vytvořit jediné stránkové pole. V dalším kroku budu postupně označovat oblasti a přidávat je.

Kliknu na dokončit a je hotovo.

Označení duplicit pomocí podmíněného formátování

Příklad

Potřebuji v tabulce označit duplicitní hodnoty. Např. v této nabídce zboží:

potřebuji zvýraznit zboží, které mám dvakrát (např. proto, že dvojitý výskyt považuji za chybu).

Návod

Označím hodnoty, ve kterých se duplicity nacházejí. V mém případě oblast A1 až A10.
Jdu na Domů / Podmíněné formátování / Zvýraznit pravidla buněk / Duplicitní hodnoty...

Vyberu, jak se mají duplicity označit a kliknu na OK.

A takhle vypadá výsledek.

Co se v Excelu vůbec učit?

Obsah článku byl přesunut sem.

Co se v Excelu vůbec učit?

Obsah článku byl přesunut sem.

Co dál po tabulkách? Relační databáze!

Během konzultací nebo výuky se často setkávám se zákazníky, kteří už v Excelu umí vše, co potřebují, a přemýšlejí, co dál.
Logicky se chtějí zaměřit na makra a Visual Basic. Na první pohled to dává smysl. S pomocí maker se dají vytvářet zajímavé aplikace a uživatelé čekají, že jim tyto aplikace výrazně usnadní práci.
Makra rád učím a o jejich užitečnosti nepochybuji.
Osobně se ale domnívám, že je ještě jedna oblast, kam po tabulkách pokračovat, a je praktičtější. A tou oblastí jsou relační databáze. Osobně bych se na databáze zaměřil dříve než na makra z těchto důvodů:

• Základní pochopení a využití relačních databází není těžké
• Když se naučíte databáze, budete moci efektivněji využít své znalosti Excelu - třeba tak, že v Excelu budete využívat data z databází
• Prakticky všechny systémy, se kterými se v práci potkáte, jsou na relačních databázích postavené. Pokud pochopíte databáze, pochopíte snadněji i ty systémy
• Základy práce s databází mohou být v zaměstnání nebo u přijímacích pohovorů vaší významnou výhodou oproti ostatním uchazečům
• Není těžké naučit se vytvářet makra. Naučit se ale vytvářet makra, která opravdu usnadní reálné pracovní úkoly, často těžké je a je to práce spíše pro programátory než pro běžné uživatele

Pokud se chcete s databázemi naučit, doporučuji začít s aplikací MS Access. Ta se sice v praxi moc nepoužívá, pro výuku je ale velmi vhodná, protože:

• Není třeba složitě instalovat
• Máte ji zřejmě na svém počítači
• Základy, které se v Accessu naučíte, můžete dobře použít ve "velkých" systémech jako Oracle, MS SQL nebo DB2

Takže, až budete mít pocit, že je vám běžný Excel malý, zvažte, jestli se dále nenaučit něco o databázích - ať už sami, nebo s námi.