Jak vytvořit kontingenční tabulku v Excelu: Výukový program pro začátečníky

Budou chvíle, kdy budete muset analyzovat velké množství dat a vytvářet snadno čitelné a srozumitelné zprávy. Kontingenční tabulky nám umožňují analyzovat taková data a vytvářet sestavy, které splňují naše požadavky na obchodní výkaznictví.

Co je kontingenční tabulka?

Kontingenční tabulka je souhrn velkého souboru dat, který obvykle obsahuje celkové údaje, průměr, minimum, maximum atd. Řekněme, že máte údaje o prodeji pro různé regiony, pomocí kontingenční tabulky můžete shrnout data podle regionů a najít průměrný prodej na region, maximální a minimální prodej na region atd. Kontingenční tabulky nám umožňují analyzovat, sumarizovat a zobrazovat v našich přehledech pouze relevantní data.

Návod na vytváření kontingenčních tabulek krok za krokem

Obrázek níže ukazuje ukázková data prodeje shromážděná z databáze North wind access.

Zde si můžete stáhnout ukázková data Excelu.

Stáhněte si výše uvedený soubor Excel Data File

Jak můžete vidět z obrázku výše, naše tabulka obsahuje mnoho dat. Řekněme, že chceme vytvořit souhrn zákazníků, seskupit všechny jejich objednávky podle produktu a zobrazit množství, jednotkovou cenu a mezisoučty pro všechny transakce.

• Otevřete soubor aplikace Excel, který jste stáhli
• Klikněte na kartu VLOŽIT na pásu karet

• Zobrazí se následující okno

• Klikněte na tlačítko pro výběr tabulky/rozsahu, jak je znázorněno na obrázku výše
• Zobrazí se následující mini okno

• Klikněte na adresu buňky A1
• Stisknutím Ctrl + A na klávesnici vyberte všechny datové buňky
• Vaše zobrazené mini okno nyní vypadá následovně

• Kliknutím na tlačítko Zavřít se vrátíte do okna možností
• Klepněte na tlačítko OK

• Vyberte následující pole
  • Jméno společnosti
  • jméno výrobku
  • Jednotková cena
  • Množství
  • Mezisoučet
• Váš pracovní list by nyní měl vypadat následovně

• Všimněte si, že výše uvedená data byla seskupena podle názvu společnosti zákazníka, názvu produktu, jednotkové ceny, součtu množství a součtu mezisoučtů.
• Všimněte si rozbalovacího tlačítka vedle položky Štítky řádků. Toto tlačítko nám umožňuje třídit/filtrovat naše data. Předpokládejme, že nás to zajímá Alfreds Futterkiste
• Klikněte na rozevírací seznam Row Labels, jak je znázorněno níže

• Odebrat zaškrtnutí z (Vybrat vše)
• vybrat Alfreds Futterkiste
• Klepněte na tlačítko OK
• Získáte následující údaje

2-rozměrné kontingenční tabulky

Dvourozměrná kontingenční tabulka je tabulka, která má pole na řádcích i sloupcích. Řekněme, že chceme uvést jména zaměstnanců jako řádky a pomocí sloupců reprezentovat jména zákazníků a vyplnit buňky celkovými tržbami.

• Aktivujte datový list prodeje
• Klepněte na kartu VLOŽIT
• Klikněte na tlačítko Kontingenční graf a tabulka
• Vyberte všechna data. Excel by si nyní měl pamatovat předchozí rozsah, takže stačí kliknout na tlačítko OK
• Pomocí nástrojů kontingenční tabulky se vytvoří nový list
• Vyberte pole, jak je znázorněno na obrázku níže

Vaše kontingenční tabulka bude nyní vypadat následovně

Vizualizace dat kontingenční tabulky pomocí grafů

S Excelem 2013 nemusíte vytvářet grafy ručně. Excel vytvoří grafy pro vás při vytváření kontingenčních tabulek, změně agregačních funkcí, použití filtrů atd.

Níže uvedený graf byl pro nás automaticky vytvořen z jednoduchého cvičení s kontingenčním grafem, které filtrovalo data AlfredPouze s Futterkiste.

Shrnutí

Kontingenční tabulky a grafy nám umožňují shrnout a analyzovat velké datové sady. Můžeme použít agregační funkce jako SUM, MINIMUM, MAXIMUM, AVERAGE atd. grafy nám automaticky vytváří a aktualizuje excel při práci s daty.