GRUPPIERENNACH und PIVOTMIT: sich selbst aktualisierende Pivot-Berichte direkt in Formeln und wann eine Pivot-Tabelle weiterhin das bessere Werkzeug ist

FAQ — Welche Fragen werden in diesem Artikel beantwortet?

Q: Was tun GRUPPIERENNACH und PIVOTMIT eigentlich?

A: Beide Funktionen verwandeln eine Tabelle mit einer einzigen Formel in einen zusammenfassenden Bericht. GRUPPIERENNACH liefert eine Zeile je Gruppe. Das Gegenstück zu einer eindimensionalen Pivot-Tabelle ist die Funktion PIVOTMIT. Sie liefert eine zweidimensionale Matrix mit einem Feld an der Seite und einem weiteren oben. Beide Funktionen aggregieren, summieren, sortieren und filtern innerhalb dieser einen Formel und aktualisieren sich automatisch, sobald sich die Quelle ändert.

Q: Worin unterscheidet sich das von der Funktion SUMMEWENNS?

A: Wenn du wissen möchtest, wie SUMMEWENNS funktioniert, ist die Antwort ganz einfach: Es beantwortet eine Zelle nach der anderen und braucht dafür eine Liste von Beschriftungen, gegen die summiert wird. Wenn du GRUPPIERENNACH verwendest, werden die vorhandenen Gruppen für dich ermittelt und der gesamte Bericht als Überlaufbereich zurückgegeben. Wenn du einen SUMMEWENNS-Bericht als Raster aus Formeln pflegst, erledigt das bei einem GRUPPIERENNACH-Bericht die Formel ganz von selbst.

Q: Wie unterscheidet sich PIVOTMIT von einer Pivot-Tabelle?

A: Eine Pivot-Tabelle ist ein interaktives Objekt, das Du durch das Ziehen von Feldern erstellst und manuell aktualisierst. PIVOTMIT ist übrigens eine Formel. Sie wird live neu berechnet, gibt ihr Ergebnis an andere Formeln weiter und ist als Text im Blatt enthalten, den Sie bei Bedarf versionieren können. Dafür verzichtet sie auf die Interaktivität, die eine Pivot-Tabelle bietet, wie zum Beispiel Datenschnitte, Drilldown, Erweitern und Reduzieren.

Q: Kann ich sortieren und filtern, ohne die Formeln SORTIEREN und FILTER zu verschachteln?

A: Ja. Beide Funktionen besitzen die Argumente „sortier_reihenfolge” (ein vorzeichenbehafteter Spaltenindex) und „filter_array” (eine Wahrheitswert-Spalte). Sortierung und Zeilenauswahl sind damit Teil der Berichtsdefinition und keine gesonderte Ebene darüber hinaus.

Q: Welche Excel-Versionen besitzen diese Funktionen?

A: Die Funktion GRUPPIERENNACH und PIVOTMIT stehen im Microsoft 365 Aktueller Kanal (Windows und Mac) sowie in Excel für das Web zur Verfügung, nach ihrer allgemeinen Freigabe Ende 2024. Sie sind in keiner Kauflizenz enthalten, weder in Excel 2016, 2019 oder 2021 noch in der Kaufversion Excel 2024. Dort liefert die Formel #NAME?.

Q: Meine Kollegen arbeiten mit einer Kauflizenz. Was verwende ich stattdessen?

A: Für eine eindimensionale Zusammenfassung bildet EINDEUTIG in Verbindung mit SUMMEWENNS GRUPPIERENNACH weitgehend nach. Für eine zweidimensionale Matrix bleibt eine Pivot-Tabelle die pragmatische Wahl. Teil 7 zeigt die genauen Muster.

