Hoe kan je meerdere veldinstellingen in een draaitabel wijzigen?

Wanneer je een draaitabel maakt in een werkblad en na het slepen van de velden naar de lijst met waarden in de Draaitabelveldenlijst, krijg je mogelijk overal dezelfde functie Aantal zoals in de volgende schermafbeelding te zien is. Maar nu wil je dat de functie Som de functie Aantal vervangt, hoe kun je de berekening van meerdere draaitabelvelden tegelijk wijzigen in Excel?

De veldinstellingen in de draaitabel handmatig één voor één wijzigen

Meerdere veldinstellingen in de draaitabel wijzigen met VBA-code

De veldinstellingen in de draaitabel handmatig één voor één wijzigen

In Excel kun je de berekening van de veldinstellingen wijzigen door de functies één voor één aan te passen in het dialoogvenster Veldinstellingen waarden, doe dit als volgt:

1. Selecteer een veld in het gebied Waarden waarvoor je de samenvattingsfunctie in de draaitabel wilt wijzigen, klik met de rechtermuisknop en kies Veldinstellingen waarden, zie onderstaande schermafbeelding:

2. Kies vervolgens in het dialoogvenster Veldinstellingen waarden het type berekening dat je wilt gebruiken onder het tabblad Waarden samenvatten door, zie onderstaande schermafbeelding:

3. Klik daarna op OK om dit dialoogvenster te sluiten, en je kunt zien dat je functie Aantal is veranderd in de functie Som, zie onderstaande schermafbeelding:

4. Herhaal de bovenstaande stappen om de berekeningsinstellingen van andere velden één voor één te wijzigen.

Opmerking: Je kunt ook de veldinstellingen wijzigen door met de rechtermuisknop op een veldcel te klikken en Waarden samenvatten door te kiezen en selecteer de gewenste berekening uit de contextmenu's.

Meerdere veldinstellingen in de draaitabel wijzigen met VBA-code

De bovenstaande methode kan tijdrovend en omslachtig zijn als er veel veldberekeningen moeten worden gewijzigd. Hier kan ik je een code introduceren om meerdere veldinstellingen tegelijk te wijzigen.

1. Klik op een willekeurige cel in je draaitabel.

2. Houd de toetsen ALT + F11 ingedrukt, en het venster Microsoft Visual Basic for Applications wordt geopend.

3. Klik op Invoegen > Module, en plak de volgende code in het modulevenster.

VBA-code: Meerdere veldinstellingen in de draaitabel wijzigen

Public Sub SetDataFieldsToSum()
'Update 20141127
Dim xPF As PivotField
Dim WorkRng As Range
Set WorkRng = Application.Selection
With WorkRng.PivotTable
   .ManualUpdate = True
   For Each xPF In .DataFields
      With xPF
         .Function = xlSum
         .NumberFormat = "#,##0"
      End With
   Next
   .ManualUpdate = False
End With
End Sub

4. Druk vervolgens op de toets F5 om deze code uit te voeren, en alle veldinstellingen in je geselecteerde draaitabel zijn in één keer omgezet naar de gewenste berekening, zie onderstaande schermafbeeldingen:

Opmerking: In de bovenstaande code kun je de functie Som wijzigen naar elke andere berekening, zoals Gemiddelde, Max, Min zoals je wilt. Je hoeft alleen de Som in deze scripts te wijzigen: Public Sub SetDataFieldsToSum() en .Function = xlSum naar andere functies.