Wie aktualisiere ich einen pivot-tabellenbereich automatisch?

Das Aktualisieren einer Pivot-Tabelle ist mühsam, nicht wahr? Wenn Sie in Ihrer Arbeit häufig Pivot-Tabellen verwenden, können Sie das sicher nachvollziehen.

Die Sache ist, dass Sie jedes Mal, wenn Sie dem Quellblatt neue Daten hinzufügen, den Quellbereich der Pivot-Tabelle aktualisieren müssen, bevor Sie Ihre Pivot-Tabelle aktualisieren.

Stellen Sie sich nun vor, dass Sie den Quellbereich jeden Tag aktualisieren müssen, wenn Sie Ihrem Quellblatt täglich Daten hinzufügen.

Und jedes Mal, wenn der Pivot-Tischbereich geändert wird, ist das ein Chaos. Ja, das ist richtig. Je häufiger Sie Daten hinzufügen, desto häufiger müssen Sie den Quellbereich aktualisieren.

Der Punkt ist also, dass Sie eine Methode benötigen, um den Quellbereich automatisch zu aktualisieren, wenn Sie neue Daten hinzufügen.

HINWEIS : Pivot-Tabellen sind Teil der mittleren Excel-Kenntnisse .

Wenden Sie die Tabelle an, um den Pivot-Tabellenbereich automatisch zu aktualisieren

Vor ein paar Tagen habe ich John Michaloudis nach seinem millionenschweren Pivot-Table-Tipp gefragt. Es heißt: Tragen Sie Ihre Quelldaten in eine Tabelle ein. Vertrauen Sie mir, das ist ein Millionen-Dollar-Trinkgeld.

Durch die Anwendung einer Tabelle in den Quelldaten müssen Sie den Quellbereich Ihrer Pivot-Tabelle nicht immer wieder ändern.

Jedes Mal, wenn Sie neue Daten hinzufügen, wird der Pivot-Tabellenbereich automatisch aktualisiert.

Konvertieren Sie Daten in eine Tabelle, bevor Sie eine Pivot-Tabelle erstellen

Stellen Sie jedes Mal vor dem Erstellen einer Pivot-Tabelle sicher, dass Sie die Tabelle mithilfe der folgenden Schritte auf die Quelldaten anwenden.

1. Wählen Sie eine der Zellen in Ihren Daten aus.
2. Verwenden Sie die Tastenkombination Strg + T oder navigieren Sie zu → Registerkarte einfügen → Tabellen → Tabelle.
3. Sie erhalten ein Popup mit Ihrem aktuellen Datenbereich.
   
4. OK klicken.
5. Um nun eine Pivot-Tabelle zu erstellen, wählen Sie eine beliebige Zelle in Ihren Daten aus. Gehen Sie zu → Registerkarte „Entwurf“ → „Extras“ → „Mit PivotTable zusammenfassen“.
   
6. OK klicken.

Wenn Sie jetzt neue Daten zu Ihrem Datenblatt hinzufügen, wird der Pivot-Tabellenbereich automatisch aktualisiert und Sie müssen nur noch Ihre Pivot-Tabelle aktualisieren.

Konvertieren Sie Daten in eine Tabelle, nachdem Sie eine Pivot-Tabelle erstellt haben

Wenn Ihr Arbeitsblatt bereits eine Pivot-Tabelle enthält, können Sie die folgenden Schritte ausführen, um Ihre Datenquelle in eine Tabelle umzuwandeln.

1. Wählen Sie eine der Zellen in Ihrer Datenquelle aus.
2. Verwenden Sie die Tastenkombination Strg + T oder navigieren Sie zu → Registerkarte einfügen → Tabellen → Tabelle.
3. Sie erhalten ein Popup mit Ihrem aktuellen Datenbereich.
4. OK klicken.
5. Wählen Sie nun eine der Zellen in Ihrer Pivot-Tabelle aus und gehen Sie zu → Analysieren → Daten → Datenquelle bearbeiten → Datenquelle bearbeiten (Dropdown-Menü).
6. Sie erhalten ein Popup, in dem Sie Ihre Datenquelle erneut auswählen können, oder Sie können auch den Tabellennamen in den Bereichseintrag eingeben.
   
7. OK klicken.

Von nun an wird der Pivot-Tabellenbereich jedes Mal, wenn Sie neue Daten zu Ihrem Quellblatt hinzufügen, vergrößert, um ihn automatisch zu aktualisieren.

Erstellen Sie mit der OFFSET-Funktion einen dynamischen Pivot-Tabellenbereich

Die andere beste Möglichkeit, den Pivot-Tabellenbereich automatisch zu aktualisieren, ist die Verwendung eines dynamischen Bereichs.

Der Dynamikbereich kann jedes Mal automatisch erweitert werden, wenn Sie Ihrem Quellblatt neue Daten hinzufügen. Hier sind die Schritte zum Erstellen eines Dynamikbereichs.

1. Gehen Sie zu → Registerkarte Formeln → Definierte Namen → Namensmanager.
2. Sobald Sie auf den Namensmanager klicken, wird ein Popup-Fenster angezeigt.
   
