Tato část kurzu podrobně popisuje, jak vytvořit kontingenční tabulku v Excelu. Tento článek byl napsán pro Excel 2007 (stejně jako novější verze). Pokyny pro starší verze Excelu naleznete v samostatném článku: Jak vytvořit kontingenční tabulku v Excelu 2003?
Jako příklad zvažte následující tabulku, která obsahuje údaje o prodeji společnosti za první čtvrtletí roku 2016:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Datum | Ref | Množství | Obchodní zástupce. | Kraj |
| 2 | 01/01/2016 | 2016 - 0001 | $ 819 | Barnes | Sever |
| 3 | 01/01/2016 | 2016 - 0002 | $ 456 | Hnědý | Jižní |
| 4 | 01/01/2016 | 2016 - 0003 | $ 538 | Jones | Jižní |
| 5 | 01/01/2016 | 2016 - 0004 | $ 1,009 | Barnes | Sever |
| 6 | 01/02/2016 | 2016 - 0005 | $ 486 | Jones | Jižní |
| 7 | 01/02/2016 | 2016 - 0006 | $ 948 | kovář | Sever |
| 8 | 01/02/2016 | 2016 - 0007 | $ 740 | Barnes | Sever |
| 9 | 01/03/2016 | 2016 - 0008 | $ 543 | kovář | Sever |
| 10 | 01/03/2016 | 2016 - 0009 | $ 820 | Hnědý | Jižní |
| 11 | ... | ... | ... | ... | ... |
Pro začátek si vytvoříme velmi jednoduchou kontingenční tabulku, která ukáže celkové tržby každého z prodejců podle tabulky výše. Chcete-li to provést, postupujte takto:
- Vyberte libovolnou buňku z rozsahu dat nebo celý rozsah, který chcete použít v kontingenční tabulce.POZOR: Pokud vyberete jednu buňku z rozsahu dat, Excel automaticky zjistí a vybere celý rozsah dat pro kontingenční tabulku. Aby Excel správně vybral rozsah, musí být splněny následující podmínky:
- Každý sloupec v oblasti dat musí mít svůj vlastní jedinečný název;
- Data nesmí obsahovat prázdné řádky.
- Kliknutím na tlačítko souhrnná tabulka (Kontingenční tabulka) v sekci Tabulky záložka (Tabulky). Vložit (Vložit) Stuhy nabídky Excel.
- Na obrazovce se objeví dialogové okno. Vytvořte kontingenční tabulku (Vytvořit kontingenční tabulku), jak je znázorněno na obrázku níže.
Ujistěte se, že vybraný rozsah odpovídá rozsahu buněk, které by měly být použity k vytvoření kontingenční tabulky. Zde můžete také určit, kam se má vytvořená kontingenční tabulka vložit. Můžete vybrat existující list a vložit na něj kontingenční tabulku nebo možnost – Na nový list (Nový pracovní list). klikněte OK. - Zobrazí se prázdná kontingenční tabulka a také panel Pole kontingenční tabulky (Seznam polí kontingenční tabulky) s více datovými poli. Všimněte si, že se jedná o záhlaví z původního datového listu.
- V panelech Pole kontingenční tabulky (Seznam polí kontingenční tabulky):
- přetáhnout Obchodní zástupce. do oblasti Řádky (Řádkové štítky);
- přetáhnout Množství в Hodnoty (Hodnoty);
- Kontrolujeme: in Hodnoty (Hodnoty) musí být hodnota Pole Částka Částka (Součet částky), а не Částka podle pole Částka (Count of Amount).
V tomto příkladu sloupec Množství obsahuje číselné hodnoty, tedy oblast Σ Hodnoty (Σ Hodnoty) budou vybrány jako výchozí Pole Částka Částka (Součet částky). Pokud ve sloupci Množství bude obsahovat nenumerické nebo prázdné hodnoty, pak lze vybrat výchozí kontingenční tabulku Částka podle pole Částka (Count of Amount). Pokud k tomu dojde, můžete změnit množství na částku následovně:
- v Σ Hodnoty (Σ Hodnoty) klikněte na Částka podle pole Částka (Count of Amount) a vyberte možnost Možnosti pole hodnoty (Nastavení hodnotového pole);
- Na kartě Upřesnit Operace (Summarise Values by) vyberte operaci Součet (Součet);
- Klikněte zde OK.
Ujistěte se, že vybraný rozsah odpovídá rozsahu buněk, které by měly být použity k vytvoření kontingenční tabulky. Zde můžete také určit, kam se má vytvořená kontingenční tabulka vložit. Můžete vybrat existující list a vložit na něj kontingenční tabulku nebo možnost – Na nový list (Nový pracovní list). klikněte OK.
Kontingenční tabulka bude vyplněna celkovými hodnotami prodejů pro každého prodejce, jak je znázorněno na obrázku výše.
Pokud chcete zobrazit objemy prodeje v peněžních jednotkách, musíte naformátovat buňky obsahující tyto hodnoty. Nejjednodušší způsob, jak to udělat, je zvýraznit buňky, jejichž formát chcete přizpůsobit, a vybrat formát Měnový sekce (měna). Číslo záložka (Číslo). Domů (Domů) Pás karet nabídky Excel (jak je uvedeno níže).
V důsledku toho bude kontingenční tabulka vypadat takto:
- kontingenční tabulka před nastavením formátu čísla
- kontingenční tabulka po nastavení formátu měny
Upozorňujeme, že výchozí formát měny závisí na nastavení systému.
Doporučené kontingenční tabulky v nejnovějších verzích Excelu
V posledních verzích Excelu (Excel 2013 nebo novější) na Vložit Je přítomno tlačítko (Vložit). Doporučené kontingenční tabulky (Doporučené kontingenční tabulky). Na základě vybraných zdrojových dat tento nástroj navrhne možné formáty kontingenční tabulky. Příklady si můžete prohlédnout na webu Microsoft Office.