Erstellen eines PivotTable-ähnlichen Berichts mit Formeln

Mit PivotTables analysieren Sie in Excel schnell und interaktiv Ihre Daten. Möchten Sie für ein Dashboard keine PivotTable verwenden, können Sie einen ähnlich aussehenden Bericht erstellen, der auf Formeln basiert. In diesem Tipp erfahren Sie, welche Funktionen Sie einsetzen können, um aus einer dynamischen Tabelle einen PivotTable-ähnlichen Bericht mit Zeilen- und Spaltenbeschriftungen zu erzeugen. Ausgangsbeispiel In einer Tabelle sollen die Umsätze eines Jahres von Kunden mit einzelnen Produkten dargestellt werden. Die Kunden mit Ihren Bestellungen sollen in Zeilen und die bestellten Produkte in Spalten erscheinen. Die Ausgangstabelle mit den Bestelldaten sieht so aus (Ausschnitt): Tabelle mit Bestellungen Die Tabelle hat den Namen Bestellungen. Ermittlung der Kunden Die Kunden, die in dem Jahr 2023 Produkte bestellt haben, sollen sortiert in verschiedenen Zeilen erscheinen. Die Formel in der Zelle G2 lautet folgendermaßen: =SORTIEREN(EINDEUTIG(Bestellungen[Kunde])) Eindeutige Kunden Mit der dynamischen Arrayfunktion EINDEUTIG identifizieren Sie alle Kunden, die im Jahr 2023 Produkte bestellt haben. Diese Kundenliste wird mit der Funktion SORTIEREN aufsteigend sortiert. Ermittlung der Produkte Die bestellten Produkte sollen in verschiedenen Spalten erscheinen. Die Formel in Zelle H1 lautet so: =ZUZEILE(SORTIEREN(EINDEUTIG(Bestellungen[Produkt]))) Das Tabellenblatt sieht nach der Fettformatierung der Produktzellen so aus: Eindeutige Kunden und Produkte Um die bestellten Produkte in mehreren Spalten darzustellen, wird die sortierte Liste mit der Funktion ZUZEILE noch von der Zeilen- in die Spaltenorientierung transformiert. Die Funktion ZUZEILE gibt in diesem Fall einen einspaltigen Zellbereich als Zeile zurück. Anstelle der Funktion ZUZEILE können Sie auch die Funktion MTRANS nutzen: =MTRANS(SORTIEREN(EINDEUTIG(Bestellungen[Produkt]))) Ermittlung der Umsätze pro Kunde und Produkt Im nächsten Schritt sollen die einzelnen Umsatzwerte ermittelt werden. In der Zelle H2 steht die folgende Formel: =SUMMEWENNS(Bestellungen[Umsatz];Bestellungen[Kunde];G2#;Bestellungen[Produkt];H1#) Das Tabellenblatt mit den Umsatzwerten sieht so aus: Umsatz mit Kunden und Produkten Für die Ermittlung der einzelnen Umsatzwerte wird die Funktion SUMMEWENNS eingesetzt. Die Umsatzwerte in der Spalte Umsatz der Tabelle Bestellungen werden gefiltert nach den Kunden und den Produkten. Als Filterkriterien für den Kunden wurde der Ausdruck G2# und für das Produkt der Ausdruck H1# verwendet. Diese beiden Ausdrücke kennzeichnen die jeweils die erste, obere Zelle eines dynamischen Überlaufbereichs. Durch diese Ausdrücke brauchen Sie die Formel mit der Funktion SUMMEWENNS nicht mehr nach unten und nach rechts kopieren. Nach Eingabe der Formel wurden den Zellen mit den ermittelten Umsatzwerten das Währungsformat zugeordnet. Ermittlung der Gesamtwerte der Kunden- und Produktumsätze Für die Summe der Umsätze der einzelnen Produkte geben Sie in Zelle H7 folgende Formel ein: =NACHSPALTE(H2#;LAMBDA(Umsatz;SUMME(Umsatz))) Für die Summe der Umsätze der einzelnen Kunden sowie den Gesamtumsatz geben Sie in Zelle L2 folgende Formel ein: =NACHZEILE(H2:K7;LAMBDA(Umsatz;SUMME(Umsatz))) Nach der Formatierung der neu verwendeten Zellen sieht das Tabellenblatt abschließend so aus: Gesamtumsatz mit Kunden und Produkten Mit der Funktion NACHSPALTE geben Sie für jede Spalte des angegebenen Zellbereichs einen berechneten Wert aus. In unserem Beispiel ist dieser Zellbereich der in Zelle H2 beginnende Überlaufbereich. In der Funktion LAMBDA-Funktion wird diesem Zellbereich der Name Umsatz zugeordnet. Der zweite Parameter definiert die Berechnungsfunktion. In unserem Fall die Funktion SUMME. Die Funktion NACHZEILE in Zelle L2 wird verwendet, um für jede Zeile des angegebenen Zellbereichs H2:K7 einen berechneten Wert auszugeben. Der Zellbereich basiert nicht auf den in Zelle H2 beginnenden Überlaufbereich, weil in dieser Formel noch der Gesamtumsatz auf Basis der Umsatzwerte der einzelnen Produkte in den Gesamtumsatz einfließen soll. Wenn die Anzahl der Kunden und Produkte schwanken sollte, ist der obige Aufbau der PivotTable nicht optimal, weil die Gesamtsummen für die Kunden- und Produktumsätze den Überlaufbereich eingrenzen. Wenn Sie den Bericht folgendermaßen bauen, wird der in Zelle I3 beginnende Überlaufbereich nicht eingrenzt: Gesamtumsatz mit Kunden und Produkten Fazit Mit der Kombination aus den dynamischen Arrayfunktionen SORTIEREN, EINDEUTIG, ZUZEILE, NACHZEILE und NACHSPALTE sowie der Funktion SUMMEWENNS können Sie schnell einen Bericht erzeugen, der einer PivotTable ähnelt. Hinweis In Kürze wird es in Excel eine neue Funktion geben, die vermutlich in der deutschen Version PIVOTMIT heißen wird. Mit dieser Funktion kann eine PivotTable mit einer einzigen Funktion erstellt werden: =PIVOTMIT(Bestellungen[Kunde];Bestellungen[Produkt];Bestellungen[Umsatz];SUMME) Im Dezember 2023 steht diese Funktion nur Anwendern zur Verfügung, die den Insiderkanal abonniert haben.

Netz: Digitalisierung von Arbeitsabläufen mit Office 365 › IT-Service Ruhr
Aktualisiert: 27.7.2024

zum Artikel gehen

Erstellen eines PivotTable-ähnlichen Berichts mit Formeln

Oesterreichische Nationalbank vereinfacht Sprache ihres Geschäftsberichts

Quick Tip: So erstellen Sie einen For Loop / Ad-hoc if loop in OutSystems

Gutachten bei Streitfällen

Messemodell für Windenergieanlage

Eigene Kreativ-Profile erstellen Lightroom CC 7.3 Update 3. April 2018