Nachdem ich diese neue Excel-Funktion entdeckt hatte, hörte ich auf, Pivot-Tabellen für Zusammenfassungen zu erstellen.

Jahrelang war meine Vorgehensweise bei schnellen Auswertungen immer dieselbe: Ich fügte eine Pivot-Tabelle ein, zog die entsprechenden Felder in die richtigen Bereiche und aktualisierte die Tabelle, sobald sich die Zahlen änderten. Das funktionierte zwar, erforderte aber mehr Klicks, als die Aufgabe eigentlich verdiente. PIVOTBY übernimmt dieselbe Gruppierung sowie Berechnung in einer einzigen Formel - direkt in der Tabelle - und muss dabei niemals aktualisiert werden. Es handelt sich dabei um eine der neueren dynamischen Array-Funktionen von Microsoft 365. Falls Ihre Tabellenkalkulation also noch auf älteren Tools basiert, ist dies ein guter Grund, diese Funktion zu verwenden - anstatt viele Klicks zu machen.

PIVOTBY liefert mir eine Zusammenfassungstabelle - ohne jegliche Einrichtung.

Es führt die Gruppierung und Aggregation in einer Formel durch.

Eine Pivot-Tabelle verlangt eine Reihe von Schritten, bevor sie etwas anzeigt. Man fügt sie ein, öffnet die Feldliste, zieht ein Feld in die Zeilen, ein weiteres in die Spalten und ein drittes in den Wertebereich - anschließend wird das Ergebnis angezeigt. PIVOTBY überspringt all das jedoch. Ich tippe eine Formel ein, drücke Enter - und schon wird eine gruppierte Zusammenfassung in die danebenliegenden Zellen eingefügt.

Angenommen, ich möchte die Gesamtverkäufe nach Region und Produktkategorie aus meiner Verkaufsliste erhalten. Die Formel lautet:

=PIVOTBY(B2:B33, C2:C33, F2:F33, SUM)

Die Regionen werden in den Zeilen angezeigt, die Produktkategorien in der Kopfzeile; die Spalte „Umsatzbetrag“ enthält in jeder Zelle die Gesamtsumme. Das Ergebnis entspricht dem, was eine Pivot-Tabelle liefern würde. Im Norden beträgt der Umsatz 30.100 US-Dollar, im Süden 28.090 US-Dollar; dabei ist die Kategorie „Sport“ im Süden mit 9.240 US-Dollar die stärkste Kategorie. Die Gesamtsummen werden separat an den Rändern angezeigt; das gesamte Ergebnis wird dabei als ein einziger Bereich neu berechnet.

Die Formel ist der Bericht - das bedeutet, ich kann genau nachlesen, wie er erstellt wurde, indem ich einfach auf die Zelle oben links klicke.

Zum Schreiben meines ersten PIVOTBY reichte eine Zeile - kein Dialogfeld war nötig.

Die Argumente entsprechen den Fragen, die ich bereits stelle.

Sobald man das Muster erkennt, liest sich die Syntax wie die Frage, die man stellen will. Hier ist der Grundaufbau:

=PIVOTBY(row_fields, col_fields, values, function, [field_headers], [row_total_depth], [row_sort_order], ...)

Die ersten drei Argumente sind die, die Sie jedes Mal verwenden werden.

• row_fields: Das sind die Spalten, die an der Seite gruppiert sind.
• col_fields: Das ist die Spalte ganz oben.
• Werte: Das sind die Daten, die zusammengefasst werden.
• Funktion: Sie bestimmt die Berechnungen.

SUM ist der naheliegende Ausgangspunkt, doch es können auch AVERAGE, COUNT, MAX und weitere Funktionen verwendet werden. Die Zählung, wie viele Verkäufe jede Region verzeichnet hat, sieht also so aus:

=PIVOTBY(B2:B33, , E2:E33, COUNT)

Wenn ich das Feld für Spaltenfelder leer lasse, erhalte ich eine Zusammenfassung in einer einzigen Spalte. Die optionalen Argumente dienen der Feinabstimmung: ob Ihre Daten Überschriften enthalten, wie tief die Zwischensummen und Gesamtsummen reichen sowie die Sortierreihenfolge entlang jeder Achse. Meist nutze ich die meisten davon nicht - aber sie sind vorhanden, falls ein Bericht sie benötigt.

Meine Zusammenfassung wird aktualisiert, sobald ich neue Verkäufe hinzufüge.

