Als regelmäßiger Leser meines Blogs weißt du wahrscheinlich, das Pivot-Tabellen eines meiner Steckenpferde sind. Schnellere und flexiblere Auswertungen über große Datenmengen sind mit anderen Mitteln in Excel kaum möglich. Und auch wenn du schon länger mit Pivot-Tabellen arbeitest, kennst du vielleicht die spezielle Gruppierungsfunktion noch nicht, die ich dir heute zeigen möchte.
Und so geht’s:
Solltest du bisher um Pivot-Tabellen eher einen großen Bogen gemacht haben, empfehle ich dir die beiden Einsteigerartikel:
Der beste Freund des Excel-Analysten: Pivot-Tabellen
Der Horror geht weiter: Pivot-Tabellen Teil 2
Du wirst feststellen, sie sind wirklich einfach!
Gruppierung in Pivot-Tabellen
Fast jeder Pivot-Tabellen-Anwender wird schon einmal die automatische Gruppierung von Datumsfeldern in Pivot-Tabellen gesehen haben: Sobald man ein Datumsfeld in den Zeilenbereich zieht, wird Excel dieses Feld automatisch nach verschiedenen Einheiten gruppieren.
Datumsfeld im Zeilenbereich
Üblicherweise werden neue Felder für Jahr, Monat und/oder Quartal eingefügt, so dass die Daten bequem auf diesen Datumsebenen verdichtet dargestellt werden können.
Neue Felder durch automatische Gruppierung
Tipp:
Wen dieses Standardverhalten stört, kann die automatische Gruppierung von Datumsfeldern in den Excel-Optionen auch abschalten:
Automatische Gruppierung deaktivieren
Weniger bekannt ist aber die Möglichkeit, ein numerisches Feld zu gruppieren. Damit bietet sich beispielsweise die Möglichkeit, eine Auftragsliste mit wenigen Klicks in unterschiedliche Umsatzklassen einteilen zu lassen. Die Beispieldatei kannst du dir hier herunterladen.
Gruppierung von numerischen Feldern
Bleiben wir bei dem Beispiel mit der Auftragsliste. Meine Beispieltabelle enthält je Auftrag einen Datensatz mit diversen Kopfdaten, einschließlich des Auftragswerts.
Auftragsliste als Quelltabelle
Nun würde ich gerne wissen, wie viele Aufträge einen Wert zwischen 0 – 250 €, 250 – 500 €, 500 – 1000 € usw. haben und wie hoch der Gesamtauftragswert innerhalb dieser Klassen ist. Dazu müssen die Aufträge zu den entsprechenden Klassen verdichtet werden.
Diese Aufgabe könnte man jetzt mit verschiedenen Tabellenfunktionen lösen, wie SUMMEWENNS und ZÄHLENWENNS. Das ist aber etwas mühsam, vor allem, wenn man später vielleicht die Umsatzklassen nochmal ändern möchte und statt 250er-Schritten vielleicht doch lieber 500er-Schritte sehen will. Mit einer Pivot-Tabelle sind das nur ein paar Klicks!
Dazu erstelle ich eine neue Pivot-Tabelle und ziehe das Feld „Auftragswert“ in den Zeilenbereich:
Ein numerisches Feld im Zeilenbereich
Zunächst wird noch jeder Auftragswert, der in der Rohdatentabelle vorkommt, einzeln aufgelistet. Jetzt stelle ich die aktive Zelle auf irgendeinen Auftragswert in der Pivot-Tabelle und klicke in den PivotTable-Tools auf die Schaltfläche „Feld gruppieren“. Damit öffnet sich ein neues Gruppierungsfenster, in dem der kleinste und größte Auftragswert meiner Liste bereits enthalten sind:
Auftragswert automatisch gruppieren
Außerdem schlägt mir Excel eine Gruppierung in 100er-Schritten vor. Diese drei Werte kann ich nun auf meine Vorstellungen hin anpassen. Als Startwerte trage ich 0 ein, als höchsten Wert einen, der etwas über dem vorgeschlagenen Höchstwert liegt. Und im Feld „Nach“ gebe ich eine Klassengröße von 250 ein:
Start-, Endwert und Schrittweite anpassen
Nach einem Klick auf „OK“ sieht meine Pivot-Tabelle schon deutlich besser aus. Statt der einzelnen individuellen Auftragswerte werden mir jetzt Klassen in 250er-Schritten angezeigt:
Gruppierter Zeilenbereich
Nun ziehe ich mir noch die Felder „Auftragsnummer“ und „Auftragswert“ in den Wertebereich und schon habe ich meine gewünschten Ergebnisse:
Fertige Pivot-Tabelle
Wenn ich jetzt die Aufträge doch lieber in 500er-Schritten gruppieren möchte, geht das ebenfalls ganz schnell. Einfach die aktive Zelle nochmal auf einen Auftragswert (in Spalte A!) stellen, die Schaltfläche „Feld gruppieren“ klicken und den neuen Schritt-Wert eintragen:
Gruppierung anpassen
Pivot mit neuer Gruppierung
Nicht schlecht, oder?
Behandlung von Ausreißern
Was passiert nun, wenn in meiner Quelltabelle neue Aufträge dazukommen, deren Auftragswerte über oder unter meinen gesetzten Grenzen von 0 und 3000 Euro liegen (zum Beispiel für eine Retoure mit einem negativen Wert)? Um das zu demonstrieren, habe ich die Auftragstabelle um zwei neue Datensätze ergänzt:
Quelltabelle mit Ausreißer-Datensätzen
Dann muss ich in meiner Pivot-Tabelle noch auf „Alle aktualisieren“ klicken und schon sieht man das neue Ergebnis. Excel hat automatisch zwei neue Klassen eingefügt, um auch die beiden Ausreißer abzudecken:
Automatisch angepasste Pivot-Tabelle
Man braucht also keine Angst zu haben, dass irgendwelche Daten verloren gehen bzw. nicht berücksichtigt werden. Und deutlich schneller und flexibler als eine Formellösung ist die Pivot-Tabelle ohnehin.
Ein kleiner Haken
So praktisch Gruppierungen auch sind, man sollte eine kleine Einschränkung kennen: Es ist leider nicht so ohne weiteres möglich, in zwei Pivot-Tabellen unterschiedliche Gruppierungen zu verwenden. Wenn ich also bezogen auf das Beispiel oben eine Pivot-Tabelle mit 250er-Schritten und eine zweite mit 500er-Schritten einrichten möchte, wird mir das nicht gelingen. Alle Pivot-Tabellen, die auf der gleichen Datenquelle basieren, verwenden automatisch auch die gleichen Gruppierungen. Sobald ich die Schrittweite ändere, gilt diese für alle Pivot-Tabellen.
(Mit einem Trick und etwas Hintergrundwissen lässt sich diese Einschränkung umgehen, aber das verrate ich in diesem Artikel)
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Wie immer, eine geniale Hilfestellung und somit Kenntniserweiterung!
Und ich glaube zu wissen, wie man die o.a. Einschränkung umgehen kann.
Werde es gleich testen!
Vielen Dank für den tollen Beitrag!
Hallo Rolf,
gern geschehen, vielen Dank für das Feedback!
Schöne Grüße,
Martin
„Man braucht als keine Angst zu haben, dass irgendwelche Daten verloren“
als -> also
Danke für den Hinweis, ist korrigiert.
Schöne Grüße,
Martin
Wie immer klasse Hinweise!
Lässt sich über das Pivot-Gruppieren auch eine „Rest-Gruppe“ definieren? Z.B. hab ich die Lieferanten Super-Wichtig AG, Total-Wichtig GmbH und dann noch 25 kleine (die in manchen Monaten Umsatz generieren und in anderen nicht. Da mich im Wesentlichen nur die beiden Großen und dann noch die Summe der Kleinen interessiert, würde ich gerne alles, was nicht Super- oder Total-Wichtig ist, als Rest zusammen anzeigen. Geht das?
Viele Grüße,
Detlef
Hallo Detlef,
jein 🙂
man kann manuelle Gruppierungen vornehmen, das habe ich vor längerer Zeit mal hier beschrieben:
https://www.tabellenexperte.de/pivot-tabellen-teil-2/
Du müsstest also alle kleinen Kunden zu einer „Sonstigen“ Gruppe zusammenfassen. Wenn jedoch später Umsätze bei neuen Kunden dazukommen, musst du sie erst wieder manuell in diese sonstige Gruppe aufnehmen.
Eine Alternative wäre, gleich in der Quelltabelle ein entsprechendes Gruppenmerkmal vorzusehen. Dann sparst du dir die manuelle Anpassung in der Pivot-Tabelle.
Schöne Grüße,
Martin
Hallo, Martin,
Geniale Tipps!
Dieser Satz …
„Alle Pivot-Tabellen, die auf der gleichen Datenquelle basieren, verwenden automatisch auch die gleichen Gruppierungen.“
… hat mir weiteres Grübeln erspart, danke!
Gibt’s den erwähnten Beitrag …
„(Mit einem Trick und etwas Hintergrundwissen lässt sich diese Einschränkung umgehen, aber das verrate ich in einem eigenen Artikel)“
… schon irgendwo? Ich hab nämlich noch keine Vorstellung von einer eleganten Lösung! Meine momentane Abhilfemaßnahme ist so vergleichsweise ungelenk.
Gruß, Christine
Hallo Christine,
ich fürchte, diesen angekündigten Artikel gibt es tatsächlich noch nicht. Aber ich habe mir jetzt mal eine Notiz gemacht, dass ich dieses Thema nochmal aufgreifen sollte. Das wird aber frühestens im März passieren.
Schöne Grüße,
Martin
Hallo, Martin,
endlich habe ich wieder Zeit gefunden, an diesem Thema weiter zu knobeln. Inhaltlich geht es um eine Mitgliederverwaltung und ich möchte z. B. Altersgruppen darstellen können, aber auch Gruppierungen nach Eintrittsdaten. Das führte zu einer Fehlermeldung.
Basis ist eine Excel-Datei mit allen Stammdaten, meine „Betrachtungen“ lege ich in einer separaten Datei an, die sich die Daten aus der Stammdatendatei auf ein Arbeitsblatt „holt“, indem ich per „=Quelldatei“Zelladresse“ die relevanten Inhalte Zelle für Zelle auf das Arbeitsblatt ziehe. (So lassen sich störende oder überflüssige Zeilen ausschließen.) Dieses neue Arbeitsblatt in der „Betrachtungsdatei“ zeigt nun meine „Pivot-Basis“, in der der gewünschte Dateibereich mit einem Namen versehen ist. Wenn sich Gruppierungsabsichten „beißen“, dann richte ich – entsprechend meiner jüngsten Idee – auf dem Arbeitsblatt „Pivot-Basis“ für dieselben Daten einen weiteren Namen ein und stütze die konkurrierende Gruppierung auf diesen zweiten Namen.
Bisher klappt’s! Vielleicht kannst du diesen Ansatz bei deinen weiteren Forschungen verifizieren und ggf. optimieren!
Schönen Gruß, Christine
Hallo Christine,
das ist natürlich auch ein pragmatischer und wirkungsvoller Ansatz, einen zweiten Bereich anlegen, der die Daten aus der Original-Quelldatei holt und somit gibt es für die Pivot-Tabellen natürlich auch getrennte Datenquellen.
Ich vermeide immer nur direkte Verweise in andere Dateien, das führt langfristig häufig zu Problemen. Dateien werden umbenannt oder verschoben oder gelöscht und man hat damit viele „tote“ Verknüpfungen. Eine alternative wäre ein Import über Power Query.
Schöne Grüße,
Martin
Schöne Grüße,
Martin
Mit einem Trick und etwas Hintergrundwissen lässt sich diese Einschränkung umgehen, aber das verrate ich in einem eigenen Artikel.
Wo finde ich diesen Artikel, genau das würde mich interessieren
Hallo uschi,
den besagten Artikel findest du hier:
https://www.tabellenexperte.de/pivot-tabellen-gruppierung-und-ein-mysterioeser-cache/
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für deine hilfreichen Tipps.
Gibt es auch eine Möglichkeit bei der nummerischen Gruppierung, die Feldinhalte zu formatieren?
Aus der Datentabelle referenziere ich auf Prozentangaben und möchte diese gruppieren. So weit so gut. Nur nach dem Gruppieren tranferiert Excel das Format „Prozent“ in das Format „Standard“. Hast du einen Vorschlag?
Hallo Oliver,
tut mir leid, da habe ich auch keinen Tipp.
Schöne Grüße,
Martin
Hallo Martin,
eine super Erklärung hast du da geschrieben, danke. Allerdings kann ich meine Werte trotzdem nicht Gruppieren, da die Schaltfläche „Gruppenfeld“ bei mir ausgegraut ist. Was mache ich in so einem Fall? …bin schier am verzweifeln…
Danke.
Gruß
Matthias
Hallo Matthias,
danke für dein Feedback. Wenn sich ein Feld nicht gruppieren lässt, liegt das häufig daran, dass irgendwelche fehlerhaften Werte in der Quelltabelle enthalten sind. Beispielsweise enthält die Datumsspalte oft irgendwelche Texteinträge, die kein echtes Datum sind. Das können übrigens auch Leerzeichen sein!
Schöne Grüße,
Martin
SUPER! Genau das, was ich gerade gesucht habe.
Vielen Dank für diesen Blog – und auch für alle anderen…
Hallo Thomas,
vielen Dank für dein Feedback, freut mich zu hören, dass der Artikel weitergeholfen hat.
Schöne Grüße,
Martin