Excel VBA-draaitabel
Draaitabellen vormen de kern van het samenvatten van het rapport van een grote hoeveelheid gegevens. We kunnen ook het proces van het maken van een draaitabel automatiseren via VBA-codering. Ze zijn een belangrijk onderdeel van elk rapport of dashboard, in Excel is het eenvoudig om tabellen te maken met een knop, maar in VBA moeten we enkele codes schrijven om onze draaitabel te automatiseren, voordat Excel 2007 en zijn oudere versies in VBA dat niet nodig hadden maak een cache voor draaitabellen, maar in Excel 2010 en zijn nieuwere versies zijn caches vereist.
VBA kan ons veel tijd besparen op onze werkplek. Hoewel het beheersen niet zo eenvoudig is, is het de moeite waard om tijd te besteden om dit te leren. Het kostte me 6 maanden om het proces van het maken van draaitabellen via VBA te begrijpen. Je weet wat die 6 maanden wonderen voor me hebben gedaan, omdat ik zoveel fouten heb gemaakt bij het maken van de draaitabel.
Maar eigenlijk heb ik geleerd van mijn fouten, en nu schrijf ik dit artikel om je te laten zien hoe je draaitabellen kunt maken met behulp van code.
Met slechts een klik op de knop kunnen we rapporten maken.
Stappen om een draaitabel te maken in VBA
Om een draaitabel te maken, is het belangrijk om gegevens te hebben. Hiervoor heb ik enkele dummy-gegevens gemaakt. U kunt het werkboek downloaden om met mij te volgen met dezelfde gegevens.
Stap 1: Draaitabel is een object om naar de draaitabel te verwijzen, declareer de variabele als draaitabellen.
Code:
Sub PivotTable () Dim PTable As PivotTable End Sub
Stap 2: Voordat we eerst een draaitabel maken, moeten we een draaitabel maken om de bron van de gegevens te definiëren.
In de gewone werkbladen zal de draaitabel zonder ons lastig te vallen een draaicache op de achtergrond creëren. Maar in VBA moeten we creëren.
Definieer hiervoor de variabele een PivotCache.
Code:
Dim PCache als PivotCache
Stap 3: Om het pivot-gegevensbereik te bepalen, definieert u de variabele als een bereik.
Code:
Dim PRange als bereik
Stap 4: Om een draaitabel in te voegen, hebben we een apart blad nodig om werkbladen voor de draaitabel toe te voegen om de variabele als een werkblad te declareren.
Code:
Dim PSheet als werkblad
Stap 5: Om te verwijzen naar de gegevens die het werkblad bevatten, declareert u op dezelfde manier nog een variabele als werkblad.
Code:
Dim DSheet als werkblad
Stap 6: Definieer tot slot nog twee variabelen als Long om de laatst gebruikte rij en kolom te vinden.
Code:
Dim LR As Long Dim LC As Long
Stap 7: Nu moeten we een nieuw blad invoegen om een draaitabel te maken. Voordien, als er een draaiblad is, moeten we dat verwijderen.
Stap 8: Stel nu de objectvariabele PSheet en DSheet in op respectievelijk Pivot Sheet en Data Sheet.
Stap 9: Zoek de laatst gebruikte rij en de laatst gebruikte kolom in het gegevensblad.
Stap 10: Stel nu het draaibereik in door de laatste rij en laatste kolom te gebruiken.
Dit zal het gegevensbereik perfect instellen. Het selecteert automatisch het gegevensbereik, zelfs als er gegevens in het gegevensblad zijn toegevoegd of verwijderd.
Stap 11: Voordat we een draaitabel maken, moeten we een draaitabel maken. Stel de pivot-cachevariabele in met behulp van de onderstaande VBA-code.
Stap 12: Maak nu een lege draaitabel.
Stap 13: Nadat we de draaitabel hebben ingevoegd, moeten we eerst het rijveld invoegen. Dus ik zal het rijveld invoegen als mijn landkolom.
Opmerking: download de werkmap om de gegevenskolommen te begrijpen.
Stap 14: Nu zal ik nog een item in het rijveld invoegen als het tweede positie-item. Ik zal het product als het tweede regelitem in het rijveld invoegen.
Stap 15: Nadat we de kolommen in het rijveld hebben ingevoegd, moeten we waarden in het kolomveld invoegen. Ik zal het "Segment" invoegen in het kolomveld.
Stap 16: Nu moeten we getallen in het gegevensveld invoegen. Dus voeg "Sales" toe aan het dataveld.
Stap 17: We zijn klaar met het overzichtsgedeelte van de draaitabel. Nu moeten we de tabel formatteren. Gebruik de onderstaande code om de draaitabel op te maken.
Voeg de onderstaande code onderaan toe om de items met rij-gearchiveerde waarden in tabelvorm weer te geven.
Oké, we zijn klaar als we deze code uitvoeren met de F5-toets of handmatig, we zouden de draaitabel als volgt moeten krijgen.
Op deze manier kunnen we met VBA-codering het proces van het maken van een draaitabel automatiseren.
Ter referentie heb ik de onderstaande code gegeven.
Sub PivotTable() Dim PTable As PivotTable Dim PCache As PivotCache Dim PRange As Range Dim PSheet As Worksheet Dim DSheet As Worksheet Dim LR As Long Dim LC As Long On Error Resume Next Application.DisplayAlerts = False Application.ScreenUpdating = False Worksheets("Pivot Sheet").Delete 'This will delete the exisiting pivot table worksheet Worksheets.Add After:=ActiveSheet ' This will add new worksheet ActiveSheet.Name = "Pivot Sheet" ' This will rename the worksheet as "Pivot Sheet" On Error GoTo 0 Set PSheet = Worksheets("Pivot Sheet") Set DSheet = Worksheets("Data Sheet") 'Find Last used row and column in data sheet LR = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LC = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column 'Set the pivot table data range Set PRange = DSheet.Cells(1, 1).Resize(LR, LC) 'Set pivot cahe Set PCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=PRange) 'Create blank pivot table Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), TableName:="Sales_Report") 'Insert country to Row Filed With PSheet.PivotTables("Sales_Report").PivotFields("Country") .Orientation = xlRowField .Position = 1 End With 'Insert Product to Row Filed & position 2 With PSheet.PivotTables("Sales_Report").PivotFields("Product") .Orientation = xlRowField .Position = 2 End With 'Insert Segment to Column Filed & position 1 With PSheet.PivotTables("Sales_Report").PivotFields("Segment") .Orientation = xlColumnField .Position = 1 End With 'Insert Sales column to the data field With PSheet.PivotTables("Sales_Report").PivotFields("Sales") .Orientation = xlDataField .Position = 1 End With 'Format Pivot Table PSheet.PivotTables("Sales_Report").ShowTableStyleRowStripes = True PSheet.PivotTables("Sales_Report").TableStyle2 = "PivotStyleMedium14" 'Show in Tabular form PSheet.PivotTables("Sales_Report").RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
