Neue Funktion PIVOTMIT

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:

Tabelle mit Bestellungen im ersten Halbjahr 2024
Tabelle mit Bestellungen im ersten Halbjahr 2024

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:

Excel-Funktion PIVOTMIT mit Kunden- und Produktsummen

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:

Excel-Funktion PIVOTMIT mit Prozentanteilen bezogen auf Spaltensummen

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:

Excel-Funktion PIVOTMIT mit Prozentanteilen bezogen auf Gesamtsumme

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:

Excel-Funktion PIVOTMIT mit Kunden- und Produktsummen absteigend

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:

Excel-Funktion PIVOTMIT mit Kunden- und Produktsummen absteigend

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.

Excel-Funktion PIVOTMIT mit Zeitraumfilter

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:

Excel-Funktion PIVOTMIT mit Funktion LAMBDA

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.

Michael Richter

Michael Richter

Autor

Ich unterstütze unsere Kunden in erster Linie durch Programmierung von Datenbankanwendungen und Zusatzmodulen von Microsoft Office. In Seminaren und Tipps auf unserer Blogseite gebe ich mein Wissen weiter.

0 Kommentare

Pin It on Pinterest

Share This