excel-vba
Pivottabeller

Anmärkningar

Skapa en pivottabell

En av de mest kraftfulla funktionerna i Excel är användningen av pivottabeller för att sortera och analysera data. Att använda VBA för att skapa och manipulera Pivots är lättare om du förstår förhållandet mellan Pivot Tabeller till Pivot Caches och hur du refererar till och använder de olika delarna av tabellerna.

Som mest grundläggande är Range ett Worksheet på ett Worksheet . Detta dataområde MÅSTE identifiera datakolumnerna med en rubrikrad som den första raden i intervallet. När pivottabellen har skapats kan användaren visa och ändra kildedata när som helst. Ändringar kan emellertid inte automatiskt eller omedelbart återspeglas i själva pivottabellen eftersom det finns en mellanliggande datalagringsstruktur som kallas Pivot-cache som är direkt ansluten till själva pivottabellen.

Om flera Pivot-tabeller behövs, baserat på samma källdata, kan Pivot Cache återanvändas som det interna datalagret för var och en av Pivot-tabellerna. Detta är en bra praxis eftersom det sparar minne och minskar storleken på Excel-filen för lagring.

Som ett exempel för att skapa en pivottabell baserad på källdata som visas i figurerna ovan:

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

Referenser MSDN Pivot Table Object

Pivottabellområden

Dessa utmärkta referenskällor ger beskrivningar och illustrationer av de olika sortimenten i pivottabeller.

referenser

• Hänvisar till Pivot Table Ranges i VBA - från Jon Peltiers tekniska blogg
• Referera till ett Excel Pivot Table Range med VBA - från globaliconnect Excel VBA

Lägga till fält till en pivottabell

Två viktiga saker att notera när du lägger till fält till en pivottabell är orientering och position. Ibland kan en utvecklare anta var ett fält är placerat, så det är alltid tydligare att uttryckligen definiera dessa parametrar. Dessa åtgärder påverkar bara den givna pivottabellen, inte pivottcachen.

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

Formatera Pivot Table Data

Det här exemplet ändrar / ställer in flera format i DataBodyRange ( DataBodyRange ) i den givna pivottabellen. Alla formattable parametrar i en vanlig Range finns. Formatering av data påverkar bara själva pivottabellen, inte pivottcachen.

OBS TableStyle2 Egenskapen heter TableStyle2 eftersom egenskapen TableStyle inte är medlem i 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