Derzeit können wir Pivot-Tabellen mithilfe von Excel-Tabellen oder dynamischen benannten Bereichen dynamisch ändern oder aktualisieren. Aber diese Techniken sind nicht narrensicher. Da Sie die Pivot-Tabelle noch manuell aktualisieren müssen. Wenn Sie große Datenmengen haben, die Tausende von Zeilen und Spalten enthalten, werden Ihnen Excel-Tabellen nicht viel helfen. Stattdessen wird Ihre Datei schwer. Bleibt also nur noch VBA.
In diesem Artikel erfahren Sie, wie wir unsere Pivot-Tabelle automatisch ändern können, um die Datenquelle zu ändern. Mit anderen Worten, wir automatisieren den manuellen Prozess des Änderns der Datenquelle, um neue Zeilen und Spalten dynamisch zu den Quelltabellen hinzuzufügen und die Änderung in der Pivot-Tabelle sofort widerzuspiegeln.
Code in Quelldatenblatt schreiben
Da dies vollständig automatisch erfolgen soll, verwenden wir anstelle eines Kernmoduls Blattmodule, um Code zu schreiben. Dadurch können wir Arbeitsblattereignisse verwenden.
Wenn sich die Quelldaten und Pivot-Tabellen in verschiedenen Blättern befinden, schreiben wir den VBA-Code, um die Pivot-Tabellen-Datenquelle im Blattobjekt zu ändern, das die Quelldaten enthält (nicht das Pivot-Tabelle enthält).
Drücken Sie STRG+F11, um den VB-Editor zu öffnen. Gehen Sie nun zum Projekt-Explorer und suchen Sie das Blatt, das die Quelldaten enthält. Doppelklicken Sie darauf.
Es öffnet sich ein neuer Codierbereich. Möglicherweise sehen Sie keine Änderung, aber jetzt haben Sie Zugriff auf die Arbeitsblattereignisse.
Klicken Sie auf das linke Dropdown-Menü und wählen Sie das Arbeitsblatt aus. Wählen Sie im linken Dropdown-Menü Deaktivieren aus. Sie sehen ein leeres Sub, das auf den Codebereichsnamen worksheet_deativate geschrieben wird. Unser Code zum dynamischen Ändern von Quelldaten und zum Aktualisieren der Pivot-Tabelle wird in diesen Codeblock aufgenommen. Dieser Code wird immer dann ausgeführt, wenn Sie vom Datenblatt zu einem anderen Blatt wechseln. Alle Arbeitsblatt-Ereignisse können Sie hier nachlesen.
Jetzt sind wir bereit, den Code zu implementieren.
Quellcode zum dynamischen Aktualisieren der Pivot-Tabelle mit neuem Bereich
Um zu erklären, wie es funktioniert, habe ich eine Arbeitsmappe. Diese Arbeitsmappe enthält zwei Blätter. Sheet1 enthält die Quelldaten, die sich ändern können. Sheet2 enthält die Pivot-Tabelle, die von den Quelldaten von Sheet2 abhängt.
Jetzt habe ich diesen Code in den Codierbereich von Sheet1 geschrieben. Ich verwende das Worksheet_Deactivate-Ereignis, damit dieser Code ausgeführt wird, um die Pivot-Tabelle zu aktualisieren, wenn wir vom Quelldatenblatt wechseln.
Private Sub Worksheet_Deactivate() Dim pt As PivotTable Dim pc As PivotCache Dim source_data As Range lstrow = Cells(Rows.Count, 1).End(xlUp).Row lstcol = Cells(1, Columns.Count).End(xlToLeft). Spaltensatz source_data = Range(Cells(1, 1), Cells(lstrow, lstcol)) Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, SourceData:=source_data) Set pt = Sheet2.PivotTables("PivotTable1") pt.ChangePivotCache PC End Sub
Wenn Sie eine ähnliche Arbeitsmappe haben, können Sie diese Daten direkt kopieren. Ich habe unten erklärt, wie dieser Code funktioniert.
Sie können die Wirkung dieses Codes in der folgenden GIF-Datei sehen.
Wie ändert dieser Code automatisch Quelldaten und aktualisiert Pivot-Tabellen?
Als erstes haben wir ein worksheet_deactivate Event verwendet. Dieses Ereignis wird nur ausgelöst, wenn das Blatt mit dem Code umgeschaltet oder deaktiviert wird. So wird der Code automatisch ausgeführt.
Um nun die Quelldaten der Pivot-Tabelle zu ändern, ändern wir die Daten im Pivot-Cache.
Eine Pivot-Tabelle wird mit dem Pivot-Cache erstellt. Der Pivot-Cache enthält die alten Quelldaten, bis die Pivot-Tabelle nicht manuell aktualisiert oder der Quelldatenbereich manuell geändert wird.
Wir haben Referenzen für Pivot-Tabellen namens pt, einen Pivot-Cache namens pc und einen Bereich namens source_data erstellt. Die Quelldaten enthalten die gesamten Daten.
Um dynamisch die gesamte Tabelle als Datenbereich zu erhalten, bestimmen wir die letzte Zeile und letzte Spalte.
lstrow = Cells(Rows.Count, 1).End(xlUp).Row
lstcol = Cells(1, Columns.Count).End(xlToLeft).Column
Mit diesen beiden Zahlen definieren wir die source_data. Wir sind uns sicher, dass der Quelldatenbereich immer bei A1 beginnt.
Set source_data = Range(Cells(1, 1), Cells(lstrow, lstcol))
Jetzt haben wir die Quelldaten, die dynamisch sind. Wir müssen es nur in der Pivot-Tabelle verwenden.
Wir speichern diese Daten im Pivot-Cache, da wir wissen, dass der Pivot-Cache alle Daten speichert.
Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, SourceData:=source_data)
Als nächstes definieren wir die Pivot-Tabelle, die wir aktualisieren möchten. Da wir die PivotTable1 (Name der Pivot-Tabelle. Sie können den Namen der Pivot-Tabelle bei der Auswahl der Pivot-Tabelle auf der Registerkarte "Analysieren" überprüfen.) auf Sheet1 aktualisieren möchten, setzen wir pt wie unten gezeigt.
Setze pt = Sheet2.PivotTables("PivotTable1")
Jetzt verwenden wir einfach diesen Pivot-Cache, um die Pivot-Tabelle zu aktualisieren. Wir verwenden die changePivotCache-Methode des pt-Objekts.
pt.ChangePivotCache-PC
Und wir haben unsere Pivot-Tabelle automatisiert. Dadurch wird Ihre Pivot-Tabelle automatisch aktualisiert. Wenn Sie über mehrere Tabellen mit derselben Datenquelle verfügen, verwenden Sie einfach denselben Cache in jedem Pivot-Tabellenobjekt.
Also, Leute, so können Sie den Datenquellenbereich in Excel dynamisch ändern. Ich hoffe, ich war erklärend genug. Wenn Sie Fragen zu diesem Artikel haben, lassen Sie es mich im Kommentarbereich unten wissen.
So aktualisieren Sie Pivot-Tabellen mit VBA automatisch: Um Ihre Pivot-Tabellen automatisch zu aktualisieren, können Sie VBA-Ereignisse verwenden. Verwenden Sie diese einfache Codezeile, um Ihre Pivot-Tabelle automatisch zu aktualisieren. Sie können eine von drei Methoden zum automatischen Aktualisieren von Pivot-Tabellen verwenden.
Makro ausführen, wenn eine Änderung am Blatt im angegebenen Bereich vorgenommen wurde: In Ihren VBA-Praktiken müssen Sie Makros ausführen, wenn sich ein bestimmter Bereich oder eine Zelle ändert. In diesem Fall verwenden wir zum Ausführen von Makros bei einer Änderung eines Zielbereichs das Änderungsereignis.
Makro ausführen, wenn Änderungen am Blatt vorgenommen werden | Um Ihr Makro bei jeder Blattaktualisierung auszuführen, verwenden wir die Arbeitsblattereignisse von VBA.
Einfachster VBA-Code zum Hervorheben der aktuellen Zeile und Spalte mit | Verwenden Sie dieses kleine VBA-Snippet, um die aktuelle Zeile und Spalte des Blatts hervorzuheben.
Die Arbeitsblattereignisse in Excel VBA | Das Arbeitsblattereignis ist wirklich nützlich, wenn Sie möchten, dass Ihre Makros ausgeführt werden, wenn ein bestimmtes Ereignis auf dem Blatt auftritt.
Populäre Artikel:
50 Excel-Kurzbefehle zur Steigerung Ihrer Produktivität | Werden Sie schneller bei Ihrer Aufgabe. Mit diesen 50 Tastenkombinationen arbeiten Sie noch schneller mit Excel.Die SVERWEIS-Funktion in Excel | Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, die verwendet wird, um Werte aus verschiedenen Bereichen und Blättern zu suchen.
ZÄHLENWENN in Excel 2016 | Zählen Sie Werte mit Bedingungen mit dieser erstaunlichen Funktion. Sie müssen Ihre Daten nicht filtern, um einen bestimmten Wert zu zählen. Die Countif-Funktion ist unerlässlich, um Ihr Dashboard vorzubereiten.
So verwenden Sie die SUMIF-Funktion in Excel | Dies ist eine weitere wesentliche Funktion des Dashboards. Dies hilft Ihnen, Werte unter bestimmten Bedingungen zusammenzufassen.