Come creare una tabella pivot con vba?

Prima di consegnarti questa guida e iniziare a utilizzare VBA per creare una tabella pivot , lascia che ti dica una cosa.

Ho imparato a usare VBA solo SEI anni fa. E la prima volta che ho scritto il codice macro per creare una tabella pivot, è stato un fallimento.

Da allora, ho imparato di più dalla mia cattiva programmazione che dai codici che funzionano davvero.

Oggi ti mostrerò un modo semplice per automatizzare le tue tabelle pivot utilizzando il codice macro.

Normalmente, quando inserisci una tabella pivot in un foglio di calcolo, avviene attraverso un processo semplice, ma l’intero processo è così veloce che non ti accorgi mai di cosa è successo.

In VBA l’intero processo è lo stesso, viene eseguito semplicemente utilizzando il codice. In questa guida, ti mostrerò ogni passaggio e spiegherò come scrivere il codice per esso.

Basta guardare l’esempio seguente, in cui puoi eseguire questo codice macro con un pulsante e restituirà in un lampo una nuova tabella pivot in un nuovo foglio di lavoro.

Senza ulteriori indugi, iniziamo a scrivere il nostro codice macro per creare una tabella pivot.

Gli 8 semplici passaggi per scrivere codice macro in VBA per creare una tabella pivot in Excel

Per tua comodità, ho diviso l’intero processo in 8 semplici passaggi. Dopo aver seguito questi passaggi, sarai in grado di automatizzare tutte le tue tabelle pivot.

Assicurati di scaricare questo file da qui per seguirlo.

1. Dichiarare le variabili

Il primo passo è dichiarare le variabili che dobbiamo utilizzare nel nostro codice per definire cose diverse.

 'Declare Variables Dim PSheet As Worksheet Dim DSheet As Worksheet Dim PCache As PivotCache Dim PTable As PivotTable Dim PRange As Range Dim LastRow As Long Dim LastCol As Long

Nel codice sopra abbiamo dichiarato:

1. PSheet: per creare un foglio per una nuova tabella pivot.
2. DSheet: da utilizzare come scheda tecnica.
3. PChache: utilizzare come nome per la cache della tabella pivot.
4. PTable: utilizzare come nome per la nostra tabella pivot.
5. PRange: per impostare l’intervallo dei dati di origine.
6. LastRow e LastCol: per ottenere l’ultima riga e colonna del nostro intervallo di dati.

2. Inserisci un nuovo foglio di calcolo

Prima di creare una tabella pivot, Excel inserisce un foglio vuoto e quindi crea su di esso una nuova tabella pivot.

E il codice seguente farà la stessa cosa per te.

Verrà inserito un nuovo foglio di lavoro con il nome “Tabella pivot” prima del foglio di lavoro attivo e se esiste già un foglio di lavoro con lo stesso nome, lo eliminerà prima.

Dopo aver inserito un nuovo foglio di lavoro, questo codice imposterà il valore della variabile PSheet nel foglio di lavoro della tabella pivot e DSheet nel foglio di lavoro dei dati di origine.

 'Declare Variables On Error Resume Next Application.DisplayAlerts = False Worksheets("PivotTable").Delete Sheets.Add Before:=ActiveSheet ActiveSheet.Name = "PivotTable" Application.DisplayAlerts = True Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets("Data")

Assicurati di sostituire i nomi dei fogli di lavoro nel codice con i nomi che hai nei tuoi dati.

3. Imposta l’intervallo dati

Ora il passaggio successivo è impostare l’intervallo di dati dal foglio di lavoro di origine. Qui devi occuparti di una cosa, non puoi specificare un intervallo di origine fisso.

È necessario un codice in grado di identificare tutti i dati nel foglio di origine. E sotto c’è il codice:

 'Define Data Range LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

Questo codice inizierà dalla prima cella della tabella dati e selezionerà fino all’ultima riga, quindi fino all’ultima colonna.