Beim Zusammenfassen einer Tabelle in Excel gab es zwanzig Jahre lang nur zwei Möglichkeiten: Entweder baute man eine Pivot-Tabelle von Hand auf oder man setzte ein Raster aus SUMMEWENNS-Formeln gegen eine im Voraus vorbereitete Liste von Beschriftungen zusammen. Beides funktioniert. Allerdings haben beide Methoden eine Schwäche: Sie pflegen sich nicht selbst. Eine Pivot-Tabelle muss manuell aktualisiert werden und ein SUMMEWENNS-Bericht bricht zusammen, sobald eine neue Kategorie hinzukommt, für die keine Zeile angelegt wurde.

Die Funktionen GRUPPIERENNACH und PIVOTMIT beseitigen diese Schwäche. Sie gehören zur gleichen Generation dynamischer Matrixfunktionen wie FILTER, SORTIEREN und EINDEUTIG und erben deren prägendes Verhalten. Eine einzige Formel liefert einen ganzen Bericht, der sich über das Raster erstreckt und sich in dem Augenblick neu berechnet, in dem sich die darunterliegenden Daten ändern. Dieser Artikel ist weniger ein Rundgang durch zwei Funktionen. Er wirft einen Blick auf eine veränderte Art, Berichte zu erstellen. Ebenso wichtig ist es, die Grenzen dieser Veränderung aufzuzeigen. Es geht darum, ob eine Pivot-Tabelle das richtige Instrument ist.

Teil 1: Drei Wege, eine Tabelle zusammenzufassen

Es hilft, die drei Ansätze nebeneinander zu sehen, denn jeder löst das Hauptproblem des vorherigen.

Die dritte Zeile ist das Thema dieses Artikels. Es geht nicht darum, dass sie die beiden anderen überflüssig macht, denn das tut sie nicht, sondern darum, dass formelbasierte Aggregation nun ein vollwertiger Bestandteil des Arbeitsblatts ist. Ein Bericht kann ein Wert sein, den andere Formeln verbrauchen, statt ein Objekt zu sein, das in einer Ecke des Blatts geparkt ist. Genau diese Veränderung macht den modernen Ansatz lernenswert und zieht sich wie ein roter Faden bis hin zum Vergleich in Teil 6.

Teil 2: Die Daten, mit denen wir arbeiten

Alle Beispiele unten verwenden dieselbe Tabelle, definiert als Excel-Tabelle mit dem Namen Sales.

Merke dir am besten die Gesamtsumme der Spalte "Betrag" von 84.750, denn auf sie wird in den Zusammenfassungen dieses Artikels Bezug genommen.

💡 Tipp: Wenn du mit der Datenverarbeitung beginnst, kannst du die Daten mit Strg+T in eine Tabelle umwandeln. Wenn du dann so strukturierte Verweise wie "Sales[Betrag]" mit den Spalten verwendest, zieht der Bericht automatisch neue Zeilen ein, sobald du welche hinzufügst. Wenn du mehr über die Gründe erfahren möchtest, schau doch mal in Warum jeder Datensatz eine Excel-Tabelle sein sollte.

Teil 3: GRUPPIERENNACH Eine Dimension wird aggregiert.

Die Signatur hat drei erforderliche und fünf optionale Argumente:

=GRUPPIERENNACH(zeilen_felder; Werte; Funktion; [feld_überschriften]; [gesamt_tiefe]; [sortier_reihenfolge]; [filter_array]; [feld_beziehung])

Die einfachste Form

Die Spalte Betrag nach Region summieren:

=GRUPPIERENNACH(Sales[Region]; Sales[Betrag]; SUMME)

Es gibt drei bemerkenswerte Dinge. Zunächst einmal hat die Funktion die drei Regionen ganz von selbst gefunden. Du hast "Nord", "Süd" oder "Ost" nie selbst eingegeben. Zweitens hat sie ganz automatisch eine Gesamtsumme ergänzt, weil standardmäßig bei gesamt_tiefe der Wert 1 eingestellt ist. Das Ergebnis ist ein Überlaufbereich, was wirklich hilfreich ist: Wenn du morgen eine vierte Region hinzufügst, erscheint sofort eine vierte Zeile im Bericht, ohne dass du eine Formel bearbeiten musst.

