In meinem letzten Blogartikel hatte ich eine neue Tabellenfunktion namens GRUPPIERENNACH vorgestellt, die seit kurzer Zeit in Microsoft 365 verfügbar ist. Damit lassen sich Datentabellen mit einer einzigen Formel fast so wie mit einer Pivot-Tabelle zusammenfassen und auswerten.
Diese Funktion ist schon wirklich ziemlich genial, sie hat nur eine wesentliche Einschränkung:
Anders als in Pivot-Tabellen sind damit keine Gruppierungen in Spalten möglich, sondern nur in Zeilen. Eine klassische Kreuztabelle mit z.B. Produkten in den Zeilen und Jahren in den Spalten kann man mit dieser Funktion leider nicht umsetzen.
Doch genau dafür gibt es eine weitere neue Funktion, die ich dir heute vorstellen möchte.
Und darum geht’s:
Beispieldatei herunterladen
Falls du den Artikel zu GRUPPIERENNACH nicht gelesen hast, solltest du das unbedingt hier nachholen. Denn die neue Funktion, die ich dir gleich zeige, hat im Grunde den gleichen Aufbau. Wenn du also GRUPPIERENNACH verstanden hast, dann ist die heutige Funktion nur noch halb so wild.
Darf ich vorstellen: PIVOTMIT
Diese Funktion ist sozusagen der große Bruder von GRUPPIERENNACH. Denn damit lassen sich auch besagte Kreuztabellen erstellen, mit denen sich – ganz wie in echten Pivot-Tabellen – auch Spaltenfelder darstellen lassen.
Das ist auch der Grund, warum es im Vergleich zu GRUPPIERENNACH noch einmal mehr Parameter gibt, nämlich bis zu maximal 11! Ein echtes Schwergewicht also. Aber lass dich auch hier nicht einschüchtern, nur die ersten 4 Parameter sind Pflicht, der Rest ist optional. Und vieles davon kennst du ja jetzt ohnehin schon von GRUPPIERENNACH (wenn du den Artikel gelesen hast).
=PIVOTMIT(row_fields; col_fields; values; function; [field_headers]; [row_total_depth]; [row_sort_order]; [col_total_depth]; [col_sort_order]; [filter_array]; [relative_to])
Die folgende Darstellung vergleicht die beiden Funktionen GRUPPIERENNACH und PIVOTMIT. Und wie man sieht, ist der Unterschied nicht sehr groß:
Die Syntax von GRUPPIERENNACH und PIVOTMIT
Es kommen im Grunde nur die Parameter hinzu, welche die Spaltendarstellung steuern, die es ja in GRUPPIERENNACH nicht gibt. Der Rest ist fast identisch.
Schauen wir uns mal ein paar Beispiele dazu an.
Beispiel 1: Umsätze nach Produkt und Region
Der erste Anwendungsfall erzeugt eine klassische Kreuztabelle, so wie üblicherweise „echte“ Pivot-Tabellen aufgebaut sind. Im Zeilenbereich wird noch Produkten und im Spaltenbereich nach Regionen ausgewertet.
Als Datenbasis dient eine formatierte („intelligente“) Tabelle mit den Namen tblVerkauf. Selbstverständlich können auch einfache Listen verwendet werden, so wie ich es im Artikel zu GRUPPIERENNACH gezeigt habe. Der Vorteil der intelligenten Tabellen liegt dabei auf der Hand: die Datenbereiche sind dynamisch und brauchen nicht ständig angepasst zu werden, wenn die Tabelle erweitert wird.
=PIVOTMIT(tblVerkauf[Produkt];tblVerkauf[Region];tblVerkauf[Umsatz];SUMME)
Beispiel 1: Umsätze nach Produkt und Region
Beispiel 2: Umsätze nach Region, Verkäufer und Monat
In diesem Beispiel kommt ein zweites Datenfeld in den Zeilenbereich. Zu jeder Region werden auch die Verkäufer angezeigt und Zwischensummen nach Region gebildet.
Außerdem gibt es eine Besonderheit: Die Verkaufstabelle enthält konkrete Datumsangaben, ich möchte aber in den Spalten auf Monatsebene verdichten. Dazu wird das Datum mit Hilfe der TEXT-Funktion einfach in das gewünschte Format gebracht:
=PIVOTMIT(tblVerkauf[[Region]:[Verkäufer]];TEXT(tblVerkauf[Datum];"MMM-JJ");tblVerkauf[Umsatz];SUMME;;2)
Beispiel 2: Umsatz nach Region, Verkäufer und Monat
Beispiel 3: Umsätze und prozentuale Anteile
Die Daten lassen sich selbstverständlich auch auf Jahresebene zusammenfassen. Zusätzlich zu den absoluten Umsätzen sollen jetzt aber auch die prozentualen Anteile ausgewiesen werden. Für den vierten Parameter („function“) werden daher zwei Funktionen benötigt: SUMME und PROZENTVON. Diese kann man jedoch nicht direkt angeben, sondern sie müssen mit Hilfe der HSTAPELN-Funktion gebündelt werden.
Und über den letzten Parameter wird schließlich gesteuert („relative_to“), worauf sich der prozentuale Anteil beziehen soll. In unserem Fall wird der Wert auf 4, um als Basis die übergeordnete Zeilensumme zu verwenden. Für den einzelne Verkäufer wäre das die jeweilige Region, für die einzelnen Regionen das Gesamtergebnis.
=PIVOTMIT(tblVerkauf[[Region]:[Verkäufer]];JAHR(tblVerkauf[Datum]);tblVerkauf[Umsatz];HSTAPELN(SUMME;PROZENTVON);0;2;;0;;;4)
Beispiel 3: Zwei Funktionen im Einsatz. SUMME und PROZENTVON
Beispiel 4: Einsatz von Datenschnitten
Ein gewichtiges Argument, das für echte Pivot-Tabellen spricht, sind Datenschnitte. Mit ihrer Hilfe lässt sich eine Pivot-Tabelle sehr komfortabel und nachvollziehbar filtern. Sowohl die GRUPPIERENNACH- als auch die PIVOTMIT-Funktion verfügen über ein Argument, um Filterspalten zu nutzen. Aber funktioniert das auch mit Datenschnitten?
Grundsätzlich erwartet das Argument 10 („filter_array“) als Ergebnis eine Liste mit WAHR- oder FALSCH-Werten (beziehungsweise 0 oder 1). Um beispielsweise nach einer bestimmten Region zu filtern, würde das folgendermaßen aussehen:
=PIVOTMIT(tblVerkauf[Produkt];tblVerkauf[Verkäufer];tblVerkauf[Umsatz];SUMME;;;;;;tblVerkauf[Region]="Nord")
Filtern nach einer einzelnen Region
Nun zum Datenschnitt. Wie du vielleicht weißt, lässt sich auch eine intelligente Tabelle per Datenschnitt filtern. Einfach in die Tabelle klicken und über das Menü „Einfügen | Datenschnitt“ den oder die gewünschten Datenschnitte erzeugen. Für mein Beispiel habe ich einen Datenschnitt für die Region erstellt und oberhalb der Tabelle platziert. Über das Datenschnitt-Menü habe ich die Spaltenzahl auf 4 eingestellt, so dass alle Region schön nebeneinander dargestellt werden.
Nun muss noch das Filterargument in der PIVOTMIT-Formel angepasst werden. Wenn man nun auf die Idee kommt, einfach nur die Region-Spalte anzusprechen, wird leider mit einem #WERT!-Fehler konfrontiert:
Die Spalte allein führt zu einem Fehler
Das liegt einfach daran, dass dadurch eben nicht die erwarteten WAHR/FALSCH bzw. 0/1-Werte geliefert werden. Fällt damit der Datenschnitt aus?
Nein!
Wir bedienen uns einfach eines kleinen Tricks: In einer neuen Filter-Spalte in der Datentabelle erstelle ich die benötigten 1 und 0-Werte. Dazu bediene ich mich der mächtigen, aber kaum beachteten AGGREGAT-Funktion.
Mit dieser speziellen Funktion lassen sich beispielsweise ausgeblendete Zeilen bei irgendwelchen Berechnungen ignorieren. Genau das, was wir brauchen: Die folgende Formel liefert den Wert 1 für sichtbare Zeilen und 0 für ausgeblendete Zeilen:
=AGGREGAT(3;5;[@Region])
Die AGGREGAT-Funktion erzeugt 0 und 1-Werte
Jetzt müssen wir in der PIVOTMIT-Funktion im Filter-Parameter nur noch auf diese Spalte zugreifen und schon können wir mit dem Datenschnitt indirekt unsere Ausgabe steuern:
Die AGGREGAT-Funktion erzeugt 0 und 1-Werte
Filtern der Ausgabe mit einem Datenschnitt
Fazit
Die im letzten Blogbeitrag vorgestellte GRUPPIERENNACH-Funktion war schon ziemlich genial. Wie man an den Beispielen in diesem Artikel gesehen hat, setzt PIVOTMIT nochmal eins drauf. Wer also maximale Dynamik in seinen Auswertungen braucht und nicht erst eine Pivot-Tabelle manuell aktualisieren möchte, bekommt mit dieser neuen Funktion ein mächtiges Werkzeug an der Hand.
Natürlich werden echte Pivot-Tabellen nicht überflüssig (Stichwort Performance). Und wer kein Microsoft 365 im Einsatz hat, für den stellt sich ohnehin nicht die Frage. Ansonsten kann ich nur empfehlen, mit dieser tollen neuen Funktion mal ein wenig herumzuspielen. Sollte sie bei dir trotz Microsoft 365 noch nicht verfügbar sein, musst du einfach noch ein wenig Geduld haben – sie kommt bestimmt!
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.
Hallo Martin,
erneut ein sehr gelungener Artikel, vielen Dank.
Eine Frage zu Deinem Beispiel 3, in dessen Auswertung Zwischensummen (je Region) und auch die Gesamtsumme erscheinen: können diese Zeilen abweichend formatiert werden (z.B. fett) und zwar dauerhaft, falls sich in der Anzahl der Gesamtzeilen etwas ändert, weil z.B. ein Verkäufer in einer Region hinzukommt?
Viele Grüße
Hans
Hallo Hans,
danke für das tolle Feedback.
Ja, so etwas lässt sich über bedingte Formatierung realisieren. Man muss nur ein geeignetes Kriterium finden, zum Beispiel die Bezeichnung „Gesamtergebnis“ in der ersten Spalte oder ein leeres Feld in der zweiten Spalte.
Der einzige Haken ist, dass man den Datenbereich sozusagen „auf Vorrat“ groß genug aufziehen muss, da die Tabelle ja dynamisch an wachsen kann.
Schöne Grüße,
Martin