Infine, imposta l’intervallo selezionato come sorgente. La parte migliore è che non è necessario modificare l’origine dati ogni volta durante la creazione della tabella pivot.

4. Crea una cache pivot

In Excel 2000 e versioni successive, prima di creare una tabella pivot, è necessario creare una cache pivot per impostare l’origine dati.

Normalmente, quando crei una tabella pivot, Excel crea automaticamente una cache pivot senza chiedertelo, ma quando devi utilizzare VBA, devi scrivere del codice per essa.

 'Define Pivot Cache Set PCache = ActiveWorkbook.PivotCaches.Create _ (SourceType:=xlDatabase, SourceData:=PRange). _ CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _ TableName:="SalesPivotTable")

Questo codice funziona in due modi, in primo luogo impostando una cache pivot utilizzando un’origine dati e in secondo luogo impostando l’indirizzo della cella nel foglio di lavoro appena inserito per inserire la tabella pivot.

Puoi cambiare la posizione della tabella pivot modificando questo codice.

5. Inserisci una tabella pivot vuota

Dopo la cache pivot, il passaggio successivo è inserire una tabella pivot vuota. Ricorda solo che quando crei una tabella pivot, ottieni sempre prima un pivot vuoto, quindi imposti tutti i valori, le colonne e le righe.

Questo codice farà la stessa cosa:

 'Insert Blank Pivot Table Set PTable = PCache.CreatePivotTable _ (TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable")

Questo codice crea una tabella pivot vuota e la denomina “SalesPivotTable”. Puoi modificare questo nome dal codice stesso.

6. Inserisci campi riga e colonna

Dopo aver creato una tabella pivot vuota, il passaggio successivo è inserire campi riga e colonna, come fai normalmente.

Per ogni campo di riga e colonna è necessario scrivere un codice. Qui vogliamo aggiungere anni e mesi nel campo riga e aree nel campo colonna.

Ecco il codice:

 'Insert Row Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Year") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Month") .Orientation = xlRowField .Position = 2 End With 'Insert Column Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Zone") .Orientation = xlColumnField .Position = 1 End With

In questo codice hai menzionato anno e mese come due campi. Ora se guardi il codice vedrai che c’è anche un numero di posizione. Questo numero di posizione definisce la sequenza dei campi.

Ogni volta che devi aggiungere più campi (Riga o Colonna), specifica la loro posizione. E puoi modificare i campi cambiando il loro nome dal codice.

7. Inserire un campo dati

La cosa principale è definire il campo valore nella tabella pivot.

Il codice per definire i valori differisce dalla definizione di righe e colonne perché qui dobbiamo definire la formattazione di numeri, posizioni e funzioni.

 'Insert Data Field With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Amount") .Orientation = xlDataField .Function = xlSum .NumberFormat = "#,##0" .Name = "Revenue " End With

Puoi aggiungere l’importo come campo valore con il codice sopra. E questo codice formatterà i valori come un numero con un separatore (,).

Usiamo xlsum per aggiungere i valori, ma puoi anche usare xlcount e altre funzioni.

8. Formattare la tabella pivot

In definitiva, è necessario utilizzare il codice per formattare la tabella pivot. Di solito esiste una formattazione predefinita in una tabella pivot, ma puoi modificare questa formattazione.

Con VBA puoi impostare lo stile di formattazione nel codice.

Il codice è:

 'Format Pivot TableActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = True ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9"

Il codice riportato sopra applicherà le strisce di linea e lo stile “Pivot Style Medium 9”, ma puoi anche utilizzare un altro stile da questo collegamento .

Finalmente il tuo codice è pronto per l’uso.

