VBA-Pivot-Tabelle

Excel VBA Pivot-Tabelle

Pivot-Tabellen sind das Herzstück der Zusammenfassung des Berichts über eine große Datenmenge. Wir können auch den Prozess der Erstellung einer Pivot-Tabelle durch VBA-Codierung automatisieren. Sie sind ein wichtiger Bestandteil jedes Berichts oder Dashboards. In Excel ist es einfach, Tabellen mit einer Schaltfläche zu erstellen. In VBA müssen wir jedoch einige Codes schreiben, um unsere Pivot-Tabelle zu automatisieren, bevor Excel 2007 und seine älteren Versionen in VBA nicht benötigt wurden Erstellen Sie einen Cache für Pivot-Tabellen, aber in Excel 2010 und seinen neueren Versionen sind Caches erforderlich.

VBA kann uns an unserem Arbeitsplatz eine Menge Zeit sparen, auch wenn es nicht so einfach ist, es zu meistern, aber es lohnt sich, Zeit zu investieren, um dies zu lernen. Ich habe 6 Monate gebraucht, um den Prozess des Erstellens von Pivot-Tabellen über VBA zu verstehen. Sie wissen, was diese 6 Monate für mich Wunder bewirkt haben, weil ich beim Versuch, die Pivot-Tabelle zu erstellen, so viele Fehler gemacht habe.

Tatsächlich habe ich aus meinen Fehlern gelernt und schreibe jetzt diesen Artikel, um Ihnen zu zeigen, wie Sie Pivot-Tabellen mit Code erstellen.

Mit nur einem Klick auf eine Schaltfläche können wir Berichte erstellen.

Schritte zum Erstellen einer Pivot-Tabelle in VBA

Sie können diese VBA Pivot-Tabellenvorlage hier herunterladen - VBA Pivot-Tabellenvorlage

Um eine Pivot-Tabelle zu erstellen, ist es wichtig, Daten zu haben. Dafür habe ich einige Dummy-Daten erstellt, Sie können die Arbeitsmappe herunterladen, um mit mir die gleichen Daten zu folgen.

Schritt 1:  Pivot-Tabelle ist ein Objekt, das auf die Pivot-Tabelle verweist und die Variable als PivotTables deklariert.

Code:

 Sub PivotTable () Dim PTable As PivotTable End Sub 

Schritt 2:  Bevor wir zuerst eine Pivot-Tabelle erstellen, müssen wir einen Pivot-Cache erstellen, um die Datenquelle zu definieren.

In einer regulären Arbeitsblatt-Pivot-Tabelle wird ohne Probleme ein Pivot-Cache im Hintergrund erstellt. Aber in VBA müssen wir erstellen.

Definieren Sie dazu die Variable einen PivotCache.

Code:

 PCache als PivotCache dimmen

Schritt 3:  Um den Pivot-Datenbereich zu bestimmen, definieren Sie die Variable als Bereich.

Code:

 PRange As Range dimmen

Schritt 4:  Um eine Pivot-Tabelle einzufügen, benötigen wir ein separates Blatt, um ein Arbeitsblatt für die Pivot-Tabelle hinzuzufügen. Deklarieren Sie die Variable als Arbeitsblatt.

Code:

 PSheet als Arbeitsblatt dimmen

Schritt 5:  Um auf die Daten zu verweisen, die das Arbeitsblatt enthalten, deklarieren Sie eine weitere Variable als Arbeitsblatt.

Code:

 DSheet als Arbeitsblatt dimmen

Schritt 6: Um die zuletzt verwendete Zeile und Spalte zu finden, definieren Sie zwei weitere Variablen als Long.

Code:

 Dim LR So lange Dim LC As Long 

Schritt 7: Jetzt müssen wir ein neues Blatt einfügen, um eine Pivot-Tabelle zu erstellen. Wenn zuvor ein Pivot Sheet vorhanden ist, müssen wir dieses löschen.

Schritt 8: Setzen Sie nun die Objektvariablen PSheet und DSheet auf Pivot Sheet bzw. Data Sheet.

Schritt 9: Suchen Sie die zuletzt verwendete Zeile und die zuletzt verwendete Spalte im Datenblatt.

Schritt 10: Stellen Sie nun den Drehbereich mithilfe der letzten Zeile und der letzten Spalte ein.

Dadurch wird der Datenbereich perfekt eingestellt. Der Datenbereich wird automatisch ausgewählt, auch wenn Daten im Datenblatt hinzugefügt oder gelöscht werden.