Wählen, wie aggregiert wird

Das dritte Argument ist eine Funktion, die per Name und ohne Klammern übergeben wird. Excel akzeptiert die gewohnten Aggregationen.
SUMME, MITTELWERT, ANZAHL, ANZAHL2, MAX, MIN, PRODUKT, MEDIAN, STABW.S, VAR.S sowie zwei, die dieser Funktionsfamilie eigen sind:

• PROZENTVON drückt jede Gruppe als Prozentsatz des Ganzen aus.
• Eine eigene LAMBDA erlaubt jede Aggregation, die Excel nicht mitbringt.

Um den Umsatzanteil jeder Region zu zeigen:

=GRUPPIERENNACH(Sales[Region]; Sales[Betrag]; PROZENTVON)

Das ergibt Nord 49,6 %, Ost 26,2 % und Süd 24,2 %, was den gleichen Anteil ergibt, den eine Pivot-Tabelle in einer Formel als "% des Gesamtergebnisses" liefern würde.

Wenn du mehr als eine Kennzahl auf einmal ausgeben willst, übergebe einfach mehrere Wertespalten oder staple mehrere Funktionen mit HSTAPELN:

=GRUPPIERENNACH(Sales[Region]; Sales[[Betrag]:[Menge]]; SUMME)     ' Summe von Betrag und von Menge, nebeneinander
=GRUPPIERENNACH(Sales[Region]; Sales[Betrag]; HSTAPELN(SUMME; MITTELWERT))   ' Summe und Mittelwert von Betrag

Für das Argument aus Teil 1 ist der LAMBDA-Weg entscheidend. Da die Aggregation selbst eine Funktion ist, kann ein Bericht eine Logik anwenden, die das feste Wertfeld-Menü einer Pivot-Tabelle nicht ausdrücken kann. Beispielsweise ist es möglich, einen gewichteten Mittelwert, einen getrimmten Durchschnitt oder eine maßgeschneiderte Margenrechnung zu berechnen. Wenn du noch keine LAMBDA-Funktion geschrieben hast, siehe LET und LAMBDA: übersichtlichere, wiederverwendbare Formeln.

Summen und Zwischensummen

Das Argument gesamt_tiefe steuert die Summen. Wenn du nur eine Gruppierungsspalte hast, kannst du zwischen 0 (eine nackte Liste, die sich eignet, wenn der Bericht eine weitere Formel speist) und 1 (die vorgegebene Gesamtsumme) wählen. Wenn du nach mehr als einem Feld gruppierst, werden die Zwischensummen, gesamt_tiefe von 2, angezeigt.

=GRUPPIERENNACH(Sales[[Region]:[Kategorie]]; Sales[Betrag]; SUMME; ; 2)

Dies erfolgt nach Regionen gruppiert und innerhalb jeder Region nach Kategorien. Nach jeder Region wird eine Zwischensumme eingefügt, am Ende steht die Gesamtsumme. Das leere Argument vor der 2 belässt feld_überschriften beim Standard. Die Position der Semikola ist entscheidend, genau wie bei jeder Excel-Funktion.

Teil 4: Sortieren und Filtern in der Formel

Den Bericht sortieren

sortier_reihenfolge nimmt einen vorzeichenbehafteten Spaltenindex an. Die Spalten werden über das Ergebnis gezählt, zuerst die zeilen_felder, dann die Werte, in unserem Bericht mit einer Gruppe und einem Wert ist Spalte 1 also die Region und Spalte 2 die Summe. Um nach der Summe zu sortieren, zuerst die größten:

=GRUPPIERENNACH(Sales[Region]; Sales[Betrag]; SUMME; ; ; -2)

Das Ergebnis wird nach den Richtungen Nord, Ost und Süd umgeordnet. Eine positive 2 würde die kleinsten zuerst sortieren. Die beiden leeren Argumente überspringen feld_überschriften und gesamt_tiefe und belassen beide beim Standard.

