Průvodce VBA po kontingenčních tabulkách

Tento tutoriál ukáže, jak pracovat s kontingenčními tabulkami pomocí VBA.

Kontingenční tabulky jsou nástroje pro sumarizaci dat, které můžete použít k čerpání klíčových přehledů a souhrnů z vašich dat. Podívejme se na příklad: v buňkách A1: D21 máme sadu zdrojových dat, která obsahuje podrobnosti o prodaných produktech, viz níže:

Použití GetPivotData k získání hodnoty

Předpokládejme, že máte kontingenční tabulku s názvem PivotTable1 s prodejem v poli Hodnoty/Data, Produkt jako pole Řádky a Oblast jako pole Sloupce. Metodu PivotTable.GetPivotData můžete použít k vrácení hodnot z kontingenčních tabulek.

Následující kód vrátí z kontingenční tabulky 1 130,00 USD (celkové tržby za východní region):

1	MsgBox ActiveCell.PivotTable.GetPivotData ("Prodej", "Region", "Východ")

V tomto případě je prodej „DataField“, „Field1“ je region a „Item1“ je východ.

Následující kód vrátí 980 $ (celkové tržby za produkt ABC v oblasti Severní) z kontingenční tabulky:

1	MsgBox ActiveCell.PivotTable.GetPivotData („Prodej“, „Produkt“, „ABC“, „Region“, „Sever“)

V tomto případě je prodej „DataField“, „Field1“ je produkt, „Item1“ je ABC, „Field2“ je region a „Item2“ je sever.

Můžete také zahrnout více než 2 pole.

Syntaxe pro GetPivotData je:

GetPivotData (DataField, Pole 1, Položka 1, Pole 2, Položka 2… ) kde:

Parametr	Popis
Datové pole	Datová pole, jako jsou tržby, množství atd., Která obsahují čísla.
Pole 1	Název sloupce nebo řádku v tabulce.
Položka 1	Název položky v poli 1 (volitelně).
Pole 2	Název sloupce nebo řádku v tabulce (volitelně).
Položka 2	Název položky v poli 2 (volitelně).

Vytvoření kontingenční tabulky na listu

Abychom mohli vytvořit kontingenční tabulku na základě výše uvedeného rozsahu dat, v buňce J2 na List1 aktivního sešitu bychom použili následující kód:

1234567891011

Pracovní listy („List1“). Buňky (1, 1). VyberteActiveWorkbook.PivotCaches.Create (SourceType: = xlDatabase, SourceData: = _"Sheet1! R1C1: R21C4", Verze: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet1! R2C10", TableName: = "PivotTable1", DefaultVersion _: = xlPivotTableVersion15Tabulky („List1“). Vyberte

Výsledek je:

Vytvoření kontingenční tabulky na novém listu

Abychom mohli vytvořit kontingenční tabulku na základě výše uvedeného rozsahu dat, na novém listu aktivního sešitu, použili bychom následující kód:

12345678910111213

Pracovní listy („List1“). Buňky (1, 1). VyberteListy.PřidatActiveWorkbook.PivotCaches.Create (SourceType: = xlDatabase, SourceData: = _"Sheet1! R1C1: R21C4", Verze: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet2! R3C1", TableName: = "PivotTable1", DefaultVersion _: = xlPivotTableVersion15Tabulky („List2“). Vyberte

Přidání polí do kontingenční tabulky

Do nově vytvořené kontingenční tabulky s názvem PivotTable1 můžete přidat pole na základě výše uvedeného rozsahu dat. Poznámka: List obsahující vaši kontingenční tabulku musí být aktivní list.

Chcete -li přidat produkt do pole řádků, použijte následující kód:

123	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Product"). Orientation = xlRowFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Product"). Pozice = 1

Chcete -li přidat oblast do pole Sloupce, použijete následující kód:

123	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Orientace = xlColumnFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Poloha = 1

Chcete -li přidat prodej do sekce hodnot s formátem čísla měny, použijte následující kód:

123456789

ActiveSheet.PivotTables ("PivotTable1"). AddDataField ActiveSheet.PivotTables (_„PivotTable1“). PivotFields („Prodej“), „Součet tržeb“, xlSumS ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Součet tržeb").NumberFormat = "$#, ## 0,00"Konec s

Výsledek je:

Změna rozložení sestavy kontingenční tabulky

Rozložení sestavy kontingenční tabulky můžete změnit. Následující kód změní rozložení sestavy kontingenční tabulky na tabulkovou formu:

1	ActiveSheet.PivotTables ("PivotTable1"). TableStyle2 = "PivotStyleLight18"

Odstranění kontingenční tabulky

Kontingenční tabulku můžete odstranit pomocí jazyka VBA. Následující kód odstraní kontingenční tabulku s názvem PivotTable1 na aktivním listu:

12	ActiveSheet.PivotTables ("PivotTable1"). PivotSelect "", xlDataAndLabel, TrueSelection.ClearContents

Naformátujte všechny kontingenční tabulky v sešitu

Všechny kontingenční tabulky v sešitu můžete formátovat pomocí VBA. Následující kód používá strukturu smyčky k procházení všemi listy sešitu a odstranění všech kontingenčních tabulek v sešitu:

12345678910111213

Dílčí formátování AllThePivotTablesInAWorkbook ()Dim wks As WorksheetDim wb jako sešitNastavit wb = ActiveWorkbookDim pt as PivotTableZa každý týden v listechPro každý pt v wks.PivotTablespt.TableStyle2 = "PivotStyleLight15"Další ptDalší týdnyEnd Sub

Chcete -li se dozvědět více o tom, jak používat smyčky ve VBA, klikněte sem.

Odebírání polí kontingenční tabulky

Pole v kontingenční tabulce můžete odebrat pomocí jazyka VBA. Následující kód odebere pole Produkt v sekci Řádky z kontingenční tabulky s názvem PivotTable1 na aktivním listu:

12	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Product"). Orientation = _xl Skryté

Vytvoření filtru

Kontingenční tabulka s názvem PivotTable1 byla vytvořena s produktem v sekci Řádky a prodejem v sekci Hodnoty. Můžete také vytvořit filtr pro kontingenční tabulku pomocí VBA. Následující kód vytvoří filtr podle Regionu v sekci Filtry:

123	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Orientace = xlPageFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Poloha = 1

Chcete -li filtrovat kontingenční tabulku na základě jediné položky sestavy, v tomto případě oblasti Východu, použijete následující kód:

12345

ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). ClearAllFiltersActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). CurrentPage = _"Východní"

Řekněme, že jste chtěli filtrovat kontingenční tabulku na základě více oblastí, v tomto případě na východ a na sever, použijte následující kód:

1234567891011121314

ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Orientace = xlPageFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Poloha = 1ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). _EnableMultiplePageItems = TrueS ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region").PivotItems („South“). Visible = False.PivotItems („West“). Visible = FalseKonec s

Aktualizace kontingenční tabulky

Kontingenční tabulku můžete obnovit ve VBA. Následující kód byste použili k aktualizaci konkrétní tabulky s názvem PivotTable1 ve VBA:

1	ActiveSheet.PivotTables ("PivotTable1"). PivotCache.Refresh