Sprievodca VBA po kontingenčných tabuľkách

Tento tutoriál vám ukáže, ako pracovať s kontingenčnými tabuľkami pomocou jazyka VBA.

Kontingenčné tabuľky sú nástroje na zhrnutie údajov, ktoré môžete použiť na čerpanie kľúčových prehľadov a súhrnov z vašich údajov. Pozrime sa na príklad: zdrojový súbor údajov máme v bunkách A1: D21 obsahujúci podrobnosti o predaných výrobkoch, uvedené nižšie:

Použitie GetPivotData na získanie hodnoty

Predpokladajme, že máte kontingenčnú tabuľku s názvom Kontingenčná tabuľka1 s tržbami v poli Hodnoty/údaje, Produkt ako poľom Riadky a Regiónom ako poľom Stĺpce. Na vrátenie hodnôt z kontingenčných tabuliek môžete použiť metódu PivotTable.GetPivotData.

Nasledujúci kód vráti z kontingenčnej tabuľky 1 130,00 USD (celkové tržby za región Východ):

1	MsgBox ActiveCell.PivotTable.GetPivotData („Predaj“, „Región“, „Východ“)

V tomto prípade je predaj „DataField“, „Field1“ je región a „Item1“ je východ.

Nasledujúci kód vráti z kontingenčnej tabuľky 980 dolárov (celkové tržby za produkt ABC v severnom regióne):

1	MsgBox ActiveCell.PivotTable.GetPivotData („Predaj“, „Produkt“, „ABC“, „Región“, „Sever“)

V tomto prípade je predaj „DataField“, „Field1“ je produkt, „Item1“ je ABC, „Field2“ je región a „Item2“ je sever.

Môžete tiež zahrnúť viac ako 2 polia.

Syntax pre GetPivotData je:

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

Parameter	Popis
Dátové pole	Dátové pole, ako je predaj, množstvo atď., Ktoré obsahuje čísla.
Pole 1	Názov stĺpca alebo riadka v tabuľke.
Položka 1	Názov položky v poli 1 (voliteľné).
Pole 2	Názov stĺpca alebo riadka v tabuľke (voliteľné).
Položka 2	Názov položky v poli 2 (voliteľné).

Vytvorenie kontingenčnej tabuľky na hárku

Na vytvorenie kontingenčnej tabuľky na základe vyššie uvedeného rozsahu údajov v bunke J2 v hárku 1 aktívneho zošita by sme použili nasledujúci kód:

1234567891011

Pracovné listy („List1“). Bunky (1, 1). VyberteActiveWorkbook.PivotCaches.Create (SourceType: = xlDatabase, SourceData: = _"List1! R1C1: R21C4", verzia: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet1! R2C10", TableName: = "PivotTable1", DefaultVersion _: = xlPivotTableVersion15Tabuľky („List1“). Vyberte

Výsledkom je:

Vytvorenie kontingenčnej tabuľky na novom hárku

Na vytvorenie kontingenčnej tabuľky na základe vyššie uvedeného rozsahu údajov na novom hárku aktívneho zošita by sme použili nasledujúci kód:

12345678910111213

Pracovné listy („List1“). Bunky (1, 1). VyberteListy. PridaťActiveWorkbook.PivotCaches.Create (SourceType: = xlDatabase, SourceData: = _"List1! R1C1: R21C4", verzia: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet2! R3C1", TableName: = "PivotTable1", DefaultVersion _: = xlPivotTableVersion15Tabuľky („List2“). Vyberte

Pridávanie polí do kontingenčnej tabuľky

Do novovytvorenej kontingenčnej tabuľky s názvom PivotTable1 môžete pridať polia na základe vyššie uvedeného rozsahu údajov. Poznámka: Hárok obsahujúci vašu kontingenčnú tabuľku musí byť aktívny list.

Na pridanie produktu do poľa riadkov by ste použili nasledujúci kód:

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

Ak chcete do poľa stĺpcov pridať oblasť, použili by ste nasledujúci kód:

123	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Orientácia = xlColumnFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Poloha = 1

Na pridanie predaja do sekcie hodnôt s formátom čísla meny by ste použili nasledujúci kód:

123456789

ActiveSheet.PivotTables ("PivotTable1"). AddDataField ActiveSheet.PivotTables (_„PivotTable1“). PivotFields („Predaj“), „Suma tržieb“, xlSumS ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Suma tržieb").NumberFormat = "$#, ## 0,00"Ukončiť s

Výsledkom je:

Zmena rozloženia zostavy kontingenčnej tabuľky

Rozloženie zostavy kontingenčnej tabuľky môžete zmeniť. Nasledujúci kód zmení rozloženie zostavy vašej kontingenčnej tabuľky na tabuľkovú formu:

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

Odstránenie kontingenčnej tabuľky

Kontingenčnú tabuľku môžete odstrániť pomocou VBA. Nasledujúci kód odstráni kontingenčnú tabuľku s názvom PivotTable1 na aktívnom hárku:

12	ActiveSheet.PivotTables ("PivotTable1"). PivotSelect "", xlDataAndLabel, TrueVýber. Jasný obsah

Naformátujte všetky kontingenčné tabuľky v zošite

Všetky kontingenčné tabuľky v zošite môžete naformátovať pomocou jazyka VBA. Nasledujúci kód používa štruktúru slučky na to, aby sa mohol prepínať medzi všetkými listami zošita a odstrániť všetky kontingenčné tabuľky v zošite:

12345678910111213

PodformátovanieVšetkyThePivotTablesInAWorkbook ()Dim wks As worksheetDim wb ako zošitNastaviť wb = ActiveWorkbookDim pt as PivotTablePre každý týždeň v listochPre každý pt In wks.PivotTablespt.TableStyle2 = "PivotStyleLight15"Ďalší bodDalsie tyzdneKoniec pod

Kliknite sem, ak sa chcete dozvedieť viac o tom, ako používať slučky vo VBA.

Odstránenie polí kontingenčnej tabuľky

Polia v kontingenčnej tabuľke môžete odstrániť pomocou VBA. Nasledujúci kód odstráni pole Produkt v sekcii Riadky z kontingenčnej tabuľky s názvom PivotTable1 v aktívnom hárku:

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

Vytvorenie filtra

Kontingenčná tabuľka s názvom PivotTable1 bola vytvorená s položkou Produkt v sekcii Riadky a Predaj v sekcii Hodnoty. Môžete tiež vytvoriť filter pre svoju kontingenčnú tabuľku pomocou jazyka VBA. Nasledujúci kód vytvorí filter podľa oblasti v sekcii Filtre:

123	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Orientácia = xlPageFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Poloha = 1

Na filtrovanie kontingenčnej tabuľky na základe jedinej položky zostavy v tomto prípade v regióne východ by ste použili nasledujúci kód:

12345

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

Povedzme, že ste chceli filtrovať svoju kontingenčnú tabuľku na základe viacerých oblastí, v tomto prípade na východ a na sever, použili by ste nasledujúci kód:

1234567891011121314

ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Orientácia = xlPageFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Poloha = 1ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). _EnableMultiplePageItems = TrueS ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region").PivotItems („Juh“). Visible = False.PivotItems („West“). Visible = FalseUkončiť s

Obnovuje sa vaša kontingenčná tabuľka

Kontingenčnú tabuľku môžete aktualizovať vo VBA. Nasledujúci kód by ste použili na aktualizáciu konkrétnej tabuľky s názvom PivotTable1 vo VBA:

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