excel-vba
Pivot-Tabellen

Bemerkungen

Erstellen einer Pivot-Tabelle

Eine der leistungsfähigsten Funktionen in Excel ist die Verwendung von Pivot-Tabellen zum Sortieren und Analysieren von Daten. Die Verwendung von VBA zum Erstellen und Bearbeiten der Pivots ist einfacher, wenn Sie die Beziehung von Pivot-Tabellen zu Pivot-Caches und den Bezug und die Verwendung der verschiedenen Teile der Tabellen verstehen.

Im Grunde, die Quelldaten ist ein Range Bereich von Daten auf einem Worksheet . Dieser Datenbereich MUSS die Datenspalten mit einer Kopfzeile als erste Zeile im Bereich identifizieren. Sobald die Pivot-Tabelle erstellt wurde, kann der Benutzer die Quelldaten jederzeit anzeigen und ändern. Änderungen werden jedoch möglicherweise nicht automatisch oder sofort in der Pivot-Tabelle selbst widergespiegelt, da eine Zwischenspeicherstruktur mit dem Namen Pivot-Cache vorhanden ist, die direkt mit der Pivot-Tabelle selbst verbunden ist.

Wenn mehrere Pivot-Tabellen auf der Grundlage derselben Quelldaten benötigt werden, kann der Pivot-Cache als interner Datenspeicher für jede der Pivot-Tabellen wiederverwendet werden. Dies ist eine bewährte Methode, da dadurch Speicher eingespart und die Größe der Excel-Datei zum Speichern reduziert wird.

So erstellen Sie eine Pivot-Tabelle basierend auf den in den Abbildungen oben dargestellten Quelldaten:

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

Verweist auf das MSDN-Pivot-Tabellenobjekt

Pivot-Tabellenbereiche

Diese hervorragenden Referenzquellen bieten Beschreibungen und Abbildungen der verschiedenen Bereiche in Pivot-Tabellen.

Verweise

• Referenzieren von Pivot-Tabellenbereichen in VBA - aus Jon Peltiers Tech-Blog
• Referenzieren eines Excel-Pivot-Tabellenbereichs mit VBA - von globaliconnect Excel VBA

Felder zu einer Pivot-Tabelle hinzufügen

Beachten Sie beim Hinzufügen von Feldern zu einer Pivot-Tabelle zwei wichtige Punkte: Ausrichtung und Position. Manchmal kann ein Entwickler davon ausgehen, wo ein Feld platziert wird. Daher ist es immer klarer, diese Parameter explizit zu definieren. Diese Aktionen betreffen nur die angegebene Pivot-Tabelle, nicht den 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

Formatieren der Pivot-Tabellendaten

In diesem Beispiel werden mehrere Formate im Datenbereich ( DataBodyRange ) der angegebenen Pivot-Tabelle geändert / festgelegt. Alle formatierbare Parameter in einem Range zur Verfügung. Das Formatieren der Daten wirkt sich nur auf die Pivot-Tabelle selbst aus, nicht auf den Pivot-Cache.

HINWEIS: Die Eigenschaft hat den Namen TableStyle2 da die TableStyle Eigenschaft kein Mitglied der PivotTable von 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