Unterm Strich: Ermitteln Sie die Unterschiede zwischen der Funktion Gruppendaten in einer Pivot-Tabelle und Gruppieren von Daten in den Quelldaten, indem Sie zusätzliche Spalten für Jahr, Monat, Quartal usw. hinzufügen.
Skill level: Intermediate
Einer der vielen Gründe, warum Pivot-Tabellen großartig sind, ist, dass sie es uns ermöglichen, Daten schnell zu gruppieren, um zusammenfassende Berichte zu erstellen. Was macht die Gruppierung der Daten zu bedeuten?, Nun, es bedeutet nur, dass wir alle Datumswerte in einer Spalte in einem Jahr, Quartal, Monat, Tag usw. gruppieren.
Wenn wir Daten auf Transaktionsebene mit einer Spalte haben, die das Transaktionsdatum enthält, möchten wir möglicherweise die Daten gruppieren, um die Daten nach Jahr, Quartal oder Monat zu melden.
Es gibt drei Möglichkeiten, die Datumsgruppierung in Excel durchzuführen:
- Wir können die Gruppenfeldfunktion der Pivot-Tabelle verwenden.
- Wir können zusätzliche Spalten in den Quelldaten mit Text oder numerischen Werten mithilfe von Formeln erstellen.,
- Wir können Beziehungen zwischen Tabellen mit Nachschlageformeln oder Power Pivot erstellen.
Ist einer besser als der andere? Nicht unbedingt. Wie alles in Excel gibt es immer ein paar verschiedene Möglichkeiten, eine Aufgabe zu erfüllen. Die Methode, die Sie hier verwenden, hängt von Ihren Anforderungen und dem Steuerkalender Ihres Unternehmens ab.
#1-Die Pro & Nachteile der Gruppenfeldfunktion
Mit der Gruppenfeldfunktion können wir schnell ein Datumsfeld in Jahr, Quartal, Monat usw. gruppieren.,
Dies bedeutet, dass die Quelldaten KEINE Spalten mit dem Namen Jahr, Quartal oder Monat enthalten müssen. Die Gruppenfeldfunktion erstellt automatisch die Gruppierungen für uns und fasst die Daten in jeder Gruppe zusammen.
Nachdem wir die Gruppen im Gruppierungsmenü erstellt haben, werden der Feldliste der Pivot-Tabelle neue Felder hinzugefügt.
Gruppieren & Gruppieren von Feldern
Hier ist eine Kurzanleitung zum Gruppieren von Feldern in Ihren Pivot-Tabellen.,
- Wählen Sie eine Zelle im Zeilen-oder Spaltenbereich der Pivot-Tabelle aus, die das zu gruppierende Feld enthält. Dies ist normalerweise ein Datumsfeld, kann aber auch Zahlen sein.
- Wählen Sie im Menüband die Registerkarte Analysieren/Optionen.
- Klicken Sie auf die Schaltfläche Gruppenfeld. Schauen Sie sich diesen Artikel an, wenn die Schaltfläche Gruppenfeld deaktiviert ist.
- Wählen Sie die Elemente aus, nach denen Sie das Feld gruppieren möchten. Für Datumsfelder sind dies Jahre, Viertel, Monate, Tage, Stunden, Minuten, Sekunden.,
- Klicken Sie auf OK.
Das Feld wird gruppiert und neue Felder werden der Liste Felder für die Gruppen hinzugefügt.
Um das Feld zu gruppieren:
- Wählen Sie eine Zelle in der Pivot-Tabelle für das gruppierte Feld aus.
- Wählen Sie im Menüband die Registerkarte Analysieren/Optionen.
- Drücken Sie die Ungroup-Taste (oberhalb der Group-Taste).
Option Automatische Datums – / Uhrzeitgruppierung
In Excel 2016 erfolgt diese Gruppierung automatisch, wenn Sie einem der Bereiche in der Pivot-Tabelle ein Datumsfeld hinzufügen. Dies ist eine Einstellung, die jetzt im Excel-Optionsmenü ein-oder ausgeschaltet werden kann.,
So deaktivieren Sie die automatische Datumsgruppierung für Pivot-Tabellen:
- Gehen Sie zur Datei > Optionen in Excel, um das Excel-Optionsfenster zu öffnen.
- Klicken Sie in der linken Seitenleiste auf die Registerkarte Daten. Wenn Sie eine ältere Version von Excel verwenden, befindet sich diese auf der Registerkarte Erweitert.
- Aktivieren Sie das Kontrollkästchen“ Automatische Gruppierung von Datums – /Uhrzeitspalten in PivotTables deaktivieren“.
- Klicken Sie auf OK.
Hier ist ein Screenshot der Einstellung im Excel-Optionsfenster.,
Wenn Sie eine ältere Version von Excel 2016 verwenden, befindet sich diese Einstellung auf der Registerkarte Erweitert der Excel-Optionen. Hier ist ein screenshot.
Wenn Sie die automatische Gruppierungsfunktion deaktivieren, können Sie die Gruppenfunktion weiterhin für Pivot-Tabellen verwenden. Die Gruppen werden NICHT automatisch erstellt, wenn Sie ein Feld in einen Bereich in der Pivot-Tabelle ziehen, aber Sie können auf die Schaltfläche Gruppe auf der Registerkarte Analysieren klicken, um das Gruppenfeldmenü aufzurufen.
Wo sind die gruppenfelder gespeichert?,
Obwohl wir die neuen Datums – /Zeitgruppenfelder in der Feldliste sehen, werden diese Felder NICHT in den Quelldaten erstellt. Sie werden tatsächlich im Pivot-Cache im Hintergrund gespeichert.
Das kann gut oder schlecht sein.
Pivot-Tabellen können sich einen Pivot-Cache teilen, wenn sie denselben Quelldatenbereich oder dieselbe Tabelle verwenden. Das bedeutet, dass alle Pivot-Tabellen, die den Cache gemeinsam nutzen, auch die Gruppierungen gemeinsam nutzen.
Wenn Sie also ein Datumsfeld in einer Pivot-Tabelle gruppieren, erhalten Sie möglicherweise unerwartete Ergebnisse in einer anderen Pivot-Tabelle., Ihre anderen Pivot-Tabellen, die denselben Pivot-Cache verwenden, können sich ebenfalls ändern. Dies kann zu frustration und Verwirrung.
Leider gibt es keine Möglichkeit zu sehen, welche Pivot-Tabellen in der Excel-Anwendung denselben Cache verwenden. Wir können dafür VBA verwenden.
Sie können den Cache freigeben, indem Sie den Quelldatenbereich ändern, aber dies kann chaotisch werden, wenn Sie viele Pivot-Tabellen haben. Mein PivotPal-Add-In verfügt auch über eine Funktion, die eine Liste aller Pivot-Tabellen in der Arbeitsmappe und den zugehörigen Pivot-Cache anzeigt.,
Der Hauptvorteil der Verwendung der Pivot-Tabellengruppenfeldfunktion besteht also darin, dass sie schnell und einfach ist. Wenn Sie nur einen schnellen Pivot-Tabellenbericht nach Jahr, Quartal oder Monat benötigen, ist dies eine einfache Lösung. Neue Funktionen in Pivot-Diagrammen ermöglichen es uns auch, die gruppierten Datumsfelder zu Drilldown/up (expand/Collapse). Mit einem Klick auf eine Schaltfläche können wir schnell von einem jährlichen Trend zu einem vierteljährlichen zu einem monatlichen Trend in einem Diagramm wechseln.,
#2 – Die Profis & Nachteile der Gruppierung von Daten in den Quelldaten
Die andere Option besteht darin, den Quelldaten, die die Gruppen berechnen, Spalten hinzuzufügen. Ich habe dies in meinem Artikel zum Gruppieren von Zeiten in Excel ausführlicher erklärt.
Wir können die Textfunktion oder einige der Datumsfunktionen (JAHR, MONAT, TAG) verwenden, um den Text oder den numerischen Wert zu berechnen, der die Gruppierung darstellt.,
Dies bedeutet, dass die gruppierten Felder tatsächlich Spalten in den Quelldaten sind und nur im Pivot-Cache gespeichert sind.
Warum ist das wichtig? Nun, es gibt uns mehr Flexibilität, Änderungen an unseren Gruppen vorzunehmen. Wenn Sie die Gruppierungsfunktion der Pivot-Tabelle verwenden, kann das Gruppieren oder Hinzufügen neuer Gruppierungen zu unerwarteten Ergebnissen für andere Pivot-Tabellen führen, die den Cache gemeinsam nutzen.,
Wenn wir den Quelldaten des Pivots eine neue Datumsgruppierung hinzufügen, werden die Pivot-Tabellen, die diese Quelldaten verwenden, nicht unbedingt beeinflusst oder geändert.
Ein weiterer Vorteil der Gruppierung von Daten in den Quelldaten besteht darin, dass Ihr Unternehmen über ein Geschäftsjahr berichtet, das sich vom Kalenderjahr unterscheidet. In diesem Fall müssen wir möglicherweise eine Formel verwenden, um Quartale oder Steuermonate zu berechnen. Wir können diese Formel als neue Spalte in den Quelldaten hinzufügen und dieses Feld dann der Pivot-Tabelle hinzufügen.,
Die Funktion Gruppenfelder in der Pivot-Tabelle berechnet KEINE Steuerkalender.
Ein weiterer Vorteil besteht darin, dass wir die Datumsgruppenspalten möglicherweise in anderen zusammenfassenden Berichten, Formeln oder Analysen außerhalb der Pivot-Tabelle wiederverwenden möchten.
Ein großer Nachteil ist hier das Sortieren. Die Pivot-Tabelle erkennt nicht immer die richtige Reihenfolge Ihrer Steuermonate, und möglicherweise müssen wir die Steuermonatsnummer an die Vorderseite des Monatsnamens anhängen.
#3 – Was ist Power Pivot?,
Power Pivot hilft bei diesem Sortier – und Gruppierungsproblem durch die Verwendung von Kalendertabellen. Kalendertabellen sind Tabellen, die eine Liste von Daten für den gesamten Zeitraum unserer Daten enthalten. Sie enthalten auch zusätzliche Spalten für das Datum Gruppierungen.
Wir können dann Power Pivot verwenden, um Beziehungen zwischen unseren Datensätzen und der Kalendertabelle zu erstellen, um die Daten zu gruppieren. Dies ähnelt einem VLOOKUP-Ansatz in Excel und einem Join in Datenbankbegriffen.,
Der Ansatz der Kalendertabelle fügt der ursprünglichen Quelldatentabelle keine Spalten hinzu und wendet die Gruppierung nicht an im Pivot-Cache. Stattdessen werden die in Power Pivot erstellten Beziehungen (Join) verwendet, um die Gruppierung im laufenden Betrieb auszuführen.
Ich halte dies für einen hybriden Ansatz, der eher der Gruppierung von Daten in den Quelldaten entspricht. Power Pivot verfügt jedoch über eine Funktion zum Sortieren nach Spalten, mit der Sie Ihre Kalendertabelle nach Geschäftsquartalen, Monaten, Wochen usw. sortieren können.,
Wir können auch Kalendertabellen mit Power Query erstellen. Verwenden Sie dann Nachschlageformeln, um die Feldgruppen in unsere Quelldaten zu ziehen. Oder verwenden Sie die Merge-Funktion von Power Query, um die Suche durchzuführen. Schauen Sie sich meinen Artikel in der erläuterten Kalendertabelle für weitere Details an.
Hier ist ein Artikel meines Freundes Ken Puls zum Erstellen einer dynamischen Kalendertabelle in Power Query.
Datumsgruppierungsmethode Vergleichsliste
Wow! Das sind viele Optionen! Und ein bisschen ein faltiges Hemd., Lassen Sie es uns in einer organisierten Liste ausbügeln… 🙂
Pivot Table Group Field Feature
- Pro – Schnell und einfach Datumsgruppen mit dem integrierten Gruppierungsmenü zu erstellen.
- Pro / Con-Pivot-Tabellen, die sich denselben Cache teilen, teilen sich auch dieselben Gruppierungen.
- Pro-Drill up/down (collapse/expand) Datumsgruppenfelder in Pivot-Diagrammen mit Schaltflächen.
- Con-Excel 2016 gruppiert Datumsfelder automatisch, sodass sie manuell nicht gruppiert werden müssen, wenn Sie die Gruppierung nicht möchten.,
Datumsspalten zu den Quelldaten hinzufügen
- Pro-Datumsspalten werden zu den Quelldaten hinzugefügt und können in anderen Analysen außerhalb einer Pivot-Tabelle verwendet werden.
- Con-Source-Daten enthalten mehr Spalten und fügt Datei. Kann für große Datensätze ineffizient sein.
- Pro-Kann Geschäftsquartale, Monate, Wochen für Steuerkalender berechnen, die nicht am 1. Januar beginnen.
- Con – Sortierung die Steuerzeiträume können herausfordernd sein und erfordern zusätzliche Zahlen in den textbasierten Daten.,
Power-Pivot-Datumstabellen
- Pro – Date-Gruppierungen, die durch Beziehungen erstellt wurden, machen die Berichterstellung effizienter und flexibler, indem Tabellen eng gehalten werden.
- Pro-Eine Datumstabelle kann mit mehreren Datensätzen wiederverwendet und zum Erstellen von Beziehungen zwischen mehreren (Fakt -) Datentabellen verwendet werden.
- Pro/Con – Erfordert das erstellen einer separaten Tabelle date. Power Query kann helfen, dies einfach zu machen.
- Con – Erfordert die Verwendung von Power Pivot. Alle Benutzer müssen Power Pivot installiert haben, um die Pivot-Tabellen zu ändern oder mit ihnen zu interagieren.
Was habe ich vermisst?,
ich glaube nicht, es gibt jede richtige oder falsche Antwort hier. Die von Ihnen gewählte Methode hängt von Ihren Anforderungen und Ihrem Prozess ab.