3. Klicken Sie in Ihrem Namensmanager-Fenster auf Neu, um einen benannten Bereich zu erstellen.
4. Geben Sie in Ihrem neuen Namensfenster ein
   1. Ein Name für Ihr neues Sortiment. Ich verwende den Namen „SourceData“.
   2. Geben Sie den Umfang des Bereichs an. Sie können zwischen dem aktuellen Arbeitsblatt oder der Arbeitsmappe wählen.
   3. Fügen Sie einen Kommentar hinzu, um Ihren benannten Bereich zu beschreiben. Geben Sie die untenstehende Formel in die Eingabeleiste „Verweisen auf“ ein.
      

       =OFFSET(PivotTableData!$A$1,0,0,COUNTA(PivotTableData!$A:$A),COUNTA(PivotTableData!$1:$1))

5. Klicken Sie abschließend auf OK.

Jetzt haben Sie den Dynamikbereich zum Erstellen einer Pivot-Tabelle.

Sie müssen lediglich eine Pivot-Tabelle mit Ihren Quelldaten erstellen und dann die Quelle mit dem benannten Bereich ändern, indem Sie dieselbe Methode verwenden, die ich bei der ersten Tabellenmethode verwendet habe.

Sobald Sie Ihrem Quellblatt neue Daten hinzugefügt haben, aktualisieren Sie einfach Ihre Pivot-Tabelle.

Wie funktioniert diese Formel?

In der obigen Formel habe ich die Offset-Funktion verwendet, um einen Dynamikbereich zu erstellen.

Ich habe Zelle A1 als Ausgangspunkt erwähnt und dann, ohne Zeilen und Spalten zu erwähnen, die Höhe und Breite des Bereichs mit COUNTA angegeben.

COUNTA zählt Zellen mit Werten aus Spalte A und Zeile 1 und weist Offset an, seine Höhe und Breite entsprechend zu erweitern.

Das Einzige, worauf Sie achten müssen, ist, dass sich zwischen Spalte A und Zeile 1 keine leere Zelle befindet.

Pivot-Tabelle mit VBA-Code aktualisieren

Die meisten Leute verwenden gerne VBA-Codes. Hier ist also der Code, der zum Aktualisieren des Pivot-Tabellenbereichs mit VBA verwendet werden kann.

 Sub UpdatePivotTableRange() Dim Data_Sheet As Worksheet Dim Pivot_Sheet As Worksheet Dim StartPoint As Range Dim DataRange As Range Dim PivotName As String Dim NewRange As String Dim LastCol As Long Dim lastRow As Long 'Set Pivot Table & Source Worksheet Set Data_Sheet = ThisWorkbook.Worksheets("PivotTableData3") Set Pivot_Sheet = ThisWorkbook.Worksheets("Pivot3") 'Enter in Pivot Table Name PivotName = "PivotTable2" 'Defining Staring Point & Dynamic Range Data_Sheet.Activate Set StartPoint = Data_Sheet.Range("A1") LastCol = StartPoint.End(xlToRight).Column DownCell = StartPoint.End(xlDown).Row Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol)) NewRange = Data_Sheet.Name & "!" & DataRange.Address(ReferenceStyle:=xlR1C1) 'Change Pivot Table Data Source Range Address Pivot_Sheet.PivotTables(PivotName). _ ChangePivotCache ActiveWorkbook. _ PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange) 'Ensure Pivot Table is Refreshed Pivot_Sheet.PivotTables(PivotName).RefreshTable 'Complete Message Pivot_Sheet.Activate MsgBox "Your Pivot Table is now updated." End Sub

Dinge, die Sie ändern müssen, bevor Sie es in Ihrer Arbeitsmappe verwenden.

1. Zeile 13: Ändern Sie den Namen des Quellarbeitsblatts.
2. Zeile 14: Ändern Sie den Namen des Pivot-Tabellenblatts.
3. Zeile 17: Ändern Sie den Namen der Pivot-Tabelle.

Wenn bei der Verwendung dieses Codes immer noch Probleme auftreten, schreiben Sie mir bitte im Kommentarfeld. Lassen Sie mich Ihnen nun zeigen, wie dieser Code funktioniert, damit Sie ihn ganz einfach an Ihre Bedürfnisse anpassen können.

 Set Data_Sheet = ThisWorkbook.Worksheets("PivotTableData3") Set Pivot_Sheet = ThisWorkbook.Worksheets("Pivot3")

Im obigen Teil des Codes haben wir die Pivot-Tabellen- und Arbeitsblattvariablen der Quelldaten angegeben. Sie können den Tabellennamen hier ändern.

 PivotName = "PivotTable2"

Geben Sie im obigen Teil des Codes den Namen der Pivot-Tabelle ein, für die Sie diesen Code verwenden möchten.

 Data_Sheet.Activate Set StartPoint = Data_Sheet.Range("A1") LastCol = StartPoint.End(xlToRight).Column DownCell = StartPoint.End(xlDown).Row Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol))

Der obige Teil des Codes erstellt einen dynamischen Bereich unter Verwendung von Zelle A1 des Datenquellen-Arbeitsblatts.

Es überprüft die letzte Spalte und die letzte Zeile mit Daten, um einen dynamischen Bereich zu erstellen. Jedes Mal, wenn Sie dieses Makro ausführen, wird ein neuer Dynamikbereich erstellt.

 'Ensure Pivot Table is Refreshed Pivot_Sheet.PivotTables(PivotName).RefreshTable 'Complete Message Pivot_Sheet.Activate MsgBox "Your Pivot Table is now updated."

Der obige Teil des Codes aktualisiert die Pivot-Tabelle und zeigt dem Benutzer eine Meldung an, dass die Pivot-Tabelle nun aktualisiert ist.