Ghid VBA pentru tabelele pivot

Acest tutorial va arăta cum să lucrați cu tabele pivot folosind VBA.

Tabelele pivot sunt instrumente de rezumare a datelor pe care le puteți utiliza pentru a desena informații și rezumate cheie din datele dvs. Să vedem un exemplu: avem un set de date sursă în celulele A1: D21 care conține detaliile produselor vândute, prezentate mai jos:

Utilizarea GetPivotData pentru a obține o valoare

Să presupunem că aveți un tabel pivot numit PivotTable1 cu vânzări în câmpul Valori / date, produs ca câmp rânduri și regiune ca câmp coloane. Puteți utiliza metoda PivotTable.GetPivotData pentru a returna valorile din tabelele pivot.

Următorul cod va returna 1.130,00 USD (totalul vânzărilor pentru regiunea Est) din tabelul pivot:

1	MsgBox ActiveCell.PivotTable.GetPivotData („Vânzări”, „Regiune”, „Est”)

În acest caz, vânzările reprezintă „DataField”, „Field1” este regiunea și „Item1” este estul.

Următorul cod va returna 980 USD (vânzările totale pentru produsul ABC în regiunea de nord) din tabelul pivot:

1	MsgBox ActiveCell.PivotTable.GetPivotData („Vânzări”, „Produs”, „ABC”, „Regiune”, „Nord”)

În acest caz, Vânzările reprezintă „DataField”, „Field1” este Produs, „Item1” este ABC, „Field2” este Regiune și „Item2” este Nord.

De asemenea, puteți include mai mult de 2 câmpuri.

Sintaxa pentru GetPivotData este:

GetPivotData (Câmp de date, Câmpul 1, Elementul 1, Câmpul2, Elementul 2… ) Unde:

Parametru	Descriere
Câmp de date	Câmp de date, cum ar fi vânzările, cantitatea etc., care conține numere.
Câmpul 1	Numele unei coloane sau câmpuri de rând din tabel.
Punctul 1	Numele unui articol din câmpul 1 (opțional).
Câmpul 2	Numele unei coloane sau câmpuri de rând din tabel (Opțional).
Punctul 2	Numele unui articol din câmpul 2 (opțional).

Crearea unui tabel pivot pe o foaie

Pentru a crea un tabel pivot pe baza intervalului de date de mai sus, pe celula J2 de pe foaia 1 a registrului de lucru activ, am folosi următorul cod:

1234567891011

Fișe de lucru („Foaie1”). Celule (1, 1). SelectațiActiveWorkbook.PivotCaches.Create (SourceType: = xlDatabase, SourceData: = _"Sheet1! R1C1: R21C4", Versiune: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet1! R2C10", TableName: = "PivotTable1", DefaultVersion _: = xlPivotTableVersion15Foi („Foaie1”). Selectați

Rezultatul este:

Crearea unui tabel pivot pe o foaie nouă

Pentru a crea un tabel pivot pe baza intervalului de date de mai sus, pe o foaie nouă, a registrului de lucru activ, am folosi următorul cod:

12345678910111213

Fișe de lucru („Foaie1”). Celule (1, 1). SelectațiFoi. AdăugațiActiveWorkbook.PivotCaches.Create (SourceType: = xlDatabase, SourceData: = _"Sheet1! R1C1: R21C4", Versiune: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet2! R3C1", TableName: = "PivotTable1", DefaultVersion _: = xlPivotTableVersion15Foi („Sheet2”). Selectați

Adăugarea câmpurilor la tabelul pivot

Puteți adăuga câmpuri în tabelul pivot nou creat numit PivotTable1 pe baza intervalului de date de mai sus. Notă: foaia care conține tabelul pivot trebuie să fie foaia activă.

Pentru a adăuga produsul în câmpul rânduri, ar trebui să utilizați următorul cod:

123	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Produs"). Orientare = xlRowFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Produs"). Poziție = 1

Pentru a adăuga regiune în câmpul Coloane, ar trebui să utilizați următorul cod:

123	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Orientation = xlColumnFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Poziție = 1

Pentru a adăuga vânzări la secțiunea Valori cu formatul numărului monedei, ar trebui să utilizați următorul cod:

123456789

ActiveSheet.PivotTables ("PivotTable1"). AddDataField ActiveSheet.PivotTables (_„PivotTable1”). PivotFields („Sales”), „Suma vânzărilor”, xlSumCu ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Suma vânzărilor").NumberFormat = "$ #, ## 0.00"Se termina cu

Rezultatul este:

Modificarea aspectului raportului din tabelul pivot

Puteți schimba aspectul raportului din tabelul pivot. Următorul cod va schimba aspectul raportului din tabelul pivot în Formular tabular:

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

Ștergerea unui tabel pivot

Puteți șterge un tabel pivot folosind VBA. Următorul cod va șterge tabelul pivot numit PivotTable1 de pe foaia activă:

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

Formatați toate tabelele pivot într-un registru de lucru

Puteți formata toate tabelele pivot într-un registru de lucru folosind VBA. Următorul cod folosește o structură de buclă pentru a parcurge toate foile unui registru de lucru și a șterge toate tabelele pivot din registrul de lucru:

12345678910111213

Sub FormattingAllThePivotTablesInAWorkbook ()Dim wks Ca foaie de lucruDim wb Ca registru de lucruSetați wb = ActiveWorkbookDim pt ca tabel pivotPentru fiecare wks În wb.SheetsPentru fiecare pct În tabele pivotpt.TableStyle2 = "PivotStyleLight15"Următorul pctUrmătoarele săptămâniSfârșitul Sub

Pentru a afla mai multe despre modul de utilizare a buclelor în VBA, faceți clic aici.

Eliminarea câmpurilor unui tabel pivot

Puteți elimina câmpurile dintr-un tabel pivot folosind VBA. Următorul cod va elimina câmpul Produs din secțiunea Rânduri dintr-un tabel pivot numit PivotTable1 din foaia activă:

12	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Produs"). Orientare = _xlAscuns

Crearea unui filtru

Un tabel pivot numit PivotTable1 a fost creat cu Produs în secțiunea Rânduri și Vânzări în secțiunea Valori. De asemenea, puteți crea un filtru pentru tabelul pivot folosind VBA. Următorul cod va crea un filtru bazat pe regiune în secțiunea Filtre:

123	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Orientation = xlPageFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Poziție = 1

Pentru a filtra tabelul pivot pe baza unui singur element de raport, în acest caz regiunea estică, ar trebui să utilizați următorul cod:

12345

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

Să presupunem că ați dorit să vă filtrați tabelul pivot pe mai multe regiuni, în acest caz est și nord, veți utiliza următorul cod:

1234567891011121314

ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Orientation = xlPageFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Poziție = 1ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). _EnableMultiplePageItems = AdevăratCu ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region").PivotItems („Sud”). Vizibil = False.PivotItems („Vest”). Vizibil = FalseSe termina cu

Reîmprospătarea tabelului pivot

Vă puteți reîmprospăta tabelul pivot în VBA. Ați utiliza următorul cod pentru a reîmprospăta un tabel specific numit PivotTable1 în VBA:

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