In meinen Blogbeiträgen vom 2.1.2020 und 14.4.2020 zeige ich, wie sich mit den neuen Array-Funktionen in Excel 365 Listen erzeugen lassen, die dynamisch gefiltert, sortiert und ohne doppelte Werte sind. Ohne VBA-Programmierung! Das Zauberwort heißt SPILL.
Was es damit auf sich hat, ist schnell erklärt: 35 Jahre lang galt in Excel, dass EINE Formel EINE Zelle mit einem Ergebnis füllt. Mit den neuen Array-Funktionen ändert sich das gründlich: nun kann eine Formel mehrere Zellen untereinander oder sogar mehrere Spalten nebeneinander mit Ergebnissen befüllen.
Mehr noch: selbst altbekannte Funktionen wie SUMMEWENN(S) oder ZÄHLENWENN(S) können jetzt mit nur einer Formel mehrere Zellen mit Ergebnissen liefern. Auch sie sind SPILL-fähig.
Bild 1: Sogar alte Excel-Funktionen beherrschen jetzt das SPILLING und liefern Ergebnisse für mehr als nur eine Zelle
So werden auch alte Excel-Funktionen SPILL-fähig
Die folgende Produktliste wurde mit EINDEUTIG und SORTIEREN erzeugt. Mit nur einer Formel werden alle Produkte in sortierter Abfolge untereinander aufgelistet.
Bild 2: Eine Formel reicht, um eine dynamisch sortierte Produktliste über mehrere Zeilen zu erzeugen
Nun soll neben den Produkten noch der jeweilige Gesamtumsatz angezeigt werden. Dies lässt sich mit SUMMEWENN erledigen. Allerdings müsste die SUMMEWENN-Formel jedes Mal angepasst werden, wenn in der Spalte links daneben Produkte hinzukommen.
Mit einem einfachen Trick wird auch die SUMMEWENN-Formel SPILL-fähig. Dazu ergänze ich das Argument für das Suchkriterium einfach um das #-Zeichen. Die Formel sieht dann wie folgt aus:
=SUMMEWENN(tbl_Verkauf[Produkt];I5#;tbl_Verkauf[Umsatz])
Bild 3: Das #-Zeichen am Ende des Suchkriteriums bewirkt, dass SUMMEWENN auch SPILL-fähig wird
Ausblick: Wie eine Produktliste nicht alphabetisch, sondern nach Umsatz sortiert wird
In einem kommenden Blogbeitrag zeige ich, wie die Produktliste per Formel so sortiert wird, dass die Produkte mit den höchsten Umsätzen automatisch ganz oben stehen.
Bild 4: Diesmal wird die Liste automatisch nicht nach Produkten, sondern nach Umsatz sortiert
Lust auf mehr zeitsparendes Wissen?
Wer mehr zu den Array-Funktionen wissen möchte und Auswertungen dynamisch und ohne lästige Anpassungen erstellen will, ist auf den Excel-Kompetenztagen in Fulda genau richtig. Hier gibt’s es in vier Kursreihen jede Menge Wissen, Techniken und Tipps rund um Excel.
Excel-Kompetenztage_2021_Kursplan
