In den letzten Jahren sind in Excel zahlreiche dynamische Arrayfunktionen wie z.B. FILTER, SORTIEREN und EINDEUTIG integriert worden. Der Katalog der Arrayfunktionen wird um die Funktion GRUPPIERENNACH erweitert. Mit dieser Funktion können Sie Daten in den Zeilen einer oder mehrerer Spalten gruppieren und nach Daten in einer anderen Spalte aggregieren. Diese Funktion können Sie nutzen, wenn Sie keine PivotTable erstellen wollen. In diesem Tipp lernen Sie einige Einsatzbereiche dieser neuen Funktion kennen.
Verfügbarkeit
Im September 2024 steht die Funktion GRUPPIERTNNACH Abonnenten von Excel für Microsoft 365 zur Verfügung, die den aktuellen Kanal eingestellt haben.
Aufbau der Funktion GRUPPIERENNACH
Die Funktion GRUPPIERENNACH kennt 8 Argumente. Die ersten 3 Argumente müssen Sie angeben:
- Zeilenfelder: Geben Sie als erstes Argument die Spalte einer dynamischen Tabelle oder den Zellbereich an, nach der Sie die Zeilen gruppieren möchten.
- Werte: Als zweites Argument geben Sie die Spalte einer dynamischen Tabelle oder den Zellbereich ein, dessen Werte aggregiert werden sollen.
- Funktion: Als drittes Argument wird eine Aggregationsfunktion erwartet wie z.B. SUMME, ANZAHL, MIN, MAX und MITTELWERT. Es gibt insgesamt 16 Aggregationsfunktionen und für flexible Berechnungen noch die LAMBDA-Funktion.
Ausgangsbeispiel
Es sollen die Bestelldaten des ersten Halbjahres 2024 analysiert werden. Die Tabelle hat folgenden Aufbau:
Der Name der dynamischen Tabelle lautet Bestellungen. Die dynamische Tabelle hat den Vorteil, dass die Spaltennamen in die Formel übernommen werden können.
Umsatz pro Kunde
Im ersten Beispiel soll der Umsatz pro Kunde ermittelt werden.
Die Formel in Zelle G2 lautet folgendermaßen.
=GRUPPIERENNACH(Bestellungen[Kunde];Bestellungen[Umsatz];SUMME)
Nachdem die Spaltenüberschriften Kunde und Umsatz eingegeben und die Umsatzwerte formatiert wurden, sieht das Ergebnis so aus:
Standardmäßig erscheint die Zeile Gesamt mit der Summe aller Umsätze am Ende des Überlaufbereichs. Die Kunden sind automatisch nach dem Namen aufsteigend sortiert worden
Anzahl Bestellungen pro Kunde
Wenn Sie die Anzahl der Bestellungen pro Kunden interessiert, ändern Sie die Formel in G2 wie folgt:
=GRUPPIERENNACH(Bestellungen[Kunde];Bestellungen[Umsatz];ANZAHL)
Das Ergebnis sieht so aus:
Anteil der Kunden am Gesamtumsatz
Der Anteil der einzelnen Kunden am Gesamtumsatz ermittelt Sie folgendermaßen:
=GRUPPIERENNACH(Bestellungen[Kunde];Bestellungen[Umsatz];PROZENTVON)
Die Funktion PROZENTVON ist ebenfalls neu.
Die Ergebnistabelle sieht nach der Formatierung der Werte so aus:
Umsatz pro Kunde, absteigend sortiert nach Umsatz
Standardmäßig werden bei der Funktion GRUPPIERENNACH die Daten in der Ergebnistabelle nach den Werten der Gruppierungsspalte aufsteigend sortiert. Wenn Sie die Daten absteigend nach der Höhe des Kundenumsatzes sortieren möchten, müssen Sie in der Formel für das sechste Argument ein Sortierkennzeichen eingeben. Die Formel sieht danach so aus:
=GRUPPIERENNACH(Bestellungen[Kunde];Bestellungen[Umsatz];SUMME;;;;-2)
Als sechstes Argument wurde der Ausdruck -2 angegeben, d.h. die zweite Spalte in der Ergebnistabelle wird absteigend sortiert. Mit dem Wert 2 sortieren Sie die Umsatzwerte aufsteigend.
Umsatz pro Kunde und Produkt
Mit der Funktion GRUPPIERENNACH können Sie die Daten nicht nur nach einer Spalte, sondern auch nach mehreren Spalten zusammenfassen. In diesem Beispiel sollen die Daten zunächst nach dem Kunden und dann nach dem Produkt gruppiert werden.
Die entsprechende Formel lautet folgendermaßen:
=GRUPPIERENNACH(Bestellungen[[Kunde]:[Produkt]];Bestellungen[Umsatz];SUMME)
Der Doppelpunkt steht für den Zellbereich von der Spalte Kunde bis zur Spalte Produkt. Im Text ist der Doppelpunkt fett formatiert, damit er auffällt.
Die Ergebnistabelle sieht so aus:
In der Ergebnistabelle erscheinen die Gesamtumsätze pro Kunden nicht. Wenn Sie die Gesamtumsätze pro Kunden zusätzlich anzeigen möchten, müssen Sie die Formel wie folgt ändern:
=GRUPPIERENNACH(Bestellungen[[Kunde]:[Produkt]];Bestellungen[Umsatz];SUMME;;2)
In der Formel wurde als fünftes Argument der Wert 2 eingetragen. Die Ziffer 2 steht für die Anzeige von Teilergebnisse der ersten Ebene und der Gesamtsumme. Die erste Ebene bilden in unserem Beispiel die Kunden.
In der Ergebnistabelle stehen die Teilergebnisse unterhalb der Einzelwerte. Sie können die Tabelle so umstellen, dass die Teilergebnisse zuerst angezeigt werden: Die Formel dafür sieht so aus:
=GRUPPIERENNACH(SPALTENWAHL(Bestellungen;3;2);Bestellungen[Umsatz];SUMME;;-2)
Als fünftes Argument wurde der Ausdruck -2 angegeben:
Ergebnis:
Umsatz pro Produkt und Kunde
Wenn Sie die Umsätze zunächst nach Produkten und dann Kunden anzeigen möchten, brauchen Sie die Ausgangstabelle nicht erst umstellen. Mit der folgenden Formel können Sie dies einfacher erreichen:
=GRUPPIERENNACH(SPALTENWAHL(Bestellungen;3;2);Bestellungen[Umsatz];SUMME;;2)
Durch die Funktion SPALTENWAHL legen Sie die Reihenfolge für die Gruppierungen fest. In diesem Beispiel wird zunächst nach der 3. Spalte in der Tabelle, in der die Produkte stehen, und dann nach der zweiten Spalte, in die Kundenamen stehen, gruppiert. Die Ergebnistabelle sieht so aus:
Umsatz nach Monaten
Um den Umsatz nach Monaten zu ermitteln, müssen Sie die Spalte Bestelldatum nach dem Monat gruppieren. Mit folgender erreichen ermitteln Sie die Monatsumsätze:
=GRUPPIERENNACH(MONAT(Bestellungen[Bestelldatum]);Bestellungen[Umsatz];SUMME)
Die Ergebnistabelle sieht so aus:
Umsatz pro Kunde mit dem Produkt PC
Die Funktion GRUPPIERENNACH erlaubt die vorherige Filterung der Daten. In unserem Beispiel soll der Umsatz pro Kunden mit dem Produkt PC berechnet werden. Die Umsätze sollen absteigend sortiert werden. Die Formel lautet so:
=GRUPPIERENNACH(Bestellungen[Kunde];Bestellungen[Umsatz];SUMME;;;-2;Bestellungen[Produkt]=”PC”)
Die Filterfunktion wird als siebtes Argument angegeben. Sie lautet in unserm Fall:
Bestellungen[Produkt]=”PC”)
Die Umsätze der einzelnen Kunden mit dem Produkt PC sehen so aus:
Anzahl der Kunden pro Produkt
In diesem Beispiel wird in der Formel nicht wie bisher eine Aggregatfunktion eingesetzt, sondern die Funktion LAMBDA. Die Formel hat folgenden Aufbau:
=GRUPPIERENNACH(Bestellungen[Produkt];Bestellungen[Kunde];LAMBDA(x;ANZAHL2(EINDEUTIG(x))))
Das Ergebnis im Tabellenblatt sieht so aus:
Mit der LAMBDA-Funktion wird zunächst die Variable x definiert. Diese Variable bezieht sich immer auf die Wertespalte, die als zweites Argument in der GRUPPIERENNACH-Funktion eingegeben wird. In unserem Beispiel ist es die Spalte Kunde in der Tabelle Bestellungen. Mit der Funktion EINDEUTIG werden pro Produkt die Kunden identifiziert, die das Produkt bestellt haben. Die Kunden werden dann pro Produkt mit der Funktion ANZAHL2 gezählt.
Die Funktion ANZAHL2 ist notwendig, weil Sie Textwerte zählen soll, und zwar die Kundennamen.
In der Zeile Gesamt steht die Zahl der Kunden, die mindestens ein Produkt bestellt haben.
Fazit
In diesem Tipp haben Sie einige Beispiele für die Anwendung der neuen Funktion GRUPPIERENNACH kennengelernt. Mit dieser Funktion werten Sie schnell und einfach Daten nach einem oder mehreren Gruppierungsmerkmalen aus.
0 Kommentare