Die nächste Array-Funktion, die ich mir überlegt habe, ist ARRAY.CREATE, die ein dynamisches Array erzeugen soll. Hierbei soll es möglich sein, das erzeugte Array mit Werten pro Spalte vorzubelegen. Auch diese benutzerdefinierte Funktion basiert auf der Excel LAMBDA-Funktion. Eine Einführung zu der LAMBDA-Funktion ist hier zu finden.
Anforderungen
Die Funktion soll anhand der Angabe einer Anzahl von Zeilen und Spalten sowie eines Standardwertes ein dynamisches Array entsprechender Größe erstellen und hierbei die Werte innerhalb des Arrays vorbelegen. Und es soll möglich sein, den Standardwert entweder als Text bzw. Bezug auf eine Zelle oder als einzeilige Matrix mit den Standardwerten pro Spalte angeben zu können. Die Syntax der Funktion wäre somit:
=ARRAY.CREATE(Rows;Columns;Defaults)
Einige Anwendungsbeispiele wären dann:
=ARRAY.CREATE(4;3;"Text")
=ARRAY.CREATE(4;3;{"A"."B"."C")
=ARRAY.CREATE(4;3;MTRANS(A1:A3))
ARRAY.CREATE
Aus den Anforderungen ergibt sich, dass drei Eingangsparameter benötigt werden. Die Funktion LET ermöglicht es, diese als Variablen zu definieren sowie auch Zwischenberechnungen durchzuführen. Gut wäre es, wenn die Angabe zu den Standardwerten möglichst flexibel wäre, was dann aber auch einige Validierungen beinhalten würde. Und, um die Matrix zu generieren, werden z.B. zur besseren Lesbarkeit Hilfsvariablen verwendet. Eine Pseudoformel mit LET könnte somit wie folgt aussehen:
=LET(Rows;5;
Columns;3;
Defaults;{"A"."B"."C"};
D;Hilfsvariable für Defaults mit Validierung;
X;Hilfsvariable für Spalten;
Y;Hilfsvariable für Zeilen;
Berechnung vom Ergebnis)
Das Beispiel definiert hier die drei Eingangsvariablen Rows (Zeilen), Columns (Spalten) und Defaults (Standardwerte) sowie die drei Hilfsvariablen D, X und Y. Als Ergebnis sollte eine Matrix mit 5 Zeilen und 3 Spalten erzeugt werden, wo die Einträge in den einzelnen Spalten jeweils mit A, B und C vorbelegt sind.
Schauen wir uns zunächst X und Y an. Mit Hilfe der Funktion SEQUENZ lassen sich Zahlenreihen als dynamisches Array erzeugen. Somit lassen sich X und Y schreiben als:
X=SEQUENZ(1;Columns)
Y=SEQUENZ(Rows;1;1;0)
Das durch X erzeugte dynamische Array beinhaltet dann die Zahlenreihe {1.2.3} als waagerechtes Array. Und das durch Y erzeugte dynamische Array beinhaltet die Zahlenreihe {1;1;1;1;1} als senkrechtes Array, da die Schrittweite auf Null gesetzt wurde. Werden X und Y nun multipliziert, ergibt sich die folgend abgebildete Matrix.
Die erzeugte Matrix beinhaltet pro Spalte genau die Positionen des Arrays zu den Standardwerten, die sich dann anhand der INDEX-Funktion aus dem Array zu den Standardwerten extrahieren lassen. Die LET-Formel kann somit auf folgende Version erweitert werden:
=LET(Rows;5;
Columns;3;
Defaults;{"A"."B"."C"};
D;Defaults;
X;SEQUENZ(1;Columns);
Y;SEQUENZ(Rows;1;1;0);
WENNFEHLER(INDEX(D;1;X*Y);""))
Die Formel erzeugt nun das erwartete Ergebnis. Das funktioniert jedoch nur solange wie für die Standardwerte auch ein Array angegeben wurde. Wird statt {"A"."B"."C"} ein Text, wie z.B. "Test" als Standardwert angegeben, wird nur die erste Spalte im erzeugten Array mit dem Standardwert belegt und die restlichen bleiben leer.
Von daher muss in einem solchen Fall die statische Angabe zum Standardwert in eine Matrix bzw. Array umgewandelt werden. Dies kann durch folgende Formel erreicht werden:
D=WENN(SPALTEN(Defaults)>1;
INDEX(Defaults;1;0);
LINKS(INDEX(Defaults;1;1);
SEQUENZ(1;Columns;LÄNGE(INDEX(Defaults;1;1));0)))
Die Formel prüft, ob die Variable Defaults mehr als eine Spalte enthält. Wenn ja, handelt es sich um ein Array und es wird die erste Zeile aus diesem Bereich extrahiert sowie alle weiteren, eventuell vorhandenen, Zeilen ignoriert. Dies entspricht dem Beispielfall von hier weiter oben.
Enthält die Angabe von Defaults jedoch nur eine Spalte, kann es sich nur um eine Einzelzelle oder einen einspaltigen Bereich handeln. Da wir jedoch keine einspaltigen Bereiche berücksichtigen möchten, greifen wir über INDEX(Defaults;1;1) grundsätzlich auf die erste Zelle zu. Das funktioniert sowohl für statische Texte, Bezüge auf Einzelzellen oder Bezüge auf einspaltige Bereiche.
Anschließend erzeugen wir mit diesem Wert ein horizontales Array über die Funktion SEQUENZ. Das Array enthält 3 Spalten (der Wert von Columns) und füllt das Array mit der Länge des abgegriffenen Standardwertes auf. Wäre der Standardwert z.B. "Test", würde das Array dreimal die Zahl 4 beinhalten. Die Funktion LINKS macht nichts anderes als dann den Standardwert zu wiederholen, dies dann aber dreimal, da die Anzahl an Zeichen als Array übergeben wurde. Als Ergebnis würde D nun den Wert {"Test"."Test"."Test"} beinhalten.
Bauen wir die Formel zu D in die LET-Formel ein, erhalten wir:
=LET(Rows;5;
Columns;3;
Defaults;{"A"."B"."C"};
D;WENN(SPALTEN(Defaults)>1;
INDEX(Defaults;1;0);
LINKS(INDEX(Defaults;1;1);
SEQUENZ(1;Columns;
LÄNGE(INDEX(Defaults;1;1));0)));
X;SEQUENZ(1;Columns);
Y;SEQUENZ(Rows;1;1;0);
WENNFEHLER(INDEX(D;1;X*Y);""))
Der nächste Schritt zur Erstellung der benutzerdefinierten Funktion ist nicht mehr schwer. Wir kapseln die Formel in eine LAMBDA-Funktion, erstellen im Namensmanager den Namen ARRAY.CREATE und weisen dem Namen die LAMBDA-Funktion zu. Die LAMBDA-Funktion sieht wie folgt aus:
=LAMBDA(Rows;Columns;Defaults;
LET(D;WENN(SPALTEN(Defaults)>>;
INDEX(Defaults;1;0);
LINKS(INDEX(Defaults;1;1);
SEQUENZ(1;Columns;
LÄNGE(INDEX(Defaults;1;1));0)));
X;SEQUENZ(1;Columns);
Y;SEQUENZ(Rows;1;1;0);
WENNFEHLER(INDEX(D;1;X*Y);"")))
Es sei noch angemerkt, dass meine Funktionen englische Namen haben, was natürlich kein Muss ist. Es können selbstverständlich auch deutsche Namen und Argumentbezeichner verwendet werden. Einzige Anforderung ist, dass diese den Excel-Regeln folgen.
Fazit
Mit diesem Artikel stehen nun vier neue Funktionen in Excel zur Verfügung. ARRAY.CREATE sowie ARRAY.REMOVE.FIRSTN, ARRAY.REMOVE.LASTN und ARRAY.REVERSE aus vorangegangen Artikeln.
Happy Exceling :-)
