Excel VBA-draaitabel

VBA-draaitabel helpt u om rapporten uit een grote gegevensset samen te vatten. Pivot kan worden beschouwd als een kleine vorm van de volledige gegevensset. De snelle weergave van grote gegevensverzamelingen is mogelijk via een draaitabel. Een draaitabel is een eenvoudige manier om gegevens dienovereenkomstig te filteren. Uit de beschikbare gegevens kunt u gegevens markeren zoals u dat wilt. Met een draaitabel kunt u de enorme gegevens combineren om de gegevens te analyseren en rapporten te produceren die voldoen aan uw zakelijke vereisten.

De excel biedt een ingebouwde draaitabel die eenvoudig te maken is door een draaitabel toe te passen op een gegevensblad. Het is mogelijk om automatisch een rapport te genereren in Excel zodra de gegevens zijn aangeleverd. VBA-codes helpen u bij het maken van een automatische draaitabel.

Hoe maak je een draaitabel met Excel VBA?

De gebruikelijke stappen om een ​​draaitabel in te voegen, is om eerst een draaitabel in te voegen vanuit het menu Invoegen en vervolgens de tabellen te selecteren die u in een draaitabel wilt veranderen. De geselecteerde tabel wordt de brongegevens en de draaitabel wordt dienovereenkomstig gemaakt.

Om een ​​spilrapport te maken, moet u de velden voor de filters, waarden, enz. Selecteren. Op dezelfde manier kunnen deze stappen worden geautomatiseerd met behulp van de VBA-codes. We hebben Excel-gegevens nodig om met een draaitabel te kunnen werken. Laten we leren hoe we een draaitabel kunnen maken via de Excel VBA-codes.

U kunt deze VBA-draaitabel Excel-sjabloon hier downloaden - VBA-draaitabel Excel-sjabloon

Stap 1 : draaitabel moet als een referentieobject worden beschouwd. Maak een object voor de draaitabel op verdere codes. Dit object wordt gebruikt om de draaitabel te wijzen. Noem de variabele als pvtable. Declareer een functie en draaitabelobject.

Code:

 Sub PivotTable () Dim pvtable As PivotTable End Sub

Stap 2: Om de gegevensbestanden te behouden, hebben we een draaicache nodig om de bron van de gegevens aan te geven. Voordat u een draaitabel maakt, declareert u een draaitabelvariabele. Verklaar de variabele pvcache voor pivot cache.

Code:

 Dim pvcache As PivotCache

Stap 3: De gegevens bevinden zich in het werkblad en moeten een bereik specificeren om de cel te raken die u nodig hebt. In de draaitabel worden de brongegevens verspreid als rijen en kolommen, dus om een ​​bepaald bereik aan te wijzen hebben we een variabele nodig. Definieer pvrange als een bereikvariabele.

Code:

 Dim bereik als bereik

Stap 4: Een werkblad moet de draaitabel invoegen die u moet maken. Declareer een variabele als een werkblad. Definieer pvsheet als een werkblad.

Code:

 Dim pvsheet als werkblad

Stap 5: U hebt een vergelijkbare variabele nodig om het gegevensblad te gebruiken, dat de gegevens moet bevatten die u als draaitabel wilt plotten. Dus de datasheetvariabele wordt gedeclareerd als pdsheet.

Code:

 Dim pdsheet als werkblad

Stap 6: U hebt nog twee variabelen nodig als lang datatype om de laatst gebruikte rij en kolom aan te geven voor het maken van draaitabellen. Dit kan elke rij of kolom zijn, dus er zijn kansen dat een aantal rijen en kolommen de limiet van het gegevenstype voor gehele getallen overschrijdt. Laten we het plr en plc noemen.

Code:

 Dim plr As Long Dim plc As Long

Stap 7: De volgende stap is verwijderen als er al een draaitabel is gemaakt. Dit helpt verwarring te voorkomen over welke tabel de brongegevens moeten worden geplot. Verwijder dus het vorige draaitabelblad en maak een nieuw blad om de draaitabel in te voegen.

Code:

 On Error Resume Next Application.DisplayAlerts = False Application.ScreenUpdating = False Worksheets ("pvsheet"). Verwijderen 'om de bestaande draaitabel in het werkblad te verwijderen Worksheets.Add After: = ActiveSheet' om een ​​nieuw werkblad toe te voegen ActiveSheet.Name = " pvsheet "'om het werkblad te hernoemen naar" pvsheet "bij fout Ga naar 0