Es gibt keinen Aktualisierungsschritt mehr, den man vergessen könnte.

Das ist der Teil, der mich überzeugt hat. Eine Pivot-Tabelle ist im Grunde ein „Snapshot“ der Daten. Fügt man eine Zeile zu den Quelldaten hinzu, bleibt die Zusammenfassung unverändert - bis man daran denkt, auf „Aktualisieren“ zu klicken. Ich habe bereits mehrere Berichte mit veralteten Gesamtsummen versendet, weil ich diesen Schritt ausgelassen habe.

PIVOTBY ist eine Formel - daher verhält sie sich auch wie eine solche. Sobald ich eine neue Zeile im referenzierten Bereich hinzufüge, werden alle betroffenen Werte automatisch neu berechnet. Füge ich eine neue Verkaufszahl für Elektronikartikel in der Ostregion zum Blatt hinzu, steigt die Gesamtsumme für die Ostregion - ohne dass ich auch nur einen Finger rühren muss.

Um dies luftdicht zu gestalten, formatiere ich die Quelldaten als Excel-Tabelle, die sich erweitert, wenn ich Zeilen hinzufüge, und verweise die Formel auf die Tabellenspalten statt auf einen festen Bereich:

=PIVOTBY(Sales[Region], Sales[Product Category], Sales[Sales Amount], SUM)

Nun erweitert sich der Bereich automatisch, wenn ich Zeilen hinzufüge, und die Zusammenfassung wächst mit.

Ehrlich gesagt ist die Pivot-Tabelle immer noch die bessere Wahl, wenn ich Felder verschieben, Slicer hinzufügen oder die Daten interaktiv erkunden möchte. PIVOTBY versucht nicht, dies zu ersetzen. Für eine schnelle Zusammenfassung, die stets die aktuellsten Zahlen enthält, kommt die Formel jedoch zuerst zum Ziel.

Ich kann Durchschnittswerte in derselben Ansicht stapeln.

Das Umschalten der Aggregationsfunktion ist der ganze Trick.

Der Funktionsparameter ist es, wodurch PIVOTBY seine Nützlichkeit beweist: Da es sich dabei um eine einzige Stelle in der Formel handelt, genügt eine einzige Änderung, um festzulegen, was im Zusammenfassungsbericht angezeigt wird - anstatt die gesamte Formel neu erstellen zu müssen.

Möchten Sie den Durchschnittsumsatz pro Verkäufer statt des Gesamtergebnisses? Ersetzen Sie SUM durch AVERAGE:

=PIVOTBY(D2:D33, , F2:F33, AVERAGE)

Das sagt mir, dass David Chen im Durchschnitt etwa 4.122 USD pro Verkauf erzielt - basierend auf seinen fünf Einträgen - während Lisa Brown im Schnitt rund 2.480 USD pro Verkauf erzielt. Die Zählungen, Maximal- und Minimalwerte funktionieren auf dieselbe Weise.

Sie können auch mehrere Spalten an das „values“-Argument übergeben, um Gesamtsummen und Durchschnittswerte nebeneinander in derselben Tabelle anzuzeigen. Das Ergebnis wirkt wie ein kleines Dashboard, das aus einer einzigen Textzeile erstellt wurde - dabei bleiben alle Werte mit den zugrunde liegenden Live-Daten verknüpft.

Wohin ich PIVOTBY als Nächstes bringe

GROUPBY und interaktive Diagramme stehen als Nächstes auf meiner Liste.

Ich entdecke immer noch neue Anwendungsmöglichkeiten für dieses Tool. Als Nächstes werde ich GROUPBY nutzen, um Zusammenfassungen zu erstellen, bei denen nur eine Achse erforderlich ist - schließlich basiert das Prinzip auf derselben Idee, nur ohne die Spaltendimension. Danach möchte ich einen Bereich, der mit PIVOTBY erstellt wurde, in ein Diagramm einbinden und beobachten, wie sich das Diagramm automatisch aktualisiert, sobald neue Verkaufszahlen eintreffen - ohne dass dazu manuelle Anpassungen nötig wären. Benutzerdefinierte Aggregationen mittels LAMBDA sind noch etwas in der Zukunft, aber der Weg dorthin ist frei. Im Moment ist es meine erste Reaktion, wenn mich jemand um eine schnelle Auswertung bittet, die bis Freitag aktuell bleibt: Ich tippe einfach eine Formel ein.