Werte gruppieren in Pivot-Tabellen 18

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 diesem 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.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar

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

18 Gedanken zu “Werte gruppieren in Pivot-Tabellen

  • Avatar-Foto
    Rolf Clausing

    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!

  • Avatar-Foto
    Detlef

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Christine

    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

    • Avatar-Foto
      Martin Weiß

      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

      • Avatar-Foto
        Christine

        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

        • Avatar-Foto
          Martin Weiß

          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

  • Avatar-Foto
    uschi

    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

  • Avatar-Foto
    Oliver

    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?

  • Avatar-Foto
    Matthias Gwosdz

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Thomas Cramer

    SUPER! Genau das, was ich gerade gesucht habe.
    Vielen Dank für diesen Blog – und auch für alle anderen…

    • Avatar-Foto
      Martin Weiß

      Hallo Thomas,

      vielen Dank für dein Feedback, freut mich zu hören, dass der Artikel weitergeholfen hat.

      Schöne Grüße,
      Martin