💡 Tipp: Der häufigste Sortierfehler ist, ab Spalte A des Blatts statt ab der ersten Spalte des Berichts zu zählen. Zähle nur die Spalten, die die Funktion zurückgibt.

Vor dem Gruppieren filtern

filter_array ist eine Spalte mit Wahrheitswerten in gleicher Höhe wie die Quelle. Sie wird ausgewertet, bevor die Gruppierung geschieht. Um nur das zweite Quartal zusammenzufassen:

=GRUPPIERENNACH(Sales[Region]; Sales[Betrag]; SUMME; ; ; ; Sales[Quartal]="Q2")

Die Formel Sales[Quartal]="Q2" erzeugt eine Spalte mit den Werten WAHR und FALSCH, Zeilen, die zu FALSCH ausgewertet werden, werden nicht berücksichtigt und die verbleibenden Zeilen werden wie üblich gruppiert. Der Q2-Bericht lautet: Nord 20.700, Süd 9.750 und Ost 18.900. Da es sich bei der Prüfung um einen gewöhnlichen Excel-Ausdruck handelt, können Sie Bedingungen mit * für UND und + für ODER verknüpfen, genau wie bei der Funktion FILTER. Die vollständige Logik boolescher Kriterien-Matrizen behandelt: Dynamische Bereiche mit FILTER, SORTIEREN, EINDEUTIG und SEQUENZ.

Teil 5: PIVOTMIT. Dabei werden zwei Dimensionen auf einmal betrachtet.

Während die Funktion GRUPPIERENNACH entlang einer Achse zusammenfasst, kreuzt die Funktion PIVOTMIT zwei Achsen. Es ergänzt ein Argument spalten_felder sowie ein passendes Paar aus Summen- und Sortierargumenten für die Spaltenachse.

=PIVOTMIT(zeilen_felder; spalten_felder; Werte; Funktion; [feld_überschriften]; [zeilen_gesamt_tiefe]; [zeilen_sortier_reihenfolge]; [spalten_gesamt_tiefe]; [spalten_sortier_reihenfolge]; [filter_array]; [relativ_zu])

Eine Kreuztabelle in einer Formel

Umsatz nach Region und Kategorie:

=PIVOTMIT(Sales[Region]; Sales[Kategorie]; Sales[Betrag]; SUMME)

Die Funktion baut die Achsen auf, füllt den Körper mit Summen und ergänzt eine Summenzeile sowie eine Summenspalte. Wenn du die ersten beiden Argumente vertauschst, wird die gesamte Tabelle umgestellt und du landest bei einer Gesamtsumme von 84.750. Dabei werden Kategorie und Region einfach an die Seite verschoben, ohne dass man sie neu aufbauen muss.

Sortieren, Summen und Prozente

Die Spaltenachse verfügt über eigene Summen- und Sortierargumente, sodass die beiden Dimensionen unabhängig voneinander gesteuert werden können. Um die Zeilen absteigend nach ihren Summen zu sortieren, während die Spalten ihre natürliche Reihenfolge behalten, gehe wie folgt vor:

=PIVOTMIT(Sales[Region]; Sales[Kategorie]; Sales[Betrag]; SUMME; ; ; -1)

PROZENTVON wird hier deutlich ausdrucksstärker, denn relativ_zu entscheidet, wovon jede Zelle ein Prozentsatz ist:

=PIVOTMIT(Sales[Region]; Sales[Kategorie]; Sales[Betrag]; PROZENTVON; ; ; ; ; ; ; 1)

Wenn du relativ_zu auf 1 setzt, wird jede Zelle als Prozentsatz ihrer Zeilensumme angezeigt, d. h. der Kategorienmix innerhalb jeder Region wird dargestellt. Setze es auf 0, um einen Prozentsatz je Spalte zu erhalten, oder auf 2, um einen Prozentsatz der Gesamtsumme zu erhalten. Mit einem Argument kannst du außerdem die gesamte Tabelle zwischen drei analytischen Fragen umschalten. Für diese müsstest du in einer Pivot-Tabelle jeweils die Wertfeldeinstellungen erneut öffnen.

