wimgielis.com

Draaitabellen en VBA

• 13/10/2012

Draaitabel analyses bijhouden

Geregeld doe ik analyses in Excel met behulp van draaitabellen. Hierbij verander ik de data op zoveel verschillende wijzen, zodat ik toch een aantal van de analyses wil opslaan of bijhouden. Waarom geen kopie van de draaitabel als harde waarden met opmaak? Zie hier:

Sub Backup_Pivot_analysis()
'''''
' VBA-code to backup pivot table views
' 13/10/12
'''''
    Dim ptRng As Range

    On Error Resume Next
    Set ptRng = ActiveCell.PivotTable.TableRange1
    On Error GoTo 0

    If Not ptRng Is Nothing Then

        Application.ScreenUpdating = 0
        With Sheets.Add(After:=Sheets(Sheets.Count))
            .Name = "Backup " & Format(Now, "yyyymmdd hhmmss")

            ptRng.Copy
            .Cells(1).PasteSpecial xlValues '12
            .Cells(1).PasteSpecial xlFormats '-4122
            .Cells(1).PasteSpecial xlPasteColumnWidths '8

            Application.CutCopyMode = 0
            Application.Goto .Cells(1)
            Application.Goto ptRng.Cells(1)

        End With

    End If

End Sub

Dit lijkt een hoop code, maar uiteindelijk gebeurt er niet zoveel. Ik test of de cursor in een draaitabel staat. Vervolgens voeg ik een nieuw tabblad toe op het einde en geef ik dat blad een naam met datum en tijd erin. Dan kopieer ik de draaitabel als harde waarden, inclusief opmaak en kolombreedtes. Tot slot zet ik op beide tabbladen de cursor goed.

De macro is geen event of gebeurtenis. Je moet dee macro in een module plaatsen, en zelf uitvoeren (klik op een knop, sneltoetscombinatie, de macro toevoegen aan een object, … er zijn vele mogeljkheden. Probeer het zeker zelf een keertje uit met behulp van het bestand dat je kunt downloaden op pagina 1. Tot slot vermeld ik nog dat collega-MVP John Walkenbach een soortgelijke manuele oplossing bedacht: the spreadsheet page.

Sommen in een draaitabel vervangen door aantallen

Als je een draaitabel aanmaakt, bepaal je de layout van de draaitabel en welke gegevens je waar wil zien: pagina, rij, kolom, data. Excel past daarbij bepaalde regels en intelligentie toe om het ons zo makkelijk mogelijk te maken. Voor numerieke gegevens zoals lengte en gewicht stelt Excel de som voor als samenvattingsfunctie:

Erg handig is zoiets ook niet, al kan Excel dit uiteraard niet echt weten. In plaats van voor elke meetwaarde de Som te vervangen in Aantal, kan je dit ook zo doen met wat VBA-code:

Sub Backup_Pivot_analysis()
'''''
' VBA-code to change counts to sums in a pivot table
' 13/10/12
'''''
    Dim df As PivotField

    On Error Resume Next
    With ActiveCell.PivotTable
        For Each df In .DataFields
            df.Function = xlCount 'xlSum
            
'df.Function = IIf(.DataFields(1).Function = xlSum, xlCount, xlSum)
        Next
    End With

End Sub

Ik geef hierboven wat alternatieven weer. Zo stelt deze code alle waarden in op Aantal (xlCount). In commentaar zet ik de code voor een Som (xlSum). En de groene regel in commentaar toont de code wanneer je het alles op Aantal wil zetten als de eerste meetwaarde op Som staat, zoniet zet je alle meetwaarden ineens op Som. Ook deze code, net als de vorige, komt in een module terecht. Kijk in het bestand in de VBA-code als je niet goed weet hoe het nu juist moet. De code voer je zelf uit na het activeren van de gewenste draaitabel.

Draaitabellen met 1 commando vernieuwen

Wens je met 1 commando alle draaitabellen in een bestand te vernieuwen, gebruik dan de RefreshAll methode. Bijvoorbeeld bij het openen van het bestand (de code komt dan bij ThisWorkbook):

Private Sub Workbook_Open()
'''''
' VBA-code to refresh all pivot tables in a workbook
' 13/10/12
'''''
    ThisWorkbook.RefreshAll

End Sub

Switchen tussen de klassieke en de nieuwe look van een draaitabel

In de nieuwere versies van Excel heeft een draaitabel standaard een andere look and feel dan ten tijde van Excel 2003 (bijvoorbeeld). Je hebt de mogelijkheid om per draaitabel terug te keren naar die eerdere voorstellingswijze (en bijhorende kenmerken). Wil je dit automatiseren, gebruik dan onderstaande korte macro:

Private Sub Pivot_Switch_Layout()
'''''
' VBA-code to switch between pivot table display modes
' 26/01/13
'''''
    On Error Resume Next
    ActiveCell.PivotTable.InGridDropZones = Not ActiveCell.PivotTable.InGridDropZones

End Sub

Conclusie

Zo, dit was het alweer. 6 tijdsbesparende en nuttige tips voor het werken met draaitabellen in VBA. Je kan nog veel verder gaan, maar dat laat ik over aan jou of bespreken we later.

• « Vorige
• 1
• 2
• Volgende »