Wie dynamisch aktualisieren Pivot-Tabelle Datenquelle Bereich in Excel
Derzeit können wir Pivot-Tabellen mithilfe von „link: / table-excel-2007-17-erstaunliche-Funktionen-von-Excel-Tabellen [Excel-Tabellen]“ oder „link: / excel-range-name-dynamic-“ dynamisch ändern oder aktualisieren. Named-Bereiche-in-Excel [Dynamic Named Ranges]. Diese Techniken sind jedoch nicht kinderleicht. Da müssen Sie die Pivot-Tabelle noch manuell aktualisieren. Wenn Sie große Datenmengen haben, die Tausende von Zeilen und Spalten enthalten, helfen Ihnen Excel-Tabellen nicht viel. Stattdessen wird Ihre Datei dadurch schwer. Der einzige Weg bleibt also VBA.
In diesem Artikel erfahren Sie, wie wir unsere Pivot-Tabelle dazu bringen können, die Datenquelle automatisch zu ändern. Mit anderen Worten, wir werden den manuellen Prozess des Änderns der Datenquelle automatisieren, um neue Zeilen und Spalten, die den Quelltabellen hinzugefügt wurden, dynamisch einzuschließen und die Änderung der Pivot-Tabelle sofort widerzuspiegeln.
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 //events-in-vba/the-worksheet-events-in-excel-vba.html[worksheet events] `verwenden.
Wenn sich die Quelldaten und Pivot-Tabellen in unterschiedlichen Blättern befinden, schreiben wir den VBA-Code, um die Datenquelle der Pivot-Tabelle in das Blattobjekt zu ändern, das die Quelldaten enthält (nicht das, das die Pivot-Tabelle enthält). 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 sehen Sie keine Änderung, 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 über alle Arbeitsblattereignisse link: / events-in-vba-das-Arbeitsblatt-Ereignisse-in-excel-vba [hier] lesen.
Jetzt können wir den Code implementieren.
Quellcode zum dynamischen Aktualisieren der Pivot-Tabelle mit neuem Bereich Um zu erklären, wie es funktioniert, habe ich eine Arbeitsmappe. Diese Arbeitsmappe enthält zwei Blätter. Sheet1 enthält die Quelldaten, die sich ändern können. Sheet2 enthält die Pivot-Tabelle, die von den Quelldaten von Sheet2 abhängt.
Jetzt habe ich diesen Code in den Codierungsbereich von sheet1 geschrieben. Ich verwende das Ereignis Worksheet_Deactivate, sodass dieser Code ausgeführt wird, um die Pivot-Tabelle zu aktualisieren, wenn wir vom Quelldatenblatt wechseln.
Private Sub Worksheet_Deactivate()
Dim pt As PivotTable
Dim pc As PivotCache
Dim source_data As Range
lstrow = Cells(Rows.Count, 1).End(xlUp).Row
lstcol = Cells(1, Columns.Count).End(xlToLeft).Column
Set source_data = Range(Cells(1, 1), Cells(lstrow, lstcol))
Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, SourceData:=source_data)
Set pt = Sheet2.PivotTables("PivotTable1")
pt.ChangePivotCache pc
End Sub
Wenn Sie eine ähnliche Arbeitsmappe haben, können Sie diese Daten direkt kopieren. Ich habe erklärt, dass dieser Code unten funktioniert.
Sie können die Wirkung dieses Codes in GIF unten sehen.
Wie ändert dieser Code automatisch die Quelldaten und aktualisiert die Pivot-Tabellen? Zuerst haben wir ein worksheet_deactivate-Ereignis verwendet. Dieses Ereignis wird nur ausgelöst, wenn das Blatt mit dem Code umgeschaltet oder deaktiviert wird. So läuft der Code also automatisch ab.
Um nun die Quelldaten der Pivot-Tabelle zu ändern, ändern wir die Daten im Pivot-Cache. Eine Pivot-Tabelle wird mithilfe des Pivot-Cache erstellt. Der Pivot-Cache enthält die alten Quelldaten, bis die Pivot-Tabelle nicht manuell aktualisiert oder der Quelldatenbereich manuell geändert wird.
Wir haben Referenzen der Pivot-Tabellen mit dem Namen pt, dem Pivot-Cache mit dem Namen pc und einem Bereich mit dem Namen source_data erstellt. Die Quelldaten enthalten die gesamten Daten. 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 (xlToLeft) .Column
Mit diesen beiden Zahlen definieren wir die Quelldaten. Wir sind sicher, dass der Quelldatenbereich immer bei A1 beginnt.
Set source_data = Range (Zellen (1, 1), Zellen (lstrow, lstcol))
Jetzt haben wir die Quelldaten, die dynamisch sind. Wir müssen es nur in der Pivot-Tabelle verwenden.
Wir speichern diese Daten im Pivot-Cache, da wir wissen, dass der Pivot-Cache alle Daten speichert.
Set pc = ThisWorkbook.PivotCaches.Create (xlDatabase, SourceData: = source_data) Als nächstes definieren wir die Pivot-Tabelle, die wir aktualisieren möchten. Da wir die PivotTable1 (Name der Pivot-Tabelle. Sie können den Namen der Pivot-Tabelle auf der Registerkarte „Analysieren“ überprüfen, während Sie die Pivot-Tabelle auswählen.) Auf Blatt1 aktualisieren möchten, setzen wir pt wie unten gezeigt.
Setze pt = Sheet2.PivotTables („PivotTable1“)
Jetzt verwenden wir einfach diesen Pivot-Cache, um die Pivot-Tabelle zu aktualisieren. Wir verwenden die changePivotCache-Methode des pt-Objekts.
pt.ChangePivotCache pc
Und wir haben unseren Pivot-Tisch automatisiert. Dadurch wird Ihre Pivot-Tabelle automatisch aktualisiert. Wenn Sie mehrere Tabellen mit derselben Datenquelle haben, verwenden Sie einfach denselben Cache in jedem Pivot-Tabellenobjekt.
Also ja Leute, so können Sie den Datenquellenbereich 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.
Verwandte Artikel:
link: / custom-functions-in-vba-How-to-Auto-Refresh-Pivot-Tabellen-using-vba-excel [So aktualisieren Sie Pivot-Tabellen automatisch mit VBA]: Zum automatischen Aktualisieren Ihrer Pivot-Tabellen, die Sie verwenden können VBA-Ereignisse. 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.
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 dies tun Sie müssen Makros ausfü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 auf dem Blatt vorgenommen wird] | Um Ihr Makro bei jeder Aktualisierung des Arbeitsblatts auszuführen, 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, um die aktuelle Zeile und Spalte des Blattes hervorzuheben.
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 auf dem Blatt auftritt.
Beliebte Artikel:
link: / Tastatur-Formel-Verknüpfungen-50-Excel-Verknüpfungen-zur-Steigerung-Ihrer-Produktivität [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 an Excel arbeiten Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, mit der Werte aus verschiedenen Bereichen und Tabellen gesucht werden. link: / tips-countif-in-microsoft-excel [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 wichtig, um Ihr Dashboard vorzubereiten.
link: / excel-formel-und-funktion-excel-sumif-funktion [Verwendung der SUMIF-Funktion in Excel] | Dies ist eine weitere wichtige Funktion des Dashboards. Auf diese Weise können Sie Werte unter bestimmten Bedingungen zusammenfassen.