Wie man Auto-Update-Pivot-Tabelle VBA in Microsoft Excel
In diesem Artikel erfahren Sie, wie Sie die Pivot-Tabelle mithilfe von VBA in Microsoft Excel 2010 automatisch aktualisieren.
Warum müssen wir das Pivot-Tabellendiagramm aktualisieren?
Wie wir alle wissen, werden Änderungen an den Quelldaten einer Pivot-Tabelle nicht sofort in der Pivot-Tabelle angezeigt. 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, ist dies möglicherweise peinlich.
In diesem Artikel erfahren Sie, wie Sie eine Pivot-Tabelle mithilfe von VBA automatisch aktualisieren.
Dies ist die einfache Syntax zum automatischen Aktualisieren von Pivot-Tabellen in der Arbeitsmappe.
'Code in Source Data Sheet Object
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, wird der Cache mit neuen Quelldaten aktualisiert, um die Änderungen in der Pivot-Tabelle widerzuspiegeln.
Wir brauchen also nur ein Makro, um den Cache der Pivot-Tabellen zu aktualisieren. Wir werden dies über einen Link tun: / events-in-vba-das-Arbeitsblatt-Ereignisse-in-excel-vba [Arbeitsblatt-Ereignis], damit wir das Makro nicht manuell ausführen müssen.
Wo kann man codieren, um Pivot-Tabellen automatisch zu aktualisieren? Wenn sich Ihre Quelldaten und Pivot-Tabellen in verschiedenen Arbeitsblättern befinden, sollte der VBA-Code in das Quelldatenblatt aufgenommen werden.
Hier verwenden wir das Worksheet_SelectionChange-Ereignis. Dadurch wird der Code immer dann 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. Im Projektexplorer sehen Sie drei Objekte, Sheet1, Sheet2 und die Arbeitsmappe.
Da Sheet2 die Quelldaten enthält, doppelklicken Sie auf das Objekt sheet2.
Jetzt sehen Sie oben im Codebereich zwei Dropdowns. Wählen Sie aus der ersten Dropdown-Liste das Arbeitsblatt aus. Und aus der zweiten Dropdown-Liste wählen Sie Deaktivieren. Dadurch wird ein leerer Subname Worksheet_Deactivate eingefügt. Unser Code wird in dieses Sub geschrieben. Alle in diesem Unterabschnitt geschriebenen Zeilen werden ausgeführt, sobald der Benutzer von diesem Blatt zu einem anderen Blatt wechselt.
Auf Blatt 1 habe ich zwei Pivot-Tabellen. Ich möchte nur eine Pivot-Tabelle aktualisieren. Dafür muss ich den Namen der Pivot-Tabelle kennen. Um den Namen einer Pivot-Tabelle zu ermitteln, wählen Sie eine beliebige Zelle in dieser Pivot-Tabelle aus und wechseln Sie zur Registerkarte Pivot-Tabellenanalyse. Auf der linken Seite sehen Sie den Namen der Pivot-Tabelle. Hier können Sie auch den Namen der Pivot-Tabelle ändern.
Nachdem wir 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 geschafft.
Wenn Sie jetzt von den Quelldaten wechseln, wird dieser VBA-Code ausgeführt, um die Pivot-Tabelle1 zu aktualisieren. Wie Sie im GIF unten sehen können.
Wie aktualisiere ich 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
Next pc
End Sub
In diesem Code verwenden wir einen For loop, um die einzelnen Pivot-Caches in der Arbeitsmappe zu durchlaufen. Das ThisWorkbook-Objekt enthält alle Pivot-Caches. Um darauf zuzugreifen, verwenden wir ThisWorkbook.PivotCaches.
Warum Worksheet_Deactivate Event verwenden?
Wenn Sie die Pivot-Tabelle aktualisieren möchten, sobald Änderungen an den Quelldaten vorgenommen werden, sollten Sie das Ereignis Worksheet_Change verwenden. Aber ich empfehle es nicht. Dadurch wird in Ihrer Arbeitsmappe der Code jedes Mal ausgeführt, wenn Sie Änderungen am Blatt vornehmen. Möglicherweise müssen Sie Hunderte von Änderungen vornehmen, bevor Sie das Ergebnis sehen möchten. Excel aktualisiert jedoch 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 Arbeitsblättern haben, ist es besser, das 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 `link: / tips-using-worksheet-change-event-to-run-macro-when- verwenden Jede Änderung wird vorgenommen [Worksheet_Change Event] `.
Private Sub Worksheet_Change(ByVal Target As Range)
Sheet1.PivotTables("PivotTable1").PivotCache.Refresh
End Sub
Wie aktualisiere ich alles in Arbeitsmappen, wenn Änderungen an den Quelldaten vorgenommen werden? 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, enthält dieser Code diese Daten nicht automatisch. Sie können link: / table-excel-2007-17-erstaunliche-Funktionen-von-Excel-Tabellen [Excel-Tabellen] verwenden, um Quelldaten zu speichern. Wenn Sie keine Tabellen verwenden möchten, können wir VBA auch zum Einfügen neuer Daten verwenden. Wir werden es im nächsten Tutorial lernen.
Hoffe, dieser Artikel über das automatische Aktualisieren der Pivot-Tabelle mit VBA in Microsoft Excel ist erklärend. Weitere Artikel zur Berechnung von Werten und zugehörigen Excel-Formeln finden Sie hier. Wenn Ihnen unsere Blogs gefallen haben, teilen Sie sie Ihren Freunden auf Facebook mit. Und Sie können uns auch auf Twitter und Facebook folgen. Wir würden gerne von Ihnen hören, uns mitteilen, wie wir unsere Arbeit verbessern, ergänzen oder innovieren und für Sie verbessern können. Schreiben Sie uns unter [email protected].
Verwandte Artikel:
Wie man Pivot-Tabelle Datenquellenbereich in Excel dynamisch aktualisiert: Zum dynamischen Ändern der Quelldatenbereich von Pivot-Tabellen verwenden wir Pivot-Caches. Verwenden Sie in VBA Pivot-Tabellen-Objekte wie unten gezeigt …
link: / events-in-vba-run-macro-if-any-change-made-on-sheet-range [Makro ausführen, wenn Änderungen am Blatt im angegebenen Bereich vorgenommen werden]: In Ihren VBA-Praktiken würden Sie erhalten die Notwendigkeit, Makros auszuführen, wenn sich ein bestimmter Bereich oder eine bestimmte Zelle ändert. In diesem Fall verwenden wir das Änderungsereignis, um Makros auszuführen, wenn eine Änderung an einem Zielbereich vorgenommen wird.
link: / tips-using-worksheet-change-event-to-run-macro-wenn-eine-Änderung vorgenommen wird [Makro ausführen, wenn eine Änderung am Blatt vorgenommen wird]: So führen Sie Ihr Makro aus, wann immer das Blatt ausgeführt wird Updates verwenden wir die Arbeitsblattereignisse von VBA.
link: / events-in-vba-einfachster-vba-code zum hervorheben der aktuellen zeile und spalte mit [Einfachster VBA-Code zum Hervorheben der aktuellen Zeile und Spalte mit]: Verwenden Sie dieses kleine VBA-Snippet zum Hervorheben die aktuelle Zeile und Spalte des Blattes.
link: / events-in-vba-das-arbeitsblatt-events-in-excel-vba [Die Arbeitsblattereignisse in Excel VBA]: Das Arbeitsblattereignis ist sehr nützlich, wenn Ihre Makros ausgeführt werden sollen, wenn ein bestimmtes Ereignis auftritt Das Blatt.
Beliebte Artikel:
link: / tips-if-condition-in-excel [Verwendung der IF-Funktion in Excel]: Die IF-Anweisung in Excel überprüft die Bedingung und gibt einen bestimmten Wert zurück, wenn die Bedingung TRUE ist, oder gibt einen anderen bestimmten Wert zurück, wenn FALSE .
link: / formeln-und-funktionen-einführung-der-vlookup-funktion [Verwendung der VLOOKUP-Funktion in Excel]: Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, die zum Nachschlagen von Werten aus verschiedenen Bereichen verwendet wird und Blätter.
link: / excel-formel-und-funktion-excel-sumif-funktion [Verwendung der SUMIF-Funktion in Excel]: Dies ist eine weitere wichtige Dashboard-Funktion. Auf diese Weise können Sie Werte unter bestimmten Bedingungen zusammenfassen.
link: / tips-countif-in-microsoft-excel [Verwendung der COUNTIF-Funktion in Excel]: Zählen Sie Werte mit Bedingungen, die diese erstaunliche Funktion verwenden. Sie müssen Ihre Daten nicht filtern, um bestimmte Werte zu zählen. Die Countif-Funktion ist wichtig, um Ihr Dashboard vorzubereiten.