Wie erstelle ich eine pivot-tabelle mit vba?

Bevor ich Ihnen diesen Leitfaden übergebe und anfange, VBA zum Erstellen einer Pivot-Tabelle zu verwenden , möchte ich Ihnen etwas sagen.

Den Umgang mit VBA habe ich erst vor SECHS Jahren gelernt. Und als ich zum ersten Mal Makrocode zum Erstellen einer Pivot-Tabelle schrieb, war es ein Fehlschlag.

Seitdem habe ich aus meiner schlechten Codierung mehr gelernt als aus Codes, die tatsächlich funktionieren.

Heute zeige ich Ihnen eine einfache Möglichkeit, Ihre Pivot-Tabellen mithilfe von Makrocode zu automatisieren.

Normalerweise geschieht das Einfügen einer Pivot-Tabelle in eine Tabelle durch einen einfachen Vorgang, aber dieser gesamte Vorgang ist so schnell, dass Sie nie bemerken, was passiert ist.

In VBA ist dieser gesamte Prozess derselbe, er wird nur mithilfe von Code ausgeführt. In dieser Anleitung zeige ich Ihnen jeden Schritt und erkläre, wie Sie Code dafür schreiben.

Schauen Sie sich einfach das Beispiel unten an, in dem Sie diesen Makrocode mit einer Schaltfläche ausführen können und er blitzschnell eine neue Pivot-Tabelle in einem neuen Arbeitsblatt zurückgibt.

Beginnen wir ohne weitere Umschweife mit dem Schreiben unseres Makrocodes, um eine Pivot-Tabelle zu erstellen.

Die 8 einfachen Schritte zum Schreiben von Makrocode in VBA zum Erstellen einer Pivot-Tabelle in Excel

Der Einfachheit halber habe ich den gesamten Vorgang in 8 einfache Schritte unterteilt. Nachdem Sie diese Schritte ausgeführt haben, können Sie alle Ihre Pivot-Tabellen automatisieren.

Laden Sie diese Datei unbedingt hier herunter, um mitzumachen.

1. Variablen deklarieren

Der erste Schritt besteht darin, die Variablen zu deklarieren, die wir in unserem Code verwenden müssen, um verschiedene Dinge zu definieren.

 '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

Im obigen Code haben wir Folgendes deklariert:

1. PSheet: um ein Blatt für eine neue Pivot-Tabelle zu erstellen.
2. DSheet: Zur Verwendung als technisches Blatt.
3. PChache: Als Name für den Pivot-Tabellen-Cache verwenden.
4. PTable: Als Namen für unsere Pivot-Tabelle verwenden.
5. PRange: um den Quelldatenbereich festzulegen.
6. LastRow und LastCol: um die letzte Zeile und Spalte unseres Datenbereichs abzurufen.

2. Fügen Sie eine neue Tabelle ein

Vor dem Erstellen einer Pivot-Tabelle fügt Excel ein leeres Blatt ein und erstellt darauf eine neue Pivot-Tabelle.

Und der folgende Code erledigt dasselbe für Sie.

Es wird ein neues Arbeitsblatt mit dem Namen „Pivot-Tabelle“ vor dem aktiven Arbeitsblatt eingefügt und wenn bereits ein Arbeitsblatt mit demselben Namen vorhanden ist, wird es zuerst gelöscht.

Nach dem Einfügen eines neuen Arbeitsblatts legt dieser Code den Wert der PSheet-Variablen im Pivot-Tabellen-Arbeitsblatt und DSheet im Quelldaten-Arbeitsblatt fest.

 '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")

Stellen Sie sicher, dass Sie die Arbeitsblattnamen im Code durch die Namen ersetzen, die Sie in Ihren Daten haben.

3. Datenbereich festlegen

Der nächste Schritt besteht nun darin, den Datenbereich aus dem Quellarbeitsblatt festzulegen. Hier müssen Sie auf eines achten, Sie können keinen festen Quellbereich angeben.

