VBA Pivot tabelite juhend

See õpetus näitab, kuidas VBA abil liigendtabelitega töötada.

Pivot -tabelid on andmete kokkuvõtmise tööriistad, mille abil saate oma andmetest põhiteavet ja kokkuvõtteid teha. Vaatame näidet: lahtrites A1: D21 on lähteandmete kogum, mis sisaldab müüdud toodete üksikasju, mis on näidatud allpool:

GetPivotData kasutamine väärtuse saamiseks

Oletame, et teil on PivotTable -liigendtabel nimega PivotTable1, mille väljal Väärtused/Andmed on müük, väljal Toode rida ja väljal Veerud piirkond. PivotTable.GetPivotData meetodit saate kasutada pöördtabelite väärtuste tagastamiseks.

Järgmine kood tagastab PivotTable -liigendtabelist 1 130,00 dollarit (idapiirkonna kogumüük):

1	MsgBox ActiveCell.PivotTable.GetPivotData ("Müük", "Piirkond", "Ida")

Sel juhul on Müük „Andmeväli”, „Väli1” on Piirkond ja „Üksus1” on Ida.

Järgmine kood tagastab pöördtabelist 980 dollarit (toote ABC kogumüük põhjapiirkonnas):

1	MsgBox ActiveCell.PivotTable.GetPivotData ("Müük", "Toode", "ABC", "Piirkond", "Põhja")

Sel juhul on Müük „Andmeväli”, „Väli1” on Toode, „Toode1” on ABC, „Väli2” on Piirkond ja „Toode2” on Põhja.

Samuti saate lisada rohkem kui 2 välja.

GetPivotData süntaks on järgmine:

GetPivotData (Andmeväli, Väli1, Punkt 1, 2. väli, Punkt2… ) kus:

Parameeter	Kirjeldus
Andmeväli	Andmeväli, näiteks müük, kogus jne, mis sisaldab numbreid.
1. väli	Tabeli veeru või rea välja nimi.
Punkt 1	Üksuse nimi väljal 1 (valikuline).
2. väli	Tabeli veeru või rea välja nimi (valikuline).
Punkt 2	Üksuse nimi väljal 2 (valikuline).

Pöördtabeli loomine lehel

Pivot -tabeli loomiseks ülaltoodud andmevahemiku alusel, aktiivse töövihiku lehe 1 lahtrisse J2, kasutaksime järgmist koodi:

1234567891011

Töölehed ("Sheet1"). Lahtrid (1, 1). ValigeActiveWorkbook.PivotCaches.Create (SourceType: = xlDatabase, SourceData: = _"Sheet1! R1C1: R21C4", versioon: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet1! R2C10", TableName: = "PivotTable1", DefaultVersion _: = xlPivotTableVersion15Sheets ("Sheet1"). Valige

Tulemuseks on:

Pöördtabeli loomine uuel lehel

Pivot -tabeli loomiseks ülaltoodud andmevahemiku alusel, aktiivse töövihiku uuel lehel, kasutaksime järgmist koodi:

12345678910111213

Töölehed ("Sheet1"). Lahtrid (1, 1). ValigeLehed. LisaActiveWorkbook.PivotCaches.Create (SourceType: = xlDatabase, SourceData: = _"Sheet1! R1C1: R21C4", versioon: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet2! R3C1", TableName: = "PivotTable1", DefaultVersion _: = xlPivotTableVersion15Arvutustabelid ("Leht2"). Valige

Väljade lisamine pöördetabelisse

Ülaltoodud andmevahemiku põhjal saate äsja loodud liigendtabelisse väljad lisada väljad PivotTable1. Märkus. Pivot -tabelit sisaldav leht peab olema aktiivne leht.

Toote väljale ridade lisamiseks kasutage järgmist koodi:

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

Piirkonna lisamiseks veeruväljale kasutage järgmist koodi:

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

Valuuta sektsiooni Müük valuutanumbri vormingus lisamiseks kasutage järgmist koodi:

123456789

ActiveSheet.PivotTables ("PivotTable1"). AddDataField ActiveSheet.PivotTables (_"PivotTable1"). PivotFields ("Müük"), "Müügi summa", xlSumActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Müügi summa").NumberFormat = "$#, ## 0.00"Lõpeta

Tulemuseks on:

Pivot tabeli aruande paigutuse muutmine

Saate muuta oma liigendtabeli aruande paigutust. Järgmine kood muudab teie liigendtabeli aruande paigutuse tabelivormiks:

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

Pivot tabeli kustutamine

Pivot tabeli saate kustutada VBA abil. Järgmine kood kustutab Pivot tabeli nimega PivotTable1 aktiivsel lehel:

12	ActiveSheet.PivotTables ("PivotTable1"). PivotSelect "", xlDataAndLabel, TrueValik. Selge sisu

Vormindage kõik töövihiku liigendtabelid

VBA abil saate vormindada kõik töövihiku liigendtabelid. Järgmine kood kasutab tsüklistruktuuri, et sirvida läbi kõik töövihiku lehed ja kustutada kõik töövihiku liigendtabelid:

12345678910111213

AlamvormingAllThePivotTablesInAWorkbook ()Dim wks kui töölehtDim wb TöövihikunaMäära wb = ActiveWorkbookDim pt PivotTable -liigendtabelinaIga nädala jaoks wb. LehedIga pt nädala kohta. PivotTable -liigendtabelidpt.TableStyle2 = "PivotStyleLight15"Järgmine ptJärgmised nädaladEnd Sub

Lisateavet silmuste kasutamise kohta VBA -s klõpsake siin.

Pöördtabeli väljade eemaldamine

Pivottabeli väljad saate eemaldada VBA abil. Järgmine kood eemaldab aktiivse lehe Pivot -tabeli nimega PivotTable1 jaotise Ridad tootevälja:

12	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Toode"). Orientatsioon = _xlPelatud

Filtri loomine

Pivot -tabel nimega PivotTable1 on loodud jaotises Toode ridadega ja jaotises Väärtused müük. VBA abil saate luua ka oma liigendtabeli filtri. Järgmine kood loob filtri, mis põhineb jaotises Filtrid piirkonnal:

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

Pivot -tabeli filtreerimiseks ühe aruandeüksuse alusel, antud juhul idapiirkonnas, kasutage järgmist koodi:

12345

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

Oletame, et soovite filtreerida oma liigendtabelit mitme piirkonna (antud juhul ida ja põhja) põhjal, kasutades järgmist koodi:

1234567891011121314

ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Orientation = xlPageFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Position = 1ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). _EnableMultiplePageItems = TõsiActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region").PivotItems ("Lõuna"). Nähtav = vale.PivotItems ("Lääs"). Nähtav = valeLõpeta

Pivot tabeli värskendamine

Pivot -tabelit saate värskendada VBA -s. Konkreetse tabeli nimega PivotTable1 VBA -s värskendamiseks kasutaksite järgmist koodi:

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