Tabelul pivot VBA

Tabel pivot Excel VBA

Tabele pivot sunt inima rezumării raportului unei cantități mari de date. De asemenea, putem automatiza procesul de creare a unei tabele pivot prin codarea VBA. Acestea sunt o parte importantă a oricărui raport sau tablou de bord, în Excel este ușor să creăm tabele cu un buton, dar în VBA trebuie să scriem câteva coduri pentru a automatiza tabelul nostru pivot, înainte de Excel 2007 și versiunile sale mai vechi în VBA, nu era creați un cache pentru tabelele pivot, dar în Excel 2010 și versiunile sale mai noi sunt necesare cache-uri.

Table of Contents

Toggle

VBA poate economisi tone de timp pentru noi la locul de muncă. Chiar dacă stăpânirea nu este atât de ușoară, ci merită să petreceți timp pentru a învăța acest lucru. Am luat 6 luni pentru a înțelege procesul de creare a tabelelor pivot prin VBA. Știi ce au făcut minuni acele 6 luni pentru că am făcut atât de multe greșeli în timp ce încercam să creez tabelul pivot.

Dar adevărul este că am învățat din greșelile mele și acum scriu acest articol pentru a vă arăta cum să creați tabele pivot folosind codul.

Cu doar un clic de buton, putem crea rapoarte.

Pași pentru a crea tabelul pivot în VBA

Pentru a crea un tabel pivot, este important să aveți date. Pentru aceasta, am creat câteva date false. Puteți descărca registrul de lucru pentru a-l urma cu mine cu aceleași date.

Pasul 1: Tabelul pivot este un obiect care face referire la tabelul pivot care declară variabila ca tabele pivot.

Cod:

Sub PivotTable()

  Dim PTable As PivotTable

End Sub

Pasul 2: Înainte de a crea mai întâi un tabel pivot, trebuie să creăm o memorie cache pivot pentru a defini sursa datelor.

În foile de lucru obișnuite, tabelul pivot fără să ne deranjeze va crea un cache pivot în fundal. Dar în VBA, trebuie să creăm.

Pentru aceasta, definiți variabila a PivotCache.

Cod:

Dim PCache As PivotCache

Pasul 3: Pentru a determina intervalul de date pivot definiți variabila ca un interval.

Cod:

Dim PRange As Range

Pasul 4: Pentru a insera un tabel pivot, avem nevoie de o foaie separată pentru a adăuga foi de lucru pentru ca tabelul pivot să declare variabila ca foaie de lucru.

Cod:

Dim PSheet As Worksheet

Pasul 5: În mod similar, pentru a face referire la datele care conțin foaia de lucru, declarați încă o variabilă ca Foaie de lucru.

Cod:

Dim DSheet As Worksheet

Pasul 6: În cele din urmă, pentru a găsi ultimul rând și coloană utilizate, definiți încă două variabile ca Long.

Cod:

Dim LR As Long
Dim LC As Long

Pasul 7: Acum, trebuie să inserăm o foaie nouă pentru a crea un tabel pivot. Înainte de aceasta, dacă există o foaie pivot, trebuie să o ștergem.

Pasul 8: Acum, setați variabila obiect PSheet și DSheet la Foaie pivot și respectiv Foaie de date.

Pasul 9: Găsiți ultimul rând folosit și ultima coloană utilizată în foaia de date.

Pasul 10: Acum setați intervalul pivot utilizând ultimul rând și ultima coloană.

Aceasta va seta gama de date perfect. Acesta va selecta automat intervalul de date, chiar dacă există orice adăugare sau ștergere a datelor în foaia de date.

Pasul 11: Înainte de a crea un tabel pivot, trebuie să creăm un cache pivot. Setați variabila cache pivot utilizând codul VBA de mai jos.

Pasul 12: Acum, creați un tabel pivot gol.

Pasul 13: După inserarea tabelului pivot, trebuie să introducem mai întâi câmpul rând. Așa că voi insera câmpul rândului ca coloană de țară.

Pasul 14: Acum, încă un element pe care îl voi introduce în câmpul rând ca element de poziție a doua. Voi insera Produsul ca al doilea element rând în câmpul rând.

Pasul 15: După inserarea coloanelor în câmpul rând, trebuie să inserăm valori în câmpul coloanei. Voi insera „Segment” în câmpul coloanei.

Pasul 16: Acum, trebuie să introducem numere în câmpul de date. Așadar, introduceți „Vânzări” în câmpul de date.

Pasul 17: Am terminat cu partea de rezumat a tabelului pivot. Acum trebuie să formatăm tabelul. Pentru a formata tabelul pivot, utilizați codul de mai jos.

Pentru a afișa elementele de valori înregistrate în rând sub formă de tabel, adăugați codul de mai jos în partea de jos.

Ok, am terminat dacă executăm acest cod folosind tasta F5 sau manual, atunci ar trebui să obținem tabelul pivot în acest fel.

Astfel, folosind codarea VBA, putem automatiza procesul de creare a unui tabel pivot.

Pentru referință, am dat codul de mai jos.

Sub PivotTable()

Dim PTable As PivotTable
Dim PCache As PivotCache
Dim PRange As Range
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim LR As Long
Dim LC As Long

On Error Resume Next
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Worksheets("Pivot Sheet").Delete 'This will delete the exisiting pivot table worksheet
Worksheets.Add After:=ActiveSheet ' This will add new worksheet
ActiveSheet.Name = "Pivot Sheet" ' This will rename the worksheet as "Pivot Sheet"
On Error GoTo 0

Set PSheet = Worksheets("Pivot Sheet")
Set DSheet = Worksheets("Data Sheet")

'Find Last used row and column in data sheet
LR = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LC = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column

'Set the pivot table data range
Set PRange = DSheet.Cells(1, 1).Resize(LR, LC)

'Set pivot cahe
Set PCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=PRange)

'Create blank pivot table
Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), TableName:="Sales_Report")

'Insert country to Row Filed
With PSheet.PivotTables("Sales_Report").PivotFields("Country")
.Orientation = xlRowField
.Position = 1
End With

'Insert Product to Row Filed & position 2
With PSheet.PivotTables("Sales_Report").PivotFields("Product")
.Orientation = xlRowField
.Position = 2
End With

'Insert Segment to Column Filed & position 1
With PSheet.PivotTables("Sales_Report").PivotFields("Segment")
.Orientation = xlColumnField
.Position = 1
End With

'Insert Sales column to the data field
With PSheet.PivotTables("Sales_Report").PivotFields("Sales")
.Orientation = xlDataField
.Position = 1
End With

'Format Pivot Table
PSheet.PivotTables("Sales_Report").ShowTableStyleRowStripes = True
PSheet.PivotTables("Sales_Report").TableStyle2 = "PivotStyleMedium14"

'Show in Tabular form
PSheet.PivotTables("Sales_Report").RowAxisLayout xlTabularRow

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Articole recomandate

Acesta a fost un ghid pentru tabelul pivot VBA. Aici aflăm cum să creăm un tabel pivot în Excel folosind codul VBA împreună cu un exemplu practic și un șablon descărcabil. Mai jos puteți găsi câteva articole utile Excel VBA –

• Filtrează în tabelul pivot
• Câmp calculat din tabelul pivot
• Tabel pivot cu foi multiple
• Slicer folosind tabelul pivot
• VBA Web Scraping