Die neuen Excel-Funktionen, die jeder liebt, können immer noch nicht das tun, was eine Pivot-Tabelle tut
Wenn neue Werkzeuge verfügbar sind, ist es sehr einfach, dass alte Werkzeuge veraltet werden; das ist typischerweise der natürliche Verlauf der Technologie. Also ist es nicht überraschend, dass man, wenn man den Menschen ein paar leistungsstarke neue Excel-Funktionen gibt, die Ergebnisse über das Arbeitsblatt verteilen können, beginnt, Gespräche darüber zu hören, dass vertraute Werkzeuge obsolet werden.
GROUPBY und PIVOTBY liefern sicherlich ein überzeugendes Argument. Sie sehen aus wie glänzende, neuere Versionen von PivotTables, und es ist schwer, sich nicht in sie zu verlieben. Nach einiger Zeit der Nutzung wird man jedoch feststellen, dass sie nicht ganz der PivotTable-Killer sind, für den sie auf den ersten Blick erscheinen mögen.
Excels beste Tastenkombination hat nichts mit der Tastatur zu tun — es ist dieses Feld
Excel hat die Dinge einfacher gemacht, ich habe es nur nie bemerkt.
Was GROUPBY und PIVOTBY tun
Sie sehen den PivotTables sehr ähnlich, aber die Ähnlichkeiten gehen nur so weit
GROUPBY und PIVOTBY können sich anfühlen wie PivotTables, die auf ihre mathematischen Grundelemente reduziert wurden, aber so einfach ist es nicht. Sie sind nicht genau dasselbe.
Die GROUPBY-Funktion verarbeitet Aggregationen entlang einer Achse. Hier ist ihre grundlegende Syntax:
=GROUPBY(row_fields, values, function, ...) Sie geben Excel an, welche Zeilenfelder die Gruppen definieren (wenn Sie mehrere Spalten auswählen, erstellen Sie hierarchische Gruppierungen), welche Werte zusammengefasst werden sollen und wie diese zusammengefasst werden sollen. Angenommen, Sie haben einen Verkaufsdatensatz und möchten den Gesamtgewinn und die durchschnittlich verkauften Einheiten hierarchisch nach Region (Primärgruppe) und Land (Sekundärgruppe) gruppiert anzeigen. Sie würden eine Formel wie diese verwenden:
=GROUPBY(A2:B100, HSTACK(N2:N100, I2:I100), VSTACK(SUM, AVERAGE), 3, 1)Hier definiert A2:B100 die hierarchischen Gruppen (Region und Land), während HSTACK(N2:N100, I2:I100) die zusammenzufassenden Wertefelder angibt (Gesamtgewinn und verkaufte Einheiten). VSTACK(SUM, AVERAGE) sagt Excel, wie diese Werte zusammengefasst werden sollen (SUMME für Gewinn und DURCHSCHNITT für verkaufte Einheiten), 3 sagt Excel, dass Überschriften und Zwischensummen einbezogen werden sollen, und 1 sagt Excel, die Ergebnisse in aufsteigender Reihenfolge zu sortieren.
Während GROUPBY Daten vertikal anhand von Zeilenfeldern zusammenfasst, fügt PIVOTBY col_fields hinzu, um Werte sowohl vertikal als auch horizontal aufzuschlüsseln. Sie können hier auch die Sortierung und Gesamtsummen steuern, die Quellaufzeichnungen vor der Aggregation filtern und sogar das Argument relative_to für Berechnungen wie Prozentsätze von Zeilen-, Spalten- oder Gesamtsummen verwenden. Angenommen, Sie möchten den Gesamtgewinn (SUM) und die durchschnittlich verkauften Einheiten (AVERAGE) analysieren, gruppiert vertikal nach Region und Land (Zeilenfelder) und horizontal nach Vertriebskanal (Spaltenfeld) aufschlüsseln. Ihre Formel würde dann so aussehen:
=PIVOTBY(A2:B100, D2:D100, HSTACK(N2:N100, I2:I100), VSTACK(SUM, AVERAGE), 3, 1, 1, 1, 1)Nach dem Argument row_fields (A2:B100) gibt es jetzt ein Argument col_fields (D2:D100), das die Spalten entsprechend dem Vertriebskanal oben gruppiert. In dieser Formel beinhaltet 3 die Kopfzeilen, die erste 1 zeigt Gesamt- und Zwischensummen für die Zeilen, die zweite 1 sortiert die Zeilen alphabetisch, die dritte 1 zeigt Gesamtsummen für die Spalten und die letzte 1 sortiert die Spalten in aufsteigender Reihenfolge.
Der wirklich wichtige Unterschied besteht jedoch darin, wo das alles passiert. GROUPBY und PIVOTBY sind Arbeitsblattfunktionen. Sie geben eine Formel ein, Excel wertet sie aus und gibt dann das resultierende Array in benachbarte Zellen aus. Wenn Sie die Quelldaten ändern, wird das Ergebnis automatisch neu berechnet.
Das eröffnet einige nützliche Möglichkeiten. Ein ausgespültes PIVOTBY-Ergebnis kann Teil eines dynamischen Dashboards werden, eine XLOOKUP- oder FILTER-Formel speisen oder einen sich ändernden Bereich für ein Diagramm bereitstellen. Sie können auch eine benutzerdefinierte LAMBDA-Funktion in das Aggregationsargument einfügen, was Ihnen deutlich mehr Kontrolle als eine herkömmliche Summe der Verkaufszusammenfassung gibt. Sortieren und Filtern sind ebenfalls in die Formeln integriert, sodass Sie nicht unbedingt Hilfsspalten oder Zwischenbereiche benötigen, nur um die Daten in Form zu bringen.
Diese Möglichkeiten sind der Grund, warum sich diese Funktionen wie ein Upgrade anfühlen. Die Funktionen erzeugen jedoch nur Ergebnisse, die einer PivotTable ähneln; sie sind nicht die PivotTable-Funktion selbst. Die Funktionen existieren in der Formelschicht, während PivotTable ein objektbasiertes Berichtswerkzeug mit eigener Benutzeroberfläche und Infrastruktur ist.
Wo PivotTables immer noch gewinnen
Die Funktionen sind leistungsstark, aber sie können das PivotTable-Erlebnis nicht ganz replizieren
Das größte Problem dabei, GROUPBY und PIVOTBY als Ersatz für PivotTables zu bezeichnen, ist, dass eine Zusammenfassung nicht unbedingt ein interaktiver Bericht ist. In einer PivotTable erstellt ein Doppelklick auf eine Zusammenfassungszelle ein neues Blatt, das die zugrunde liegenden Transaktionsdaten anzeigt. Die Funktionen liefern das berechnete Ergebnis, aber es gibt keine entsprechende eingebaute Drill-Down-Funktion.
Dasselbe gilt für das Erweitern und Zusammenklappen von Hierarchien. PivotTables ermöglichen es Ihnen, zwischen einer Übersicht auf hoher Ebene und den zugrunde liegenden Gruppen mit vertrauten Erweitern-/Zusammenklappen-Steuerelementen zu wechseln. Ein PIVOTBY- oder GROUPBY-Ergebnis ist ein dynamisches Array: leistungsfähig, flexibel und automatisch angepasst, aber letztlich ein berechnetes Raster und kein interaktives Gliederungsformat.
Dann gibt es den Aufgabenbereich PivotTable-Felder. Ein anderes Teammitglied kann ein Feld von Filtern zu Zeilen ziehen, ein anderes in Werte verschieben und sofort einen anderen Bericht sehen. Mit PIVOTBY bedeutet das Ändern der Berichtsstruktur, die Argumente der Formel zu ändern. Das ist für einen erfahrenen Excel-Benutzer kaum schwierig, aber für jemanden, der lieber „Region“ in die Spalten zieht, als eine Formel zu bearbeiten, ist es eine ganz andere Erfahrung.
PivotTables haben auch ein viel größeres Ökosystem dahinter. Sie können mit dem Datenmodell und Power Pivot von Excel arbeiten, wodurch mehrere verwandte Tabellen und sehr große Datensätze in die Analyse einbezogen werden können. DAX-Maßnahmen können anspruchsvollere Geschäftslogik, Zeitintelligenz und KPIs bieten. PivotTables können auch eine Verbindung zu externen und unternehmensweiten Datenquellen herstellen, während Excel sie mit Live-Verbindungen zu unterstützten semantischen Power BI-Modellen verwenden kann.
Es gibt auch kleinere Annehmlichkeiten. GETPIVOTDATA gibt PivotTables eine strukturierte Möglichkeit, bestimmte Werte abzurufen. Bei einem GROUPBY- oder PIVOTBY-Array müssen Sie in der Regel Ihre eigene Suchlogik um das ausgeworfene Ergebnis herum erstellen.
Ironischerweise kann die Flexibilität von Formeln auch ihre eigenen Probleme schaffen. Angenommen, Sie möchten eine laufende Summe oder eine periodische Abweichung. Die Funktionen sind hervorragend darin, Werte nach Zeilen- und Spaltengruppen zu aggregieren, aber Berechnungen, die von Beziehungen zwischen Zeilen abhängen, sind nicht ihre natürliche Stärke. Sie könnten schließlich das Ergebnis in LET, LAMBDA, MAP, SCAN, BYROW, VSTACK, TAKE, DROP, MAKEARRAY, SUMIFS oder andere dynamische Array-Funktionen einbetten. Das kann eine beeindruckend kompakte und automatisierte Lösung ergeben, aber es kann auch dazu führen, dass Sie eine Formel haben, die niemand anderes im Team sechs Monate später anfassen möchte.
Excels neue Gruppe von Funktionen wird dein Gehirn auf eine gute Weise zum Explodieren bringen
Sobald man diese versteht, fühlen sich Tabellenkalkulationen lebendig an.
Die Echtzeit-Neuberechnung ist auch nicht automatisch ein Vorteil. Komplexe Formeln mit dynamischen Arrays über große Bereiche können sich jedes Mal neu berechnen, wenn das Arbeitsblatt geändert wird. Der PivotCache einer PivotTable hingegen trennt den Bericht von Rohdatenänderungen, bis Sie ihn aktualisieren. Dieser zusätzliche Schritt kann nützlich sein, wenn die Leistung der Arbeitsmappe ein Problem darstellt.
Schließlich gibt es die Kompatibilität. GROUPBY und PIVOTBY hängen von modernen Excel-Versionen ab, einschließlich Microsoft 365 und Excel 2024 und später. Wenn Sie die Arbeitsmappe in einer älteren Version öffnen, wird Sie diese clevere neue Formel mit einer der nervigsten Fehlermeldungen von Excel begrüßen: „#NAME?“.
Verschiedene Werkzeuge, verschiedene Aufgaben
GROUPBY und PIVOTBY töten PivotTables nicht wirklich. Sie bringen die Pivot-ähnliche Analyse in die immer leistungsfähigere Formelschicht von Excel. Das ist eine fantastische Lösung, wenn Sie Zusammenfassungen wünschen, die automatisch Dashboards, dynamische Arrays, Diagramme, Suchen und andere Formeln speisen können.
PivotTables befinden sich in einem anderen Bereich. Sie sind interaktive Analyseumgebungen, die Drill-down, Drag-and-Drop-Erkundung, Integration des Datenmodells und Verbindungen zu größeren Business-Intelligence-Ökosystemen bieten. Die Frage ist also nicht unbedingt, einen Gewinner auszuwählen. Es geht darum, das richtige Werkzeug für die Aufgabe auszuwählen.