Excel PIVOTMIT Funktion

Überblick

Mit der neuen Funktion PIVOTMIT lassen sich Daten aggregieren. Traditionell würde man dies mit Pivot-Tabelle machen. Jetzt lässt sich dies auch per Formel erledigen, was den Vorteil hat, dass Änderungen in den Ausgangsdaten direkt in das Ergebnis von PIVOTMIT einfließen. Bei Pivot-Tabellen muss man dahingegen noch auf aktualisieren klicken. Man kann sich somit also einen Klick sparen und garantiert damit, dass die Auswertung immer auf dem neusten Stand ist.

Syntax

=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])

Argumente

row_fields - Zeilenfelder
col_fields - Spaltenfelder
values - Werte
function - Funktion: SUMME / PROZENTVON / MITTELWERT / MEDIAN / ANZAHL / ANZAHL2 / MAX / MIN / PRODUKT / MATRIXZUTEXT / TEXTKETTE / STABW.S / STABW.N / VAR.S / VAR.P / MODUS. EINF / LAMBDA
field_headers - Spaltenköpfe: 0 - Nein / 1- Ja, aber nicht anzeigen / 2 - Nein aber generien / 3 - Ja und anzeigen
row_total_depth - 0 - Keine Summen / 1 - Gesamtergebnisse / 2 - Gesamt- und Teilergebnisse / -1 Gesamtsummen oben / -2 Gesamt- und Teilergebnisse oben
row_sort_order - 1 - aufsteigend / -1 absteigend
col_total_depth - 0 - Keine Summen / 1 - Gesamtergebnisse / 2 - Gesamt- und Teilergebnisse / -1 Gesamtsummen oben / -2 Gesamt- und Teilergebnisse oben
col_sort_order - 1 - aufsteigend / -1 absteigend
filter_array - Einspaltiges Feld mit Wahrheitswerten (WAHR/FALSCH), das angibt, ob die entsprechende Datenzeile berücksichtigt werden soll. Das Feld muss genausoviele Einträge wie row_fields haben.
relative_to - 0 - Spaltensummen (Standard) / 1 - Zeilensummen / 2 - Gesamtsumme / 3 - Übergeordnete Spalte insgesamt / 4 - Übergeordnete Zeile gesamt

Beispiele für PIVOTMIT

Sonstige - PIVOTMIT

1) PIVOTMIT mit den 4 Standardargumenten

In Excel wurde kürzlich die neue Funktion PIVOTMIT eingeführt, mit der Du Daten aus einer Tabelle pivotieren, also in mehrere Spalten schreiben kannst. Die Funktion hat folgende vier erforderlichen Argumente:

  1. Die Spalte, deren Einträge als Zeilen der Pivotisierung dienen sollen. In unserem Beispiel die Spalte C.
  2. Die Spalte, deren Einträge als Zeilen der Pivotisierung dienen sollen. In unserem Beispiel die Spalte D.
  3. Die Spalte, deren Einträge die zu aggregierenden Werte enthält. In unserem Beispiel die Spalte E.
  4. Die Funktion, nach der die Werte zusammengefasst werden sollen. Neben den Standardfunktionen SUMME, ANZAHL, MITTELWERT, etc. hat Microsoft auch hier eine neue Funktion mit dem Namen PROZENTVON eingeführt, mit der sich die jeweiligen prozentualen Anteile am Gesamtergebnis berechnen lassen.
PIVOTMIT 4 Argumente

Das Ergebnis mit SUMME sieht (in € formatiert) folgendermaßen aus:

PIVOTMIT 4 Argumente mit Ergebnis

Wie Du das Gesamtergebnis ausblenden oder Spaltenüberschriften hinzufügen kannst, erfährst Du in Punkt 4 dieses Artikels.

2) PIVOTMIT mit mehreren Spalten oder Zeilen

Die Tabelle enthält nun auch das Verkaufsjahr:

PIVOTMIT Verkaufsjahr

Du kannst auch nach mehreren Spalten gleichzeitig pivotieren und mehrere Spalten mit Werten aggreggieren, indem du in der Formel im jeweiligen Parameter mehrere Spalten eingibst. Die jeweiligen Spalten müssen hierbei zusammenhängen.

