Kürzlich hatte ich dir ja schon ein paar neue Excel-Funktionen vorgestellt, die noch nicht der breiten Öffentlichkeit zur Verfügung stehen: SORTIERE, FILTER und EINDEUTIG. Falls du diesen spannenden Einblick in die nahe Zukunft verpasst hast, empfehle ich dir unbedingt, diesen Artikel nachzulesen: Ein Blick in die Zukunft: Dynamische Arrayfunktionen
Heute zeige ich zwei weitere interessante Array-Funktionen, die bald Einzug in die Excel-Welt finden dürften: SORTIERENNACH und SEQUENZ.
Und so geht’s:
SORTIERENNACH
Die im ersten Artikel hatte ich ja schon die neue SORTIEREN-Funktion vorgestellt. Eine vorhandene Liste automatisch und dynamisch per Tabellenfunktion sortieren zu lassen, ist schon ziemlich praktisch. Die sehr ähnliche Funktion SORTIERENNACH geht noch einen Schritt weiter: Mit ihr lässt sich eine Liste gleich nach mehreren Kriterien sortieren.
Als Beispiel habe ich wieder eine kleine Umsatzliste vorbereitet, die als Tabelle formatiert ist und den Namen tblUmsatz hat:
Die unsortierte Ausgangstabelle
Diese unsortierte Liste soll jetzt per Formel zuerst noch Region, dann nach Produkt und innerhalb des Produkts noch absteigend nach Umsatz sortiert werden. Was mit der SORTIERENNACH-Funktion ein Kinderspiel ist. Die allgemeine Syntax lautet:
=SORTIERENNACH(Matrix;NachMatrix1;Sortierreihenfolge1;NachMatrix2;Sortierreihenfolge2...)
Im Klartext heißt das, im ersten Argument gibt man die zu sortierende Tabelle an. Und dann kommen immer Zweierblöcke: zuerst die Spalte, nach der sortiert werden soll und dann noch die Reihenfolge (1 = aufsteigend, -1 = absteigend). Bezogen auf mein Beispiel sieht das dann so aus:
Das Ergebnis von SORTIERENNACH
Auch hier reicht es wieder, die Formel nur in einer Zelle einzutippen (bei mir in F1) und Excel ist schlau genug, die Formel in alle benötigten Zellen „überlaufen“ zu lassen. Und das ganze komplett dynamisch. Das heißt, wenn sich in der Quelltabelle etwas ändert oder neue Daten hinzukommen, passt sich die sortierte Tabelle automatisch an:
Die Sortierung passt sich dynamisch an
SEQUENZ
Diese etwas unscheinbare Funktion erzeugt eine beliebige fortlaufende Zahlensequenz. Im einfachsten Fall übergibt man nur einen Wert an und Excel erzeugt die entsprechend lange Liste:
Eine einfache Zahlensequenz
Das ist zugegeben noch ziemlich unspektakulär. Man kann allerdings auch ein paar zusätzliche optionale Argumente angeben:
=SEQUENZ(Zeilen;[Spalten];[Anfang];[Schritt])
Damit ließe sich beispielsweise eine Matrix von 10 x 10 Zellen erzeugen, die einen Startwert von 100 in Fünferschritten hochzählt:
Eine individuell abgestimmte Sequenz
Noch interessanter wird es, wenn man die SEQUENZ-Funktion mit anderen Tabellenfunktionen kombiniert. So lässt sich zusammen mit der DATUM-Funktion schnell ein kleiner Kalender zaubern:
=DATUM(2019;1;SEQUENZ(31;12;1))
Die Datumstabelle, jedoch noch nicht formatiert
Hier wird noch ein kleines Problem sichtbar: Die Formatierung passt nur in der ersten Zelle. Die anderen Zellen müssen von Hand auf das richtige Zahlenformat geändert werden.
Die fertige Datumstabelle korrekt formatiert
Und dass es sich hier um eine Array-Funktion handelt, sieht man schnell, wenn man SEQUENZ und SUMME miteinander kombiniert. So liefert die Formel…
=SUMME(SEQUENZ(100))
…als Ergebnis der Addition aller Zahlen von 1 bis 100:
SUMME und SEQUENZ in Kombination
Und das ganz ohne die umständliche Matrix-Tastenkombination Strg+Umschalt+Eingabe.
Noch steht nicht fest, wann genau diese neuen dynamischen Array-Funktionen in die normalen Office-365-Lizenzen ausgerollt werden. Absehbar ist jedoch, dass sie vieles von dem, was wir bisher aus Excel kennen, auf den Kopf stellen werden. Denn gerade die Kombination dieser neuen Array-Funktionen mit vorhandenen Tabellenfunktionen wird vieles auf einfache Weise ermöglichen, was bisher nur mit komplizierten Kniffen oder eben gar nicht möglich war.
Das soll es erst einmal gewesen sein mit dem Ausblick in die (nahe?) Excel-Zukunft. Beim nächsten Mal gibt es wieder ein paar handfeste Tipps aus dem Hier und Jetzt!
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Die Sortieren-Funktion wäre jetzt genau das, was mich retten würde. Suche mir gerade einen Wolf, wie ich in Excel 2016 einen definierten Bereich übernehmen kann. Vielleicht hast du da eine Idee:
Per einiger Funktionen kann ich den Bereich berechnen, welchen ich übernehmen will (also z.B. Z32S2 bis Z100S25). Diese Bereiche ändern sich je nach Input, weshalb ich sie von Excel automatisch finden lasse. Jetzt bräuchte ich ein Tabellenblatt, in welches Excel automatisch eben diesen Bereich kopiert. Nicht mehr und nicht weniger. Und das ohne VBA.
Hallo Tim,
einen sich dynamisch verändernden Bereich an anderer Stelle anzuzeigen, wird ohne VBA schwierig bis unmöglich. Zumindest mir fällt dazu keine Lösung ein.
Schöne Grüße,
Martin
Klasse erklärt, danke für die Mühe!
Gern geschehen!
Schöne Grüße,
Martin