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 zu sehen. Und wenn Sie eine aktualisierte Datei senden, ohne die Pivot-Tabellen zu aktualisieren, können Sie sich peinlich fühlen.
In diesem Artikel erfahren Sie, 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 Private Sub Worksheet_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.
Private Sub Worksheet_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.
Private Sub Worksheet_Deactivate() 'Sheet1.PivotTables("PivotTable1").PivotCache.Refresh For Each PC In ThisWorkbook.PivotCaches pc.Refresh 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
Bitte beachten Sie, dass dieser Code die Datenquelle nicht ändert. 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.
Also ja, Kumpel, so können Sie die Pivot-Tabellen in Excel automatisch aktualisieren. Ich hoffe, ich war erklärend genug und dieser Artikel hat Ihnen gute Dienste geleistet. Wenn Sie Fragen zu diesem Thema haben, können Sie mich im Kommentarbereich unten stellen.
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 | 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.