Stap 8: Ken een objectvariabele voor respectievelijk het zwenkblad en gegevensblad toe aan de variabelen pvsheet en pdsheet. Dit wordt verder gebruikt voor het specificeren van de werkbladen.

Code:

 Set pvsheet = Worksheets ("pvsheet") Set pdsheet = Worksheets ("pdsheet")

Stap 9: Nadat de werkbladen zijn ingesteld, is het volgende item dat we de laatst gebruikte rij en kolom nodig hebben om een ​​draaiverslag te maken. Zoek de laatst gebruikte rij en kolom met behulp van de gedeclareerde variabelen plr en plc.

Code:

 'twee variabele om de laatst gebruikte rij en kolom in pdsheet te vinden plr = pdsheet.Cells (Rows.Count, 1) .End (xlUp) .Row plc = pdsheet.Cells (1, Columns.Count) .End (xlToLeft) .Column

Stap 10: Zoals eerder vermeld, worden de gegevens in het werkblad gespecificeerd door het cellenbereik. U moet het draaibereik instellen in de volgende stap. Het is al als variabel verklaard om het pivotbereik "pvrange" te gebruiken.

Code:

 'initialiseren van draaitabelgegevensbereik Set pvrange = pdsheet.Cells (1, 1) .Resize (plr, plc)

Aangezien het bereik wordt ingesteld met behulp van de eigenschap resize van celbereiken, wordt de grootte van het pvrange aangepast en wordt het pivotbereik dienovereenkomstig aangepast. Dus de pvrange zal zich aanpassen als er enige toevoeging of verwijdering van de rijen of kolommen plaatsvindt.

Stap 11: Het is tijd om de pivot cache in te stellen die de bron is van de draaitabel. Gebruik het object pvcache om de broncache in te stellen.

Code:

 'pivot cahe Set pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange)

Stap 12: Hier wordt het verkooprapport voor verschillende producten hierdoor omgezet in een draaitabel. Maak een draaitabel als leeg waaraan u de gegevensset verder kunt toevoegen.

Code:

 'nieuwe lege draaitabel Set pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cells (1, 1), TableName: = "Sales_Report")

Stap 13: Nadat de pivot is ingevoegd, moet u de verschillende velden opgeven die u in de draaitabel wilt invoegen. Voeg dus het eerste rij veld in. Hier begint de eerste rij met het product.

Code:

 'Product invoegen in rij gearchiveerd met pvsheet.PivotTables ("Sales_Report"). PivotFields ("Product"). Orientation = xlRowField. Position = 1 End With

Stap 14: De volgende is om het tweede veld op te geven dat u in de draaitabel wilt invoegen. Voeg op dezelfde manier de veldstraat van de tweede rij in de draaitabel in.

Code:

 'Voeg straat naar rij in Filed & position 2 With pvsheet.PivotTables ("Sales_Report"). PivotFields ("Street"). Orientation = xlRowField. Position = 2 End With

Stap 15: Voeg het volgende veld in de draaitabel in en het volgende is een stad. Geef de code om het stadsveld in te voegen.

Code:

 'Plaats stad invoegen in kolom opgeslagen met pvsheet.PivotTables ("Sales_Report"). PivotFields ("Town"). Oriëntatie = xlColumnField. Positie = 1 Einde met

Stap 16: Tot nu toe waren de ingevoegde gegevens van het teksttype. Nu moeten we het aantal verkopen in de draaitabel invoegen.

Code:

 'Voeg de kolom Sales in het gegevensveld in met pvsheet.PivotTables ("Sales_Report"). PivotFields ("Sales"). Orientation = xlDataField. Position = 1 End With

Stap 17: U hebt de velden ingevoegd die een draaitabel moeten maken. En de draaitabel is bijna klaar nu kunt u het formaat van de draaitabel instellen. Dit specificeert het type tabel via tabelstijl. De indeling van de rijassen wordt ook ingesteld zoals u dat wilt.

Code:

 'stel het formaat Draaitabel in pvsheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True pvsheet.PivotTables ("Sales_Report"). TableStyle2 = "PivotStyleMedium14"

Stap 18: Om de items met rij gearchiveerde waarden in tabelvorm weer te geven, voegt u de onderstaande code toe aan de onderkant.

Code:

 'om de draaitabel in tabelvorm pvsheet.PivotTables ("Sales_Report") weer te geven. RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True

