Fazit: Erfahren Sie, wie Sie mithilfe eines einfachen Makros Pivot-Tabellen automatisch aktualisieren, wenn Änderungen an den Quelldaten vorgenommen werden. Ich teile auch eine Nicht-Makro-Lösung, um die Pivot-Tabellen beim Öffnen der Datei zu aktualisieren. Enthält ein Video-Tutorial und das Herunterladen von Excel-Dateien.
Schwierigkeitsgrad: Mittelstufe
Herunterladen der Excel-Datei
Wenn Sie am besten selbst lernen, können Sie dies tun Laden Sie die Datei herunter, die ich im Video verwende, um sie zu verfolgen. Hier ist die Excel-Datei, die den VBA-Code enthält.
Pivot-Tabelle automatisch aktualisieren.xlsm (41,5 KB)
Pivot-Tabellen automatisch aktualisieren
Können Ihre Pivot-Tabellen sofort und automatisch aktualisiert werden, wenn sich ihre Quelldaten ändern?
Absolut. Dies erfordert die Verwendung eines wirklich einfachen Makros, das ich Ihnen unten zeigen werde.
Wenn Sie mit Makros nicht allzu vertraut sind und VBA empfehle ich, meine kostenlose dreiteilige Videoserie zu lesen, um mit den Makros & VBA zu beginnen.
Wenn Sie noch keine Erfahrung mit Pivot-Tabellen haben, Ich habe eine Reihe, die Ihnen erklärt, was sie sind und wie man sie verwendet. Sehen Sie sich das erste Video dieser Reihe auf Pivot-Tabellen an & Dashboards
Um unsere Pivot-Tabellen automatisch zu aktualisieren, schreiben wir ein Makro mit einer einfachen Anweisung. Diese Anweisung lautet im Wesentlichen: Wenn ich Änderungen an meinem Arbeitsblatt vornehme, aktualisieren Sie alle Pivot-Tabellen und Datenverbindungen. Führen Sie die folgenden Schritte aus, um das Makro zu erstellen.
Öffnen Sie den Visual Basic-Editor.
Klicken Sie dazu auf der Registerkarte Entwickler des Menübands auf die Schaltfläche Visual Basic. P. >
Die Tastenkombination zum Öffnen des Visual Basic-Editors lautet Alt + F11.
Wenn Sie dies nicht sehen Auf der Registerkarte Entwickler können Sie sie anhand der Anweisungen hier sichtbar machen. Sie müssen dies nur einmal tun, und dann wird die Registerkarte Entwickler jedes Mal angezeigt, wenn Sie Excel in Zukunft öffnen.
Öffnen Sie das Blattmodul, das Ihre Quelldaten enthält.
Suchen Sie im Projekt-Explorer-Fenster des Visual Basic-Editors die Arbeitsmappe, die Sie ändern möchten. Unter dieser Arbeitsmappe sind die Blätter in der Arbeitsmappe aufgeführt. Wählen Sie das Blatt aus, das die Quelldaten enthält. Doppelklicken Sie dann darauf.
Wenn das Projekt-Explorer-Fenster nicht angezeigt wird, können Sie es in der Ansicht aktivieren Menü (Tastenkombination: Strg + R).
Fügen Sie ein neues Ereignis für Arbeitsblattänderungen hinzu.
Durch Doppelklicken auf das Blatt wird das Codemodul für dieses Objekt geöffnet. Innerhalb des Codemoduls möchten wir ein Ereignismakro erstellen. Wählen Sie dazu links im Dropdown-Feld Objekt die Option Arbeitsblatt.
Damit wird ein Worksheet_SelectionChange-Ereignis hinzugefügt das Modul, das wir eigentlich nicht wollen, also werden wir es gleich löschen. Bevor wir dies tun, gehen wir zum Dropdown-Menü Prozedur auf der rechten Seite und wählen Sie Ändern.
Dies fügt ein neues hinzu Ereignis oben mit dem Namen Worksheet_Change. Jetzt werden wir den unnötigen Code darunter markieren und löschen.
Das Ereignismakro Worksheet_Change wird jedes Mal ausgeführt, wenn eine Änderung vorgenommen wird zu Zellen in diesem Arbeitsblatt. Wir können dem Worksheet_Change-Ereignis VBA-Code hinzufügen, um Aktionen auszuführen, wenn der Benutzer Zellen bearbeitet.
Hinweis: Das standardmäßig hinzugefügte SelectionChange-Ereignis wird jedes Mal ausgeführt, wenn der Benutzer eine Zelle im Blatt auswählt. Da der Code nur ausgeführt werden soll, wenn der Benutzer Zellen bearbeitet / ändert, verwenden wir das Change-Ereignis. Lesen Sie meinen Artikel zu VBA-Codemodulen & Ausführen von Makros basierend auf Benutzerereignissen, um mehr über die Blattmodule und -ereignisse zu erfahren.
Fügen Sie den zu aktualisierenden VBA-Code hinzu Alle Pivot-Tabellen.
Geben Sie als Nächstes direkt unter der Zeile Worksheet_Change die folgende Anweisung ein:
ThisWorkbook.RefreshAll
Die RefreshAll-Methode aktualisiert alle Pivot-Tabellen, Abfragen und Datenverbindungen in der Arbeitsmappe. Diese Aktion ist dieselbe, als ob Sie manuell auf die Schaltfläche Aktualisieren auf der Registerkarte Daten klicken.
Wenn Sie diese Codezeile zum Ereignis Worksheet_Change hinzufügen, wird die Arbeitsmappe aktualisiert, wenn eine Änderung am Arbeitsblatt vorgenommen wird, das der Code ist in.
Pivot-Tabelle & Quelldaten auf demselben Blatt
Aleksandrs stellte in den YouTube-Videokommentaren eine großartige Frage. Wenn sich Ihre Pivot-Tabelle und Ihre Quelldaten auf demselben Blatt befinden, müssen Sie Code hinzufügen, um Ereignisse zu deaktivieren.
Die Aktualisierung versetzt das Ereignis in eine rekursive Schleife und kann zum Absturz von Excel führen. Hier ist der Code, um dies zu verhindern.
Application.EnableEvents = False ThisWorkbook.RefreshAllApplication.EnableEvents = True
Überprüfen, ob Ihr Makro ausgeführt wird
Eine Möglichkeit, um zu überprüfen, ob das Makro funktioniert ist es zu testen. Nehmen Sie eine Änderung an den Quelldaten vor und prüfen Sie, ob diese in Ihrer Pivot-Tabelle wiedergegeben sind.Wenn Ihre Änderung nicht leicht zu erkennen ist, weil Sie zu viele Daten haben, oder aus einem anderen Grund, können Sie auf andere Weise feststellen, ob Ihr Makro ausgelöst wird.
Im VB-Editor können Sie auf klicken graue Spalte links neben Ihrem Worksheet_Change-Makro. Dadurch wird ein roter Kreis angezeigt. Außerdem wird diese Codezeile rot hervorgehoben.
Dies wird als Stopp oder Haltepunkt bezeichnet.
Die Tastenkombination zum Ein- und Ausschalten eines Haltepunkts lautet: F9
Wenn jetzt eine Aktion ausgeführt wird, die das Makro auslöst, springt Excel zum VB-Editor und hält das Makro an, damit Sie den Code überprüfen können. In unserem Fall handelt es sich bei dieser Aktion um eine Änderung, die im Arbeitsblatt vorgenommen wird.
Sie können dann F8 drücken, um die einzelnen Schritte zu durchlaufen oder drücken Sie F5, um zum Ende (oder zum nächsten Haltepunkt) zu gelangen.
Wenn Sie eine Änderung am Arbeitsblatt vornehmen und Excel Sie nicht in den VB-Editor zieht, wissen Sie, dass ein Problem mit vorliegt Das Makro wird nicht ausgeführt. In diesem Fall haben Sie die Datei wahrscheinlich nicht als makrofähige Arbeitsmappe (XLSM) und / oder als aktivierte Makros gespeichert. Möglicherweise müssen Sie & speichern, die Datei schließen, erneut öffnen und Makros aktivieren.
Um den Haltepunkt zu entfernen, den Sie auf dem Makro platziert haben, Klicken Sie einfach auf den roten Kreis, um ihn auszublenden (Tastenkombination: F9).
Die Tastenkombination zum Löschen aller Haltepunkte lautet: Strg + Umschalt + F9
Pivot-Tabellen ohne a aktualisieren Makro
Ein Nachteil bei der Verwendung dieses Makros zum Aktualisieren Ihrer Pivot-Tabellen besteht darin, dass bei jeder Ausführung des Makros der Rückgängig-Verlauf verloren geht. Mit anderen Worten, wenn Sie auf die Schaltfläche „Rückgängig“ klicken (oder Strg + Z drücken), merkt sich Excel nicht an das, was Sie zuletzt getan haben, und kann es daher nicht rückgängig machen. Folglich wird nichts passieren und Ihre letzte Änderung wird nicht rückgängig gemacht.
Es gibt eine Alternative, mit der Sie Ihren Rückgängig-Verlauf beibehalten können. Diese Alternative aktualisiert Ihre Pivot-Tabelle jedoch nur beim Öffnen der Arbeitsmappe, nicht jedes Mal, wenn eine Änderung vorgenommen wird. So können Sie diese Option verwenden.
Ausgehend von einer beliebigen Zelle in Ihrer Pivot-Tabelle:
- Wechseln Sie in der Multifunktionsleiste zur Registerkarte Analysieren.
- Wählen Sie die Schaltfläche Optionen.
- Wechseln Sie im neuen Fenster zur Registerkarte Daten.
- Aktivieren Sie das Kontrollkästchen „Daten beim Öffnen der Datei aktualisieren“.
Nach dem Klicken auf OK wird möglicherweise die folgende Warnmeldung angezeigt, wenn mehrere Pivot-Tabellen aus demselben Quelldatenbereich erstellt wurden. Klicken Sie einfach auf OK, um den Vorgang zu durchlaufen.
Nochmals nur zum Vergleich: Wenn Sie diese Option verwenden, behalten Sie den Rückgängig-Verlauf bei, aktualisieren die Pivot-Tabelle jedoch nur, wenn die Arbeitsmappe geschlossen und erneut geöffnet wird. Wenn Sie die Makrooption verwenden, verlieren Sie den Rückgängig-Verlauf, die Pivot-Tabelle wird jedoch automatisch aktualisiert Dies gilt immer dann, wenn Änderungen in der Arbeitsmappe vorgenommen werden.
Variationen zum Aktualisieren von Pivot-Tabellen
Das von uns betrachtete Makro aktualisiert nicht nur Ihre Pivot-Tabellen, sondern auch alle Abfragen . Wenn Sie nur Pivot-Tabellen aktualisieren möchten, können Sie stattdessen den Befehl „ThisWorkbook.RefreshAll“ durch diesen Code ersetzen:
Sub Refresh_All_Pivot_Table_Caches()"Refresh all pivot caches in the workbook."Pivot tables are automatically refreshed when cache is refreshed.Dim pc As PivotCache "Refresh all pivot tables For Each pc In ThisWorkbook.PivotCaches pc.Refresh Next pc End Sub
Jede Pivot-Tabelle ist mit einer verbunden zugrunde liegender Pivot-Cache, der mit den Quelldaten verbunden ist. Der Code durchläuft alle Pivot-Caches in der Arbeitsmappe und aktualisiert jeden. Pivot-Tabellen aus demselben Quellbereich können Pivot-Caches gemeinsam nutzen. Diese Methode ist also schneller als das Durchlaufen aller Pivot-Tabellen
Nehmen wir an, Sie möchten nur eine bestimmte Pivot-Tabelle aktualisieren. In diesem Fall können Sie den Code „ThisWorkbook.RefreshAll“ mit dem folgenden Code austauschen.
Und schließlich, wenn Wenn Sie Power Query verwenden und die Hintergrundaktualisierung deaktivieren möchten, damit Abfragen aktualisiert werden, BEVOR Pivot-Tabellen, habe ich einen Artikel geschrieben, in dem erläutert wird, wie dies durch Deaktivieren der Hintergrundaktualisierung für die Abfragen erfolgt.
Verwenden Sie die Option Ereignis stattdessen deaktivieren
Eine andere Option ist die Verwendung des Ereignisses Worksheet_Deactivate anstelle von Worksheet_Change. Das Ereignis Worksheet_Deactivate wird jedes Mal ausgeführt, wenn der Benutzer das Blatt verlässt und ein anderes Blatt auswählt. Auf diese Weise kann der Benutzer alle Änderungen an vornehmen Die Quelldaten und dann die Pivot-Tabelle werden automatisch aktualisiert, wenn sie zu einem anderen Blatt wechseln, einschließlich der Blätter, die die Pivot-Tabelle enthalten.
Private Sub Worksheet_Deactivate() ThisWorkbook.RefreshAllEnd Sub
Dieser Code wird weiterhin in das Blattmodul eingefügt, das die Quelldaten enthält. Dies ist eine gute Option, wenn Ihre Pivot-Tabellen oder Datenverbindungen eine benötigen Das Aktualisieren dauert einige Sekunden oder länger, und Sie möchten nicht jedes Mal warten, wenn Änderungen an den Quelldaten vorgenommen werden.
Das einzige Mal, wenn Sie dies möglicherweise nicht verwenden möchten, ist Ihre Pivot-Tabelle und Quelle Daten sind auf dem gleichen Blatt.Dies ist normalerweise ein seltener Fall und etwas, das ich im Allgemeinen nicht empfehle.
Dank des Vorschlags von Ted in diesem Fall.
Zeit sparen & Verlegenheit
Ich hoffe, dieser Artikel hilft Ihnen, Zeit zu sparen und den Benutzern Ihrer Dateien die Arbeit zu erleichtern. Er kann auch dazu beitragen, Verlegenheit zu vermeiden, wenn Sie vergessen, Pivot-Tabellen vor dem Versenden von Berichten zu aktualisieren Ich habe diesen Fehler öfter gemacht, als ich zugeben möchte … 🙂