Schritt 11: Bevor wir eine Pivot-Tabelle erstellen, müssen wir einen Pivot-Cache erstellen. Legen Sie die Pivot-Cache-Variable mithilfe des folgenden VBA-Codes fest.

Schritt 12: Erstellen Sie nun eine leere Pivot-Tabelle.

Schritt 13: Nach dem Einfügen der Pivot-Tabelle müssen wir zuerst das Zeilenfeld einfügen. Also werde ich das Zeilenfeld als meine Länderspalte einfügen.

Hinweis: Laden Sie die Arbeitsmappe herunter, um die Datenspalten zu verstehen.

Schritt 14: Nun ein weiteres Element, das ich als zweites Positionselement in das Zeilenfeld einfügen werde. Ich werde das Produkt als zweite Werbebuchung in das Zeilenfeld einfügen.

Schritt 15: Nach dem Einfügen der Spalten in das Zeilenfeld müssen Werte in das Spaltenfeld eingefügt werden. Ich werde das "Segment" in das Spaltenfeld einfügen.

Schritt 16: Jetzt müssen wir Zahlen in das Datenfeld einfügen. Fügen Sie also "Sales" in das Datenfeld ein.

Schritt 17: Wir sind mit dem Pivot-Tabellenzusammenfassungsteil fertig. Jetzt müssen wir die Tabelle formatieren. Verwenden Sie den folgenden Code, um die Pivot-Tabelle zu formatieren.

Hinweis: Um mehr verschiedene Tabellenstile zu erhalten, zeichnen Sie diese Makros auf und rufen Sie die Tabellenstile ab.

Fügen Sie den folgenden Code unten hinzu, um die Elemente mit den Zeilenwerten in Tabellenform anzuzeigen.

Ok, wir sind fertig, wenn wir diesen Code mit der Taste F5 oder manuell ausführen, sollten wir die Pivot-Tabelle wie folgt erhalten.

Auf diese Weise können wir mithilfe der VBA-Codierung den Prozess der Erstellung einer Pivot-Tabelle automatisieren.

Als Referenz habe ich den folgenden Code angegeben.

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 Fortsetzen Nächste Anwendung.DisplayAlerts = False Application.ScreenUpdating = False Worksheets ("Pivot Sheet "). Delete 'Dies löscht das vorhandene Arbeitsblatt der Pivot-Tabelle. Arbeitsblätter.Add After: = ActiveSheet' Dies fügt ein neues Arbeitsblatt hinzu. ActiveSheet.Name =" Pivot Sheet "'Dies benennt das Arbeitsblatt bei Fehler GoTo 0 in" Pivot Sheet "um Set PSheet = Arbeitsblätter ("Pivot Sheet") Set DSheet = Arbeitsblätter ("Datenblatt") 'Zuletzt verwendete Zeile und Spalte im Datenblatt suchen LR = DSheet.Cells (Rows.Count, 1) .End (xlUp) .Row LC = DSheet.Cells (1, Columns.Count) .End (xlToLeft) .Column 'Legen Sie den Datenbereich der Pivot-Tabelle fest. Set PRange = DSheet.Zellen (1, 1) .Resize (LR, LC) 'Pivot setzen cahe Set PCache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = PRange)' Leere Pivot-Tabelle erstellen Set PTable = PCache.CreatePivotTable (TableDestination: = PSheet. Cells (1, 1), TableName: = "Sales_Report") 'Land in Zeile einfügen, die mit PSheet.PivotTables ("Sales_Report") abgelegt wurde. PivotFields ("Country") .Orientation = xlRowField .Position = 1 End With' Produkt einfügen in Zeile abgelegt & Position 2 mit PSheet.PivotTables ("Sales_Report"). PivotFields ("Produkt") .Orientation = xlRowField .Position = 2 Ende mit 'Segment in Spalte einfügen abgelegt & Position 1 mit PSheet.PivotTables ("Sales_Report"). PivotFields ("Segment") .Orientation = xlColumnField .Position = 1 End With 'Fügen Sie die Spalte Sales in das Datenfeld With PSheet ein.PivotTables ("Sales_Report"). PivotFields ("Sales") .Orientation = xlDataField .Position = 1 End With 'Format Pivot Table PSheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True PSheet.PivotTables ("Sales_Reportle") "PivotStyleMedium14" 'In tabellarischer Form anzeigen PSheet.PivotTables ("Sales_Report"). RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub) .RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub) .RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub