Office-Blog

Sollen Listen ausgewertet werden, denken viele sofort an Pivot-Tabellen. Dass es auch ohne Pivot geht, zeigt die unten rechts gezeigte Umsatzauswertung nach Artikeln und Regionen.

Die Auswertung rechts entsteht mit nur drei Formeln

Mit nur drei Formeln wird diese Auswertung aufgebaut:

• die erste Formel listet in Spalte H die Artikel in sortierter Reihenfolge auf,
• die zweite sorgt für die Spaltenüberschriften ab Spalte I – natürlich auch sortiert,
• die dritte summiert die Umsätze aus der links gezeigten grauen Umsatzliste nach Artikeln und Regionen.

Dank der Formeln ist die Auswertung komplett dynamisch: kommen neue Artikel oder Regionen hinzu, erweitert sie sich automatisch nach unten bzw. nach rechts. Mit den folgenden Schritten wird die Auswertung erstellt.

Die Artikel dynamisch auflisten mit EINDEUTIG und SORTIEREN

• Zuerst wandele ich die Umsatzliste aus Spalte B bis E mit Strg + T in eine intelligente Tabelle um und gebe ihr den Namen tbl_Verkauf.
• Unterhalb der Überschrift Artikel erzeuge ich in H4 eine dynamische Auflistung aller Artikel mit der Formel =EINDEUTIG(tbl_Verkauf[Artikel]).

Mit nur einer Formel alle Artikel auflisten lassen

Wichtig: Die Formel liefert nicht nur für Zelle H4 ein Ergebnis, sondern erzeugt einen Bereich von Ergebnissen, der hier im Beispiel über acht Zellen reicht: von H4 bis H11. Dieser SPILL-Bereich ist der entscheidende Vorteil der neuen dynamischen Arrayfunktionen in Excel 365 und 2021.

So lasse ich Excel das Sortieren automatisch erledigen

Damit die Liste mit den Artikeln stets alphabetisch sortiert ist, ergänze ich die Formel noch, und zwar mit der Funktion SORTIEREN.
Die komplette Formel lautet dann: =SORTIEREN(EINDEUTIG(tbl_Verkauf[Artikel])).

Durch Ergänzen der Funktion SORTIEREN wird die Artikelliste gleich noch dynamisch sortiert

Die Regionen als Spaltenüberschriften anzeigen mit EINDEUTIG & MTRANS

Rechts neben der Spaltenüberschrift Artikel brauche ich ab Zelle I4 die Namen der Regionen als Spaltenüberschriften. Die Regionen tippe ich aber nicht händisch ein, sondern erzeuge sie ebenfalls mit der Funktion EINDEUTIG.

Allerdings soll der Spillbereich diesmal nicht nach unten, sondern von links nach rechts laufen. Denn ich brauche die Namen der Regionen nebeneinander. Daher kombiniere ich EINDEUTIG mit der Funktion MTRANS. Sie dreht die Liste, die von EINDEUTIG geliefert wird, um 90 Grad.

• In Zelle I4 gebe ich daher folgende Formel ein: =MTRANS(EINDEUTIG(tbl_Verkauf[Region])).
• Damit die Namen der Regionen alphabetisch geordnet erscheinen, ergänze ich die Formel wieder mit Sortieren.
  Die komplettierte Formel lautet dann: =MTRANS(SORTIEREN(EINDEUTIG(tbl_Verkauf[Region]))).

Die mit EINDEUTIG erzeugte Liste der Regionen wird mit MTRANS um 90 Grad gedreht und somit horizontal angezeigt

Die Umsätze nach zwei Kriterien zusammenfassen

Nun summiere ich mit nur einer Formel alle Umsätze nach Artikeln und Regionen. Da ich Umsätze nach zwei Kriterien zusammenfassen will, verwende ich die Funktion SUMMEWENNS. Sie erlaubt es, mehrere Kriterien beim Summieren zu berücksichtigen.

In Zelle I4 stelle ich die folgende Formel zusammen:
=SUMMEWENNS(tbl_Verkauf[[Umsatz ]];tbl_Verkauf[Artikel];H4#;tbl_Verkauf[Region];I3#)

Alle Umsätze werden nach zwei Kriterien zusammengefasst mit nur einer SUMMEWENNS-Formel, die einen dynamischen Ergebnisbereich über 32 Zellen nach rechts und nach unten erzeugt

• Diese eine Formel in Zelle I4 reicht, um einen Ergebnisbereich von 32 Zellen zu erzeugen.
• Entscheidend dafür sind die beiden Bezüge H4# und I3#.
• Die #-Zeichen hinter den beiden Zellbezügen H4 und I3 weisen Excel an, die SPILL-Bereiche zu berücksichtigen, die ab H4 bzw. I3 beginnen.

Tipp: Da SPILL-Formeln keine Formate übertragen, sollte vorab ein ausreichend großer Bereich als Währung formatiert werden.

Vergleich und Fazit

Die folgende Abbildung zeigt, wie die Auswertung aussehen würde, wenn sie mittels Pivot-Tabelle erzeugt wird. Sie hat mindestens drei Nachteile:

• Sie weist eine zusätzliche, störende Zeile mit Summe von auf. Diese müsste ich ausblenden.
• Beim Formatieren der Pivot-Tabelle bin ich weniger flexibel als bei der oben gezeigten Formel-Lösung.
• Kommen neue Umsätze hinzu oder ändern sich die Regionen, muss ich erst jedes Mal per Rechtsklick den Befehl Aktualisieren ausführen.

So würde die Umsatzauswertung mit einer Pivot-Tabelle aussehen

Fazit: Die mit nur drei Formeln aufgebaute Auswertung ist nicht nur eine Alternative zu Pivot, sondern in dem Fall sogar die bessere Lösung. Dank der SPILL-Bereiche  ist sie komplett dynamisch. Eine Aktualisierung ist nicht nötig.

Mehr zum Aufbau kompakter Auswertungen und zur Nutzung von Arrayfunktionen …

… gibt es bei den Excel-Kompetenztagen am 28. Juni in den Kursen zum Aufbau eines Info-Cockpits und zu den neuen Arrayfunktionen in Excel 365. Der Flyer hier unten liefert alle Informationen zum Ablauf und zu den Inhalten der insgesamt 36 Kurse.

Neugierig auf weitere Beispiele für dynamische Funktionen?

EINDEUTIG und SORTIEREN sind nur zwei von mehreren SPILL-Funktionen, bei denen mit nur einer Formel in mehreren Zellen Ergebnisse erzeugt werden. Weitere Beiträge zum SPILL-Verhalten gibt es hier im Blog über folgende URL: https://www.office-kompetenz.de/?s=spill