Tabella pivot VBA | Passaggi per creare una tabella pivot in VBA

Tabella pivot VBA di Excel

Le tabelle pivot sono il cuore del riepilogo del rapporto di una grande quantità di dati. Possiamo anche automatizzare il processo di creazione di una tabella pivot tramite la codifica VBA. Sono una parte importante di qualsiasi report o dashboard, in Excel è facile creare tabelle con un pulsante ma in VBA dobbiamo scrivere alcuni codici per automatizzare la nostra tabella pivot, prima di Excel 2007 e delle sue versioni precedenti in VBA non ne avevamo bisogno creare una cache per le tabelle pivot, ma in Excel 2010 e nelle versioni più recenti sono necessarie le cache.

VBA può farci risparmiare un sacco di tempo sul posto di lavoro, anche se padroneggiarlo non è così facile, ma vale la pena dedicare tempo per impararlo. Ho impiegato 6 mesi per comprendere il processo di creazione di tabelle pivot tramite VBA. Sai cosa quei 6 mesi hanno fatto miracoli per me perché ho fatto tanti errori mentre cercavo di creare la tabella pivot.

Ma la cosa reale è che ho imparato dai miei errori e ora sto scrivendo questo articolo per mostrarti come creare tabelle pivot usando il codice.

Con un semplice clic di un pulsante, possiamo creare rapporti.

Passaggi per creare una tabella pivot in VBA

Puoi scaricare questo modello di tabella pivot VBA qui - Modello di tabella pivot VBA

Per creare una tabella pivot è importante disporre di dati. Per questo ho creato dei dati fittizi, puoi scaricare la cartella di lavoro da seguire con me con gli stessi dati.

Passaggio 1: la tabella pivot è un oggetto per fare riferimento alla tabella pivot dichiarare la variabile come tabelle pivot.

Codice:

 Tabella pivot secondaria () Dim PTable come tabella pivot End Sub

Passaggio 2: prima di creare una tabella pivot, dobbiamo creare una cache pivot per definire l'origine dei dati.

Nella normale tabella pivot del foglio di lavoro senza problemi creeremo una cache pivot in background. Ma in VBA dobbiamo creare.

Per questo definire la variabile PivotCache.

Codice:

 Dim PCache As PivotCache

Passaggio 3: per determinare l'intervallo di dati pivot, definire la variabile come intervallo.

Codice:

 Dim PRange As Range

Passaggio 4: per inserire una tabella pivot abbiamo bisogno di un foglio separato per aggiungere un foglio di lavoro per la tabella pivot dichiarare la variabile come foglio di lavoro.

Codice:

 Dim PSheet come foglio di lavoro

Passaggio 5: allo stesso modo per fare riferimento ai dati contenenti il foglio di lavoro, dichiarare un'altra variabile come foglio di lavoro.

Codice:

 Dim DSheet come foglio di lavoro

Passaggio 6: infine, per trovare l'ultima riga e colonna utilizzata, definire altre due variabili come Long.

Codice:

 Dim LR Finché Dim LC Finché

Passaggio 7: ora dobbiamo inserire un nuovo foglio per creare una tabella pivot. Prima di ciò, se è presente un foglio pivot, è necessario eliminarlo.

Passaggio 8: ora imposta la variabile oggetto PSheet e DSheet rispettivamente su Foglio pivot e Foglio dati.

Passaggio 9: trova l'ultima riga utilizzata e l'ultima colonna utilizzata nel foglio dati.

Passaggio 10: ora imposta l'intervallo di pivot utilizzando l'ultima riga e l'ultima colonna.

Questo imposterà perfettamente l'intervallo di dati. Selezionerà automaticamente l'intervallo di dati anche se sono presenti aggiunte o cancellazioni di dati nel foglio dati.

Passaggio 11: prima di creare una tabella pivot, dobbiamo creare una cache pivot. Imposta la variabile della cache pivot utilizzando il codice VBA di seguito.

Passaggio 12: ora crea una tabella pivot vuota.

Passaggio 13: dopo aver inserito la tabella pivot, è necessario inserire prima il campo riga. Quindi inserirò il campo riga come colonna Paese.

Nota: scarica la cartella di lavoro per comprendere le colonne di dati.

Passaggio 14: ora un altro elemento che inserirò nel campo riga come secondo elemento di posizione. Inserirò il prodotto come seconda voce di riga nel campo riga.

Passaggio 15: dopo aver inserito le colonne nel campo della riga, è necessario inserire i valori nel campo della colonna. Inserirò il "Segmento" nel campo della colonna.

Passaggio 16: ora dobbiamo inserire i numeri nel campo dati. Quindi inserisci "Vendite" nel campo dati.

Passaggio 17: abbiamo finito con la parte di riepilogo della tabella pivot, ora dobbiamo formattare la tabella. Per formattare la tabella pivot utilizzare il codice seguente.

Nota: per avere stili di tabella più diversi, registrarli macro e ottenere gli stili di tabella.

Per mostrare gli elementi dei valori archiviati nella riga in forma tabulare, aggiungere il codice sottostante in basso.

Ok, abbiamo finito se eseguiamo questo codice usando il tasto F5 o manualmente, dovremmo ottenere la tabella pivot in questo modo.

In questo modo, utilizzando la codifica VBA, possiamo automatizzare il processo di creazione di una tabella pivot.

Per tuo riferimento, ho fornito il codice di seguito.

 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