Průvodce kontingenční tabulkou

2.8.3 Průvodce kontingenční tabulkou

Ing. Marek Laurenčík

V minulém dílu jsme si zběžně ukázali tvorbu a jednoduchou práci s kontingenční tabulkou. V tomto dílu si detailně procvičíme a ukážeme průvodce kontingenční tabulkou se všemi jeho možnostmi, následně všechny úpravy potřebné v kontingenční tabulce a nakonec formátování tabulky.

Budeme vycházet z tabulky skladových zásob v souboru 2_8_3_sklad.xls. Tento soubor již byl několikrát procvičován v jiných oblastech. My si na něm velmi detailně ukážeme vše kolem kontingenčních tabulek.

V uvedeném souboru je přehledná tabulka skladových zásob počítačových dílů. U každého dílu je uveden jeho přesný název (Komponenta) a Typ. Dále zde máme uveden Sklad, ve kterém je díl uložen a který Dealer jej má na starosti. U všech dílů máme dále k dispozici počet kusů. Současně je tabulka doplněna cenami jednotlivých dílů, cenami s DPH a prodejní cenou. Pro práci s celkovým objemem zboží na skladě máme v tabulce poslední vypočítávaný sloupec: Cena celkem (kusů x cena)

Chceme celou tabulku zpřehlednit tak, abychom viděli, kolik kusů jednotlivých typů zboží máme na jednotlivých skladech, s možností volby jednotlivých dealerů.

1. Nejprve si otevřeme soubor 2_8_3_sklad.xls. Na listu Sklad máme následující tabulku.

2. Následně spustíme průvodce kontingenční tabulkou pomocí příkazu v menu „Data/Kontingenční tabulka a graf ...“:

Spustí se nám průvodce kontingenční tabulky:

1. V tomto prvním kroku průvodce v horní části volíme údaj, odkud bereme data pro kontingenční tabulku:

• Seznam nebo databáze MS Excel – to je náš případ. Vycházíme z naší tabulky v Excelu, kde máme náš zdroj dat – databázi skladu (zdroj může být i v jiné než otevřené tabulce MS Excel)

• Externí zdroj dat – možnost propojení údajů do kontingenční tabulky z externích dat (např. skladová databáze, personální program, externí databáze přístupná po síti, apod...) – tento příklad budeme podrobně brát v jedné z následujících kapitol.

• Násobné oblasti sloučení – kontingenční tabulka, která vychází z několika stejně strukturovaných tabulek (např. několik stejných tabulek tržeb po měsících...) – uvedeme si na konci této kapitoly

• Jiná kontingenční tabulka nebo graf – doplnění další kontingenční tabulky k již existující

Ve spodní části kontingenční tabulky volíme její typ:

• Kontingenční tabulka – námi požadovaná kontingenční tabulka

• Kontingenční graf – graf kontingenční tabulky – probírán v minulé kapitole

Volíme „ Seznam nebo databáze Microsoft Excel“ a typ „ Kontingenční tabulka“. Pokračujeme tlačítkem „Další >“.

4. Nyní označíme oblast dat pro kontingenční tabulku. Ve většině případů se nám oblast označí automaticky. Někdy však Excel do pole Oblast zapíše název oblasti „Databáze“ :

po pokračování tlačítkem „Další >“ se nám zobrazí následující chybové hlášení:

V tom případě musíme pomocí tlačítka dialogu

nebo tlačítka „Procházet...“ oblast ručně označit.

Nakonec tedy máme označenou oblast dat (ručně nebo automaticky):

A pokračujeme tlačítkem „Další >“.

5. Ve třetím, posledním kroku zvolíme umístění kontingenční tabulky. Tabulku můžeme umístit na nový list (náš případ) nebo na stávající list od příslušné buňky (vhodné pro umístění dat a kontingenční tabulky pod sebe do jednoho listu – např. pro přehlednost v rámci listu, pokud jsou na jiných listech jiná data s kontingenčními tabulkami). V případě volného listu určeného pro kontingenční tabulku volíme tento „existující list“:

Pomocí tlačítka „Rozvržení“ a „Možnosti“ můžete již zde vytvářet kontingenční tabulku a nastavovat její další možnosti – viz následující text.

Průvodce tvorbou kontingenční tabulky ukončíme pomocí tlačítka Dokončit.

• Pomocí myši si přetáhněte pole „Typ“ do prostoru řádkových polí – „Sem přetáhněte řádková pole“ (viz předchozí obrázek – žlutá nápověda):

  

• Následně přetáhněte pole „Sklad“ do prostoru sloupcových polí – „Sem přetáhněte sloupcová pole“:

  

• A nakonec přetáhněte pole „Dealer“ do prostoru stránkových polí – „Sem přetáhněte stránková pole“:

  

• pokud do tabulky přetáhneme pole, ve kterých je text, výsledný souhrn v tabulce bude počet hodnot (např. ks)

• pokud do tabulky přetáhneme pole, ve kterých jsou čísla, bude výsledný souhrn v tabulce součet (SUMA)

• pokud bychom chtěli zobrazit jiné výpočty souhrnu, vložíme do tabulky libovolné hodnoty a následně pomocí nastavení datových položek změníme matematický výraz (viz další lekce).

Pro náš příklad přetáhneme myší pole „Kusů“ dovnitř kontingenční tabulky. Výsledek je následující:

Nyní v této kontingenční tabulce vidíme velice přehledně počet kusů jednotlivých typů zboží, rozdělený podle skladů.

Pro kontrolu naleznete hotovou kontingenční tabulku v souboru 2_8_3_sklad2.xls.

Kontingenční tabulka a násobné oblasti sloučení

Na příkladu 2_8_3_trzby.xls si ukážeme příklad na tvorbu kontingenční tabulky s násobnými oblastmi sloučení.

V tomto příkladu máme měsíční tržby našeho střediska a pomocí kontingenční tabulky chceme vytvořit souhrn dat z jednotlivých měsíců do jednoho souhrnného listu.

Otevřeme si uvedený…