Sie benötigen Code, der alle Daten im Quellblatt identifizieren kann. Und unten ist der 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)

Dieser Code beginnt in der ersten Zelle der Datentabelle und wählt bis zur letzten Zeile und dann bis zur letzten Spalte aus.

Und schließlich legen Sie diesen ausgewählten Bereich als Quelle fest. Das Beste daran ist, dass Sie beim Erstellen der Pivot-Tabelle nicht jedes Mal die Datenquelle ändern müssen.

4. Erstellen Sie einen Pivot-Cache

In Excel 2000 und höher müssen Sie vor dem Erstellen einer Pivot-Tabelle einen Pivot-Cache erstellen, um die Datenquelle festzulegen.

Wenn Sie eine Pivot-Tabelle erstellen, erstellt Excel normalerweise automatisch einen Pivot-Cache, ohne Sie zu fragen. Wenn Sie jedoch VBA verwenden müssen, müssen Sie Code dafür schreiben.

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

Dieser Code funktioniert auf zwei Arten: Erstens, indem er einen Pivot-Cache mithilfe einer Datenquelle einrichtet, und zweitens, indem er die Zellenadresse im neu eingefügten Arbeitsblatt festlegt, um die Pivot-Tabelle einzufügen.

Sie können die Position der Pivot-Tabelle ändern, indem Sie diesen Code ändern.

5. Fügen Sie eine leere Pivot-Tabelle ein

Nach dem Pivot-Cache besteht der nächste Schritt darin, eine leere Pivot-Tabelle einzufügen. Denken Sie daran, dass Sie beim Erstellen einer Pivot-Tabelle immer zuerst einen leeren Pivot erhalten und dann alle Werte, Spalten und Zeilen festlegen.

Dieser Code macht dasselbe:

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

Dieser Code erstellt eine leere Pivot-Tabelle und nennt sie „SalesPivotTable“. Sie können diesen Namen im Code selbst ändern.

6. Zeilen- und Spaltenfelder einfügen

Nachdem Sie eine leere Pivot-Tabelle erstellt haben, besteht der nächste Schritt darin, wie gewohnt Zeilen- und Spaltenfelder einzufügen.

Für jedes Zeilen- und Spaltenfeld müssen Sie einen Code schreiben. Hier wollen wir Jahre und Monate im Zeilenfeld und Bereiche im Spaltenfeld hinzufügen.

Hier ist der 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 diesem Code haben Sie Jahr und Monat als zwei Felder erwähnt. Wenn Sie sich nun den Code ansehen, werden Sie feststellen, dass dort auch eine Positionsnummer vorhanden ist. Diese Positionsnummer definiert die Reihenfolge der Felder.

Geben Sie jedes Mal, wenn Sie mehrere Felder (Zeile oder Spalte) hinzufügen müssen, deren Position an. Und Sie können die Felder ändern, indem Sie ihren Namen im Code ändern.

7. Fügen Sie ein Datenfeld ein

Die Hauptsache ist, das Wertefeld in Ihrer Pivot-Tabelle zu definieren.

Der Code zum Definieren von Werten unterscheidet sich vom Definieren von Zeilen und Spalten, da wir hier die Formatierung von Zahlen, Positionen und Funktionen definieren müssen.

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

Mit dem Code oben können Sie den Betrag als Wertfeld hinzufügen. Und dieser Code formatiert die Werte als Zahl mit einem Trennzeichen (,).

Wir verwenden xlsum, um die Werte zu addieren, Sie können aber auch xlcount und andere Funktionen verwenden.

8. Formatieren Sie die Pivot-Tabelle

Letztendlich müssen Sie Code verwenden, um Ihre Pivot-Tabelle zu formatieren. Normalerweise gibt es in einer Pivot-Tabelle eine Standardformatierung, Sie können diese Formatierung jedoch ändern.

Mit VBA können Sie den Formatierungsstil im Code festlegen.

