Kontingenční tabulky

Pro kontingenční tabulku je nutné ji nejprve vytvořit a následně přidávat sloupce ze zdrojových dat do jednotlivých polí jako filter, řádky, sloupce a hodnoty:

Kontingenční tabulka bude tvořena na následujících sloupcích:

Tvorba Kontingenční tabulky

Tvorba kontingenční tabulky se tvoří pomocí „Cache“ (mezipaměti) pomocí PivotCaches.Create.

Obecný postup

• Určení zdrojových dat
• Tvorba Cache pro kontingenční tabulku
• Vložení kontingenční tabulky do listu
• Přidání polí do řádků, sloupců a hodnot

Použití

Dim ws as Worksheet

Set ws = ThisWorkbook.Sheets(„Data“) – Deklarace listu

Set rngData = ws.Range(„A1“).CurrentRegion – Dynamické uložení oblasti do proměnné pomocí CurrentRegion

Set ptCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData) – Nastavení Cache pro tvorbu kontingenční tabulky

Set pt = ptCache.CreatePivotTable(TableDestination:=ws.Range(„H3″), TableName:=“MojePivotka“) – Vložení pivotky do stejného listu ale do sloupce „H“ s názvem „MojePivotka“.

Přidání pole do řádků Kontingenční tabulky

Řádková pole definují, jak budou data zobrazena po řádcích. V klasickém Excelu by to znamenalo přesunutí sloupce dat do pole „Řádky“ (Rows).

Použití

pt.PivotFields(„Typ kurzu“).Orientation = xlRowField – Přidání prvního pole do pivotky.

pt.PivotFields(„Typ kurzu“).Position = 1 – Pole bude jako první oblast v pivotce.

pt.PivotFields(„Název kurzu“).Orientation = xlRowField – Přidání druhého pole do pivotky.

pt.PivotFields(„Název kurzu“).Position = 2 – Pole bude jako druhá oblast

Výsledná pivotka v polích bude vypadat následovně:

Je možné použít možnost With pro pt.PivotFileds, aby nemusel být psán před všemi příkazy jako Orientation a Position.

Praktické vysvětlení

Orientation = xlRowField – přidá pole (sloupce) do řádkové oblasti kontingenční tabulky.

Position = 1 – nastaví pole jako první řádkové pole. V kontingenční tabulce je možné vkládat více polí do řádkové oblasti pro detailnější rozpad kontingenční tabulky. Pokud by mělo být vloženo další pole pod dané pole, použil by se příkaz Position = 2 atd.

Přidání pole do sloupců Kontingenční tabulky

Sloupcové pole definují, jak budou data zobrazena po sloupcích. V klasickém Excelu by to znamenalo přesunutí sloupce dat do pole „Sloupce“ (Columns).

Použití

pt.PivotFields(„Typ zákazníka“).Orientation = xlColumnField – Přidání prvního sloupcového pole do pivotky.

pt.PivotFields(„Typ zákazníka“).Position = 1– Pole bude jako první sloupcová oblast v pivotce.

Výsledná pivotka v sloupcových polích bude vypadat následovně:

Je možné použít možnost With pro pt.PivotFileds, aby nemusel být psán před všemi příkazy jako Orientation a Position.

Praktické vysvětlení

Orientation = xlColumnField – přidá pole (sloupce) do sloupcové oblasti kontingenční tabulky.

Position = 1 – nastaví pole jako první sloupcové pole. V kontingenční tabulce je možné vkládat více polí do sloupcové oblasti pro detailnější rozpad kontingenční tabulky. Pokud by mělo být vloženo další pole pod dané pole, použil by se příkaz Position = 2 atd.

Přidání pole hodnot do Kontingenční tabulky

Hodnotová pole definují, co se má počítat, zdali součet, průměr, počet a další. V klasickém Excelu by to znamenalo přesunutí sloupce dat do pole „Hodnoty“ (Values). Autoamticky je přidána hodnota sumy daného sloupce. Pokud je potřebné změnit sumu na jinou veličinu, je nutné nejprve dát daný příkaz do proměnné.

Použití

Do pivotky bude sloupce „Cena“ vložen do hodnot se sumou a s formátováním měny.

pt.PivotFields(„Cena“).Orientation = xlDataField – Automaticky bude přidána suma hodnot

pt.PivotFields(„Cena“).NumberFormat = „#,##0 Kč“ – Formát měny

Výsledná pivotka v polích bude vypadat následovně:

Pokud nemá být přidána suma, ale jiná veličina, např. počet, kód by vypadal následovně:

Set df = pt.AddDataField(pt.PivotFields(„Cena“), „Celková Cena“) – Přidáno jako součet

df.Function = xlCount – Změna na počet bez přidání dalšího hodnotového pole

Je možné použít možnost With pro pt.PivotFileds, aby nemusel být psán před všemi příkazy jako Orientation, NumberFormat a Position.

Pokud je nutné přidat další pole do hodnot, nepoužívá se příkaz Position jako u sloupců, řádků nebo filtrů, ale opět je nutné využít příkaz DataField. Pokud je potřebné mít více polí (sloupců) v hodnotách, je nutné použít příkaz AddDataField. Pokud by měl být přidán sloupec „Počet“ do hodnot v tabulce, vypadal by kód následovně:

pt.AddDataField pt.PivotFields(„Počet“), „Počet Prodejů“, xlCount – Přidání počtu hodnot ze sloupce „Počet“, který bude v kontingenční tabulce nazván jako „Počet Prodejů“. V Excelu by tah hodnotové pole po přidání dalšího pole vypadalo náslevoně:

Praktické vysvětlení

Orientation = xlDataField – přidá pole do hodnot automaticky se sumou

NumberFormat = „#,##0 Kč“ – nastaví formát na měnu.

Další možnosti function kromě sumy

Konstantní název	Popis
xlSum	Součet číselných hodnot (výchozí pro čísla).
xlCount	Počet neprázdných buněk (funguje i na text).
xlAverage	Průměr číselných hodnot.
xlMax	Maximální hodnota v oblasti.
xlMin	Minimální hodnota v oblasti.
xlProduct	Součin všech hodnot.
xlCountNums	Počet číselných hodnot (nepočítá text).
xlStdDev	Směrodatná odchylka vzorku.
xlStdDevP	Směrodatná odchylka celé populace.
xlVar	Rozptyl vzorku.
xlVarP	Rozptyl celé populace.

Přidání pole filtru do kontingenční tabulky

Filtrové pole definuje možnost filtru kontingenční tabulky na základě stanoveného pole. V klasickém Excelu by to znamenalo přesunutí sloupce dat do pole „Filtry“ (Filters).

Použití

pt.PivotFields(„Datum“).Orientation = xlPageField – Přidání prvního filtru do pole do pivotky.

pt.PivotFields(„Datum“).Position = 1– Pole bude jako první filtrovaná oblast v pivotce.

Výsledná pivotka ve filtrovaném poli bude vypadat následovně:

Je možné použít možnost With pro pt.PivotFileds, aby nemusel být psán před všemi příkazy jako Orientation a Position.

Praktické vysvětlení

Orientation = xlPageField – přidá pole (sloupce) do filtrované oblasti kontingenční tabulky.

Position = 1 – nastaví pole jako první filtrované pole. V kontingenční tabulce je možné vkládat více polí do filtrované oblasti pro detailnější filtr kontingenční tabulky. Pokud by mělo být vloženo další pole pod dané pole, použil by se příkaz Position = 2 atd.

Další příkazy pro kontingenční tabulky

Příkaz	Popis
.ClearTable	Odstraní všechna pole z kontingenční tabulky.
.RefreshTable	Aktualizuje data v kontingenční tabulce.
.ShowPages	Vytvoří samostatné listy pro každou hodnotu ve vybraném poli.
.DataBodyRange	Odkazuje na oblast s daty v kontingenční tabulce.