Kürzlich hatte ich in einem Artikel einen kleinen Überblick gegeben, welchen interessanten Neuigkeiten es in Excel aus Microsoft 365 im Laufe dieses Jahres so gab.
Eine dieser Neuigkeiten möchte ich im heutigen Beitrag etwas näher beleuchten. Eine Tabellenfunktion, die in meinen Augen ein absoluter Hammer ist, denn sie wird zukünftig vielleicht sogar die eine oder andere Pivot-Tabelle überflüssig machen.
Wer also Microsoft 365 im Einsatz hat, sollte unbedingt weiterlesen. Alle anderen Anwender kommen leider nicht in den Genuss dieser fantastischen Funktion, tut mir sehr leid. Aber lesen darfst du diesen Artikel natürlich trotzdem 😉
Und los geht’s!
Beispieldatei herunterladen
Datentabellen verdichten
Wer bisher Daten einer Liste oder Tabelle gruppiert auswerten wollte, tat diese vermutlich mit einer Pivot-Tabelle. Im folgenden Bild habe ich links eine kleine Umsatzliste vorbereitet, die ich rechts daneben mit Hilfe einer Pivot-Tabelle nach Vertriebsregionen zusammengefasst habe:
Mein Pivot-Einsteigerkurs wird dir auch die letzte Angst davor nehmen und dir dabei helfen, deine Auswertungen zukünftig einfacher, schneller und flexibler zu machen. Und das auch in älteren Excel-Versionen!
Seit Kurzem gibt es jedoch eine neue Tabellenfunktion, die genau das ebenso hinbekommt. Und im Gegensatz zu Pivot-Tabellen nicht erst aktualisiert werden muss, wenn sich die Quelldaten verändert haben.
Bühne frei für GRUPPIERENNACH
Im aktuellen Kanal von Excel aus Microsoft 365 gibt es seit September 2024 unter anderem die neue Funktion GRUPPIERENNACH
Falls diese Funktion bei dir noch nicht verfügbar ist, kann das daran liegen, dass deine Excel-Version zum halbjährlichen oder jährlichen Enterprise-Kanal gehört und Aktualisierungen daher erst zeitverzögert bei dir ankommen. Dann heißt es, einfach noch ein wenig Geduld haben. Mehr zu den verschiedenen Updatekanälen findest du hier.
Dann schauen wir uns mal an, wie die Gruppierung nach Regionen ohne eine Pivot-Tabelle mit GRUPPIERENNACH funktioniert:
Eine einzige Formel in Zelle H8 und schon hat man das Ergebnis! Wie das geübte Auge an der blauen Umrahmung erkannt hat, handelt es sich dabei um eine dynamische Arrayfunktion. Das heißt, sie läuft automatisch in so viele Zellen über, wie zur Darstellung der Ergebnisse eben gebraucht werden.
Bevor gleich noch tiefer in die Möglichkeiten dieser Funktion einsteigen, schauen wir uns erst die grundsätzliche Syntax an. Denn an die Funktion können bis zu 8(!) Parameter übergeben werden. Dabei sind nur die ersten 3 obligatorisch, die restlichen 5 Parameter sind optional.
Bis zu 8 Parameter für alle Einsatzbereiche
Wie sich an der Anzahl der Parameter schon erahnen lässt, kann die Funktion durchaus etwas komplexer werden. Etwas ärgerlich dabei ist, dass Microsoft offensichtlich wenig Energie in die Übersetzung der Parameter steckt, die Namen der Parameter in der Intellisense-Hilfe offenbaren nicht auf Anhieb, worum es eigentlich geht.
=GRUPPIERENNACH(row_fields; values; function; [field_headers]; [total_depth]; [sort_order]; [filter_array]; [field_relationship])
Bitte lass dich nicht von der schieren Anzahl der Parameter verschrecken! Es lohnt sich wirklich, sich ein wenig damit zu beschäftigen. Daher hier ein kleiner Überblick:
Eines der Highlights ist der 3. Parameter „function“. Hier kann man auf 17 verschiedene Funktionen zurückgreifen, um seine Daten zusammenzufassen: Von der einfachen Summe über Minimum (MIN), Maximum (MAX) oder auch Prozentanteile („PROZENTVON“) sind praktisch alle wichtigen Funktionen vorhanden. Und wem die Klassiker nicht ausreichen, der kann sich sogar mit der neuen LAMBDA-Funktion noch mehr austoben.
Schauen wir uns doch gleich mal ein paar weitere praktische Beispiele an.
Weitere praktische Anwendungsbeispiele
Beispiel 2:
fasst die Daten auch nach Regionen zusammen, sortiert die Ergebnisse aber absteigend nach der Umsatzhöhe:
=GRUPPIERENNACH(B8:B32;F8:F32; SUMME;;;-2)
Optionale Parameter, die man nicht benötigt, können mit einem Semikolon einfach übersprungen werden, das kennt man ja schon aus anderen Excel-Funktionen. Erwähnenswert an diesem Beispiel ist der 6. Parameter für die Sortierung. Hier gibt man einfach die Spaltennummer an, nach der sortiert werden soll. Und für eine absteigende Sortierung sorgt einfach ein Minuszeichen vor der Spaltennummer. Genial!
Beispiel 3:
Hier sollen neben den Regionen auch noch die Verkäufer in der Auswertung erscheinen. Da die beiden Spalten in der Datentabelle unmittelbar nebeneinander liegen, werden im ersten Parameter einfache beide Quellspalten angegeben (B8:C32)
=GRUPPIERENNACH(B8:C32;F8:F32;SUMME)
Beispiel 4:
Mit Hilfe des 5. Parameters („total_depth“) können neben dem Gesamtergebnis auch noch Zwischensummen nach jeder Region eingefügt werden:
=GRUPPIERENNACH(B8:C32;F8:F32;SUMME;;2)
Beispiel 5:
Was ist aber, wenn man mehrere Spalten ausgeben möchte, die in der Datentabelle NICHT unmittelbar nebeneinander liegen? In diesem Fall werden die einzelnen Spalten einfach mit der HSTAPELN-Funktion zusammengefasst:
=GRUPPIERENNACH(HSTAPELN(B8:B32;D8:D32);F8:F32;SUMME;;2)
Auf den ersten Blick vielleicht nicht ganz intuitiv, aber sehr wirksam!
Beispiel 6:
Dass man diesen Trick mit HSTAPELN auch für die Wertespalten nutzen kann, zeigt dieses Beispiel. Hier werden neben den Umsätzen auch noch die Mengen ausgegeben:
=GRUPPIERENNACH(D8:D32;HSTAPELN(E8:E32;F8:F32);SUMME)
Die Liste der Beispiel ließe sich noch endlos fortführen. Aber ich denke, du hast jetzt schon eine Ahnung davon bekommen, was in der GRUPPIERENNACH-Funktion steckt.
Pivot-Tabelle oder GRUPPIERENNACH: Was ist besser?
Wie immer kommt es auf den konkreten Anwendungsfall an. Und falls du kein Microsoft 365 nutzt, hat sich die Frage ohnehin erübrigt.
Hier ist eine kleine Gegenüberstellung der beiden Techniken.
Pivot-Tabelle | GRUPPIERENNACH | |
---|---|---|
Verfügbarkeit | in allen Excel-Versionen verfügbar | nur in Microsoft 365 |
Aktualisierung | Muss manuell aktualisiert werden | automatisch |
Performance | auch für sehr große Datenmengen geeignet | aufgrund der automatischen Aktualisierung eher für kleine bis mittlere Datenmengen geeignet |
Anzeige von Gesamtergebnissen | können nur um unteren Ende der Pivot-Tabelle angezeigt werden | können auch oben angezeigt werden |
Formatierung | viele vordefinierte Formatvorlagen | manuell oder mit bedingter Formatierung |
Anzahl an Aggregatsfunktionen | 11 | 17 (inklusive LAMBDA) |
Ausgabe von Text im Wertebereich | nicht möglich | auch Texte sind im Wertebereich möglich |
Fazit und Ausblick
Du wirst mir wahrscheinlich zustimmen, dass die GRUPPIERENNACH-Funktion zu den interessantesten Funktionen gehören dürfte, die in den letzten Jahren in Excel eingeführt wurden. Es war noch nie so einfach, ohne eine Pivot-Tabelle Daten auf diese Art und Weise auszuwerten. Die integrierten Sortier- und Filterfunktionen ermöglichen passgenaue Ausgaben, ohne zwingend auf weitere Excel-Funktionen zurückgreifen zu müssen.
Wie man in der kleinen Vergleichstabelle oben sieht, haben Pivot-Tabellen aber weiterhin ihre Berechtigung und können ihre Stärken vor allem im Umgang mit großen Datenmengen ausspielen. Wer in der Vergangenheit schon mit Pivot-Tabellen gearbeitet hat oder zumindest gesehen hat, wie einfach man durch das bloße Ziehen, Einfügen oder Ersetzen von Feldern seine Auswertungen aus unterschiedlichen Blickrichtungen betrachten kann, wird gerne weiterhin darauf zurückgreifen.
Aber es wird in Zukunft sicherlich genügend Anwendungsfälle geben, wo die Dynamik von GRUPPIERENNACH vielleicht einfach die bessere Wahl sein wird. Die oben gezeigten Beispiele geben ja schon einen guten Einblick in die Möglichkeiten, weitere Beispiele werden hier auf dem Blog sicherlich noch folgen.
Im nächsten Artikel werde ich dir noch eine neue Funktion vorstellen, die sogar noch einen Schritt weiter geht. Es bleibt also spannend!
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.
Wieder ein sehr schöner Artikel. Leider wird bei uns in der Firma das 365 immer nur sehr verzögert freigegeben, so dass ich mich noch gedulden muss, die GRUPPEIERNACH-Funktion auszuprobieren.
Hallo Jochen,
ja, das ist gut möglich. Die Funktion kommt aber auf jeden Fall und die Vorfreude ist ja bekanntermaßen die schönste Freude 🙂
Schöne Grüße,
Martin
Wow, das ist tatsächlich eine sehr interessante Funktion.
Ja, Monika, die Funktion ist ein echtes Highlight!
Schöne Grüße,
Martin
Danke für diesen interessanten Beitrag. Habe ich gleich ausprobiert.
Hi Dagmar,
freut mich zu hören, dass du die Funktion auch schon zur Verfügung hast.
Schöne Grüße,
Martin
Danke, Martin!
Und wow – ja bei mir ist GruppierenNach enthalten. So was von einfach… hat wunderbar funktioniert.
Pivot werde ich wohl noch eine Weile verwenden, ich habe noch leider viele Excel-User in meinem Umkreis, die noch mit der Version weniger MS365 arbeiten. Werde ich wohl am Feedback merken 🙂
Anhand Deiner tollen Beispieldateien lerne ich das schneller wie an den Schritt-für-Schritt-Erklärungen, aber jeder halt, wie er Zugang findet. Nochmals herzlichen Dank und e gueti Zyt!
Hallo Barbara,
freut mich, wenn das bei dir auch gleich funktioniert hat. Ja, die Abwärtskompatibilität ist bei solchen neuen Funktionen immer ein Problem. Auch deswegen werden richtige Pivot-Tabellen weiterhin eine gute Option bleiben.
Schöne Grüße,
Martin
Hallo Martin,
spannende Funktion, danke für die Erklärung, vor allem in Kombination mit HSTAPELN.
Noch ein Unterschied zu Pivot Tabellen ist mir eingefallen: Datenschnitte
Außer man wandelt die Ausgangstabelle in eine intelligente Tabelle um.
Herzliche Grüße
Hallo Christof,
guter Hinweis zu den Datenschnitten, da gehe ich im nächsten Blogartikel auch darauf ein.
Schöne Grüße,
Martin
Mega. Vielen Dank! Dein Beitrag ist wieder sehr wertvoll.
Hallo Martin,
vielen Dank, freut mich zu hören!
Schöne Grüße,
Martin
Bei intelligenten Tabellen klappt es leider nicht mit der Übernahme der Überschrift. Oder?
Hallo Jürgen,
doch, es klappt auch bei intelligenten Tabellen. Der Parameter „field_headers“ verhält sich in Tabellen genauso wie in normalen Listen. Es kommt in beiden Fällen aber darauf an, ob in der Markierung die Kopfzeilen eingeschlossen wurden oder nicht.
Schöne Grüße,
Martin