In diesem Artikel erfahren Sie, wie Sie Pivot-Tabellendaten in Excel automatisch aktualisieren.
Szenario:
Wie wir alle wissen, werden Änderungen an den Quelldaten einer Pivot-Tabelle nicht sofort in der Pivot-Tabelle widergespiegelt. Wir müssen die Pivot-Tabellen aktualisieren, um die Änderungen beim Öffnen einer Excel-Arbeitsmappe anzuzeigen. Und wenn Sie eine aktualisierte Datei senden, ohne die Pivot-Tabellen zu aktualisieren, können Sie sich peinlich fühlen. Hier erfahren Sie, wie Sie die Aktualisierungsoption bei der Verwendung von Pivot-Tabellen finden
Aktualisieren Sie die Daten beim Öffnen einer Datei in Excel
Erstellen Sie zuerst eine Pivot-Tabelle und klicken Sie dann mit der rechten Maustaste auf eine beliebige Pivot-Tabellenzelle.
Gehen Sie zu Pivot-Tabellenoptionen> Registerkarte Daten> Aktivieren Sie das Kontrollkästchen, das sagt: Daten beim Öffnen einer Datei aktualisieren
Dadurch wird die automatische Aktualisierung der Daten aktiviert, wenn die Datei geöffnet wird.
Beispiel :
All dies kann verwirrend sein zu verstehen. Lassen Sie uns anhand eines Beispiels verstehen, wie die Funktion verwendet wird. Hier haben wir einige Daten und müssen zuerst eine Pivot-Tabelle erstellen und dann die Optionen finden, um Pivot-Tabellen automatisch zu aktualisieren.
Erstellen Sie eine Pivot-Tabelle und klicken Sie dann mit der rechten Maustaste auf eine beliebige Pivot-Tabellenzelle, wie unten gezeigt.
Wählen Sie die Pivot-Tabellen-Optionen und dies öffnet ein Pivot-Tabellen-Optionen-Dialogfeld.
Wählen Sie die Registerkarte Daten und aktivieren Sie das Kontrollkästchen, das beim Öffnen der Datei Daten aktualisieren sagt. Sie können dies durchführen, ohne die Datei mit VBA zu öffnen und zu schließen.
Verwenden von VBA
Hier erfahren wir, wie Sie eine Pivot-Tabelle mit VBA automatisch aktualisieren. Dieser Weg ist einfacher, als Sie es sich vorgestellt haben.
Dies ist die einfache Syntax zum automatischen Aktualisieren von Pivot-Tabellen in der Arbeitsmappe.
| 'Code im Quelldatenblattobjekt
Privates Unterarbeitsblatt_Deactivate() Sheetname_of_pivot_table.PivotTables("pivot_table_name").PivotCache.Refresh End Sub |
Was sind Pivot-Caches?
Jede Pivot-Tabelle speichert die Daten im Pivot-Cache. Aus diesem Grund kann Pivot frühere Daten anzeigen. Wenn wir Pivot-Tabellen aktualisieren, aktualisiert es den Cache mit neuen Quelldaten, um die Änderungen an der Pivot-Tabelle widerzuspiegeln.
Wir brauchen also nur ein Makro, um den Cache der Pivot-Tabellen zu aktualisieren. Wir tun dies mit einem Arbeitsblattereignis, damit wir das Makro nicht manuell ausführen müssen.
Wo codiere ich, um Pivot-Tabellen automatisch zu aktualisieren?
Wenn sich Ihre Quelldaten und Pivot-Tabellen in verschiedenen Blättern befinden, sollte der VBA-Code in das Quelldatenblatt aufgenommen werden.
Hier verwenden wir das Worksheet_SelectionChange-Ereignis. Dadurch wird der Code ausgeführt, wenn wir vom Quelldatenblatt zu einem anderen Blatt wechseln. Ich werde später erklären, warum ich dieses Ereignis verwendet habe.
Hier habe ich Quelldaten in Blatt2 und Pivot-Tabellen in Blatt1.
Öffnen Sie VBE mit STRG+F11-Taste. Im Projektexplorer sehen Sie drei Objekte, Sheet1, Sheet2 und die Arbeitsmappe.
Da Sheet2 die Quelldaten enthält, doppelklicken Sie auf das Sheet2-Objekt.
Jetzt sehen Sie oben im Codebereich zwei Dropdown-Listen. Wählen Sie aus der ersten Dropdown-Liste das Arbeitsblatt aus. Und aus dem zweiten Dropdown wählen Sie Deaktivieren. Dadurch wird ein leerer Untername Worksheet_Deactivate eingefügt. Unser Code wird in dieses Sub geschrieben. Alle Zeilen, die in dieses Sub geschrieben werden, werden ausgeführt, sobald der Benutzer von diesem Blatt zu einem anderen Blatt wechselt.
Auf Sheet1 habe ich zwei Pivot-Tabellen. Ich möchte nur eine Pivot-Tabelle aktualisieren. Dazu muss ich den Namen der Pivot-Tabelle kennen. Um den Namen einer Pivot-Tabelle zu erfahren, wählen Sie eine beliebige Zelle in dieser Pivot-Tabelle aus und gehen Sie zur Registerkarte Analyse der Pivot-Tabelle. Auf der linken Seite sehen Sie den Namen der Pivot-Tabelle. Sie können hier auch den Namen der Pivot-Tabelle ändern.
Nachdem wir nun den Namen der Pivot-Tabelle kennen, können wir eine einfache Zeile schreiben, um die Pivot-Tabelle zu aktualisieren.
| Privates Unterarbeitsblatt_Deactivate()
Sheet1.PivotTables("PivotTable1").PivotCache.Refresh End Sub |
Und es ist vollbracht.
Wenn Sie nun von den Quelldaten wechseln, wird dieser VBA-Code ausgeführt, um die Pivot-Tabelle1 zu aktualisieren. Wie Sie im folgenden Gif sehen können.
So aktualisieren Sie alle Pivot-Tabellen in der Arbeitsmappe
Im obigen Beispiel wollten wir nur eine bestimmte Pivot-Tabelle aktualisieren. Wenn Sie jedoch alle Pivot-Tabellen in einer Arbeitsmappe aktualisieren möchten, müssen Sie nur geringfügige Änderungen an Ihrem Code vornehmen.
| Privates Unterarbeitsblatt_Deactivate()
'Sheet1.PivotTables("PivotTable1").PivotCache.Refresh Für jeden PC in ThisWorkbook.PivotCaches Stk.Aktualisieren Nächster PC End Sub |
In diesem Code verwenden wir eine For-Schleife, um alle Pivot-Caches in der Arbeitsmappe zu durchlaufen. Das ThisWorkbook-Objekt enthält alle Pivot-Caches. Um darauf zuzugreifen, verwenden wir ThisWorkbook.PivotCaches.
Warum das Ereignis Worksheet_Deactivate verwenden?
Wenn Sie die Pivot-Tabelle aktualisieren möchten, sobald eine Änderung an den Quelldaten vorgenommen wird, sollten Sie das Ereignis Worksheet_Change verwenden. Aber ich empfehle es nicht. Dadurch wird Ihre Arbeitsmappe den Code jedes Mal ausführen, wenn Sie Änderungen im Arbeitsblatt vornehmen. Möglicherweise müssen Sie Hunderte von Änderungen vornehmen, bevor Sie das Ergebnis sehen möchten. Aber Excel aktualisiert die Pivot-Tabelle bei jeder Änderung. Dies führt zu einer Verschwendung von Verarbeitungszeit und Ressourcen. Wenn Sie also Pivot-Tabellen und Daten in verschiedenen Blättern haben, ist es besser, Arbeitsblatt-Deaktivierungsereignis zu verwenden. Damit können Sie Ihre Arbeit beenden. Sobald Sie zu Pivot-Tabellenblättern wechseln, um die Änderungen anzuzeigen, werden die Änderungen geändert.
Wenn sich Pivot-Tabellen und Quelldaten auf demselben Blatt befinden und Pivot-Tabellen automatisch aktualisiert werden sollen, können Sie Worksheet_Change Event verwenden.
| Private Sub Worksheet_Change (ByVal Target As Range)
Sheet1.PivotTables("PivotTable1").PivotCache.Refresh End Sub |
Wie aktualisiere ich alles in Arbeitsmappen, wenn eine Änderung in den Quelldaten vorgenommen wird?
Wenn Sie alles in einer Arbeitsmappe aktualisieren möchten (Diagramme, Pivot-Tabellen, Formeln usw.), können Sie den Befehl ThisWorkbook.RefreshAll verwenden.
| Private Sub Worksheet_Change (ByVal Target As Range)
ThisWorkbook.RefreshAll End Sub |
Notiz : Der Code ändert die Datenquelle nicht. Wenn Sie also Daten unterhalb der Quelldaten hinzufügen, werden diese Daten nicht automatisch in diesen Code aufgenommen. Sie können Excel-Tabellen verwenden, um Quelldaten zu speichern. Wenn Sie keine Tabellen verwenden möchten, können wir VBA auch zum Einbinden neuer Daten verwenden. Wir werden es im nächsten Tutorial lernen.
Ich hoffe, dieser Artikel zum automatischen Aktualisieren von Pivot-Tabellendaten in Excel ist erklärend. Weitere Artikel zum Berechnen von Werten und zugehörigen Excel-Formeln finden Sie hier. Wenn Ihnen unsere Blogs gefallen haben, teilen Sie sie mit Ihren Freunden auf Facebook. Und Sie können uns auch auf Twitter und Facebook folgen. Wir würden uns freuen, von Ihnen zu hören, lassen Sie uns wissen, wie wir unsere Arbeit verbessern, ergänzen oder erneuern und für Sie verbessern können. Schreiben Sie uns auf der E-Mail-Site.
So aktualisieren Sie den Datenquellenbereich der Pivot-Tabelle in Excel dynamisch : Um den Quelldatenbereich von Pivot-Tabellen dynamisch zu ändern, verwenden wir Pivot-Caches. Diese wenigen Zeilen können jede Pivot-Tabelle dynamisch aktualisieren, indem der Quelldatenbereich geändert wird. Verwenden Sie in VBA Pivot-Tabellenobjekte wie unten gezeigt…
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 : Erledigen Sie Ihre Aufgaben in Excel schneller. Diese Verknüpfungen helfen Ihnen, Ihre Arbeitseffizienz in Excel zu steigern.
So verwenden Sie 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.
So verwenden Sie die IF-Funktion in Excel : Die IF-Anweisung in Excel überprüft die Bedingung und gibt einen bestimmten Wert zurück, wenn die Bedingung WAHR ist, oder einen anderen bestimmten Wert, wenn FALSE.
So verwenden Sie die SUMIF-Funktion in Excel : Dies ist eine weitere wesentliche Funktion des Dashboards. Dies hilft Ihnen, Werte unter bestimmten Bedingungen zusammenzufassen.
So verwenden Sie die ZÄHLENWENN-Funktion in Excel : Zählen Sie Werte mit Bedingungen mit dieser erstaunlichen Funktion. Sie müssen Ihre Daten nicht filtern, um bestimmte Werte zu zählen. Die Countif-Funktion ist unerlässlich, um Ihr Dashboard vorzubereiten.