(C) 2009 - 2021 by Mourad Louha · Alle Rechte vorbehalten

Zwei Bereiche per Formel zusammenführen

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.

Zwei Bereiche per Formel zusammenführen

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.

Zwei einspaltige Tabellen per Formel zusammenführen

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)

Elemente einer Liste dynamisch ausgeben

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")

Pseudo-Wenn-Funktion zur Ausgabe beider Listen

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)))

Zwei mehrspaltige Tabellen per Formel zusammenführen

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 :-)

 

 
Comments
Stefan Kleynemeyer

Ihr Beiden seid schon ‚Knaller‘

Hi Stefan

herzlichen Dank :-)

Gruß,
Mourad

Hi there,

as I guess, that some english speaking readers will reach this site through the Excel Blog from the MS TechCommunity and online translators are not able to translate formulas correctly, I am listing the formula translations below:

The formula using INDEX and SEQUENCE:

=INDEX(Liste_1,SEQUENCE(ROWS(Liste_1)),1)

The pseudo-formula using IF:

=IF(SEQUENCE(ROWS(Liste_1)+ROWS(Liste_2))<ROWS(Liste_1)+1,"Liste_1","Liste_2")

The full formula using IF:

=IF(SEQUENCE(ROWS(Liste_1)+ROWS(Liste_2))<ROWS(Liste_1)+1,
 INDEX(Liste_1,SEQUENCE(ROWS(Liste_1)+ROWS(Liste_2)),1),
 INDEX(Liste_2,SEQUENCE(ROWS(Liste_1)+ROWS(Liste_2))-ROWS(Liste_1),1))

The formula using LET

=LET(X,ROWS(Liste_1),Y,ROWS(Liste_2),Z,SEQUENCE(X+Y),
 IF(Z<X+1,INDEX(Liste_1,Z,1),INDEX(Liste_2,Z-X,1)))

The formula for multicolumn tables using LET:

=LET(C,SEQUENCE(1,COLUMNS(Liste_1)),X,ROWS(Liste_1),Y,ROWS(Liste_2),Z,SEQUENCE(X+Y),
 IF(Z<X+1,INDEX(Liste_1,Z,C),INDEX(Liste_2,Z-X,C)))

If you need to translate the formulas into other languages, such as e.g. French, Spanish or Italian, please have a look on my Online Excel Formula Translator.

 

Ausgezeichnetes Dokument. Vielen Dank für Ihre Arbeit.

Hallo Iván

Vielen Dank! – Many thanks! :-)

Mourad

Hi,
I tracked this down after getting an answer to a very similar problem on another website and used your clear explanations to create a better solution for myself
the post is here:
https://www.myonlinetraininghub.com/excel-forum/excel/unique-values-from-two-sources

Many thanks,

jim

Hi Jim,

thank you very much :-) Glad, that my post contributed to your solution.

Best.
Mourad

Neat! Also works nicely to aggregate dynamic array formulas instead of tables.
=LET(C;SEQUENZ(1;SPALTEN(B3#));X;ZEILEN(B3#);Y;ZEILEN(E3#);Z;SEQUENZ(X+Y); WENN(Z<X+1;B3#;INDEX(E3#;Z-X;C)))

Hi Udo,

thanks, that’s awesome :-)

Best,
Mourad

Ousseyni Sawadogo

Besten Dank Euch beiden! Gott segne Euch!

Hey Mourad, vielen Dank für das tolle Tutorial!

Gibt es eine Möglichkeit, die Formel so zu erweitern, dass noch eine dritte (oder beliebig viele andere Tabellen) mit in die dynamische Konsolidierung einbezogen werden können? Leider fehlt mir scheinbar komplett der logische Durchblick und meine Versuche, die Formel entsprechend anzupassen, sind bisher allesamt gescheitert… :(

Viele Grüße
Ray

Mourad Louha

Hallo Ray,

ganz dynamisch für eine beliebige Anzahl an Tabellen dürfte schwierig werden. Bei drei Tabellen könnte die Formel wie folgt aussehen:

=LET(vnSpalten;SEQUENZ(1;SPALTEN(Liste_1));
     vnZeilen1;ZEILEN(Liste_1);
     vnZeilen2;ZEILEN(Liste_2);
     vnZeilen3;ZEILEN(Liste_3);
     vnMatrix;SEQUENZ(vnZeilen1+vnZeilen2+vnZeilen3);
     WENN(vnMatrix < vnZeilen1+1;INDEX(Liste_1;vnMatrix;vnSpalten);
     WENN(vnMatrix < vnZeilen1+vnZeilen2+1;
     INDEX(Liste_2;vnMatrix-vnZeilen1;vnSpalten);
     INDEX(Liste_3;vnMatrix-vnZeilen2-vnZeilen1;vnSpalten))))

Das Prinzip ist die Intervalle für die Werte von SEQUENZ (1 … N) jeweils den Intervallen zuzuordnen, die sich aus der Anzahl der Zeilen pro Tabelle ergeben

Viele Grüße,
Mourad

Gunnar Lorentzen

Sehr elegant. Vielen Dank dafür.

Hallo Gunnar,

vielen Dank. Freut mich.

Viele Grüße,
Mourad