VBA Vodič za zaokretne tablice

Ovaj će vodič pokazati kako raditi s zaokretnim tablicama koristeći VBA.

Zaokretne tablice su alati za sažimanje podataka koje možete koristiti za izvlačenje ključnih uvida i sažetaka iz svojih podataka. Pogledajmo primjer: imamo izvorne skupove podataka u ćelijama A1: D21 koji sadrže detalje o prodanim proizvodima, prikazane u nastavku:

Korištenje GetPivotData za dobivanje vrijednosti

Pretpostavimo da imate zaokretnu tablicu pod nazivom zaokretna tablica1 s prodajom u polju Vrijednosti/Podaci, Proizvod kao polje Redovi i Regija kao polje Stupci. Za vraćanje vrijednosti iz zaokretnih tablica možete koristiti metodu zaokretne tablice.GetPivotData.

Sljedeći kôd će iz zaokretne tablice vratiti 1.130,00 USD (ukupna prodaja za istočnu regiju):

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

U ovom slučaju, prodaja je “Polje podataka”, “Polje1” je regija, a “Stavka1” je istok.

Sljedeći kôd će iz zaokretne tablice vratiti 980 USD (ukupna prodaja proizvoda ABC u sjevernoj regiji):

1	MsgBox ActiveCell.PivotTable.GetPivotData ("Prodaja", "Proizvod", "ABC", "Regija", "Sjever")

U ovom slučaju, prodaja je “Polje podataka”, “Polje1” je proizvod, “Stavka1” je ABC, “Polje2” je regija, a “Stavka2” je sjever.

Također možete uključiti više od 2 polja.

Sintaksa za GetPivotData je:

GetPivotData (Polje podataka, Polje1, Stavka 1, Polje 2, Stavka 2… ) gdje:

Parametar	Opis
Podatkovno polje	Podatkovno polje poput prodaje, količine itd. Koje sadrži brojeve.
Polje 1	Naziv stupca ili polja retka u tablici.
Stavka 1	Naziv stavke u polju 1 (izborno).
Polje 2	Naziv stupca ili polja retka u tablici (izborno).
Stavka 2	Naziv stavke u polju 2 (izborno).

Stvaranje zaokretne tablice na listu

Kako bismo stvorili zaokretnu tablicu na temelju gornjeg raspona podataka, u ćeliji J2 na listu 1 aktivne radne knjige koristili bismo sljedeći kôd:

1234567891011

