Jak vytvářet kontingenční tabulky v Excelu

Spoluautorem článku je Pracovnící wikiHow

Kontingenční tabulky jsou interaktivní tabulky, které uživatelům umožňují slučovat a spojovat velké objemy dat do snadno čitelného tabelárního formátu, usnadňujícího reportování a analýzu. Lze jimi třídit, počítat a sčítat data, a navíc je lze využít v řadě tabulkových procesorů. Excel nabízí snadnou tvorbu kontingenčních tabulek jednoduchým přetažením relevantních dat do patřičných boxů. Tato data pak můžete filtrovat a třídit, a také v nich identifikovat trendy a vzory.

Část 1

Část 1 ze 3:

Založení kontingenční tabulky

1
Otevřete si sešit, ze kterého chcete kontingenční tabulku vytvářet. Kontingenční tabulka umožňuje z dat ze sešitu vytvářet vizuální reporty. Můžete s ní počítat bez nutnosti vkládat jakékoliv vzorce a kopírovat buňky. K vytvoření kontingenční tabulky budete potřebovat sešit s několika záznamy.
- Kontingenční tabulku také můžete v Excelu vytvořit z vnějšího zdroje dat, například z programu Access. Tabulku můžete vložit do prázdného sešitu v Excelu.
2
Ověřte si, že jsou vaše data pro tvorbu kontingenční tabulky vhodná. Kontingenční tabulka není univerzálním řešením. Abyste mohli těžit z jejích výhod, musí vaše data splňovat určitá základní kritéria: ^{[1]
X
Zroj výzkumu}
- Sešit by měl obsahovat minimálně jeden sloupec duplicitních hodnot. To v podstatě znamená, že by se minimálně v jednom sloupci měla data opakovat. V příkladu v další sekci obsahuje sloupec "Druh produktu" dvě hodnoty: "Stůl" a "Židle".
- Sešit by měl obsahovat číselné údaje. Ty budou v kontingenční tabulce porovnávány a sčítány. V příkladu z další sekce obsahuje numerická data sloupec "Prodeje".
3
Spusťte průvodce tvorbou kontingenční tabulky. Klikněte na vršku okna Excelu na záložku "Vložit", a poté na tlačítko "Kontingenční tabulka" v levé části panelu Vložit.
- Pokud používáte Excel 2003 nebo starší, klikněte na nabídku Data a zvolte Kontingenční tabulka a kontingenční graf...
4
Vyberte typ dat, který chcete použít. Ve výchozím nastavení vybere Excel všechna data z aktivního listu. Kliknutím a tažením myši můžete vybrat jen jeho určitou část, nebo můžete zadat rozsah buněk ručně.
- Pokud využíváte data z externího zdroje, klikněte na možnost "Použít zdroj externích dat", a pak klikněte na tlačítko Zvolit připojení.... Procházejte připojenými databázemi, které máte v počítači uložené.
5
Vyberte umístění kontingenční tabulky. Po nastavení rozsahu dat navolte ve stejném okně možnosti umístění. Ve výchozím nastavení umístí Excel tabulku do nového listu, přičemž budete mít možnost přepínat tam a zpět pomocí záložek na spodní hraně okna. Můžete si také zvolit umístění kontingenční tabulky do stejného listu, v němž se nachází vaše data. Pak budete mít možnost vybrat buňku, do které chcete tabulku umístit. ^{[2]
X
Zroj výzkumu}
- Když jste s nastavením spokojeni, klikněte na OK. Dojde k vložení kontingenční tabulky a změně prostředí programu.
Reklama

Část 2

Část 2 ze 3:

Nastavení kontingenční tabulky