In unserem Beispiel wollen wir in den Zeilen die Verkäufer und Produkte stehen haben und in den Spalten die Jahre. Die zusammengefassten Werte sollen summiert werden:

PIVOTMIT mehrere Zeilen

Das Ergebnis sieht folgendermaßen aus. Wie wir sehen, sind die Werte in den Zeilen nach einer Überkategorie, den Verkäufern, und einer Unterkategorie, dem Produkt aufgeteilt und werden in den Spalten über die Jahre aufsummiert:

PIVOTMIT mehrere Zeilen Ergebnis

3) Eine Funktion auf einen PARAMETER anwenden

Es ist auch möglich, das Jahr zu ermitteln, indem wir auf den Parameter mit den Datumseinträgen die Funktion JAHR() anwenden, um direkt nach Jahren zu pivotisieren:

PIVOTMIT Datum in Jahr umwandeln

Das Ergebnis sieht folgendermaßen aus:

PIVOTMIT Datum in Jahr umwandeln Ergebnis

4) Die zusätzlichen Paramenter von PIVOTMIT für Überschriften, Gesamt- und Teilergebnisse, Sortierungen und Filterungen

Neben den 4 erforderlichen Parametern hast Du die Möglichkeit, in GRUPPIERENNACH auch weitere optionale Parameter einzugeben, um das Ergebnis nach Deinen Wünschen zu verändern. Die Parameter in der richtigen Reihenfolge sind:

  1. Ein Parameter für Überschriften der Zeilen. Dieser gibt an, ob die Spalten im 1. Parameter Überschriften besitzen und ob diese angezeigt werden sollen. Du hast hierbei folgende Möglichkeiten:
    • 0 - keine Überschriften, auch keine anzeigen.
    • 1 - es gibt Überschriften, aber sie sollen nicht angezeigt werden.
    • 2 - es gibt keine Überschriften, aber es sollen welche generiert werden.
    • 3 - es gibt Überschriften, bitte anzeigen.
  2. Ein Parameter dafür, ob für die Zeilen Teil- oder Gesamtsummen generiert werden sollen. Die Möglichkeiten hier sind:
    • 0 - keine Gesamtsummen
    • 1 - Gesamtsummen
    • 2 - Gesamt - und Zwischensummen
    • -1 - Gesamtsummen oben
    • -2 - Gesamt und Zwischensummen oben
  3. Ein Parameter zur Sortierung, der angibt, welche und wie die Zeilen sortiert werden sollen. Gib hierzu die Nummer der Spalte an, nach der du sortieren willst. Positive Zahlen bedeuten aufsteigende Sortierung und negative Zahlen absteigende Sortierung.

4., 5., 6. Diese sind die Parameter für die Überschriften, Teil- bzw. Gesamtsummen und Sortierungen der Spalten

7. Ein Parameter zur Filterung deiner Ausgangsdaten nach Belieben. So könntest du bspw. folgendes eingeben, um alle Zeilen mit "Thomas" als Verkäufer herauszufiltern: 
C2:C84<>"Thomas"

Solltest Du einen dieser optionalen Parameter nicht brauchen, springe durch einen leeren Text zwischen den Semikolons (;) einfach zum nächsten.

5) Daten gruppieren statt pivotieren

Möchtest Du deine Daten nicht pivotieren, sondern nach bestimmten Spalten gruppieren, kannst Du das mit der ebenfalls neuen Excel-Funktion GRUPPIERENNACH tun.

6) Werde Zum Excelhero

Die zahlreichen Excel-Funktionen, Pivot-Tabellen oder Power Query sind nicht nur leistungsfähige Werkzeuge für die Datenmanipulation und -Bereinigung in Excel, sondern auch ein unverzichtbarer Bestandteil, wenn Du deine Fähigkeiten im Umgang mit Daten auf das nächste Level heben möchtest. Wenn Du deine Kenntnisse in Excel vertiefen und seine volle Kraft entfesseln möchtest, laden ich dich herzlich zu meinem Excelkurs ein. Hier wirst Du nicht nur die Grundlagen erlernen, sondern auch fortgeschrittene Techniken meistern, die dir dabei helfen, Daten effizienter und effektiver zu verarbeiten. Investiere in dein Wissen und setze dich an die Spitze der Datenanalyse-Experten!