Teil 6: GRUPPIERENNACH/PIVOTMIT im Vergleich zu Pivot-Tabellen. Es geht darum, das richtige Werkzeug zu wählen.

Das ist die Entscheidung, auf die der Artikel hinausläuft. Die ehrliche Antwort lautet, dass keines der Werkzeuge in allem gewinnt, da sie sich für unterschiedliche Aufgaben eignen.

Der Formelansatz ist die bessere Wahl, wenn der Bericht sich selbst pflegen soll, wenn sein Ergebnis eine andere Formel speisen muss, eine Dashboard-Zahl, eine Diagrammquelle, eine weitere Berechnung oder wenn die Aggregation eine ist, die eine Pivot-Tabelle nicht ausdrücken kann, eine LAMBDA-Funktion aber schon. Weil die Formel Text in einer Zelle ist, fügt sie sich außerdem gut in eine Versionsverwaltung und in eine geprüfte, revisionssichere Arbeitsmappe ein, was besonders in einem regulierten oder finanznahen Umfeld von Vorteil ist.

Wenn du statt zu berichten lieber die Daten erkunden möchtest, ist die Pivot-Tabelle die bessere Wahl. Du kannst einen Datenschnitt hinzufügen, in eine Zahl hineinbohren, Gruppen erweitern und reduzieren oder das Layout per Drag-and-drop umgestalten. Sie ist auch die richtige Lösung, wenn die Datei mit einer Kauflizenz geöffnet werden muss. Außerdem kann sie über das Datenmodell Millionen von Zeilen skalieren, was Arbeitsblattformeln nicht können. Wenn der Datenbestand das Raster übersteigt, ist Power Pivot die naheliegende Lösung, anstatt eine längere Formel zu verwenden. Schau dir malPower Pivot und das Datenmodell: ein Anfängerleitfaden an.

Eine brauchbare Faustregel lautet: Wenn das Ergebnis ein Liefergegenstand ist, der von selbst aktuell bleiben muss, verwende die Formel. Wenn die Aufgabe eine interaktive Analyse ist, verwende die Pivot-Tabelle. In vielen realen Arbeitsmappen werden beide Methoden verwendet: ein PIVOTMIT, das ein Live-Dashboard speist, und daneben eine Pivot-Tabelle für spontane Fragen.

Teil 7: Verfügbarkeit und der Fallback für Kaufversionen

Die Funktionen GRUPPIERENNACH und PIVOTMIT sind seit Ende 2024 im Microsoft 365 Aktueller Kanal unter Windows und Mac sowie in Excel für das Web allgemein verfügbar. In keiner Kauflizenz sind sie enthalten, weder in Excel 2016, 2019 oder 2021 noch in der Kaufversion Excel 2024. Eine Arbeitsmappe, die diese Funktionen verwendet, zeigt in diesen Versionen die Fehlermeldung _xlfn.GRUPPIERENNACH und #NAME?. Diese Unterscheidung ist für deutsche KMU und Behörden, in denen Kauflizenzen weiterhin verbreitet sind, von Bedeutung. Das Gesamtbild findest du unter Excel 365 vs. ältere Excel-Versionen.

Für eine eindimensionale Zusammenfassung können die Funktionen EINDEUTIG und SUMMEWENNS zusammen die Funktion GRUPPIERENNACH weitgehend ersetzen. Unter Excel 2021, das dynamische Bereiche, aber nicht die Funktion GRUPPIERENNACH besitzt, gilt:

