VBA Vodnik po vrtilnih tabelah

Ta vadnica bo pokazala, kako delati z vrtilnimi tabelami z uporabo VBA.

Vrtilne tabele so orodja za povzemanje podatkov, ki jih lahko uporabite za pridobivanje ključnih vpogledov in povzetkov iz svojih podatkov. Poglejmo primer: v celicah A1: D21 imamo nabor izvornih podatkov, ki vsebuje podrobnosti o prodanih izdelkih, prikazane spodaj:

Uporaba GetPivotData za pridobitev vrednosti

Predpostavimo, da imate vrtilno tabelo, imenovano vrtilna tabela1 s prodajo v polju Vrednosti/Podatki, Izdelek kot polje Vrstice in Regija kot polje Stolpci. Za vrnitev vrednosti iz vrtilnih tabel lahko uporabite metodo PivotTable.GetPivotData.

Naslednja koda bo iz vrtilne tabele vrnila 1.130,00 USD (skupna prodaja za vzhodno regijo):

1	MsgBox ActiveCell.PivotTable.GetPivotData ("Prodaja", "Regija", "Vzhod")

V tem primeru je prodaja »podatkovno polje«, »polje1« je regija in »postavka1« je vzhod.

Naslednja koda bo iz vrtilne tabele vrnila 980 USD (skupna prodaja izdelka ABC v severni regiji):

1	MsgBox ActiveCell.PivotTable.GetPivotData ("Prodaja", "Izdelek", "ABC", "Regija", "Sever")

V tem primeru je prodaja »podatkovno polje«, »polje1« je izdelek, »postavka1« je ABC, »polje2« je regija in »postavka2« je sever.

Vključite lahko tudi več kot 2 polja.

Sintaksa za GetPivotData je:

GetPivotData (DataField, Polje 1, Postavka 1, Polje 2, Postavka 2… ) kje:

Parameter	Opis
Podatkovno polje	Podatkovno polje, kot je prodaja, količina itd., Ki vsebuje številke.
Polje 1	Ime stolpca ali polja vrstice v tabeli.
Postavka 1	Ime predmeta v polju 1 (izbirno).
Polje 2	Ime stolpca ali polja vrstice v tabeli (izbirno).
2. točka	Ime predmeta v polju 2 (izbirno).

Ustvarjanje vrtilne tabele na listu

Za ustvarjanje vrtilne tabele na podlagi zgornjega obsega podatkov v celici J2 na listu 1 aktivnega delovnega zvezka bi uporabili naslednjo kodo:

1234567891011

Delovni listi ("List1"). Celice (1, 1) .IzberiActiveWorkbook.PivotCaches.Create (SourceType: = xlDatabase, SourceData: = _"List1! R1C1: R21C4", Različica: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet1! R2C10", TableName: = "PivotTable1", DefaultVersion _: = xlPivotTableVersion15Listi ("List1"). Izberite

Rezultat je:

Ustvarjanje vrtilne tabele na novem listu

Za ustvarjanje vrtilne tabele na podlagi zgornjega obsega podatkov na novem listu aktivnega delovnega zvezka bi uporabili naslednjo kodo:

12345678910111213

Delovni listi ("List1"). Celice (1, 1) .IzberiListi.DodajActiveWorkbook.PivotCaches.Create (SourceType: = xlDatabase, SourceData: = _"List1! R1C1: R21C4", Različica: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet2! R3C1", TableName: = "PivotTable1", DefaultVersion _: = xlPivotTableVersion15Listi ("List2"). Izberite

Dodajanje polj v vrtilno tabelo

Na novo ustvarjeni vrtilni tabeli, imenovani vrtilna tabela1, lahko dodate polja na podlagi zgornjega obsega podatkov. Opomba: List, ki vsebuje vašo vrtilno tabelo, mora biti aktivni list.

Če želite v polje z vrsticami dodati izdelek, uporabite naslednjo kodo:

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

Če želite dodati regijo v polje stolpcev, uporabite naslednjo kodo:

123	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Orientation = xlColumnFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Position = 1

Za dodajanje prodaje v razdelek Vrednosti z obliko številke valute uporabite naslednjo kodo:

123456789

ActiveSheet.PivotTables ("PivotTable1"). AddDataField ActiveSheet.PivotTables (_"PivotTable1"). PivotFields ("Sales"), "Sum of Sales", xlSumZ ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Sum of Sales").NumberFormat = "$#, ## 0.00"Končaj s

Rezultat je:

Spreminjanje postavitve poročila vrtilne tabele

Postavitev poročila svoje vrtilne tabele lahko spremenite. Naslednja koda bo spremenila postavitev poročila vaše vrtilne tabele v tabelarni obrazec:

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

Brisanje vrtilne tabele

Vrtilno tabelo lahko izbrišete z uporabo VBA. Naslednja koda bo izbrisala vrtilno tabelo z imenom vrtilna tabela1 na aktivnem listu:

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

Oblikujte vse vrtilne tabele v delovnem zvezku

Vse vrtilne tabele v delovnem zvezku lahko oblikujete z uporabo VBA. Naslednja koda uporablja strukturo zanke za prelistavanje vseh listov delovnega zvezka in brisanje vseh vrtilnih tabel v delovnem zvezku:

12345678910111213

PodoblikovanjeAllThePivotTablesInAWorkbook ()Zatemni tedne kot delovni listDim wb Kot delovni zvezekNastavite wb = ActiveWorkbookDim pt kot vrtilno tabeloZa vsak teden v wb.SheetsZa vsako točko V tednih. Vrtilne tabelept.TableStyle2 = "PivotStyleLight15"Naslednja točkaNaslednji tedniEnd Sub

Če želite izvedeti več o uporabi zank v VBA, kliknite tukaj.

Odstranjevanje polj vrtilne tabele

Polja v vrtilni tabeli lahko odstranite z uporabo VBA. Naslednja koda bo odstranila polje Izdelek v razdelku Vrstice iz vrtilne tabele z imenom Vrtilna tabela1 v aktivnem listu:

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

Ustvarjanje filtra

Vrtilna tabela z imenom PivotTable1 je bila ustvarjena z izdelkom v razdelku vrstice in prodajo v razdelku vrednosti. S pomočjo VBA lahko ustvarite tudi filter za vrtilno tabelo. Naslednja koda bo ustvarila filter na podlagi regije v razdelku Filtri:

123	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Orientation = xlPageFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Position = 1

Če želite filtrirati vrtilno tabelo na podlagi ene postavke poročila, v tem primeru na vzhodu, bi uporabili naslednjo kodo:

12345

ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). ClearAllFiltersActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). CurrentPage = _"Vzhod"

Recimo, da ste želeli filtrirati svojo vrtilno tabelo glede na več regij, v tem primeru na vzhodu in severu, bi uporabili naslednjo kodo:

1234567891011121314

ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Orientation = xlPageFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Position = 1ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). _EnableMultiplePageItems = TrueZ ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region").Vrstni elementi ("jug"). Vidno = napačno.PivotItems ("West"). Visible = FalseKončaj s

Osveževanje vrtilne tabele

Svojo vrtilno tabelo lahko osvežite v VBA. Za osvežitev posebne tabele, imenovane vrtilna tabela1, v VBA uporabite naslednjo kodo:

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