Kontingenční tabulka Excelu je jedním z nejvýkonnějších nástrojů, který vám může pomoci shrnout a analyzovat velké datové sady efektivním způsobem.
Kontingenční tabulka je jedním z nejvýkonnějších a nejužitečnějších nástrojů pro sumarizaci dat v Excelu, který umožňuje rychle shrnout, seřadit, reorganizovat, analyzovat, seskupovat a počítat velké datové sady.
Kontingenční tabulka vám umožňuje otáčet (nebo pivotovat) data uložená v tabulce tak, abyste je viděli z různých perspektiv a měli jasnou představu o velkých souborech dat.
Tento tutoriál vám poskytne podrobné pokyny, jak vytvářet a používat kontingenční tabulky v Excelu.
Uspořádejte svá data
Chcete-li vytvořit kontingenční tabulku, vaše data by měla mít strukturu tabulky nebo databáze. Musíte tedy svá data uspořádat do řádků a sloupců. Chcete-li převést rozsah dat na tabulku, vyberte všechna data, přejděte na kartu „Vložit“ a klikněte na „Tabulka“. V dialogovém okně Vytvořit tabulku klikněte na ‚OK‘ pro převod datové sady na tabulku.
Použití tabulky Excel jako zdrojové datové sady pro vytvoření kontingenční tabulky činí kontingenční tabulku dynamickou. Když přidáte nebo odeberete položky v tabulce Excel, data v pivotu se aktualizují s ní.
Předpokládejme, že máte velký soubor dat, jak je uvedeno níže, sestává z více než 500 záznamů a 7 polí. Datum, region, typ prodejce, společnost, množství, příjem a zisk.
Vložit kontingenční tabulku
Nejprve vyberte všechny buňky, které obsahují data, přejděte na kartu „Vložit“ a klikněte na „Kontingenční graf“. Poté z rozevírací nabídky vyberte možnost „Kontingenční graf a kontingenční tabulka“.
Otevře se dialogové okno Vytvořit kontingenční tabulku. Excel by automaticky identifikoval a vyplnil správný rozsah v poli Tabulka/Rozsah, jinak by vybral správnou tabulku nebo rozsah buněk. Poté zadejte cílové umístění vaší kontingenční tabulky Excel, může to být „Nový list“ nebo „Stávající list“ a klikněte na „OK“.
Pokud zvolíte „Nový list“, vytvoří se nový list s prázdnou kontingenční tabulkou a kontingenčním grafem v samostatném listu.
Sestavte si svou kontingenční tabulku
Na novém listu uvidíte na levé straně okna Excel prázdnou kontingenční tabulku a na pravém okraji okna Excel podokno „Pole kontingenční tabulky“, kde najdete všechny možnosti pro konfiguraci vaší kontingenční tabulky.
Podokno Pole kontingenční tabulky je rozděleno na dvě vodorovné části: část Pole (horní část podokna) a část Rozvržení (dolní část podokna)
- The Sekce pole uvádí všechna pole (sloupce), které jste přidali do tabulky. Tyto názvy polí jsou všechny názvy sloupců z vaší zdrojové tabulky.
- The Sekce rozložení má 4 oblasti, tj. Filtry, Sloupce, Řádky a Hodnoty, pomocí kterých můžete pole uspořádat a znovu uspořádat.
Přidat pole do kontingenční tabulky
Chcete-li vytvořit kontingenční tabulku, přetáhněte pole z části Pole do oblastí části Rozvržení. Můžete také přetahovat pole mezi oblastmi.
Přidat řádky
Začneme přidáním pole „Společnost“ do sekce Řádky. Obvykle se do oblasti Řádek v rozvržení přidávají nenumerická pole. Jednoduše přetáhněte pole ‚Společnost‘ do oblasti ‚Řádek‘.
Všechny názvy společností ze sloupce ‚Společnost‘ ve zdrojové tabulce budou přidány jako řádky do kontingenční tabulky a budou seřazeny vzestupně, ale pořadí můžete změnit kliknutím na rozbalovací tlačítko v buňce Označení řádků.
Přidat hodnoty
Přidali jste řádek, nyní do této tabulky přidáme hodnotu, aby se z ní stala jednorozměrná tabulka. Jednorozměrnou kontingenční tabulku můžete vytvořit přidáním pouze popisků řádků nebo sloupců a jejich příslušných hodnot do oblastí. Oblast hodnot je místo, kde se ukládají výpočty/hodnoty.
Na výše uvedeném příkladu obrazovky máme řadu společností, ale chceme zjistit celkový příjem každé společnosti. Chcete-li to získat, jednoduše přetáhněte pole „Příjmy“ do pole „Hodnota“.
Pokud si přejete odstranit některá pole ze sekce Oblasti, jednoduše zrušte zaškrtnutí políčka vedle pole v sekci Pole.
Nyní máme jednorozměrnou tabulku společností (označení řádků) spolu se součtem tržeb.
Přidat sloupec
Dvourozměrný stůl
Řádky a sloupce dohromady vytvoří dvourozměrnou tabulku a vyplní buňky třetím rozměrem hodnot. Předpokládejme, že chcete vytvořit kontingenční tabulku tak, že uvedete názvy společností jako řádky a pomocí sloupců zobrazíte data a vyplníte buňky celkovými příjmy.
Když přidáte pole ‚Datum‘ do oblasti ‚Sloupce‘, Excel do polí sloupců automaticky přidá ‚Čtvrtletní‘ a ‚Roky‘ pro výpočet a lepší shrnutí dat.
Nyní máme dvourozměrnou tabulku s trojrozměrnými hodnotami.
Přidat filtry
V případě, že chcete filtrovat data podle ‚Region‘, můžete přetáhnout pole ‚Region‘ do oblasti Filtr.
Tím se nad kontingenční tabulku přidá rozbalovací nabídka s vybraným „Pole filtru“. Díky tomu můžete odfiltrovat příjmy společností za každý rok podle regionu.
Ve výchozím nastavení jsou vybrány všechny regiony, zrušte jejich zaškrtnutí a vyberte pouze region, podle kterého chcete data filtrovat. Chcete-li tabulku filtrovat podle více položek, zaškrtněte políčko vedle možnosti „Vybrat více položek“ ve spodní části rozevíracího seznamu. A vyberte více regionů.
Výsledek:
Řazení
Chcete-li seřadit hodnotu tabulky vzestupně nebo sestupně, klikněte pravým tlačítkem myši na libovolnou buňku ve sloupci Součet tržeb, rozbalte položku „Seřadit“ a vyberte pořadí.
Výsledek:
Seskupování
Řekněme, že máte v kontingenční tabulce data seřazená podle měsíců, ale nechcete je vidět měsíčně, místo toho chcete data přeskupit do finančních čtvrtletí. Můžete to udělat ve své kontingenční tabulce.
Nejprve vyberte sloupce a klikněte na ně pravým tlačítkem. Poté z rozbalovací nabídky vyberte možnost „Skupina“.
V okně Seskupení vyberte „Čtvrtletí“ a „Roky“, protože je chceme organizovat do finančních čtvrtletí každého roku. Poté klikněte na „OK“.
Nyní jsou vaše data uspořádána do finančních čtvrtletí každého roku.
Nastavení pole hodnot
Ve výchozím nastavení kontingenční tabulka shrnuje číselné hodnoty pomocí funkce Součet. V oblasti Hodnoty však můžete změnit typ výpočtu, který se používá.
Chcete-li změnit funkci souhrnu, klikněte pravým tlačítkem na libovolný údaj v tabulce, klikněte na „Shrnout hodnoty podle“ a vyberte svou možnost.
Případně můžete kliknout na šipku dolů vedle ‚Součet ..‘ v oblasti hodnot v části pole a vybrat ‚Nastavení hodnotového pole‘.
V „Nastavení hodnotového pole“ vyberte svou funkci pro shrnutí dat. Poté klikněte na „OK“. Pro náš příklad volíme ‚Počet‘, abychom spočítali počet zisků.
Výsledek:
Kontingenční tabulky Excelu také umožňují zobrazovat hodnoty různými způsoby, například zobrazit celkové součty jako procenta nebo sloupce celkem jako procenta nebo součet řádků jako procenta nebo seřadit hodnoty od nejmenší po největší a naopak, mnoho dalších.
Chcete-li zobrazit hodnoty v procentech, klikněte pravým tlačítkem kdekoli v tabulce, poté klikněte na „Zobrazit hodnoty jako“ a vyberte svou možnost.
Když zvolíme ‚% z celkového počtu sloupců‘, výsledek bude takový,
Aktualizujte kontingenční tabulku
Přestože je sestava kontingenční tabulky dynamická, při provádění změn ve zdrojové tabulce Excel automaticky neobnovuje data v kontingenční tabulce. Aby se data aktualizovala, je třeba jej ručně ‚obnovit‘.
Klikněte kamkoli do kontingenční tabulky a přejděte na kartu „Analyzovat“, klikněte na tlačítko „Obnovit“ ve skupině Data. Chcete-li aktualizovat aktuální kontingenční tabulku v listu, klikněte na možnost „Obnovit“. Pokud chcete obnovit všechny kontingenční tabulky v sešitu, klikněte na ‚Obnovit vše‘.
Případně můžete kliknout pravým tlačítkem na tabulku a vybrat možnost „Obnovit“.
A je to. Doufáme, že vám tento článek poskytne podrobný přehled kontingenčních tabulek Excelu a pomůže vám je vytvořit.