Ich habe aufgehört, Pivot-Tabellen für Zusammenfassungen zu erstellen, nachdem ich diese neue Excel-Funktion gefunden habe

Jahrelang war mein Reflex bei jeder schnellen Auswertung derselbe. Ich fügte eine Pivot-Tabelle ein, zog Felder in die richtigen Kästchen und aktualisierte sie dann, wann immer sich die Zahlen änderten. Es funktionierte, aber erforderte mehr Klicks, als die Frage verdiente. PIVOTBY erledigt dieselbe Gruppierung und Berechnung in einer einzigen Formel, die direkt im Raster lebt, und sie muss niemals aktualisiert werden. Es ist eine der neueren dynamischen Array-Funktionen von Microsoft 365, also wenn deine Tabelle noch auf älteren Werkzeugen basiert, hier ist, was diese Funktion es wert macht, sie zu tippen statt zu klicken.

PIVOTBY gibt mir eine Zusammenfassungstabelle ohne jegliche Einrichtung

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

Eine Pivot-Tabelle verlangt eine Abfolge von Schritten, bevor sie dir irgendetwas zeigt. Du fügst sie ein, öffnest die Feldliste, ziehst ein Feld in die Zeilen, ein anderes in die Spalten, ein drittes in die Werte und positionierst dann das Ergebnis. Aber PIVOTBY überspringt das alles. Ich tippe eine Formel ein, drücke Enter, und eine gruppierte Zusammenfassung verteilt sich über die Zellen daneben.

Sagen Sie, ich möchte den Gesamtumsatz nach Region und Produktkategorie aus meinem Verkaufsblatt. Die Formel ist:

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

Region verläuft die Zeilen hinunter, die Produktkategorie oben quer, und die Spalte Umsatzbetrag wird in jeder Zelle summiert. Das Ergebnis spiegelt wider, was mir eine Pivot-Tabelle liefern würde. Norden liegt bei 30.100 $, Süden bei 28.090 $, wobei Sport die stärkste Kategorie im Süden mit 9.240 $ist. Gesamtsummen erscheinen an den Rändern separat, und das Ganze wird als ein einzelner ausgegebener Bereich neu berechnet.

Die Formel ist der Bericht, was bedeutet, dass ich genau sehen kann, wie er erstellt wurde, nur indem ich auf die Zelle oben links klicke.

Das Schreiben meines ersten PIVOTBY dauerte eine Zeile, kein Dialogfeld

Die Argumente entsprechen den Fragen, die ich bereits stelle

Sobald Sie das Muster sehen, liest sich die Syntax wie die Frage, die Sie stellen. Hier ist das Grundgerüst:

=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: Es ist die Spalte, die die Seite hinunter gruppiert wird.
• col_fields: Es ist die Spalte oben.
• Werte: Es sind die Daten, die zusammengefasst werden.
• Funktion: Sie entscheidet die Berechnungen.

SUM ist der offensichtliche Ausgangspunkt, aber es akzeptiert auch AVERAGE, COUNT, MAX und mehr. Also sieht eine Zählung, wie viele Verkäufe jede Region verbucht hat, so aus:

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

Wenn man das Feld für die Spalten leer lässt, erhält man eine Zusammenfassung mit einer einzigen Spalte. Die optionalen Argumente kümmern sich um den Feinschliff: ob Ihre Daten Überschriften haben, wie tief die Zwischensummen und Gesamtsummen gehen und die Sortierreihenfolge jeder Achse. Die meisten von ihnen benutze ich selten, aber sie sind da, wenn 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 eine Momentaufnahme. Fügen Sie Ihrer Quelldatenzeile eine Zeile hinzu, und die Zusammenfassung bleibt unverändert, bis Sie daran denken, auf Aktualisieren zu klicken. Ich habe mehrere Berichte mit einer veralteten Gesamtsumme verschickt, weil ich diesen Schritt übersprungen habe.

PIVOTBY ist eine Formel, also verhält sie sich wie eine. In dem Moment, in dem ich eine neue Zeile innerhalb des referenzierten Bereichs hinzufüge, berechnet sich jede betroffene Zahl von selbst neu. Füge einen frischen Verkauf von Elektronikartikeln der Region Ost in das Blatt ein, und die Gesamtsumme für Ost steigt, ohne dass ich einen Finger rühren muss.

Um dies luftdicht zu machen, formatiere ich die Quelle als Excel-Tabelle, die sich beim Hinzufügen von Zeilen erweitert, und verweise die Formel auf die Tabellenspalten anstelle eines festen Bereichs:

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

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

Um fair zu sein, gewinnt eine Pivot-Tabelle immer noch, wenn ich Felder verschieben, Slicer hinzufügen oder die Daten interaktiv erkunden möchte. PIVOTBY versucht das nicht zu ersetzen. Für eine schnelle Zusammenfassung, die immer die neuesten Zahlen widerspiegelt, ist die Formel jedoch schneller.

Ich kann Durchschnitte in derselben Ansicht stapeln

Das Austauschen der Aggregationsfunktion ist der ganze Trick

Das Funktionsargument ist der Punkt, an dem PIVOTBY seinen Nutzen zeigt, denn als einzelner Platzhalter in der Formel bedeutet das Ändern dessen, was der Bericht zusammenfasst, eine Bearbeitung statt einem Neubau.

Möchten Sie den durchschnittlichen Umsatz pro Verkäufer anstelle einer Gesamtsumme? Ersetzen Sie SUM durch AVERAGE:

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

Das sagt mir, dass David Chen im Durchschnitt etwa 4.122 $pro Verkauf über seine fünf Einträge erzielt, während Lisa Brown bei ihren nahe bei 2.480 $liegt. Zählungen, Maximal- und Minimalwerte funktionieren auf die gleiche Weise.

Sie können auch mehr als eine Spalte in das Werte-Argument eingeben, um Gesamtsummen und Durchschnitte nebeneinander in derselben ausgegebenen Tabelle anzuzeigen. Das Ergebnis liest sich wie ein kleines Dashboard, das aus einer einzigen Textzeile erstellt wurde, und jede Zahl bleibt mit den darunter liegenden Live-Daten verbunden.

Wohin ich PIVOTBY als Nächstes bringe

GROUPBY und Live-Diagramme stehen als Nächstes auf meiner Liste

Ich finde immer noch neue Orte, um dieses Ding zu zeigen. GROUPBY ist als Nächstes dran für Zusammenfassungen, die nur eine Achse benötigen, da es dasselbe Konzept ohne die Spaltendimension ist. Danach möchte ich einen PIVOTBY-Bereich in ein Diagramm einbinden und beobachten, wie sich die Visualisierung selbst neu zeichnet, während neue Verkäufe eingehen, ohne dass manuell am Bereich etwas geändert werden muss. Eigene LAMBDA-Aggregationen sind noch weiter in der Zukunft, aber die Tür ist offen. Für den Moment, wenn mich jemand nach einer schnellen Übersicht fragt, die bis Freitag nicht veraltet sein wird, ist das Eintippen einer Formel mein erster Schritt geworden.