Radni listovi ("List1"). Ćelije (1, 1) .OdaberiteActiveWorkbook.PivotCaches.Create (Izvorni tip: = xlDatabase, SourceData: = _"List1! R1C1: R21C4", Verzija: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet1! R2C10", TableName: = "PivotTable1", DefaultVersion _: = xlPivotTableVersion15Listovi ("List 1"). Odaberite

Rezultat je:

Stvaranje zaokretne tablice na novom listu

Kako bismo stvorili zaokretnu tablicu na temelju gornjeg raspona podataka, na novom listu, aktivne radne knjige, upotrijebili bismo sljedeći kôd:

12345678910111213

Radni listovi ("List1"). Ćelije (1, 1) .OdaberiteListovi.DodajActiveWorkbook.PivotCaches.Create (Izvorni tip: = xlDatabase, SourceData: = _"List1! R1C1: R21C4", Verzija: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet2! R3C1", TableName: = "PivotTable1", DefaultVersion _: = xlPivotTableVersion15Listovi ("List 2"). Odaberite

Dodavanje polja u zaokretnu tablicu

Možete dodati polja u novostvorenu zaokretnu tablicu pod nazivom zaokretna tablica1 na temelju gornjeg raspona podataka. Napomena: List koji sadrži vašu zaokretnu tablicu mora biti aktivni list.

Da biste dodali Proizvod u polje redaka, upotrijebili biste sljedeći kôd:

123	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Product"). Orijentacija = xlRowFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Product"). Pozicija = 1

Da biste dodali Regiju u polje Stupci, upotrijebili biste sljedeći kôd:

123	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Orijentacija = xlColumnFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Pozicija = 1

Za dodavanje prodaje u odjeljak vrijednosti s formatom broja valute upotrijebili biste sljedeći kôd:

123456789

ActiveSheet.PivotTables ("Zaokretna tablica1"). AddDataField ActiveSheet.PivotTables (_"Zaokretna tablica1"). Zaokretna polja ("Prodaja"), "Zbir prodaje", xlSumS ActiveSheet.PivotTables ("Zaokretna tablica1"). Zaokretna polja ("Zbir prodaje").NumberFormat = "$#, ## 0.00"Završi s

Rezultat je:

Promjena izgleda izvješća zaokretne tablice

Možete promijeniti izgled izvješća svoje zaokretne tablice. Sljedeći kôd promijenit će izgled izvješća vaše zaokretne tablice u tablični obrazac:

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

Brisanje zaokretne tablice

Zaokretnu tablicu možete izbrisati pomoću VBA. Sljedeći kôd izbrisat će zaokretnu tablicu pod nazivom zaokretna tablica1 na aktivnom listu:

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

Formatirajte sve zaokretne tablice u radnoj knjizi

Pomoću VBA možete oblikovati sve zaokretne tablice u radnoj knjizi. Sljedeći kôd koristi strukturu petlje kako bi prošao kroz sve listove radne knjige i izbrisao sve zaokretne tablice u radnoj knjizi:

12345678910111213

PodoblikovanjeAllThePivotTablesInAWorkbook ()Zatamni tjedni kao radni listDim wb Kao radna bilježnicaPostavite wb = ActiveWorkbookDim pt kao zaokretnu tablicuZa svaki tjedan U wb.SheetsZa svaku točku U tv. Zaokretne tablicept.TableStyle2 = "PivotStyleLight15"Sljedeća točkaSljedeći tjedniKraj podm

Kliknite ovdje ako želite saznati više o upotrebi petlji u VBA -i.

Uklanjanje polja zaokretne tablice

Pomoću VBA -a možete ukloniti polja u zaokretnoj tablici. Sljedeći kôd uklonit će polje Proizvod u odjeljku Redovi iz zaokretne tablice pod nazivom PivotTable1 u aktivnom listu:

12	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Product"). Orijentacija = _xlSkriveno

Izrada filtra

Zaokretna tablica pod nazivom PivotTable1 stvorena je s proizvodom u odjeljku Redovi i prodajom u odjeljku vrijednosti. Također možete stvoriti filtar za zaokretnu tablicu pomoću VBA. Sljedeći kôd će stvoriti filtar na temelju regije u odjeljku Filtri:

123	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Orijentacija = xlPageFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Pozicija = 1

Za filtriranje zaokretne tablice na temelju jedne stavke izvješća, u ovom slučaju istočne regije, upotrijebili biste sljedeći kôd:

12345

ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). ClearAllFiltersActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). CurrentPage = _"Istočno"

Recimo da ste htjeli filtrirati zaokretnu tablicu na temelju više regija, u ovom slučaju Istok i Sjever, upotrijebili biste sljedeći kôd:

1234567891011121314

ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Orijentacija = xlPageFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Pozicija = 1ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). _EnableMultiplePageItems = IstinaS ActiveSheet.PivotTables ("Zaokretna tablica1"). Zaokretna polja ("Regija").Zaporne stavke ("jug"). Vidljivo = Netačno.Zaporne stavke ("Zapad"). Vidljivo = NetačnoZavrši s

Osvježavanje zaokretne tablice

Svoju zaokretnu tablicu možete osvježiti u VBA. Upotrijebili biste sljedeći kôd kako biste osvježili određenu tablicu pod nazivom Zaokretna tablica1 u VBA -i:

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