1
Přidejte řádkové pole. Při tvorbě kontingenční tabulky v zásadě třídíte data dle řádků a sloupců. Strukturu tabulky určuje to, který prvek kam přidáte. Přetáhněte pole ze Seznamu polí (napravo) do sekce Řádkových polí kontingenční tabulky, čímž dojde k vložení dat.
- Příklad: vaše společnost prodává dva produkty: stoly a židle. Máte sešit s čísly (Prodeje) ke každému produktu (Druh produktu) prodanému v pěti obchodech (Obchody). Chcete zjistit, kolik kusů každého produktu bylo v každém obchodě prodáno.
- Přetáhněte pole Obchody ze seznamu polí do sekce Řádková pole v kontingenční tabulce. Objeví se seznam obchodů, každý obchod v jednom řádku.
2
Přidejte sloupcové pole. Stejně jako řádky, i sloupce vám umožňují třídit a zobrazovat vaše data. V příkladu výše jsme přidali pole Obchody do sekce Řádková pole. Abyste zjistili, kolik každého druhu produktu bylo prodáno, přetáhněte pole Druh produktu do sekce Sloupcová pole.
3
Přidejte pole hodnot. Nyní, když máte rozvrženou strukturu, můžete přidat data, která chcete v tabulce zobrazit. Přetáhněte pole Prodeje do sekce Pole hodnot kontingenční tabulky. V tabulce uvidíte zobrazené údaje o prodejích obou produktů v každém z obchodů, a vpravo také sloupec Suma. ^{[3]
X
Zroj výzkumu}
- Ve všech předchozích krocích můžete pole namísto do tabulky přetahovat i do odpovídajících rámečků v Seznamu polí.
4
Přidejte do sekce více polí. Kontingenční tabulky umožňují do každé ze sekcí přidat více polí, což vám dává ještě přesnější kontrolu nad zobrazením dat. Uvažujme příklad výše a řekněme, že vyrábíte několik druhů stolů a několik druhů židlí. Váš sešit obsahuje údaje nejen o tom, zda je produkt židle nebo stůl (Druh produktu), ale také informace o konkrétním modelu prodaného stolu či židle (Model).
- Přetáhněte pole Model do sekce Sloupcová pole. Sloupce se nyní rozdělí na prodeje jednotlivých modelů a prodeje druhů produktu. Pořadí, v jakém se tyto štítky zobrazují, můžete změnit kliknutím na tlačítko s šipkou vedle pole v rámečcích (u pravého dolního okraje okna). Pořadí změníte výběrem "Přesunout nahoru" či "Přesunout dolů".
5
Změňte způsob zobrazení dat. Způsob zobrazení hodnot můžete změnit kliknutím na ikonu šipky vedle hodnoty v rámečku Hodnot. Zvolte možnost "Nastavení pole hodnot" a změňte výpočet hodnot. Například si můžete zobrazit procentuelní hodnoty místo absolutních, nebo průměr hodnot namísto jejich sumy.
- Abyste mohli využít tuto funkci, je možné jedno pole do rámečku Hodnot přidat několikrát. V příkladu výše je zobrazena celková suma prodejů v každém obchodě. Při dalším vložení pole Prodeje můžete změnit nastavení hodnot tak, aby zobrazovalo procentuelní podíl celkových prodejů.
6
Naučte se, jak hodnoty upravovat. Při změně způsobu výpočtu hodnot máte na výběr z několika možností, které by měli korespondovat s vašimi potřebami.
- Suma – Suma je pro pole hodnot výchozím nastavením. Excel sečte všechny hodnoty ve zvoleném poli.
- Počet – Vrací počet buněk zvoleného pole, které obsahují data.
- Průměr – Vrací průměr hodnot ve zvoleném poli.
7
Přidejte filtraci. Oblast "Filtr sestavy" obsahuje pole, která umožňují listovat souhrny dat v kontingenční tabulce na základě filtrace určitých skupin dat. Tato pole se chovají v reportu jako filtry. Například pokud nastavíte jako filtr namísto Popisku řádků pole Obchody, budete moci zobrazit celkové prodeje každého obchodu zvlášť, nebo naopak sledovat několik obchodů současně.

Reklama

Část 3

Část 3 ze 3:

Využití kontingenční tabulky

1
Třiďte a filtrujte výsledky. Jednou z klíčových funkcí kontingenční tabulky je schopnost třídit výsledky a zobrazovat dynamické reporty. Každý popisek můžete utřídit a vyfiltrovat kliknutím na dolů mířící šipku vedle záhlaví popisku. Takto můžete seznam seřadit, nebo z něj vyfiltrovat zobrazení jen určitých záznamů. ^{[4]
X
Zroj výzkumu}
2
Aktualizujte sešit. Kontingenční tabulka se při změně zdrojového sešitu automaticky aktualizuje. To se skvěle hodí pro sledování sešitů a pozorování změn v nich. .
3
Změňte uspořádání kontingenční tabulky. V kontingenční tabulce lze velmi snadno měnit umístění a pořadí polí. Zkuste jednotlivá pole přetáhnout na různá místa, abyste dostali kontingenční tabulku, která perfektně odpovídá vašim potřebám.
- Právě odsud pochází název „pivot table“, anglický výraz pro kontingenční tabulku. Přesun dat na jiné místo je označovaný "pivoting" („otáčení“), neboť tak měníte směr zobrazení dat.
4
Vytvořte si Kontingenční graf. Můžete jej využít pro dynamické zobrazování grafických reportů. Vytvořit kontingenční graf je hračka, neboť jej lze získat přímo z hotové kontingenční tabulky.

Reklama

Tipy

Když použijete příkaz Importovat data z nabídky Data, získáte více možností, jak data vložit, od databázových spojení MS Office přes soubory Excelu, databáze Accessu, textové soubory, ODBC DSN, webové stránky až po OLAP a XML/XSL. Pak již můžete s daty pracovat stejně, jako by byla z listu v Excelu.
Pokud využíváte funkci Automatický filtr (v nabídce "Data", "Seřadit a filtrovat"), při vytváření kontingenční tabulky ji vypněte. Jakmile kontingenční tabulku vytvoříte, můžete ji zase zapnout.

Reklama