=EINDEUTIG(Sales[Region])                           ' in A2 — lässt die eindeutigen Regionen überlaufen
=SUMMEWENNS(Sales[Betrag]; Sales[Region]; A2#)      ' in B2 — lässt die Summen daneben überlaufen

Der Überlaufbezug A2# hält die beiden Spalten deckungsgleich, während Regionen hinzukommen oder wegfallen. In Excel 2016 oder 2019, in denen es keinen Überlauf gibt, listest du die Regionen mit dem Befehl Daten ▸ Duplikate entfernen auf und verwenden je Zeile die Funktion SUMMEWENN. Die Mechanik des bedingten Summierens wird unter SUMMEWENNS: bedingte Summe mit mehreren Kriterien behandelt.

Für eine zweidimensionale Matrix gibt es auf einer Kauflizenz kein knappes Formeläquivalent, weshalb eine Pivot-Tabelle die richtige Lösung ist. Dies ist einer der in Teil 6 beschriebenen Fälle, in denen die Pivot-Tabelle kein Notbehelf, sondern das richtige Werkzeug ist.

Teil 8: Häufige Fehlerbilder

Zusammenfassung

Die Funktionen GRUPPIERENNACH und PIVOTMIT verwandeln das Erstellen von Berichten von einer Objektbau- zu einer Formelaufgabe. Darin liegt ihre eigentliche Bedeutung.

• GRUPPIERENNACH liefert eine Zeile je Gruppe, während PIVOTMIT eine zweidimensionale Matrix liefert. Beide finden die Gruppen für dich und beide laufen über.
• Der Bericht berechnet sich automatisch neu und kann andere Formeln speisen, eine Eigenschaft, die eine Pivot-Tabelle nicht hat.
• Sortieren (sortier_reihenfolge), Filtern (filter_array) und Summen (gesamt_tiefe) sind Argumente, keine zusätzlichen Ebenen.
• PROZENTVON und eigene LAMBDA-Aggregationen gehen über das feste Wertfeld-Menü einer Pivot-Tabelle hinaus. relativ_zu deutet eine ganze Matrix als Zeilen-, Spalten- oder Gesamtsummen-Prozente um.
• Wähle die Formel, wenn das Ergebnis von selbst aktuell bleiben oder weitere Berechnungen speisen muss. Wähle eine Pivot-Tabelle für interaktive Erkundung, sehr große Daten oder wenn die Datei mit auf einer Kauflizenz geöffnet werden muss.
• Die Funktionen erfordern Microsoft 365 oder Excel für das Web. Mit einer Kauflizenz kannst du die Funktionen EINDEUTIG und SUMMEWENNS für eine Liste oder eine Pivot-Tabelle für eine Matrix nutzen.

Das Prinzip ist dasselbe, das sich durch diese Reihe zieht. Bevorzuge das Werkzeug, das den Bericht zugleich korrekt und selbstpflegend macht, und entscheide bewusst über den Zeitpunkt, ab dem Interaktivität oder Kompatibilität eine Pivot-Tabelle zur besseren Antwort macht.

Siehe auch: Dynamische Bereiche mit FILTER, SORTIEREN, EINDEUTIG und SEQUENZ für das Überlaufverhalten, auf das sich diese Funktionen stützen, SUMMEWENNS: bedingte Summe mit mehreren Kriterien für den Fallback auf Kauflizenzen, Warum jeder Datensatz eine Excel-Tabelle sein sollte für die strukturierten Verweise, die diese Berichte stabil halten, Power Pivot und das Datenmodell: ein Anfängerleitfaden für den Fall, dass ein Datenbestand die Arbeitsblattformeln übersteigt, und Excel 365 vs. ältere Excel-Versionen für das vollständige Verfügbarkeitsbild.

Dieser Artikel ist Teil der helpme.safeoffice.de-Reihe, die praxisnahe Anleitungen zu Excel-Funktionen, Arbeitsmappen-Modellierung und Datenlösungen bietet. Die Reihe richtet sich an Unternehmen, die wirksame, wartbare Werkzeuge möchten, die jeder verstehen kann.