Der Code lautet:

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

Der obige Code wendet Linienstreifen und den Stil „Pivot Style Medium 9“ an, Sie können jedoch auch einen anderen Stil über diesen Link verwenden.

Schließlich ist Ihr Code einsatzbereit.

[VOLLSTÄNDIGER CODE] Verwenden Sie VBA, um eine PivotTable in Excel zu erstellen – Makro kopieren und einfügen

 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

Pivot-Tabelle auf vorhandenem Arbeitsblatt

Der oben verwendete Code erstellt eine PivotTable in einem neuen Arbeitsblatt. Manchmal müssen Sie jedoch eine PivotTable in ein Arbeitsblatt einfügen, das sich bereits in der Arbeitsmappe befindet.

Geben Sie im obigen Code (Pivot-Tabelle im neuen Arbeitsblatt) in dem Teil, in dem Sie den Code zum Einfügen eines neuen Arbeitsblatts geschrieben haben, einen Namen ein. Bitte nehmen Sie einige Änderungen am Code vor.

Keine Sorge; Ich zeige es dir.

Zuerst müssen Sie das Arbeitsblatt (bereits in der Arbeitsmappe) angeben, in das Sie Ihre Pivot-Tabelle einfügen möchten.

Und dazu müssen Sie den folgenden Code verwenden:

Anstatt ein neues Arbeitsblatt einzufügen, müssen Sie den Namen des Arbeitsblatts in der PSheet-Variablen angeben.

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

Es gibt noch ein bisschen zu tun. Der erste von Ihnen verwendete Code löscht das gleichnamige Arbeitsblatt (sofern vorhanden), bevor der Pivot eingefügt wird.

Wenn Sie eine PivotTable in das vorhandene Arbeitsblatt einfügen, ist dort möglicherweise bereits eine PivotTable mit demselben Namen vorhanden.

Was ich damit sagen will ist, dass Sie zuerst diesen Drehpunkt entfernen müssen.

Dazu müssen Sie den Code hinzufügen, der den gleichnamigen Pivot aus dem Arbeitsblatt entfernen soll (falls vorhanden), bevor Sie einen neuen einfügen.

Hier ist der Code, den Sie hinzufügen müssen:

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

Lassen Sie mich Ihnen sagen, was dieser Code bewirkt.

Zunächst wird lediglich PSheet als das Arbeitsblatt festgelegt, in das Sie die Pivot-Tabelle bereits in Ihre Arbeitsmappe einfügen möchten, und Datenarbeitsblätter werden als DSheet festgelegt.

Anschließend wird das Arbeitsblatt aktiviert und die „Sales PivotTable“ daraus entfernt.

Wichtig: Wenn die Namen der Arbeitsblätter in Ihrer Arbeitsmappe unterschiedlich sind, können Sie sie im Code ändern. Ich habe den Code hervorgehoben, an dem Sie dies tun müssen.

Am Ende,

Mit diesem Code können wir Ihre Pivot-Tabellen automatisieren. Und das Beste daran ist, dass es sich um ein einzigartiges Setup handelt. Danach brauchen wir nur noch einen Klick, um eine Pivot-Tabelle zu erstellen, und Sie können viel Zeit sparen. Jetzt sag mir eins.

Haben Sie jemals VBA-Code zum Erstellen einer Pivot-Tabelle verwendet?

Bitte teilen Sie mir Ihre Meinung im Kommentarfeld mit; Ich möchte sie gerne mit Ihnen teilen und diesen Tipp mit Ihren Freunden teilen.

Ähnliche Artikel:

• Gesamtsumme in einer Pivot-Tabelle in Excel hinzufügen oder entfernen
• Fügen Sie die laufende Summe zu einer Pivot-Tabelle in Excel hinzu
• Aktualisieren Sie automatisch eine Pivot-Tabelle in Excel
• Fügen Sie ein berechnetes Feld und Element hinzu
• Löschen Sie eine PivotTable in Excel