Mein Excel-Kollege Andreas Thehos fragte letztens in einer Excel-Gruppe bei Facebook, ob jemand eine Idee hätte, wie zwei unabhängige Bereiche anhand einer Excel-Formel und dynamisch in einem Bereich zusammengefasst werden könnten.
Ich hatte Andreas einen Vorschlag gemacht, der auf den in neueren Excel-Versionen (Excel 365) verfügbaren dynamischen Arrays beruht und anschließend die Formel noch mithilfe der LET-Funktion vereinfacht. Andreas und ich haben dann anschließend gemeinsam die Formel noch verfeinert und auch Lösungen für mehrspaltige Tabellen gefunden. Beide Lösungen stelle ich in diesem Artikel vor.
Einspaltige Tabellen
Die Frage von Andreas bezog sich ursprünglich auf zwei Tabellen, die nur eine Spalte aufweisen. Die beiden Tabellen wurden respektive in Liste_1 (links) und Liste_2 (rechts) benannt. Beide Tabellen setzen natürlich ähnliche Daten voraus, hier beispielhaft die Vornamen einiger Personen.
Liste_1 weist in diesem Beispiel insgesamt 4 Einträge auf. Möchte man auf ein Element aus Liste_1 per Excel-Funktion zugreifen, bietet sich die INDEX-Funktion an. Beispielsweise, um das zweite Element von Liste_1 abzugreifen, schreiben wir die Formel =INDEX(Liste_1;2;1) in eine Zelle und erhalten als Ergebnis Nadja.
Möchten wir nun alle vier Einträge als dynamisches Array anzeigen lassen, gibt es verschiedene Möglichkeiten. Eine Möglichkeit ist, eine Liste der Positionen als Array an das zweite Argument der Excel-Funktion INDEX zu übergeben. Das erste Argument von INDEX erwartet die Angabe einer Matrix bzw. eines Bereichs und das dritte Argument die Spaltennummer. Das sieht dann wie folgt aus:
=INDEX(Liste_1;{1;2;3;4};1)
Der wesentliche Nachteil ist in obiger Formel jedoch, dass es nicht dynamisch ist. Würden wir Liste_1 um eine neue Zeile erweitern, müssten wir auch die Formel anpassen Zum Glück kennt aber Excel die Funktion SEQUENZ, die eine Folge von Zahlen als Array liefert. Passen wir die Formel etwas an:
=INDEX(Liste_1;SEQUENZ(4;1;1;1);1)
Das erste Argument von SEQUENZ entspricht der Zeilenanzahl – hier 4 – und die anderen Argumente respektive der Spaltenanzahl, Startwert und Schrittweite. Die letzten drei Argumente sind optional und können auch weggelassen werden. Vereinfacht, somit:
=INDEX(Liste_1;SEQUENZ(4);1)
Jetzt ist aber die Zahl 4 auch hier wiederum statisch und wir müssten die Formel anpassen, sobald ein neuen Eintrag der ersten Liste hinzufügt wird. Excel bietet jedoch zur Dynamisierung die Funktion ZEILEN an, die die Anzahl der Zeilen eines Bereichs ermittelt. Die Formel wird nun:
=INDEX(Liste_1;SEQUENZ(ZEILEN(Liste_1));1)
Damit wird nun die erste Liste dynamisch als Array ausgegeben. Analog können wir nun das Verfahren auf die zweiten Liste anwenden:
=INDEX(Liste_2;SEQUENZ(ZEILEN(Liste_2));1)
Jetzt müssen wir beide Listen zusammenführen. Beide Listen weisen zusammen insgesamt 6 Einträge aus. Eine Formel müsste somit aus insgesamt 6 Einträgen die 4 Einträge von Liste_1 und die 2 Einträge von Liste_2 nehmen. Das hört sich nach WENN-Funktion an. Bauen wir somit erstmal eine Pseudo-Wenn-Funktion auf:
=WENN(SEQUENZ(ZEILEN(Liste_1)+ZEILEN(Liste_2))<ZEILEN(Liste_1)+1;"Liste_1";"Liste_2")
Für den Fall, dass unser Zähler kleiner 5 ist, wird Liste_1 verwendet und wir können die entsprechende INDEX-Formel für Liste_1 nehmen. Ist der Zähler größer 4 nehmen wir Liste 2.
Für Liste_2 müssen wir allerdings beachten, dass wir die Anzahl der Zeilen von Liste_1 vom Zähler wieder abziehen. Denn der Zähler wäre ja 5 und 6, aber die Index-Formel für die zweite Liste muss wieder bei 1 beginnen. Die Gesamtformel sieht dann folglich so aus:
=WENN(SEQUENZ(ZEILEN(Liste_1)+ZEILEN(Liste_2))<ZEILEN(Liste_1)+1;
INDEX(Liste_1;SEQUENZ(ZEILEN(Liste_1)+ZEILEN(Liste_2));1);
INDEX(Liste_2;SEQUENZ(ZEILEN(Liste_1)+ZEILEN(Liste_2))-ZEILEN(Liste_1);1))
Wie zu sehen, ist die Formel schon recht kompliziert. Sofern die Excel-Funktion LET bereits verfügbar ist, lässt sich die Formel wesentlich eleganter schreiben. Definieren wir dazu die drei Variablen X, Y und Z, um die vorherige Formel etwas parametrisieren:
X = ZEILEN(Liste_1)
Y = ZEILEN(Liste_2)
Z = SEQUENZ(X+Y)
Unsere Formel lässt sich jetzt wie folgt schreiben und wird deutlich lesbarer:
=WENN(Z<X+1;INDEX(Liste_1;Z;1);INDEX(Liste_2;Z-X;1))
Verpacken wir das ganze nun in die Funktion LET. Wir erhalten:
=LET(X;ZEILEN(Liste_1);Y;ZEILEN(Liste_2);Z;SEQUENZ(X+Y);
WENN(Z<X+1;INDEX(Liste_1;Z;1);INDEX(Liste_2;Z-X;1)))
Damit ist das Problem für einspaltige Tabellen gelöst.
Es kann natürlich in Excel Situationen geben, wo keine intelligenten Tabellen verwendet werden können. Die Formel funktioniert jedoich auch mit benannten Bereichen, die wiederum ebenfalls dynamisch sein können. Angenommen, die einspaltigen Bereiche heißen Bereich_1 und Bereich_2, dann würde die Formel lauten:
=LET(X;ZEILEN(Bereich_1);Y;ZEILEN(Bereich_2);Z;SEQUENZ(X+Y);
WENN(Z<X+1;INDEX(Bereich_1;Z;1);INDEX(Bereich_2;Z-X;1)))
Mehrspaltige Tabellen
Nachdem Andreas und ich das Problem mit einer einspaltigen Liste gelöst hatten, kam sehr schnell die Frage auf, wie verhält es sich eigentlich mit mehrspaltigen Bereichen bzw. Tabellen?
Der Lösungsprozess war ziemlich spannend, denn einige Versuche unter der Verwendung der Funktion WAHL und weiteren Funktionen wollte nicht so recht klappen. Ich hatte dann die Idee, einen vierten Parameter in der LET-Funktion anzulegen: nämlich ein horizontales Array zu den Spalten und dieses an die INDEX-Funktion zu übergeben.
=LET(C;{1.2};X;ZEILEN(Liste_1);Y;ZEILEN(Liste_2);Z;SEQUENZ(X+Y);
WENN(Z<X+1;INDEX(Liste_1;Z;C);INDEX(Liste_2;Z-X;C)))
Daraufhin hatte Andreas wieder die Idee, aus dem Array zu den Spalten ein dynamisches Array zu machen und – na klar – wieder SEQUENZ einzusetzen. Zu beachten ist hier, dass das zweite Argument in der Funktion SEQUENZ die Spaltenanzahl angibt.
=LET(C;SEQUENZ(1;SPALTEN(Liste_1));X;ZEILEN(Liste_1);Y;ZEILEN(Liste_2);Z;SEQUENZ(X+Y);
WENN(Z<X+1;INDEX(Liste_1;Z;C);INDEX(Liste_2;Z-X;C)))
Somit erweitert sich das dynamische Array ganz automatisch und nicht nur, wenn neue Zeilen, sondern auch neue Spalten in beiden Tabellen hinzugefügt werden.
Andreas erzählt mir letztens noch, dass die Formel noch etwas weiter vereinfacht werden kann: und zwar ist INDEX(Liste_1;Z;C) gar nicht notwendig, sondern kann ganz einfach durch Liste_1 ersetzt werden.
Fazit
Wie an dieser Problemstellung zu sehen, eröffnen dynamische Arrays Möglichkeiten, die bisher in Excel anhand von Formeln entweder gar nicht oder nur mit viel Aufwand zu lösen waren. Die Funktion LET ermöglicht es zudem, neben einem Geschwindigkeitsvorteil, komplexere Formeln in besser lesbare und strukturierte Formeln umzuschreiben.
Das Problem hätte in diesem Beispiel auch anhand von PowerQuery oder gar VBA gelöst werden können. Der Reiz war jedoch sehr groß eine Formellösung zu finden. Danke Andreas, es hat viel Spass gemacht. Andreas hat übrigens auf seinem YouTube-Kanal auch ein Video zur einspaltigen Lösung und ein Video zur mehrspaltigen Lösung publiziert. Schaut mal rein.
Happy Exceling :-)
Ihr Beiden seid schon ‚Knaller‘