Hoe maak je een draaitabel met vba?

Voordat ik u deze handleiding overhandig en VBA ga gebruiken om een draaitabel te maken , wil ik u iets vertellen.

Ik heb VBA pas zes jaar geleden leren gebruiken. En de eerste keer dat ik macrocode schreef om een draaitabel te maken, was het een mislukking.

Sindsdien heb ik meer geleerd van mijn slechte codering dan van codes die echt werken.

Vandaag laat ik u een eenvoudige manier zien om uw draaitabellen te automatiseren met behulp van macrocode.

Wanneer u normaal gesproken een draaitabel in een spreadsheet invoegt, gebeurt dit via een eenvoudig proces, maar dit hele proces gaat zo snel dat u nooit merkt wat er is gebeurd.

In VBA is dit hele proces hetzelfde, het draait gewoon met code. In deze handleiding laat ik u elke stap zien en leg ik uit hoe u er code voor schrijft.

Kijk maar eens naar het onderstaande voorbeeld, waar u deze macrocode met een knop kunt uitvoeren, en deze retourneert in een mum van tijd een nieuwe draaitabel in een nieuw werkblad.

Laten we zonder verder oponthoud beginnen met het schrijven van onze macrocode om een draaitabel te maken.

De 8 eenvoudige stappen om macrocode in VBA te schrijven om een draaitabel in Excel te maken

Voor uw gemak heb ik het hele proces in 8 eenvoudige stappen verdeeld. Nadat u deze stappen heeft gevolgd, kunt u al uw draaitabellen automatiseren.

Zorg ervoor dat u dit bestand hier downloadt om mee te volgen.

1. Declareer variabelen

De eerste stap is het declareren van de variabelen die we in onze code moeten gebruiken om verschillende dingen te definiëren.

 'Declare Variables Dim PSheet As Worksheet Dim DSheet As Worksheet Dim PCache As PivotCache Dim PTable As PivotTable Dim PRange As Range Dim LastRow As Long Dim LastCol As Long

In de bovenstaande code hebben we verklaard:

1. PSheet: om een blad te maken voor een nieuwe draaitabel.
2. DSheet: te gebruiken als technische fiche.
3. PChache: Gebruik als de naam voor de draaitabelcache.
4. PTable: Gebruik als naam voor onze draaitabel.
5. PRange: om het bereik van de brongegevens in te stellen.
6. LastRow en LastCol: om de laatste rij en kolom van ons gegevensbereik te verkrijgen.

2. Voeg een nieuw spreadsheet in

Voordat u een draaitabel maakt, voegt Excel een leeg blad in en maakt er vervolgens een nieuwe draaitabel op.

En de onderstaande code zal hetzelfde voor u doen.

Er wordt een nieuw werkblad ingevoegd met de naam “Draaitabel” vóór het actieve werkblad en als er al een werkblad met dezelfde naam is, wordt dit eerst verwijderd.

Na het invoegen van een nieuw werkblad stelt deze code de waarde in van de PSheet-variabele op het draaitabelwerkblad en DSheet op het brongegevenswerkblad.

 'Declare Variables On Error Resume Next Application.DisplayAlerts = False Worksheets("PivotTable").Delete Sheets.Add Before:=ActiveSheet ActiveSheet.Name = "PivotTable" Application.DisplayAlerts = True Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets("Data")

Zorg ervoor dat u de werkbladnamen in de code vervangt door de namen die u in uw gegevens heeft.

3. Stel het gegevensbereik in

De volgende stap is nu het gegevensbereik van het bronwerkblad in te stellen. Hier moet u op één ding letten: u kunt geen vast bronbereik opgeven.

U hebt code nodig die alle gegevens in het bronblad kan identificeren. En hieronder staat de code:

 'Define Data Range LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

Deze code begint vanaf de eerste cel van de gegevenstabel en selecteert tot de laatste rij en vervolgens tot de laatste kolom.

En ten slotte stelt u dit geselecteerde bereik in als bron. Het beste is dat u de gegevensbron niet elke keer hoeft te wijzigen terwijl u de draaitabel maakt.

