Beim Reporting wird in der Regel immer in festgelegten Strukturen berichtet, Das bedeutet, es gibt eine festgelegte Anzahl an Berichtseinheiten (z.B. Regionen, Zeitraum). Soll beides veränderlich sein, ist das nur mit Power Query lösbar, es sei denn man hat Excel365 oder Excel 2021 im Einsatz. Das folgende Beispiel soll eine Kreuztabelle zeigen, deren Anzahl der Zeilen und Spalten sich verändern, die Werte aber dennoch aggregiert werden.
Die Datenquelle ist eine intelligente Tabelle, die unter Verwendung verschiedener Funktionen zu einer dynamischen Kreuztabelle umgewandelt wird:
- Die Tabelle soll sich dynamisch erweitern, wenn neue Regionen oder neue Monate in der Datenquelle vorkommen.
- Die Aggregation der Werte erfolgt über die Zeilen- und Spaltenbeschriftungen (SUMMEWENNS()
Das Endergebnis soll dann so aussehen:
Dynamische Spalten- und Zeilenbeschriftungen
Als Spaltenbeschriftung wird eine Liste aller Regionen benötigt, die in Datenquelle [T_Daten] vorkommen. Mit Excel365 (oder Excel 2021) und der Funktion EINDEUTIG ist das kein Problem. Im Zielbereich wird folgende Formel eingegeben:
C2: =EINDEUTIG(T_Daten[Region])
Durch den Einsatz der Funktion EINDEUTIG() wird jede Region nur ein einziges Mal angezeigt. Da es sich um dynamische Array-Funktion handelt, wird die Liste automatisch länger, sobald neue Regionen in der Datenquelle dazukommt.
Dynamische Aggregation der Werte
Auch der Bereich der Spaltenüberschrift soll sich dynamisch erweitern, so dass hierfür wieder die Funktion EINDEUTIG() zum Einsatz kommt. Da die Monate aufsteigend sortiert dargestellt werden sollen, kommt außerdem die Funktion SORTIERN() zum Einsatz. Da beide Funktionen die Ergebnisse zeilenweise liefern, kommt noch die Funktion MTRANS()zum Einsatz, die Datenbereiche transponiert:
B2: =MTRANS(SORTIEREN(EINDEUTIG(T_Daten[Monat])))
In Abhängigkeit der Spalten- und Zeilenbeschriftungen solle nun die Werte aggregiert werden. Dazu verwenden wir die Funktion SUMMEWENNS. Die summiert mit bis zu 127 Kriterien, hier sind es nur zwei, Regionen und Monate:
C3: =SUMMEWENNS(T_Daten[Umsatz];T_Daten[Region];B3#;T_Daten[Monat];C2#)
Das Interessante an den Argumenten der Funktion sind die Hashs (#) hinter den beiden Bezügen auf die Suchkriterien (B3# und C2#). Damit geben wir an, dass es sich jeweils um die erste Zelle eines Überlaufs-handelt. So wird auch die Funktion SUMMEWENNS() zu einer dynamischen Funktion, die automatisch für alle vorhandenen Zeilen und Spalten „kopiert“ wird (=Überlauf).
C2#: Dies ist der Wert oder die Bedingung, die für den Bereich „Monat“ geprüft wird, während B3# dies für den Bereich der „Regionen“ prüft.
Das nachfolgende Video zeigt den gesamten und insgesamt schnell erstellten Vorgang.
