Excel-Daten automatisch formatieren mit der neuen PIVOTBY-Funktion
Beim Arbeiten mit komplexen Tabellenkalkulationen wird es schwierig, alle Werte manuell zu verfolgen und zu analysieren. Anstatt eine herkömmliche Pivot-Tabelle zu erstellen und mehrere Schritte auszuführen, bietet die neue PIVOTBY-Funktion von Excel eine einfache Möglichkeit, Daten basierend auf den von Ihnen angegebenen Spalten automatisch neu zu organisieren und zu gruppieren.
Sobald Sie die Formel eingegeben haben, wird die Tabelle neu angeordnet, um aggregierte oder statistische Werte nach Kategorie, Datum oder anderen Kriterien anzuzeigen, ohne dass ein weiteres Eingreifen erforderlich ist. In diesem Handbuch zeigen wir anhand praktischer Beispiele, wie die Funktion verwendet wird, und vergleichen sie mit herkömmlichen Kompilierungsmethoden, um den erheblichen Unterschied hinsichtlich Leistung und Benutzerfreundlichkeit zu veranschaulichen.
Mit der PIVOTBY-Funktion in Excel können Sie Zahlen aggregieren, ohne Ihre Daten in einer Pivot-Tabelle neu erstellen zu müssen. Darüber hinaus werden Datenzusammenfassungen, die über PIVOTBY generiert werden, automatisch aktualisiert, um Änderungen in Ihren Originaldaten widerzuspiegeln, und Sie können die Anzeige durch Ändern der Formel anpassen.
ملاحظة
Wenn Sie bereits wissen, wie die Funktion GROUPBY verwendet wird, ist das Erlernen der Verwendung ganz einfach. Der Unterschied besteht darin, dass GROUPBY Variablen nur in Zeilen gruppiert, während PIVOTBY über ein zusätzliches Argument verfügt, mit dem Sie auch bestimmte Variablen in Spalten gruppieren können.
PIVOTBY-Struktur
Die PIVOTBY-Funktion ermöglicht Ihnen die Eingabe von insgesamt 11 Argumenten. Obwohl dies überwältigend erscheinen mag, liegt der Vorteil einer solchen Medienvielfalt darin, dass Sie das Ergebnis so anpassen können, dass es dem entspricht, was die Daten anzeigen sollen.
Allerdings sind nur die ersten vier Felder erforderlich.
Pflichtfelder
PIVOTBY-Felder werden verwendet, um anzugeben, welche Variablen Sie in Zeilen und Spalten anzeigen möchten, wo Sie Werte finden und wie Sie Ihre Daten gruppieren.
=PIVOTBY(a,b,c,d)
Wo
- a sind die Zellen, die die Kategorie(n) enthalten, die als Zeilenüberschriften unten links im Ergebnis erscheinen sollen,
- b sind die Zellen, die die Kategorie(n) enthalten, die als Spaltenüberschriften oben in Ihrem Ergebnis angezeigt werden sollen,
- c sind die Werte, die in der Mitte Ihres Ergebnisses erscheinen, entsprechend den Zeilen und Spalten, die Sie für die Argumente a und b angegeben haben, und d ist die Excel-Funktion (oder eine, die Sie selbst mit LAMBDA erstellen), die bestimmt, wie die Daten gruppiert werden.
ملاحظة
Obwohl die Argumente a und b für PIVOTBY erforderlich sind, um den Ergebnistyp zu erzeugen, für den es erstellt wurde, können Sie sie weglassen, um Daten nur in Zeilen oder Spalten zu gruppieren.
Optionale Felder
Optionale PIVOTBY-Felder ermöglichen Ihnen das Einfügen von Überschriften und Summen, das Sortieren Ihrer Daten und die Angabe einiger zusätzlicher Parameter – unter ganz bestimmten Umständen:
=PIVOTBY(a,b,c,d,[e,f,g,h,i,j,k])
| Der Koeffizient | Beschreibung | Bemerkungen |
|---|---|---|
| e | Eine Zahl, die angibt, ob die Operanden a, b und c Spaltenüberschriften haben und ob diese im Ergebnis angezeigt werden sollen. | 0 = Keine Spaltenüberschriften (Standard) 1 = Enthält Überschriften, wird aber nicht angezeigt 2 = Keine Header, aber sie werden automatisch generiert 3 = Enthält Überschriften und wird angezeigt |
| f | Eine Zahl, die angibt, ob der Operand a Summen und Zwischensummen enthält. | 0 = Keine Summen 1 = Hauptgruppen unten (Standard) 2 = Hauptsumme und Zwischensummen darunter -1 = Hauptgruppen oben -2 = Haupt- und Zwischensummen oben |
| g | Eine Zahl, die angibt, wie die Spalten angeordnet sind. | Die Zahl bezieht sich auf die Spalten innerhalb des Parameters a, gefolgt von der Spalte im Parameter c. Wenn Sie beispielsweise zwei Spalten im Parameter a haben, bedeutet die Zahl „2“, dass nach der zweiten Spalte sortiert wird, und „3“ bedeutet, dass nach Parameter c sortiert wird. Eine positive Zahl bedeutet aufsteigende oder alphabetische Reihenfolge, eine negative Zahl bedeutet das Gegenteil. |
| h | Eine Zahl, die angibt, ob der Operand b Summen und Zwischensummen enthält. | 0 = Keine Summen 1 = Große Summen rechts (Standard) 2 = Haupt- und Zwischensummen rechts -1 = Hauptgruppen auf der linken Seite -2 = Haupt- und Zwischensummen auf der linken Seite |
| i | Eine Zahl, die angibt, wie die Zeilen angeordnet werden. | Die Zahl bezieht sich auf die Spalten innerhalb des Parameters b, gefolgt von der Spalte im Parameter c. Es funktioniert auf die gleiche Weise wie der G-Faktor. |
| j | Ein logischer Ausdruck oder eine Formel zum Filtern von Zeilen, die bestimmte Werte oder Text enthalten. | Nicht verfügbar |
| k | Eine Zahl, die Ihnen zusätzliche Kontrolle über die Durchführung von Berechnungen gibt, wenn Sie die Funktion PERCENTOF im Parameter d verwenden. | 0 = Spaltensummen (Standard) 1 = Zeilensummen 2 = Gesamt 3 = Summe der übergeordneten Spalte 4 = Summe der Klasse des Vaters |
PIVOTBY: Nur die benötigten Medien verwenden
Lassen Sie mich Ihnen zunächst zeigen, wie Sie die PIVOTBY-Funktion in ihrer einfachsten Form mit nur den vier erforderlichen Feldern verwenden.
Nehmen wir an, Sie erhalten diese Tabelle mit dem Namen „Sports_Viewers“. Die Tabelle zeigt die Live-Einschaltquoten (Spalte D) für sechs Sportarten (Spalte B) in vier Regionen (Spalte C) über vier Jahre (Spalte A) hinweg. Sie werden gebeten, eine Zusammenfassung der Gesamtzahl der Zuschauer nach Sportart und Jahr zu erstellen.
Sie können hierfür die Funktion GROUPBY verwenden, müssen dabei jedoch optionale Argumente verwenden, um ein Ergebnis zu erzielen, das leichter zu analysieren ist. Da GROUPBY die Daten außerdem nur in Zeilen anordnet, kann es sein, dass Sie am Ende eine lange Liste mit Informationen erhalten, die schwer zu interpretieren sind.
Stattdessen können Sie mit der Funktion PIVOTBY eine oder mehrere Variablen nehmen und sie in Spalten einfügen, wodurch Sie ein klareres Bild davon bekommen, wie sich die Zahlen zusammensetzen.
ملاحظة
Da die PIVOTBY-Funktion ein dynamisches Dropdown-Array erzeugt, müssen Sie ihre Formel in einen Bereich der Tabelle schreiben, der nicht als Excel-Tabelle formatiert ist.
Geben Sie dazu in Zelle F1 Folgendes ein:
=PIVOTBY(Sports_Viewers[Sport],Sports_Viewers[Jahr],Sports_Viewers[Zuschauer],SUMME)
Wo
- Sports_Viewers[Sport] ist die benannte Spalte in der benannten Tabelle, die die sechs verschiedenen Sportarten darstellt. Dies sind die Zeilenüberschriften unten links im Ergebnis.
- Sports_Viewers[Year] ist die Jahresspalte in der Tabelle und dies sind die Spaltenüberschriften über dem Ergebnis.
- Sports_Viewers[Viewers] ist die Spalte, die zeigt, wie viele Leute diese Sportarten in diesen Jahren gesehen haben, daher werden diese Daten den Hauptteil des Ergebnisses ausmachen.
SUM teilt Excel mit, dass die Zahlen in den Gesamtspalten addiert werden sollen. Wenn Sie hingegen beispielsweise den Durchschnitt verwenden, wird Ihnen im Ergebnis der Durchschnitt dieser Zahlen angezeigt.
Rat
Anstatt Tabellen- und Spaltennamen manuell in die Formel einzugeben (auch als strukturierte Referenzen bezeichnet), wählt Excel die Tabellenzellen automatisch aus, wenn Sie sie mit der Maus auswählen.
Mit der Art und Weise, wie PIVOTBY Daten anzeigt und organisiert, können Sie Erkenntnisse direkt aus den Ergebnissen gewinnen. Beispielsweise stellen Sie möglicherweise fest, dass für bestimmte Sportarten in bestimmten Jahren keine Daten vorliegen. Wenn die optionalen Felder nicht vorhanden sind, fügt Excel den PIVOTBY-Ergebniszeilen und -spalten automatisch Summen hinzu, sodass Sie sofort die Gesamtzahl der Aufrufe für jedes Jahr und jede Sportart sehen können.
Ohne die Verwendung optionaler Argumente sortiert Excel das Ergebnis auch alphabetisch oder aufsteigend nach dem Argument „A“ und schließt keine Spaltenüberschriften für das Argument „A“ ein.
Nehmen wir nun an, Sie möchten eine Spalte einfügen, die jede Sportart in vier verschiedene Regionen unterteilt. Dazu müssen Sie sowohl die Sportart als auch die Region in das Argument „A“ einbeziehen:
=PIVOTBY(Sports_Viewers[[Sport]:[Region]],Sports_Viewers[Jahr],Sports_Viewers[Zuschauer],SUMME)
Rat
Um nicht benachbarte Spalten in einem Argument zu gruppieren, verwenden Sie die Funktion CHOOSECOLS.
Dieses Ergebnis bietet eine detailliertere Analyse Ihrer Daten, indem die Gesamtaufrufe jeder Sportart nach Region angezeigt werden. Da dem Argument jedoch eine zusätzliche Variable hinzugefügt wird, können Sie einige optionale Argumente eingeben, um das Ergebnis klarer zu machen.
PIVOTBY: Optionale Medien verwenden
Wenn Sie in Excel eine Formel erstellen, wird jedes Argument durch ein Komma getrennt. Dies bedeutet, dass Sie, wenn Sie eines der optionalen Argumente überspringen möchten, einfach ein erstes Komma eingeben, um das Argument zu öffnen, gefolgt von einem zweiten Komma, um es zu schließen. Das Fehlen von irgendetwas zwischen diesen Kommas zeigt an, dass Sie dieses Argument absichtlich weggelassen haben, sodass das Programm die Standardeinstellung verwenden möchte.
Beispiel 1: Summen und Zwischensummen
Eine Möglichkeit, das PIVOTBY-Ergebnis im vorherigen Beispiel zu veranschaulichen, besteht darin, die Zwischensummenzeilen für jede Sportart hinzuzufügen.
Geben Sie dazu Folgendes ein:
=PIVOTBY(Sportzuschauer[[Sport]:[Region]],Sportzuschauer[Jahr],Sportzuschauer[Zuschauer],SUMME,, 2)
Beachten Sie, dass die beiden Kommas nach dem Argument d (SUMME) anzeigen, dass Sie das Argument e (Kopf) überspringen, das Argument f jedoch einschließen möchten (in diesem Fall bedeutet die 2, dass Sie am Ende jeder Sportart eine Zwischensumme sowie am Ende der Gesamtpunktzahl die Gesamtsummen einfügen möchten).
ملاحظة
Um die Daten noch deutlicher zu machen, habe ich die bedingte Formatierung verwendet, um alle Zeilen im Ergebnis farblich zu kennzeichnen, in denen Spalte F nicht leer ist, Spalte G leer ist und Spalte F nicht den Ausdruck „Gesamtsumme“ enthält. Das Anwenden einer direkten Formatierung auf dünn besetzte Arrays wird nicht empfohlen, da die Formatierung an die Zellen und nicht an die Daten gebunden ist. Dies bedeutet, dass sich das Format nicht ändert, wenn sich die Daten ändern.
Beispiel 2: Sortierreihenfolge
Sehen wir uns nun eine andere Möglichkeit zur Steuerung Ihres Ergebnisses an: Sortieren nach einer der Ausgabespalten.
Schreiben:
=PIVOTBY(Sportzuschauer[[Sport]:[Region]],Sportzuschauer[Jahr],Sportzuschauer[Zuschauer],SUMME,, 2,3)
Dies bedeutet, dass Sie nach dem Überspringen des Arguments e zusätzlich zur Einbeziehung von Gesamtsummen und Zwischensummen (Nr. 2 im Argument f) Ihre Daten auch nach der Gesamtzahl der Aufrufe in aufsteigender Reihenfolge sortieren möchten (Nr. 3 im Argument g).
Beachten Sie, dass bei der Sortierung nach den Gesamtzuschauerzahlen nicht nur die Sportarten nach der Gesamtzuschauerzahl sortiert werden, sondern auch die regionalen Unterkategorien nach ihren Zwischensummen.
Beispiel 3: Prozentsätze
So zeigen Sie Ihr Ergebnis in Prozenten an.
Schreiben:
=PIVOTBY(Sports_Viewers[Sport],Sports_Viewers[Jahr],Sports_Viewers[Zuschauer],PROZENT VON,,,2,,,,2)
Sagen Sie Excel, dass Sportarten die Zeilenüberschriften sein sollen (Argument A), Jahre die Spaltenüberschriften (Argument B) und dass Ihre Daten als Prozentsätze (Argument D) gruppiert werden sollen (Argument C). Die ersten 2 (das z-Argument) stellen die Reihenfolge dar, die in diesem Fall nach Werten erfolgt. Schließlich teilt die zweite 2 (das k-Argument) Excel mit, dass die Prozentsätze relativ zu den Gesamtdaten sein sollen. Nicht ausgefüllte Kommas teilen Excel mit, dass die Standardeinstellung für die Argumente e, f, h, i und y verwendet werden soll.
Wählen Sie nun die Zellen aus, die die Dezimalergebnisse enthalten, und klicken Sie auf das Symbol „Prozentformat“ in der Gruppe „Zahl“ auf der Registerkarte „Start“ des Menübands. Klicken Sie in der Zwischenzeit auf die Schaltflächen „Dezimalzahl erhöhen“ und „Dezimalzahl verringern“, um die Anzahl der Dezimalstellen festzulegen.
Rat
Geben Sie unterhalb des Ergebnisses zusätzliche Zeilen an, falls der ursprünglichen Tabelle in Zukunft weitere Daten hinzugefügt werden.
Nehmen Sie sich abschließend einen Moment Zeit, um zu verarbeiten, was Ihnen dieses Ergebnis sagt.
Beispielsweise machen die Zuschauerzahlen beim Baseball im Jahr 2022 11,5 % der Gesamtzuschauerzahlen aller Sportarten im Laufe der Jahre aus. Da die Daten in der Spalte „Gesamt“ aufsteigend sortiert sind, können Sie sehen, dass fast ein Viertel (23,7 %) der gesamten Zuschauerzahlen von Basketball-Zuschauern stammt.
Zusätzlich zur Verwendung von SUM, AVERAGE und PERCENTOF für die PIVOTBY-Funktion können Sie auch andere Aggregatfunktionen wie COUNT, MIN und MAX verwenden.
Die PIVOTBY-Funktion bietet eine intelligente Lösung für alle, die ihre Daten manuell organisieren oder herkömmliche Pivot-Tabellen verwenden.
Mit dieser Funktion vereinfachen Sie Ihre Analyse und beschleunigen die Erstellung Ihrer Tages- oder Monatsberichte erheblich.
Sie müssen die Formel nur einmal erstellen und die Daten werden automatisch entsprechend den von Ihnen angegebenen Kategorien neu strukturiert.
Probieren Sie es bei Ihrer nächsten Datei aus und sehen Sie sofort einen Unterschied in der Art und Weise, wie Sie Daten anzeigen und analysieren.