4. Maak een draaitache

In Excel 2000 en hoger moet u, voordat u een draaitabel maakt, een draaitabel maken om de gegevensbron in te stellen.

Normaal gesproken maakt Excel, wanneer u een draaitabel maakt, automatisch een draaitabel zonder u dit te vragen, maar als u VBA moet gebruiken, moet u er code voor schrijven.

 'Define Pivot Cache Set PCache = ActiveWorkbook.PivotCaches.Create _ (SourceType:=xlDatabase, SourceData:=PRange). _ CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _ TableName:="SalesPivotTable")

Deze code werkt op twee manieren: ten eerste stelt u een draaitabel in met behulp van een gegevensbron, en ten tweede stelt u het celadres in het nieuw ingevoegde werkblad in om de draaitabel in te voegen.

U kunt de positie van de draaitabel wijzigen door deze code aan te passen.

5. Voeg een lege draaitabel in

Na de draaitabel is de volgende stap het invoegen van een lege draaitabel. Houd er rekening mee dat wanneer u een draaitabel maakt, u altijd eerst een lege draaitabel krijgt en vervolgens alle waarden, kolommen en rijen instelt.

Deze code zal hetzelfde doen:

 'Insert Blank Pivot Table Set PTable = PCache.CreatePivotTable _ (TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable")

Met deze code wordt een lege draaitabel gemaakt en deze ‘SalesPivotTable’ genoemd. U kunt deze naam wijzigen vanuit de code zelf.

6. Rij- en kolomvelden invoegen

Nadat u een lege draaitabel heeft gemaakt, is de volgende stap het invoegen van rij- en kolomvelden, zoals u normaal doet.

Voor elk rij- en kolomveld moet u een code schrijven. Hier willen we jaren en maanden toevoegen in het rijveld en gebieden in het kolomveld.

Hier is de code:

 'Insert Row Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Year") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Month") .Orientation = xlRowField .Position = 2 End With 'Insert Column Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Zone") .Orientation = xlColumnField .Position = 1 End With

In deze code heb je jaar en maand als twee velden vermeld. Als u nu naar de code kijkt, ziet u dat daar ook een positienummer staat. Dit positienummer definieert de volgorde van de velden.

Elke keer dat u meerdere velden (rij of kolom) moet toevoegen, geeft u hun positie op. En u kunt de velden wijzigen door hun naam uit de code te wijzigen.

7. Voeg een gegevensveld in

Het belangrijkste is om het waardeveld in uw draaitabel te definiëren.

De code voor het definiëren van waarden verschilt van het definiëren van rijen en kolommen, omdat we hier de opmaak van getallen, posities en functies moeten definiëren.

 'Insert Data Field With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Amount") .Orientation = xlDataField .Function = xlSum .NumberFormat = "#,##0" .Name = "Revenue " End With

Met bovenstaande code kunt u het bedrag als waardeveld toevoegen. En deze code formatteert de waarden als een getal met een scheidingsteken (,).

We gebruiken xlsum om de waarden op te tellen, maar je kunt ook xlcount en andere functies gebruiken.

8. Formatteer de draaitabel

Uiteindelijk moet u code gebruiken om uw draaitabel op te maken. Er is meestal een standaardopmaak in een draaitabel, maar u kunt deze opmaak wijzigen.

Met VBA kunt u de opmaakstijl in code instellen.

De code is:

 'Format Pivot TableActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = True ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9"

De bovenstaande code past lijnstroken en de stijl “Pivot Style Medium 9” toe, maar u kunt ook een andere stijl van deze link gebruiken.

Eindelijk is uw code klaar voor gebruik.

