Eine weitere Array-Funktion, die ich hier vorstellen möchte, ist ARRAY.APPEND, die zwei Bereiche zu einem Bereich zusammenfassen soll und das Ergebnis als dynamisches Array zurückliefert. Die Idee basiert auf meinen Artikel Zwei Bereiche per Formel zusammenführen, erweitert jedoch die im Artikel gezeigte Funktionalität um die Berücksichtigung zwei unterschiedlich breiter Bereiche.
Wie in meinen vorangegangen Artikeln zu neuen Array-Funktionen, basisert auch diese Array-Funktion auf der in Excel 365 kürzlich eingeführten LAMBDA-Funktion. Eine Einführung zu der LAMBDA-Funktion ist hier zu finden.
Anforderungen
Die Funktion soll zwei unabhängige Bereiche untereinander als dynamisches Array zurückliefern. Hierbei können die zwei Eingangsbereiche eine unterschiedliche Anzahl an Spalten aufweisen. Und, der Bereich, der weniger Spalten aufweist, soll mit Standardwerten aufgefüllt werden, was dann bedeutet, dass es möglich sein sollte, die Standardwerte auch als Matrix zu übergeben. Die Syntax der Funktion wäre somit:
=ARRAY.APPEND(Array1;Array2;Defaults)
Einige Anwendungsbeispiele wären dann:
=ARRAY.APPEND($A$2:$C$9;$D$2:$E$4;{"-".0.0})
=ARRAY.APPEND($A$2:$C$9;$D$2:$E$4;"-")
=ARRAY.APPEND($A$2:$C$9;$D$2:$E$4;MTRANS(A1:A3))
ARRAY.APPEND
Die Funktion LET ermöglicht es, die drei Eingangsparameter Array1 (erster Bereich), Array2 (zweiter Bereich) und Defaults (Standardwerte) als Variablen zu definieren. Es erscheint auch sinnvoll, einige Zwischenberechnungen durchzuführen, bevor die Berechnung des Gesamtergebnisses erfolgt. Eine Pseudoformel mit LET könnte somit wie folgt aussehen:
=LET(Array1;A2:C9;
Array2;D2:E4;
Defaults;"-";
R;Hilfsvariable für die Anzahl der Zeilen;
C;Hilfsvariable für die Anzahl der Spalten;
D;Hilfsvariable für die Standardwerte;
X;Hilfsvariable für die Sequenz an Zeilen;
Y;Hilfsvariable für die Sequenz an Spalten;
Berechnung vom Ergebnis)
Die Variablen R und C definieren zwei Hilfsvariablen, die die Anzahl der Zeilen und Spalten des Ergebnisbereichs berechnen sollen. Dies ist recht einfach und sieht wie folgt aus:
R=ZEILEN(Array1)+ZEILEN(Array2)
C=MAX(SPALTEN(Array1);SPALTEN(Array2)))
Die Anzahl der Zeilen ist die Summe der Zeilenanzahl der jeweiligen Bereiche und die Anzahl der Spalte entspricht der höchsten Spaltenanzahl der zwei Bereiche.
Die Variable D ist vorgesehen, eine Überprüfung Von Defaults vorzunehmen. Und zwar, ob es sich bei Default um einen Einzelwert oder eine waagerechte Matrix bzw. Array handelt. Dies kann durch folgende Formel erreicht werden:
D=WENN(SPALTEN(Defaults)>1;
INDEX(Defaults;1;0);
LINKS(INDEX(Defaults;1;1);
SEQUENZ(1;C;LÄNGE(INDEX(Defaults;1;1));0)))
Die Formel prüft zunächst, ob die Anzahl der Spalten in Default größer als 1 ist. Wenn ja, dann handelt es sich um eine Matrix und es wird über die Funktion INDEX deren erste Zeile extrahiert. Wenn nein, dann handelt es sich um einen Einzelwert oder gegebenenfalls einen einspaltigen Bereich. Im letzteren Fall benötigen aber nur die erste Zelle, weshalb INDEX(Defaults;1;1) verwendet wird. Das funktioniert auch für einen Einzelwert, weshalb keine weitere Prüfung notwendig ist. Die Kombination der Funktionen LINKS und SEQUENZ erzeugt aus dem Einzelwert wiederum eine Matrix, deren Werte alle den Wert des Einzelwertes übernehmen. Bei drei Spalten und dem Standardwert "Text" ergäbe das dann {"Text"."Text"."Text"}.
Schauen wir nun auf X und Y. Mit Hilfe der Funktion SEQUENZ lassen sich Zahlenreihen als dynamisches Array erzeugen. Basierend auf der Berechnung von R und C lassen sich X und Y schreiben als:
X=SEQUENZ(1;C)
Y=SEQUENZ(R)
X erzeugt somit ein Array von R Zeilen {1;2; … ;R} und Y ein Array von C Spalten {1.2. … C}. Damit haben wir nun die Berechnung der Hilfsvariablen abgeschlossen. Kommen wir also zur Berechnung des Endergebnisses. Die Formel hierfür lautet wie folgt:
=WENN(Y<ZEILEN(Array1)+1;
WENNFEHLER(INDEX(Array1;Y;X);
WENNFEHLER(INDEX(D;1;X);""));
WENNFEHLER(INDEX(Array2;Y-ZEILEN(Array1);X);
WENNFEHLER(INDEX(D;1;X);"")))
Was macht die Formel?
In einem ersten Schritt wird geprüft, ob Y – also der Zähler in Zeilenrichtung – kleiner ist als die Anzahl der Zeilen im ersten Bereich. Wenn ja, dann wird über die Funktion INDEX pro Zeile (Y) und Spalte (X) der Wert an der entsprechenden Position im ersten Bereich extrahiert. Tritt ein Fehler auf – nämlich dann, wenn der Bereich weniger als X Spalten hat – wird der Standardwert an der Position X genommen. Tritt auch hier ein Fehler auf, wird der Wert leer gelassen.
Ist der Zähler Y größer als die Anzahl der Zeilen im ersten Bereich, sollen die Zeilen aus dem zweiten Bereich adressiert werden. Die Funktionsweise ist ähnlich zu der für den ersten Bereich, mit dem Unterschied, dass dem Zähler Y die Anzahl der Zeilen aus dem ersten Bereich abgezogen werden muss, damit Y wieder bei 1 beginnt.
Damit ist nun die LET-Formel vollständig:
=LET(Array1;A2:C9;
Array2;D2:E4;
Defaults;"-";
R;ZEILEN(Array1)+ZEILEN(Array2);
C;MAX(SPALTEN(Array1);SPALTEN(Array2));
D;WENN(SPALTEN(Defaults)>1;
INDEX(Defaults;1;0);
LINKS(INDEX(Defaults;1;1);
SEQUENZ(1;C;
LÄNGE(INDEX(Defaults;1;1));0)));
X;SEQUENZ(1;C);
Y;SEQUENZ(R);
WENN(Y<ZEILEN(Array1)+1;
WENNFEHLER(INDEX(Array1;Y;X);
WENNFEHLER(INDEX(D;1;X);""));
WENNFEHLER(INDEX(Array2;Y-ZEILEN(Array1);X);
WENNFEHLER(INDEX(D;1;X);""))))
Der abschließend Schritt zur Erstellung der benutzerdefinierten Funktion ist nicht mehr schwer. Wir kapseln die Formel in eine LAMBDA-Funktion, erstellen im Namensmanager den Namen ARRAY.APPEND und weisen dem Namen die LAMBDA-Funktion zu. Die LAMBDA-Funktion sieht wie folgt aus:
=LAMBDA(Array1;Array2;Defaults;
LET(R;ZEILEN(Array1)+ZEILEN(Array2);
C;MAX(SPALTEN(Array1);SPALTEN(Array2));
D;WENN(SPALTEN(Defaults)>1;
INDEX(Defaults;1;0);
LINKS(INDEX(Defaults;1;1);
SEQUENZ(1;C;
LÄNGE(INDEX(Defaults;1;1));0)));
X;SEQUENZ(1;C);
Y;SEQUENZ(R);
WENN(Y<ZEILEN(Array1)+1;
WENNFEHLER(INDEX(Array1;Y;X);
WENNFEHLER(INDEX(D;1;X);""));
WENNFEHLER(INDEX(Array2;Y-ZEILEN(Array1);X);
WENNFEHLER(INDEX(D;1;X);""))))
Die Funktion LET definiert in diesem Beispiel die Hilfsvariablen R,C, D, X und Y. Es besteht natürlich keine Verpflichtung, dies genauso zu machen, wie hier gezeigt. Beispielweise könnten die Variablen R und C auch direkt in den weiteren Variablen verwendet werden. Excel setzt hier keine speziellen Vorgaben.
Fazit
ARRAY.APPEND ergänzt nun als fünfte Array-Funktion die bereits erstellten Array-Funktionen ARRAY.CREATE, ARRAY.REMOVE.FIRSTN, ARRAY.REMOVE.LASTN und ARRAY.REVERSE aus vorangegangen Artikeln.
Happy Exceling :-)
