Excel VBA-Pivot-Tabelle
Mit der VBA-Pivot-Tabelle können Sie Berichte aus einem großen Datensatz zusammenfassen. Pivot kann als winzige Form des gesamten Datensatzes betrachtet werden. Die Schnellansicht großer Datenmengen ist über eine Pivot-Tabelle möglich. Eine Pivot-Tabelle ist eine einfache Möglichkeit, Daten entsprechend zu filtern. Aus den verfügbaren Daten können Sie die gewünschten Daten hervorheben. Mithilfe einer Pivot-Tabelle können Sie die riesigen Datenmengen kombinieren, um die Daten zu analysieren und Berichte zu erstellen, die Ihren Geschäftsanforderungen entsprechen.
Das Excel bietet eine integrierte Pivot-Tabelle, die einfach erstellt werden kann, indem eine Pivot-Tabelle über einem Datenblatt angewendet wird. Es ist möglich, einen Bericht automatisch in Excel zu erstellen, sobald die Daten geliefert wurden. Mithilfe von VBA-Codes können Sie eine automatische Pivot-Tabelle erstellen.
Erstellen einer Pivot-Tabelle mit Excel VBA
Die üblichen Schritte zum Einfügen einer Pivot-Tabelle bestehen darin, zuerst eine Pivot-Tabelle aus dem Menü Einfügen einzufügen und dann die Tabellen auszuwählen, die Sie in eine Pivot-Tabelle ändern möchten. Die ausgewählte Tabelle wird zu den Quelldaten, und die Pivot-Tabelle wird entsprechend erstellt.
Um einen Pivot-Bericht zu erstellen, müssen Sie die Felder für die Filter, Werte usw. auswählen. Auf ähnliche Weise können diese Schritte mithilfe der VBA-Codes automatisiert werden. Wir benötigen ein Excel-Sheet, um mit einer Pivot-Tabelle arbeiten zu können. Lassen Sie uns lernen, wie Sie mithilfe der Excel VBA-Codes eine Pivot-Tabelle erstellen.
Sie können diese Excel-Vorlage für VBA-Pivot-Tabellen hier herunterladen - Excel-Vorlage für VBA-Pivot-TabellenSchritt 1 : Pivot-Tabelle sollte als Referenzobjekt betrachtet werden. Erstellen Sie ein Objekt für die Pivot-Tabelle mit weiteren Codes. Dieses Objekt wird verwendet, um auf die Pivot-Tabelle zu zeigen. Benennen Sie die Variable als pvtable. Deklarieren Sie eine Funktion und ein Pivot-Tabellenobjekt.
Code:
Sub PivotTable () Dim pvtable As PivotTable End Sub
Schritt 2: Um die Datendateien zu behalten, benötigen wir einen Pivot-Cache, um die Datenquelle zu deklarieren. Vor dem Erstellen einer Pivot-Tabelle deklarieren Sie eine Pivot-Cash-Variable. Deklarieren Sie die Variable pvcache für den Pivot-Cache.
Code:
Pvcache als PivotCache dimmen
Schritt 3: Die Daten befinden sich innerhalb des Arbeitsblatts und sollten einen Bereich angeben, der die gewünschte Zelle trifft. In der Pivot-Tabelle sind die Quelldaten als Zeilen und Spalten verteilt, sodass für einen bestimmten Bereich eine Variable benötigt wird. Definieren Sie pvrange als Bereichsvariable.
Code:
Dim pvrange As Range
Schritt 4: Ein Arbeitsblatt muss die Pivot-Tabelle einfügen, die Sie erstellen müssen. Deklarieren Sie eine Variable als Arbeitsblatt. Definieren Sie pvsheet als Arbeitsblatt.
Code:
Pvsheet als Arbeitsblatt dimmen
Schritt 5: Sie benötigen eine ähnliche Variable, um das Datenblatt zu verwenden, das die Daten enthalten soll, die Sie als Pivot-Tabelle darstellen möchten. Daher wird die Datenblattvariable als pdsheet deklariert.
Code:
Pdsheet als Arbeitsblatt dimmen
Schritt 6: Sie benötigen zwei weitere Variablen als langen Datentyp, um die zuletzt verwendete Zeile und Spalte für die Erstellung der Pivot-Tabelle anzugeben. Dies kann eine beliebige Zeile oder Spalte sein, sodass die Wahrscheinlichkeit besteht, dass eine Anzahl von Zeilen und Spalten über die Datentypbeschränkung für Ganzzahlen hinausgeht. Nennen wir es plr und plc.
Code:
Dim plr as long dim plc as long
Schritt 7: Im nächsten Schritt löschen Sie, ob bereits eine Pivot-Tabelle erstellt wurde. Dies hilft zu vermeiden, dass Unklarheiten darüber entstehen, in welcher Tabelle die Quelldaten aufgezeichnet werden sollen. Löschen Sie daher das vorherige Pivot-Tabellenblatt und erstellen Sie ein neues Blatt, um die Pivot-Tabelle einzufügen.
Code:
On Error Resume Next Application.DisplayAlerts = False Application.ScreenUpdating = False Arbeitsblätter ("pvsheet"). Löschen Sie ", um die vorhandene Pivot-Tabelle im Arbeitsblatt zu löschen. Hinzufügen nach: = ActiveSheet", um ein neues Arbeitsblatt hinzuzufügen. ActiveSheet.Name = " pvsheet "', um das Arbeitsblatt in" pvsheet "umzubenennen. On Error GoTo 0
Schritt 8: Ordnen Sie den Variablen pvsheet und pdsheet eine Objektvariable für das Drehblatt bzw. das Datenblatt zu. Dies wird weiter verwendet, um die Arbeitsblätter festzulegen.
Code:
Set pvsheet = Arbeitsblätter ("pvsheet") Set pdsheet = Arbeitsblätter ("pdsheet")
Schritt 9: Sobald die Arbeitsblätter festgelegt sind, benötigen wir als nächstes die zuletzt verwendete Zeile und Spalte, um einen Pivot-Bericht zu erstellen. Suchen Sie die zuletzt verwendete Zeile und Spalte mit den deklarierten Variablen plr und plc.
Code:
'zwei Variablen zum Suchen der zuletzt verwendeten Zeile und Spalte in pdsheet plr = pdsheet.Cells (Rows.Count, 1) .End (xlUp) .Row plc = pdsheet.Cells (1, Columns.Count) .End (xlToLeft) .Column
Schritt 10: Wie bereits erwähnt, werden die Daten im Arbeitsblatt durch den Zellbereich bestimmt. Im nächsten Schritt müssen Sie den Schwenkbereich einstellen. Es ist bereits als Variable deklariert, den Pivot-Bereich "pvrange" zu verwenden.
Code:
'Datenbereich der Pivot-Tabelle initialisieren Set pvrange = pdsheet.Cells (1, 1) .Resize (plr, plc)
Da der Bereich unter Verwendung der Größenänderungseigenschaft von Zellbereichen festgelegt wird, wird durch die Größenänderung des pv-Bereichs der Schwenkbereich entsprechend angepasst. Der pvrange wird also angepasst, wenn Zeilen oder Spalten hinzugefügt oder gelöscht werden.
Schritt 11: Es ist Zeit, den Pivot-Cache festzulegen, der die Quelle der Pivot-Tabelle ist. Verwenden Sie das Objekt pvcache, um den Quellcache festzulegen.
Code:
Setzen Sie pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange)
Schritt 12: Hier wird der Verkaufsbericht für verschiedene Produkte in eine Pivot-Tabelle umgewandelt. Erstellen Sie eine Pivot-Tabelle als Leerzeichen, zu der Sie den Datensatz weiter hinzufügen können.
Code:
'neue leere Pivot-Tabelle Set pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cells (1, 1), TableName: = "Sales_Report")
Schritt 13: Nach dem Einfügen des Pivots müssen Sie die verschiedenen Felder angeben, die Sie in die Pivottabelle einfügen möchten. Fügen Sie also das Feld in der ersten Zeile ein. Hier beginnt die erste Reihe mit dem Produkt.
Code:
'Produkt in Zeile einfügen, die mit pvsheet.PivotTables ("Sales_Report") abgelegt wurde. PivotFields ("Product") .Orientation = xlRowField .Position = 1 End With
Schritt 14: Als Nächstes geben Sie das zweite Feld an, das Sie in die Pivot-Tabelle einfügen möchten. Fügen Sie auf die gleiche Weise die Feldstraße in der zweiten Zeile in die Pivot-Tabelle ein.
Code:
'Straße in Zeile einfügen, abgelegt & Position 2 mit pvsheet.PivotTables ("Sales_Report"). PivotFields ("Street") .Orientation = xlRowField .Position = 2 End With
Schritt 15: Fügen Sie das nächste Feld in die Pivot-Tabelle ein und das nächste ist eine Stadt. Geben Sie den Code ein, um das Stadtfeld einzufügen.
Code:
'Ort in Spalte einfügen, die mit pvsheet.PivotTables ("Sales_Report") abgelegt wurde. PivotFields ("Town") .Orientation = xlColumnField .Position = 1 End With
Schritt 16: Bis dahin waren die eingegebenen Daten vom Typ Text. Jetzt müssen wir die Anzahl der Verkäufe in die Pivot-Tabelle einfügen.
Code:
'Fügen Sie die Spalte Sales in das Datenfeld With pvsheet.PivotTables ("Sales_Report") ein. PivotFields ("Sales") .Orientation = xlDataField .Position = 1 End With
Schritt 17: Sie haben die Felder eingefügt, die zum Erstellen einer Pivot-Tabelle erforderlich sind. Und der Pivot-Tisch ist fast fertig. Jetzt können Sie das Format des Pivot-Tisches festlegen. Dadurch wird der Tabellentyp über den Tabellenstil festgelegt. Das Layout der Zeilenachse wird ebenfalls wie gewünscht festgelegt.
Code:
'setze das Format Pivot Table pvsheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True pvsheet.PivotTables ("Sales_Report"). TableStyle2 = "PivotStyleMedium14"
Schritt 18: Um die Elemente der Zeilenfeldwerte in Tabellenform anzuzeigen, fügen Sie den folgenden Code am unteren Rand hinzu.
Code:
'um die Pivot-Tabelle in Tabellenform anzuzeigen pvsheet.PivotTables ("Sales_Report"). RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True
Schritt 19: Drücken Sie die Run-Taste oder drücken Sie F5, um den Code auszuführen. Dadurch wird eine Pivot-Tabelle aus dem Datenquellblatt erstellt. Die in Zeilen und Spalten angegebenen Daten werden in die Pivot-Tabelle geändert. Die Pivot-Tabelle wird auf dem Arbeitsblatt der Pivot-Tabelle angezeigt.
Überprüfen Sie die Ausgabe und Sie können sehen, dass die Datenquelle in die Pivot-Tabelle wie folgt konvertiert wird. Die genannten Spalten werden in die Filterformulare konvertiert.
Auf der rechten Seite sind Drehfelder sichtbar. Sie können je nach Bedarf Änderungen vornehmen, damit die Daten angezeigt werden.
Zu Ihrer Information habe ich den folgenden Code angegeben.
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 As Long Dim plc As Long On Error Resume Next Application.DisplayAlerts = False Application.ScreenUpdating = False Worksheets ("pvsheet ") .Löschen Sie ', um die vorhandene Pivot-Tabelle in den Arbeitsblättern zu löschen. Hinzufügen nach: = ActiveSheet', um ein neues Arbeitsblatt hinzuzufügen. ActiveSheet.Name =" pvsheet "', um das Arbeitsblatt in" pvsheet "umzubenennen. Bei Fehler Gehe zu 0 Setze pvsheet = Arbeitsblätter ("pvsheet") Setzen Sie die Variable pdsheet = Arbeitsblätter ("pdsheet") auf zwei, um die zuletzt verwendete Zeile und Spalte in pdsheet zu finden. Plr = pdsheet.Cells (Rows.Count, 1) .End (xlUp) .Row plc = pdsheet. Cells (1, Columns.Count) .End (xlToLeft) .Column 'initialisiert den Datenbereich der Pivot-Tabelle. Set pvrange = pdsheet.Cells (1, 1) .Resize (plr, plc)' Pivot Cahe Set pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange) 'neue leere Pivot-Tabelle Set pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cel ls (1, 1), TableName: = "Sales_Report") 'Produkt in Zeile einfügen, die mit pvsheet.PivotTables ("Sales_Report") abgelegt wurde. PivotFields ("Product") .Orientation = xlRowField .Position = 1 End With' Insert Street to Zeile abgelegt und Position 2 mit pvsheet.PivotTables ("Sales_Report"). PivotFields ("Street") .Orientation = xlRowField .Position = 2 Ende mit 'Ort in Spalte einfügen, die mit pvsheet.PivotTables ("Sales_Report") abgelegt wurde. PivotFields ("). Town ") .Orientation = xlColumnField .Position = 1 End With 'Sales-Spalte in das Datenfeld einfügen With pvsheet.PivotTables (" Sales_Report "). PivotFields (" Sales ") .Orientation = xlDataField .Position = 1 End With' setzt das Pivot Table pvsheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True pvsheet.PivotTables ("Sales_Report"). TableStyle2 = "PivotStyleMedium14" 'zum Anzeigen der PivotTables ("Sales_Report") .DisplayAlerts = True Application.ScreenUpdating = True End Sub
Dinge, an die man sich erinnern sollte
- Geben Sie das Quellblatt an, in dem die Daten für eine Pivot-Tabelle bereitgestellt werden sollen.
- Die Anfangszeile und die Anfangsspalte sollten korrekt angegeben werden.
- Verwenden Sie die erforderlichen Objekte, um den Pivot-Cache, den Bereich, den Anfang und das Ende von Zeilen zu bestimmen.
- Ordnen Sie die Datenquelle mit dem richtigen Format an, da dies der Automatisierungsprozess ist.
Empfohlene Artikel
Dies ist eine Anleitung zur VBA-Pivot-Tabelle. Hier wird erläutert, wie eine Pivot-Tabelle mit VBA-Codes in Excel zusammen mit einem Beispiel und einer herunterladbaren Excel-Vorlage erstellt wird. Sie können sich auch die folgenden Artikel ansehen, um mehr zu erfahren -
- So löschen Sie Pivot-Tabelle in Excel?
- Verwenden von VBA in PowerPoint
- Drehtisch mit mehreren Blättern
- Wie erstelle ich einen Pivot Table Slicer?
- Aktualisieren Sie die Pivot-Tabelle in VBA
- VBA On Error Resume Next (Beispiele)
- Excel VBA ScreenUpdating