In unserem letzten Tipp haben Sie die Excel-Funktion GRUPPIERENNACH kennengelernt. Mit dieser Funktion gruppieren Sie Werte in einem oder mehreren Zellbereichen und aggregieren die Daten in einem anderen Zellbereich. Die Ergebnisse werden in einem Überlaufbereich anzeigt. Wenn dieser Überlaufbereich zu lang oder zu unübersichtlich wird, können Sie besser die Funktion PIVOTMIT einsetzen. Mit dieser Funktion ordnen Sie die unterschiedlichen Gruppen in einem Zielbereich in verschiedenen Spalten an. Der erzeugte Überlaufbereich ähnelt einer PivotTable. In diesem Tipp lernen Sie einige Anwendungsbereiche dieser Funktion kennen.
Lesen Sie hier weiter
Verfügbarkeit
Im Oktober 2024 steht die Funktion PIVOTMIT Abonnenten von Excel für Microsoft 365 zur Verfügung, die den aktuellen Kanal eingestellt haben.
Aufbau der Funktion PIVIOTMIT
Die Funktion PIVOTMIT kennt 11 Argumente. Die ersten 4 Argumente müssen Sie angeben:
- Zeilenfelder: Geben Sie als erstes Argument die Spalte einer dynamischen Tabelle oder den Zellbereich an, nach der Sie die Zeilen gruppieren möchten. Die einzelnen Gruppen werden in Zeilen anzeigt.
- Spaltenfelder: Geben Sie als zweites Argument eine weitere Spalte einer dynamischen Tabelle oder den Zellbereich an, nach der Sie die Werte gruppieren möchten. Die einzelnen Gruppen werden in Spalten anzeigt.
- Werte: Als drittes Argument geben Sie die Spalte einer dynamischen Tabelle oder den Zellbereich ein, dessen Werte aggregiert werden sollen.
- Funktion: Als viertes Argument wird eine Aggregationsfunktion erwartet wie z.B. SUMME, ANZAHL, MIN, MAX und MITTELWERT. Es gibt insgesamt 16 Aggregationsfunktionen und für flexible Berechnungen noch die LAMBDA-Funktion.
Ausgangsbeispiel
Die Bestelldaten des ersten Halbjahres 2024 sollen mit der Funktion PIVOTMIT analysiert werden. Die Tabelle hat folgenden Aufbau:

Der Name der dynamischen Tabelle lautet Bestellungen. Die dynamische Tabelle hat den Vorteil, dass die Spaltennamen in die Formel übernommen werden können.
Umsatz nach Kunde und Produkt
Im ersten Beispiel soll der Umsatz pro Kunde und Produkt ermittelt werden.
Die Formel in Zelle G2 lautet folgendermaßen.
=PIVOTMIT(Bestellungen[Kunde];Bestellungen[Produkt];Bestellungen[Umsatz];SUMME)
Nachdem die Umsatzwerte formatiert wurden, sieht das Ergebnis so aus:

Standardmäßig erscheinen die Summen pro Kunde und Produkt sowie die Gesamtsumme aller Umsätze am Ende des Überlaufbereichs. Die Kunden und Produkte werden automatisch nach dem Namen bzw. den Bezeichnungen aufsteigend sortiert.
Umsatzanteile nach Kunde und Produkt
Im nächsten Beispiel sollen die Umsatzanteile pro Kunde und Produkt ermittelt werden. Die Formel lautet so:
=PIVOTMIT(Bestellungen[Kunde];Bestellungen[Produkt];Bestellungen[Umsatz];PROZENTVON)
In diesem Beispiel wurde die Aggregatsfunktion SUMME durch PROZENTVON ersetzt.
Die Tabelle mit den Ergebnisdaten sieht so aus:

Auffällig an dieser Tabelle ist die standardmäßige Darstellung der Anteile in Prozent des Spaltengesamtergebnisses. Wenn Sie alle Anteile in Prozent des Gesamtergebnisses sehen möchten, müssen Sie in der obigen Formel den elften und letzten Parameter angeben:
=PIVOTMIT(Bestellungen[Kunde];Bestellungen[Produkt];Bestellungen[Umsatz];PROZENTVON;;;;;;;2)
Die Tabelle sieht danach so aus:

Der Wert 2 steht für die Berechnung der Anteile auf Basis der Gesamtsumme aller Umsätze. Folgende Werte können Sie als elften Parameter verwenden:
0 = Spaltensummen (Standard)
1 = Zeilensummen
2 = Gesamtsumme
3 = übergeordnete Spalte insgesamt
4 = übergeordnete Zeile gesamt
Umsatz nach Kunde absteigend und nach Produkt aufsteigend
Wenn die Tabelle nach den Kundenumsätzen absteigend sortieren möchten, können Sie folgende Formel verwenden:
=PIVOTMIT(Bestellungen[Kunde];Bestellungen[Produkt];Bestellungen[Umsatz];SUMME;;;-2)
Die Tabelle sieht nach der Änderung so aus:

