excel-vba
Tabelle pivot

Osservazioni

Creazione di una tabella pivot

Una delle funzionalità più potenti di Excel è l'uso di tabelle pivot per ordinare e analizzare i dati. L'utilizzo di VBA per creare e manipolare i pivot è più semplice se si capisce la relazione tra tabelle pivot e cache pivot e come fare riferimento e utilizzare le diverse parti delle tabelle.

Nella sua forma più semplice, i dati di origine sono un'area di dati Range su un Worksheet . Questa area dati DEVE identificare le colonne di dati con una riga di intestazione come prima riga dell'intervallo. Una volta creata la tabella pivot, l'utente può visualizzare e modificare i dati di origine in qualsiasi momento. Tuttavia, le modifiche potrebbero non essere riflesse automaticamente o immediatamente nella tabella pivot stessa poiché esiste una struttura di archiviazione dei dati intermedia denominata Pivot Cache direttamente connessa alla tabella pivot stessa.

Se sono necessarie più tabelle pivot, in base agli stessi dati di origine, la Pivot Cache può essere riutilizzata come archivio dati interno per ciascuna tabella pivot. Questa è una buona pratica perché salva la memoria e riduce la dimensione del file Excel per l'archiviazione.

Ad esempio, per creare una tabella pivot basata sui dati di origine mostrati nelle figure sopra:

Sub test()
    Dim pt As PivotTable
    Set pt = CreatePivotTable(ThisWorkbook.Sheets("Sheet1").Range("A1:E15"))
End Sub

Function CreatePivotTable(ByRef srcData As Range) As PivotTable
    '--- creates a Pivot Table from the given source data and
    '    assumes that the first row contains valid header data
    '    for the columns
    Dim thisPivot As PivotTable
    Dim dataSheet As Worksheet
    Dim ptSheet As Worksheet
    Dim ptCache As PivotCache
    
    '--- the Pivot Cache must be created first...
    Set ptCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                                                  SourceData:=srcData)
    '--- ... then use the Pivot Cache to create the Table
    Set ptSheet = ThisWorkbook.Sheets.Add
    Set thisPivot = ptCache.CreatePivotTable(TableDestination:=ptSheet.Range("A3"))
    Set CreatePivotTable = thisPivot
End Function

Riferimenti Oggetto tabella pivot MSDN

Intervalli di tabella pivot

Queste eccellenti fonti di riferimento forniscono descrizioni e illustrazioni dei vari intervalli nelle tabelle pivot.

Riferimenti

• Fare riferimento a intervalli di tabelle pivot in VBA - dal blog tecnico di Jon Peltier
• Riferimento a un intervallo di tabelle pivot di Excel mediante VBA : da Globaliconnect Excel VBA

Aggiunta di campi a una tabella pivot

Due cose importanti da notare quando si aggiungono campi a una tabella pivot sono Orientamento e Posizione. A volte uno sviluppatore può assumere dove viene inserito un campo, quindi è sempre più chiaro definire esplicitamente questi parametri. Queste azioni influiscono solo sulla tabella pivot fornita, non sulla Pivot Cache.

Dim thisPivot As PivotTable
Dim ptSheet As Worksheet
Dim ptField As PivotField

Set ptSheet = ThisWorkbook.Sheets("SheetNameWithPivotTable")
Set thisPivot = ptSheet.PivotTables(1)

With thisPivot
    Set ptField = .PivotFields("Gender")
    ptField.Orientation = xlRowField
    ptField.Position = 1
    Set ptField = .PivotFields("LastName")
    ptField.Orientation = xlRowField
    ptField.Position = 2
    Set ptField = .PivotFields("ShirtSize")
    ptField.Orientation = xlColumnField
    ptField.Position = 1
    Set ptField = .AddDataField(.PivotFields("Cost"), "Sum of Cost", xlSum)
    .InGridDropZones = True
    .RowAxisLayout xlTabularRow
End With

Formattazione dei dati della tabella pivot

Questo esempio modifica / imposta diversi formati nell'area di intervallo dati ( DataBodyRange ) della tabella pivot fornita. Sono disponibili tutti i parametri formattabili in una Range standard. La formattazione dei dati ha effetto solo sulla tabella pivot stessa, non sulla pivot cache.

NOTA: la proprietà è denominata TableStyle2 perché la proprietà TableStyle non è un membro delle proprietà dell'oggetto della PivotTable .

Dim thisPivot As PivotTable
Dim ptSheet As Worksheet
Dim ptField As PivotField

Set ptSheet = ThisWorkbook.Sheets("SheetNameWithPivotTable")
Set thisPivot = ptSheet.PivotTables(1)

With thisPivot
    .DataBodyRange.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)"
    .DataBodyRange.HorizontalAlignment = xlRight
    .ColumnRange.HorizontalAlignment = xlCenter
    .TableStyle2 = "PivotStyleMedium9"
End With