[VOLLEDIGE CODE] Gebruik VBA om een draaitabel in Excel te maken – Macro kopiëren en plakken

 Sub InsertPivotTable() 'Macro By Exceladvisor.org 'Declare Variables Dim PSheet As Worksheet Dim DSheet As Worksheet Dim PCache As PivotCache Dim PTable As PivotTable Dim PRange As Range Dim LastRow As Long Dim LastCol As Long 'Insert a New Blank Worksheet On Error Resume Next Application.DisplayAlerts = False Worksheets("PivotTable").Delete Sheets.Add Before:=ActiveSheet ActiveSheet.Name = "PivotTable" Application.DisplayAlerts = True Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets("Data") 'Define Data Range LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol) 'Define Pivot Cache Set PCache = ActiveWorkbook.PivotCaches.Create _ (SourceType:=xlDatabase, SourceData:=PRange). _ CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _ TableName:="SalesPivotTable") 'Insert Blank Pivot Table Set PTable = PCache.CreatePivotTable _ (TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable") 'Insert Row Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Year") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Month") .Orientation = xlRowField .Position = 2 End With 'Insert Column Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Zone") .Orientation = xlColumnField .Position = 1 End With 'Insert Data Field With ActiveSheet.PivotTables("SalesPivotTable").PivotFields ("Amount") .Orientation = xlDataField .Function = xlSum .NumberFormat = "#,##0" .Name = "Revenue " End With 'Format Pivot Table ActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = True ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9" End Sub

Draaitabel op bestaand werkblad

Met de code die we hierboven hebben gebruikt, wordt een draaitabel op een nieuw werkblad gemaakt, maar soms moet u een draaitabel invoegen in een werkblad dat al in de werkmap staat.

In de bovenstaande code (draaitabel in nieuw werkblad), in het gedeelte waar u de code hebt geschreven om een nieuw werkblad in te voegen, geeft u dit een naam. Breng enkele wijzigingen aan in de code.

Geen zorgen; Ik zal het je laten zien.

Eerst moet u het werkblad opgeven (al in de werkmap) waar u uw draaitabel wilt invoegen.

En hiervoor moet u de onderstaande code gebruiken:

In plaats van een nieuw werkblad in te voegen, moet u de naam van het werkblad opgeven in de PSheet-variabele.

 Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets(“Data”)

Er is nog een beetje meer te doen. De eerste code die u gebruikte, verwijdert het werkblad met dezelfde naam (indien aanwezig) voordat het draaipunt wordt ingevoegd.

Wanneer u een draaitabel in het bestaande werkblad invoegt, heeft u daar mogelijk al een draaitabel met dezelfde naam.

Wat ik bedoel is dat je eerst dat draaipunt moet verwijderen.

Om dit te doen, moet u de code toevoegen die de spil met dezelfde naam uit het werkblad moet verwijderen (als deze er is) voordat u een nieuwe invoegt.

Hier is de code die je moet toevoegen:

 Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets(“Data”) Worksheets("PivotTable").Activate On Error Resume Next ActiveSheet.PivotTables("SalesPivotTable").TableRange2.Clear

Laat me je vertellen wat deze code doet.

Ten eerste wordt PSheet ingesteld als het werkblad waarin u de draaitabel al in uw werkmap wilt invoegen en worden gegevenswerkbladen ingesteld als DSheet.

Daarna wordt het werkblad geactiveerd en wordt de “Verkoopdraaitabel” eruit verwijderd.

Belangrijk: Als de namen van de werkbladen in uw werkmap verschillen, kunt u deze vanuit code wijzigen. Ik heb de code gemarkeerd waar je dit moet doen.

Aan het einde,

Met deze code kunnen wij uw draaitabellen automatiseren. En het beste is dat het een unieke opzet is; daarna hebben we slechts één klik nodig om een draaitabel te maken en kunt u veel tijd besparen. Vertel me nu één ding.

Heeft u ooit VBA-code gebruikt om een draaitabel te maken?

Deel uw mening met mij in het opmerkingenveld; Ik deel ze graag met jou en deel deze tip met je vrienden.

Soortgelijke artikelen:

• Voeg een eindtotaal toe aan of verwijder het uit een draaitabel in Excel
• Voeg het lopende totaal toe aan een draaitabel in Excel
• Update automatisch een draaitabel in Excel
• Voeg een berekend veld en item toe
• Verwijder een draaitabel in Excel