Úvodní stránka Excel(entní) taháky Vzorová data
 

Excel - Kontingenční tabulky - skupina a přehled

K tomuto návodu si můžete stáhnout excelovský soubor s daty a řešením.

Kontingenční tabulky - skupina a přehled

1. Seskupení položek

  1. Hodnoty ve SLOUPCOVÉM i ŘÁDKOVÉM poli můžeme seskupovat podle potřeby.

  2. Excel nám umožňuje data seskupovat podle kalendářních položek (týdny, měsíce, čtvrtletí, roky).

  3. Zároveň můžeme hodnoty seskupovat podle individuálně zadaných kritérií (věk v dekádách, cena v definovaných intervalech... )

excel_kontingenční_tabulka_1

excel_kontingenční_tabulka_2

excel_kontingenční_tabulka_3


2. Kombinované seskupení položek

  1. Hodnoty ve SLOUPCOVÉM i ŘÁDKOVÉM poli můžeme seskupovat podle potřeby současně.


excel_kontingenční_tabulka_4

3. Vytvoření seskupení podle kalendářních položek

  • nejdříve vytvoříme kontingenční tabulku BEZ SESKUPENÝCH položek.
  • excel_kontingenční_tabulka_5
  • Aktivujeme některou z buněk obsahující datum v kontingenční tabulce.

  • Zobrazíme panel nástrojů pro práci s kontingenční tabulkou (kliknutím PRAVÝM tlačítkem myši)

  • Použijeme příkaz Skupina a přehled > Seskupit...

  • excel_kontingenční_tabulka_6

  • V dialogovém okně zvolíme seskupování podle klíče "čtvrtletí" a "roky".

  • excel_kontingenční_tabulka_7

  • Výsledné seskupení v kontingenční tabulce:

  • excel_kontingenční_tabulka_8

    Poznámka:

    Pokud seskupování hodnot v celém časovém rozsahu není potřeba, můžeme omezit výběr pomocí položek "Začátek" a "Konec" v dialogovém okně"Seskupit".

    V zadání podle obrázku vpravo se omezujeme pouze na položky odpovídající kalendířnímu roku 2007.
    excel_kontingenční_tabulka_12
    Úprava popisného textu

  • aktivujeme buňku s excelem vloženým textem "čtv1" a přepíšeme text podle našich potřeb (1. čtvrtletí, 1Q, Q-1, Quarterly 1, IQ...)

  • Potvrdíme stiskem klávesy Enter

  • excel_kontingenční_tabulka_9
  • Excel nám při změně popisných textů pomůže tím, že změní popis všech stejných položek - v našem příkladu stačí přepsat pouze v jedné buňce text "čtv1" na "4. čtvrtletí" - Excel text změní i pro další rok

  • excel_kontingenční_tabulka_10
  • Výsledné zobrazení kontingenční tabulky

  • excel_kontingenční_tabulka_11

     Poznámka:

    Při vytváření časových souhrnů se uživatelé často potýkají s dvojicí chyb - jedna vyplývá z interpretace hodnot Excelem a druhá z nesprávného zadání podmínek souhrnů.


    4. Chybové hlášení "Tento výběr nelze seskupit"

    V některých situacích Excel odmítne vytvořit seskupení dat.

    Tato situace nastává tehdy, když se pokoušíme seskupit data, která obsahují nedatumovou položku - buď je datum zadán jako text, nebo sloupec / řádek obsahuje prázdné buňky (v našem příkladu je příčinou chyby prázdná buňka s adresou A10).

    Řešením je problémové buňky ze zdrojových dat odstranit, nebo nahradit jejich obsah hodnotou, kterou Excel interpretuje jako skutečné datum.

    excel_kontingenční_tabulka_13

    5. Seskupení položek přesahující zamýšlené limity

    Podívejme se na dvojici kontingenčních tabulek na obrázku vpravo.

    První kontingenční tabulka v souhrnu za 4. čtvrtletí obsahuje jak položky roku 2006, tak i položky roku 2007 - což zřejmě není v souladu s naší představou. Pokud totiž při seskupování zadáme pouze požadavek na sdružování dat podle čtvrtletí, zajímá excel pouze informace o kalendářním měsíci, nikoliv o kalendářním roce.

    Ve druhé kontingenční tabulce jsou již data sdružena "řádným" způsobem - v dialogovém okně jsme zadali požadavek na seskupování jak podle čtvrtletí, tak i podle roku.

    excel_kontingenční_tabulka_16

    Pokud chceme nad základními daty v našem příkladu zobrazit suhrnná data za čtvrtletí POUZE roku 2007, máme v zásadě k dispozici 3 možná řešení.

    1. Kontingenční tabulku vytvořit pouze nad daty roku 2007 - zde oblast dat A7:E33.

    2. Omezit seskupování dat v dialogovém okně "Seskupit" nastavením začátku na hodnotu 1.1.2007.

      excel_kontingenční_tabulka_20

    3. Seskupit data podle roku i čtvrtletí a pak zobrazit pouze rok 2007.

      excel_kontingenční_tabulka_21

    excel_kontingenční_tabulka_19

    6. Závěrečné formátování kontingenční tabulky,

    případně začlenění dalšího kritéria pro souhrn (zde kalendářní měsíc), může výrazně zvýšit vypovídací schopnost a přehlednost výsledných dat.

    excel_kontingenční_tabulka_22


    Úvodní stránka Excel(entní) taháky Vzorová data

     

     

    Klíčová slova - Microsoft Excel,Excel tipy,Excel,kontingenční tabulka,pivot table,seskupení,skupina a přehled,souhrn,průvodce