So aktualisieren Sie dynamisch alle Pivot-Tabellen-Datenquellen in Excel
In einem früheren Artikel haben wir gelernt, wie Sie einzelne Pivot-Tabellen dynamisch ändern und aktualisieren können, indem Sie Datenquellen verkleinern oder erweitern.
In diesem Artikel werden wir Erfahren Sie, wie wir alle Pivot-Tabellen in einer Arbeitsmappe dazu bringen können, die Datenquelle automatisch zu ändern. Mit anderen Worten, anstatt jeweils eine Pivot-Tabelle zu ändern, werden wir versuchen, die Datenquelle aller Pivot-Tabellen in der Arbeitsmappe so zu ändern, dass neue Zeilen und Spalten, die den Quelltabellen hinzugefügt wurden, dynamisch eingeschlossen werden und die Änderung der Pivot-Tabellen sofort wiedergegeben wird.
Code in Quelldatenblatt schreiben
Da dies vollständig automatisch erfolgen soll, verwenden wir Blattmodule, um Code anstelle eines Kernmoduls zu schreiben. Auf diese Weise können wir Arbeitsblattereignisse verwenden.
Wenn sich die Quelldaten und Pivot-Tabellen in verschiedenen Blättern befinden, schreiben wir den VBA-Code, um die Datenquelle der Pivot-Tabelle in dem Blattobjekt zu ändern, das die Quelldaten enthält ( nicht das enthält Pivot-Tabellen).
Drücken Sie STRG + F11, um den VB-Editor zu öffnen. Gehen Sie nun zum Projektexplorer und suchen Sie das Blatt, das die Quelldaten enthält. Doppelklicken Sie darauf.
Ein neuer Codierungsbereich wird geöffnet. Möglicherweise werden keine Änderungen angezeigt, 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ü die Option Deaktivieren. Auf dem Codebereichsnamen worksheet_deativate wird ein leeres Sub angezeigt. 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. Sie können hier über alle Arbeitsblattereignisse lesen.
Jetzt können wir den Code implementieren.
Quellcode zum dynamischen Aktualisieren aller Pivot-Tabellen in der Arbeitsmappe mit neuem Bereich
Um zu erklären, wie es funktioniert, habe ich eine Arbeitsmappe. Diese Arbeitsmappe enthält drei Blätter. Sheet1 enthält die Quelldaten, die sich ändern können. Sheet2 und Sheet3 enthalten Pivot-Tabellen, die von den Quelldaten von Sheet2 abhängen.
Jetzt habe ich diesen Code in den Codierungsbereich von Sheet1 geschrieben. Ich verwende das Worksheet_Deactivate-Ereignis, sodass dieser Code ausgeführt wird, um das zu aktualisieren Pivot-Tabelle, wenn wir vom Quelldatenblatt wechseln.
Wenn Sie eine ähnliche Arbeitsmappe haben, können Sie diese Daten direkt kopieren. Ich habe erklärt, dass dieser Code unten funktioniert, damit Sie ihn gemäß Ihren Anforderungen ändern können.
Sie können die Wirkung dieses Codes in GIF unten sehen.
Wie ändert sich dieser Code automatisch? Quelldaten und Pivot-Tabellen aktualisieren?
Zuerst haben wir ein worksheet_deactivate-Ereignis verwendet. Dieses Ereignis wird nur ausgelöst, wenn das Blatt mit dem Code umgeschaltet oder deaktiviert wird. Auf diese Weise wird der Code automatisch ausgeführt.
Um die gesamte Tabelle dynamisch als Datenbereich abzurufen, bestimmen wir die letzte Zeile und die letzte Spalte.
lstrow = Cells (Rows.Count, 1) .End (xlUp) .Row
lstcol = Cells (1, Columns.Count) .End (xlToL eft) .Column
Mit diesen beiden Zahlen definieren wir die Quelldaten. Wir sind sicher, dass der Quelldatenbereich immer bei A1 beginnt. Sie können Ihre eigene Anfangszellenreferenz definieren.
Setzen Sie source_data = Range (Zellen (1, 1), Zellen (lstrow, lstcol)).
Jetzt haben wir die Quelldaten dynamisch. Wir müssen es nur in der Pivot-Tabelle verwenden.
Da wir nicht wissen, wie viele Pivot-Tabellen eine Arbeitsmappe gleichzeitig enthalten wird, werden wir jedes Blatt und jede Pivot-Tabelle jedes Blattes durchlaufen dass keine Pivot-Tabelle mehr übrig ist. Hierfür verwenden wir verschachtelte for-Schleifen.
Für jedes ws in diesem Arbeitsbuch. Arbeitsblätter
Für jedes pt in ws.PivotTables
pt.ChangePivotCache _
ThisWorkbook.PivotCaches.Create (_
SourceType: = xlDatabase, _
SourceData: = source_data)
Nächstes pt
Nächstes ws
Die erste Schleife durchläuft jedes Blatt. Die zweite Schleife durchläuft jede Pivot-Tabelle in einem Blatt.
Die Pivot-Tabellen werden zugewiesen Wir verwenden die ChangePivotCache-Methode des pt-Objekts. Wir erstellen dynamisch einen Pivot-Cache mit der ThisWorkbook.PivotCaches.Create
-Methode. Diese Methode verwendet zwei Variablen SourceType und SourceData. Als Quelltyp deklarieren wir xlDatabase und Als SourceData übergeben wir den zuvor berechneten Bereich source_data.
Und dass i sitzen. Wir haben unsere Pivot-Tabellen automatisiert. Dadurch werden automatisch alle Pivot-Tabellen in der Arbeitsmappe aktualisiert.
Auf diese Weise können Sie die Datenquellenbereiche aller Pivot-Tabellen in einer Arbeitsmappe 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.
Dynamisches Aktualisieren des Datenquellenbereichs für Pivot-Tabellen in Excel: Um den Quelldatenbereich von Pivot-Tabellen dynamisch zu ändern, verwenden wir Pivot-Caches. Diese wenigen Zeilen können jede Pivot-Tabelle dynamisch aktualisieren, indem Sie den Quelldatenbereich ändern.
So aktualisieren Sie Pivot-Tabellen mithilfe von 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 der drei Methoden zum automatischen Aktualisieren von Pivot-Tabellen verwenden.
Makro ausführen, wenn Änderungen am Blatt im angegebenen Bereich vorgenommen wurden: In Ihren VBA-Praktiken müssen Sie Makros ausführen, wenn ein bestimmter Bereich oder Zellveränderungen. In diesem Fall verwenden wir das Änderungsereignis, um Makros auszuführen, wenn eine Änderung an einem Zielbereich vorgenommen wird.
Makro ausführen, wenn eine Änderung auf Blatt | vorgenommen wird Um Ihr Makro bei jeder Aktualisierung des Arbeitsblatts auszuführen, verwenden wir die Arbeitsblattereignisse von VBA.
Einfachster VBA-Code zum Hervorheben der aktuellen Zeile und Spalte mithilfe von | Verwenden Sie dieses kleine VBA-Snippet, um die aktuelle Zeile und Spalte des Blattes hervorzuheben.
Die Arbeitsblattereignisse in Excel VBA | Das Arbeitsblattereignis ist sehr nützlich, wenn Ihre Makros ausgeführt werden sollen, wenn ein bestimmtes Ereignis auf dem Blatt auftritt.
Beliebte Artikel:
50 Excel-Verknüpfungen zur Steigerung Ihrer Produktivität | Werden Sie schneller bei Ihrer Aufgabe. Mit diesen 50 Verknüpfungen können Sie noch schneller in Excel arbeiten. Die VLOOKUP-Funktion in Excel | Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, mit der Werte aus verschiedenen Bereichen und Tabellen gesucht werden.
COUNTIF in Excel 2016 | Zählen Sie Werte mit Bedingungen, die diese erstaunliche Funktion verwenden. Sie müssen Ihre Daten nicht filtern, um einen bestimmten Wert zu zählen. Die Countif-Funktion ist für die Vorbereitung Ihres Dashboards unerlässlich.
Verwenden der SUMIF-Funktion in Excel | Dies ist eine weitere wichtige Dashboard-Funktion. Auf diese Weise können Sie Werte zusammenfassen unter bestimmten Bedingungen.