Werte gruppieren in Pivot-Tabellen 4

Eine spezielle Gruppierungsfunktion in Pivot-Tabellen stellt klassische Formellösungen komplett in den Schatten.
 

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 einem eigenen Artikel)

 

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

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.



Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

4 Gedanken zu “Werte gruppieren in Pivot-Tabellen