Entscheidend ist in der Formel der siebte Parameter, mit der die Sortierung der Zeilen angegeben können. Der Wert -2 steht für eine absteigende Sortierung der Umsatzwerte aus Sicht des Kunden. Mit dem Wert 2 sortieren Sie die Umsatzwerte aufsteigend.. Warum muss für eine Sortierung der Umsätze die Zahl 2 genommen werden? In unserem Beispiel gibt es ein Zeilenfeld mit den Kundennamen und ein Datenfeld mit den Umsatzwerten. Die Kunden stehen somit an erster Stelle und die Umsätze an zweiter Stelle. Die Zahl 2 steht somit für die zweite Zeile mit den Umsatzwerten.
Für den siebten Parameter sind für unser Beispiel folgende Werte sinnvoll und möglich:
1 = Aufsteigende Sortierung nach Kundennamen (Standard)
-1 = Absteigende Sortierung nach Kundennamen
2 = aufsteigende Sortierung nach Umsatzwerten
-2 = absteigende Sortierung nach Umsatzwerten
Der Wert 3 bzw. -3 ist nur dann sinnvoll, wenn Sie mindestens zwei Zeilenfelder in der Formel definiert haben. Ansonsten erscheint im Überlaufbereich nur der Fehlerhinweis #WERT!
Umsatz absteigend nach Kunde und Produkt
Wenn Sie die Umsätze der Produkte ebenfalls absteigend sortieren möchten, geben Sie in der Formel für den neunten Parameter den Wert -2 ein:
=PIVOTMIT(Bestellungen[Kunde];Bestellungen[Produkt];Bestellungen[Umsatz];SUMME;;;-2;;-2)
Die Tabelle hat folgendes Aussehen:

Umsatz nach Kunde und Produkt im bestimmten Zeitraum
Sie können die zu berücksichtigenden Werte mit der Funktion PIVOTMIT genauso filtern wie mit der Funktion GRUPPIERENNACH. Als Beispiel sollen nur die Umsätze im Zeitraum vom 01.04. bis zum 31.05.2024 in die Berechnungen einfließen. Die passende Formel dafür lautet so:
=PIVOTMIT(Bestellungen[Kunde];Bestellungen[Produkt];Bestellungen[Umsatz];SUMME;;;;;;(Bestellungen[Bestelldatum]>=DATUM(2024;4;1)) *(Bestellungen[Bestelldatum]<=DATUM(2024;5;31)))
Die Umsatzwerte in der Tabelle sind niedriger als in den vorherigen Beispielen. Kunden, mit denen in diesem Zeitraum kein Umsatz generiert wurde, erscheinen nicht mehr.

Die Filterbedingung geben Sie als zehnten Parameter in der Formel an.
Provision nach Kunde und Monat mit Hilfe der Lambda-Funktion
Im letzten Beispiel soll die Funktion LAMBDA in eine Formel integriert werden. Anhand der Umsätze sollen Provisionen pro Kunde und Monat anhand der folgenden Regel ermittelt werden:
Wenn der einzelne Umsatzwert größer oder gleich 1000 ist, soll eine Provision von 5% auf den Umsatzwert gezahlt werden, ansonsten eine Provision von 3%.
Die Formel dafür lautet:
=PIVOTMIT(Bestellungen[Kunde];MONAT(Bestellungen[Bestelldatum]);Bestellungen[Umsatz];LAMBDA(x;SUMME(WENN(x>=1000;x0,05;x0,03))))
Die Ergebnistabelle mit den Provisionsbeträgen nach Kunden und Monaten sieht so aus:

Die LAMDBA-Funktion mit der integrierten Funktionen WENN und SUMME geben Sie als vierten Parameter in die Formel ein. Der Parameter x in der LAMBDA-Funktion steht für jeden einzelnen zu prüfenden Umsatzwert in der Ausgangstabelle.
Fazit
In diesen Tipp haben Sie Anwendungsfälle für die Funktion PIVOTMIT kennengelernt. Wenn Sie die Daten in einer Tabelle oder Zellbereich nach zwei oder mehreren Spalten gruppieren möchten, können Sie sowohl die Funktion GRUPPIERENNACH als auch die Funktion PIVOTMIT verwenden. Die von der Funktion PIVOTMIT erzeugte Ergebnistabelle ist übersichtlicher, wenn die Anzahl der Gruppen ist in einem Zellbereich nicht zu hoch ist, um sie in mehreren Spalten anzeigen zu lassen.
0 Kommentare