Stap 19: Druk op de knop Uitvoeren of druk op F5 om de code uit te voeren. Dit levert een draaitabel op uit het gegevensbronblad. De gegevens in rijen en kolommen worden gewijzigd in de draaitabel. De draaitabel is zichtbaar op het werkblad van de draaitabel.

Controleer de uitvoer en u kunt zien dat de gegevensbron is geconverteerd naar de draaitabel zoals hieronder, de genoemde kolommen worden omgezet in de filterformulieren.

Draaivelden zijn aan de rechterkant zichtbaar. U kunt wijzigingen aanbrengen op basis van uw behoefte aan hoe de gegevens moeten worden weergegeven.

Ter referentie heb ik onderstaande code gegeven.

Code:

 Sub PivotTable () Dim pvtable As PivotTable Dim pvcache As PivotCache Dim pvrange As Range Dim pvsheet As Worksheet Dim pdsheet As Worksheet Dim plr Zo lang Dim plc Zo lang bij Fout Hervatten Volgende Application.DisplayAlerts = Valse Application.ScreenUpdating = False Worksheets ("pvsheet "). Verwijderen 'om de bestaande draaitabel in het werkblad te verwijderen Werkbladen. Na toevoegen: = ActiveSheet' om een ​​nieuw werkblad toe te voegen ActiveSheet.Name =" pvsheet "om het werkblad te hernoemen naar" pvsheet "Bij fout GoTo 0 pvsheet instellen = Werkbladen ("pvsheet") Stel pdsheet = Worksheets ("pdsheet") in 'twee variabele om de laatst gebruikte rij en kolom in pdsheet te vinden plr = pdsheet.Cells (Rows.Count, 1) .End (xlUp) .Row plc = pdsheet. Cellen (1, Columns.Count) .Einde (xlToLeft). Kolom 'initialiseren draaitabel gegevensbereik instellen pvrange = pdsheet.Cells (1, 1) .Resize (plr, plc)' pivot cahe Set pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange) 'nieuwe lege draaitabel Set pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cel ls (1, 1), TableName: = "Sales_Report") 'Product invoegen in rij gearchiveerd met pvsheet.PivotTables ("Sales_Report"). PivotFields ("Product"). Oriëntatie = xlRowField. Position = 1 End with' Insert Street to Rij ingediend & positie 2 Met pvsheet.PivotTables ("Sales_Report"). PivotFields ("Street"). Oriëntatie = xlRowField .Position = 2 Eind met 'Plaats invoegen in kolom Ingediend met pvsheet.PivotTables ("Sales_Report"). PivotFields (" Town ") .Orientation = xlColumnField .Position = 1 Einde met 'Verkoopkolom invoegen in het gegevensveld met pvsheet.PivotTables (" Sales_Report "). PivotFields (" Sales "). Oriëntatie = xlDataField .Position = 1 Einde met' stel de formatteren draaitabel pvsheet.PivotTables ( "Sales_Report"). ShowTableStyleRowStripes = True pvsheet.PivotTables ( "Sales_Report"). TableStyle2 = "PivotStyleMedium14" om de spillijst in tabelvorm pvsheet.PivotTables ( "Sales_Report"). RowAxisLayout xlTabularRow Application tonen .DisplayAlerts = True Application.ScreenUpdating = True End Sub

Dingen om te onthouden

  • Geef het bronblad op waar de gegevens moeten worden geleverd voor een draaitabel.
  • De startrij en kolom moeten correct worden vermeld.
  • Gebruik de benodigde objecten om de draaicache, het bereik, het begin en het einde van rijen te bepalen.
  • Schik de gegevensbron met het juiste formaat, aangezien dit het automatiseringsproces is.

Aanbevolen artikelen

Dit is een handleiding voor de VBA-draaitabel. Hier bespreken we hoe u een draaitabel kunt maken met behulp van VBA-codes in Excel samen met een voorbeeld en een downloadbare Excel-sjabloon. U kunt ook de volgende artikelen bekijken voor meer informatie -

  1. Hoe draaitabel in Excel te verwijderen?
  2. VBA gebruiken in PowerPoint
  3. Draaitabel met meerdere vellen
  4. Hoe Pivot Table Slicer maken?
  5. Vernieuw de draaitabel in VBA
  6. VBA On Error Resume Next (voorbeelden)
  7. Excel VBA ScreenUpdating

Categorie:

VBA