excel-vba
Draaitabellen

Opmerkingen

Een draaitabel maken

Een van de krachtigste mogelijkheden in Excel is het gebruik van draaitabellen om gegevens te sorteren en te analyseren. Het gebruik van VBA om de draaipunten te maken en te manipuleren is eenvoudiger als u de relatie van draaitabellen met draaipaches begrijpt en hoe u naar de verschillende delen van de tabellen kunt verwijzen en deze kunt gebruiken.

In de basis zijn uw brongegevens een Range met gegevens op een Worksheet . Dit gegevensgebied MOET de gegevenskolommen met een koprij identificeren als de eerste rij in het bereik. Nadat de draaitabel is gemaakt, kan de gebruiker de brongegevens op elk gewenst moment bekijken en wijzigen. Wijzigingen kunnen echter niet automatisch of onmiddellijk worden weerspiegeld in de draaitabel zelf, omdat er een tussenliggende structuur voor gegevensopslag is, de draaitabel genaamd, die rechtstreeks is verbonden met de draaitabel zelf.

Als er meerdere draaitabellen nodig zijn, op basis van dezelfde brongegevens, kan de draaitabel opnieuw worden gebruikt als het interne gegevensarchief voor elk van de draaitabellen. Dit is een goede gewoonte omdat het geheugen bespaart en de grootte van het Excel-bestand voor opslag verkleint.

Als voorbeeld, om een draaitabel te maken op basis van de brongegevens in de bovenstaande afbeeldingen:

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

Verwijzingen MSDN-draaitabelobject

Draaitabelbereiken

Deze uitstekende referentiebronnen bieden beschrijvingen en illustraties van de verschillende bereiken in draaitabellen.

Referenties

• Verwijzen naar draaitabelbereiken in VBA - van Jon Peltier's technische blog
• Verwijzen naar een Excel- draaitabelbereik met VBA - van globaliconnect Excel VBA

Velden toevoegen aan een draaitabel

Twee belangrijke dingen om op te letten bij het toevoegen van velden aan een draaitabel zijn Oriëntatie en Positie. Soms kan een ontwikkelaar aannemen waar een veld wordt geplaatst, dus het is altijd duidelijker om deze parameters expliciet te definiëren. Deze acties hebben alleen invloed op de gegeven draaitabel, niet op de draaitabel.

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

De draaitabelgegevens opmaken

In dit voorbeeld worden verschillende indelingen in het gegevensbereikgebied ( DataBodyRange ) van de gegeven DataBodyRange gewijzigd / ingesteld. Alle formattable parameters in een standaard Range zijn beschikbaar. Het formatteren van de gegevens heeft alleen invloed op de draaitabel zelf, niet op de draaitabel.

OPMERKING: de eigenschap heeft de naam TableStyle2 omdat de eigenschap TableStyle geen lid is van de objecteigenschappen van de 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