Categorie
Automazione Excel

Automatizzare la creazione di tabelle pivot in Excel

In questo articolo vedremo come è possibile automatizzare la creazione e la gestione delle tabelle pivot in Excel.

Utilizzando un esempio vedremo come scrivere una macro che può essere utilizzata per creare una tabella pivot, definire le sue proprietà, selezionare i campi da visualizzare nella tabella pivot e, infine, eseguirne il calcolo.

Utilizziamo come base dati l’elenco delle provincie della Lombardia, Piemonte e Liguria e ed il numero di abitanti

automazione pivot (1)automazione pivot (1)

La tabella pivot che vogliamo creare aggrega il totale degli abitanti e il totale delle provincie per regione

automazione pivot (2)automazione pivot (2)

Una macro è una serie di istruzioni che possono essere eseguite in modo automatico in Excel. Si possono eseguire macro in Excel per automatizzare le attività ripetitive, come ad esempio creare formule complesse, applicare formattazione condizionale, filtrare i dati e altro ancora.

Una macro può essere generata automaticamente da Excel con la funzionalità di registrazione ma per creare una macro complessa in Excel è necessario utilizzare il Visual Basic per Applicazioni (VBA). VBA è un linguaggio di programmazione che può essere utilizzato per automatizzare le attività in Excel.

Vediamo come scrivere una macro utilizzando VBA per creare pivot. Dal menu Excel:

-cliccare sulla voce Sviluppo

Se non trovate la voce Sviluppo nel menu è necessario spuntare la voce ‘Sviluppo’ dalle ‘Opzioni di Excel’ alla voce ‘Personalizza barra multifunzione’.

Cliccando sulla voce Sviluppo appare nella barra l’icona Macro : cliccando su di essa si apre la finestra di dialogo Macro

-digitare il nome senza spazi alla macro che si sta andando a creare e cliccando sul pulsante ‘Crea’ si aprirà il modulo VBA.

Il modulo riporterà sulla destra l’editor di codice con preimpostati inizio e fine della subroutine. Se il nome dato alla macro è CreaPivot, troveremo le due righe

Sub CreaPivot()

End Sub

All’ interno di queste andremo a scrivere il codice per creare una pivot:

-dichiariamo le variabili che utilizziamo

Dim DataSheet, NewPivotSheet As Worksheet
Dim PvtCache As PivotCache
Dim PvtTable As PivotTable
Dim PvtRange As Range
Dim LastRow, LastCol As Integer

-assegnamo valori alle variabili

Set DataSheet = Worksheets(“Regioni”)
Set NewPivotSheet = ActiveWorkbook.Sheets.Add
NewPivotSheet.Name = “NewPivot”
LastRow = DataSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DataSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PvtRange = DataSheet.Cells(1, 1).Resize(LastRow, LastCol)

Per creare una tabella pivot con codice VBA prima si definisce una cache della tabella pivot con PivotCaches.Create poi si crea la tabella pivot con
PivotCaches.CreatePivotTable

Set PvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PvtRange, Version:=xlPivotTableVersion15) ‘.CreatePivotTable(TableDestination:=NewPivotSheet.Cells(2, 2), TableName:=”Tabella Pivot Auto”)
Set PvtTable = PvtCache.CreatePivotTable(TableDestination:=NewPivotSheet.Cells(1, 1), TableName:=”Tabella Pivot Auto”)

-impostiamo i valori righe e valori di aggregazione della tabella pivot

‘Valori righe
With ActiveSheet.PivotTables(“Tabella Pivot Auto”).PivotFields(“Divisione amministrativa 1 (Stato / provincia / altro)”)
.Orientation = xlRowField
End With

With ActiveSheet.PivotTables(“Tabella Pivot Auto”).PivotFields(“Provincia”)
.Orientation = xlRowField
End With

‘Valori di aggregazione
With ActiveSheet.PivotTables(“Tabella Pivot Auto”).PivotFields(“Popolazione”)
.Orientation = xlDataField
.Function = xlSum
End With

With ActiveSheet.PivotTables(“Tabella Pivot Auto”).PivotFields(“Provincia”)
.Orientation = xlDataField
.Function = xlCount
End With

Salvata la macro, la si può eseguire cliccando sul pulsante Esegui.
L’ esecuzione della macro aggiungerà un foglio NewPivot con la tabella pivot.

Sub CreaPivot()

‘Variabili
Dim DataSheet, NewPivotSheet As Worksheet
Dim PvtCache As PivotCache
Dim PvtTable As PivotTable
Dim PvtRange As Range
Dim LastRow, LastCol As Integer

‘Foglio dati e foglio pivot
Set DataSheet = Worksheets(“Regioni”)
Set NewPivotSheet = ActiveWorkbook.Sheets.Add
NewPivotSheet.Name = “NewPivot”

LastRow = DataSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DataSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PvtRange = DataSheet.Cells(1, 1).Resize(LastRow, LastCol)

Set PvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PvtRange, Version:=xlPivotTableVersion15) ‘.CreatePivotTable(TableDestination:=NewPivotSheet.Cells(2, 2), TableName:=”Tabella Pivot Auto”)
Set PvtTable = PvtCache.CreatePivotTable(TableDestination:=NewPivotSheet.Cells(1, 1), TableName:=”Tabella Pivot Auto”)

‘Valori righe
With ActiveSheet.PivotTables(“Tabella Pivot Auto”).PivotFields(“Divisione amministrativa 1 (Stato / provincia / altro)”)
.Orientation = xlRowField
End With

With ActiveSheet.PivotTables(“Tabella Pivot Auto”).PivotFields(“Provincia”)
.Orientation = xlRowField
End With

‘Valori di aggregazione
With ActiveSheet.PivotTables(“Tabella Pivot Auto”).PivotFields(“Popolazione”)
.Orientation = xlDataField
.Function = xlSum
End With

With ActiveSheet.PivotTables(“Tabella Pivot Auto”).PivotFields(“Provincia”)
.Orientation = xlDataField
.Function = xlCount
End With

End Sub