[CODICE COMPLETO] Utilizza VBA per creare una tabella pivot in Excel: copia e incolla macro

 Sub InsertPivotTable() 'Macro By Exceladvisor.org 'Declare Variables Dim PSheet As Worksheet Dim DSheet As Worksheet Dim PCache As PivotCache Dim PTable As PivotTable Dim PRange As Range Dim LastRow As Long Dim LastCol As Long 'Insert a New Blank Worksheet On Error Resume Next Application.DisplayAlerts = False Worksheets("PivotTable").Delete Sheets.Add Before:=ActiveSheet ActiveSheet.Name = "PivotTable" Application.DisplayAlerts = True Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets("Data") 'Define Data Range LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol) 'Define Pivot Cache Set PCache = ActiveWorkbook.PivotCaches.Create _ (SourceType:=xlDatabase, SourceData:=PRange). _ CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _ TableName:="SalesPivotTable") 'Insert Blank Pivot Table Set PTable = PCache.CreatePivotTable _ (TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable") 'Insert Row Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Year") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Month") .Orientation = xlRowField .Position = 2 End With 'Insert Column Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Zone") .Orientation = xlColumnField .Position = 1 End With 'Insert Data Field With ActiveSheet.PivotTables("SalesPivotTable").PivotFields ("Amount") .Orientation = xlDataField .Function = xlSum .NumberFormat = "#,##0" .Name = "Revenue " End With 'Format Pivot Table ActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = True ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9" End Sub

Tabella pivot sul foglio di lavoro esistente

Il codice utilizzato in precedenza crea una tabella pivot su un nuovo foglio di lavoro, ma a volte è necessario inserire una tabella pivot in un foglio di lavoro già presente nella cartella di lavoro.

Nel codice sopra (tabella pivot nel nuovo foglio di lavoro), nella parte in cui hai scritto il codice per inserire un nuovo foglio di lavoro, quindi assegnagli un nome. Si prega di apportare alcune modifiche al codice.

Non preoccuparti; Ti mostrerò.

Per prima cosa devi specificare il foglio di lavoro (già presente nella cartella di lavoro) in cui vuoi inserire la tua tabella pivot.

E per questo, devi utilizzare il codice seguente:

Invece di inserire un nuovo foglio di lavoro, è necessario specificare il nome del foglio di lavoro nella variabile PSheet.

 Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets(“Data”)

C’è ancora un po’ da fare. Il primo codice utilizzato elimina il foglio di lavoro con lo stesso nome (se esiste) prima di inserire il pivot.

Quando inserisci una tabella pivot nel foglio di lavoro esistente, potresti già avere una tabella pivot con lo stesso nome.

Quello che sto dicendo è che devi prima rimuovere quel perno.

Per fare ciò è necessario aggiungere il codice che dovrebbe rimuovere il pivot con lo stesso nome dal foglio di lavoro (se presente) prima di inserirne uno nuovo.

Ecco il codice che devi aggiungere:

 Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets(“Data”) Worksheets("PivotTable").Activate On Error Resume Next ActiveSheet.PivotTables("SalesPivotTable").TableRange2.Clear

Lascia che ti dica cosa fa questo codice.

Innanzitutto, imposta semplicemente PSheet come foglio di lavoro in cui desideri inserire la tabella pivot già nella cartella di lavoro e imposta i fogli di lavoro dati come DSheet.

Successivamente, attiva il foglio di lavoro e rimuove da esso la “Tabella pivot vendite”.

Importante: se i nomi dei fogli di lavoro nella cartella di lavoro differiscono, puoi modificarli dal codice. Ho evidenziato il codice in cui devi farlo.

Alla fine,

Usando questo codice possiamo automatizzare le tue tabelle pivot. E la parte migliore è che si tratta di una configurazione unica; dopodiché ci basta un clic per creare la tabella pivot e puoi risparmiare molto tempo. Ora dimmi una cosa.

Hai mai utilizzato il codice VBA per creare una tabella pivot?

Per favore condividi le tue opinioni con me nella casella dei commenti; Vorrei condividerli con te e condividere questo suggerimento con i tuoi amici.

Articoli simili:

• Aggiungi o rimuovi il totale complessivo in una tabella pivot in Excel
• Aggiungi il totale parziale a una tabella pivot in Excel
• Aggiorna automaticamente una tabella pivot in Excel
• Aggiungi un campo calcolato e un elemento
• Elimina una tabella pivot in Excel