Derzeit teste ich einige LAMBDA-Funktionen, die es ermöglichen sollen, in Excel rein formelbasierte Beechnungen mit sehr großen Zahlen durchzuführen zu können, wie beispielsweise eine Addition oder Multiplikation solcher Zahlen. Bislang sind solche Berechnungen nur über Add-Ins oder VBA möglich.
Um dem vorgenannten Ziel näher zu kommen, habe ich bereits einige Array-Hilfsfunktionen, wie z.B. ARRAY.FROMTEXT.EX oder ARRAY.REVERSE entwickelt und auch hier im Blog vorgestellt. ARRAY.PUSH und ARRAY.PULL sind zwei weitere Hilfsfunktionen, die dazu dienen die Elemente eines Bereichs verschieben zu können.
Anforderungen
Die Funktion ARRAY.PUSH soll die Werte eines Bereichs um eine Position nach unten verschieben und die erste Zeile mit Standardwerten belegen. Das Array wird dabei nicht vergrößert, was denn heißt, dass das letzte Element aus dem Array verschwindet. ARRAY.PULL soll dann in gegensätzlicher Richtung die Werte eines Bereichs um eine Position nach oben verschieben und die Werte in der letzten Zeile mit den übergebenen Standardwerten belegen. Die Syntax ist recht simpel:
=ARRAY.PUSH(Array;Defaults)
=ARRAY.PULL(Array;Defaults)
Bezogen auf die oben aufgeführte beispielhafte Abbildung lauten die Formel dann:
D2=ARRAY.PUSH(A2:C9;{"Z";99;999})
G2=ARRAY.PULL(A2:C9;{"Z";99;999})
ARRAY.PUSH
Die Syntax definiert die beiden Eingangsvariablen Array und Defaults. Es erscheint sinnvoll, eine Überprüfung von Defaults in einer Hilfsvariable vorzunehmen sowie zwei Variablen als Laufvariablen zu definieren, weshalb eine Pseudoformel mit Hilfe der Funktion LET wie folgt aussieht:
=LET(Array;A2:C9;
Defaults;{"Z";99;999};
D;Hilfsvariable zur Validierung der Standardwerte;
X;Hilfsvariable für Spalten;
Y;Hilfsvariable für Zeilen;
Berechnung vom Ergebnis)
Die Formel zur Überprüfung der in Defaults übergebenen Standardwerte lautet:
D=WENN(SPALTEN(Defaults)>1;INDEX(Defaults;1;0);MTRANS(Defaults))
Da das Array um eine Position nach unten verschoben werden soll, also zeilenweise, sollten die Standardwerte auch nur aus einer Zeile bestehen. Gibt der User für die Standardwerte einen Bereich mit mehreren Spalten und Zeilen an, wird nur die erste Zeile dieses Bereichs verwendet. Das funktioniert natürlich auch, wenn nur ein einzeiliger Bereich angegeben wird. Referenziert der User hingegen einen einspaltigen Bereich – wie z.B. B1:B3 – für die Standardwerte, transponieren wir diesen Bereich.
Kommen wir nun den Variablen X und Y, deren Formeln jeweils zwei Zahlenreihen bilden. Angenommen unser Eingangsbereich hat, wie oben abgebildet, 3 Spalten und 8 Zeilen. Dann erzeugt X eine eine Zahlenreihe von 1 bis 3 und Y eine Zahlenreihe von 0 bis 7 anhand folgender Formeln:
X=SEQUENZ(1;SPALTEN(Array))
Y=SEQUENZ(ZEILEN(Array);1;0)
Das bedeutet, dass der Zeilenindex um eine Position nach unten versetzt wird. Wird das Array mit Y durchlaufen, wandern alle Werte einer Zeile in die nächste Zeile. Die letzte Zeile entfällt dann. Zudem lässt sich Y mit dem Wert gleich Null abfangen und die Standardwerte einsetzen. Die Formel für die Berechnung lautet dann:
WENN(Y<1;WENNFEHLER(INDEX(D;1;X);"");INDEX(Array;Y;X))
Enthält das Array mit den Standardwerten weniger Spalten als das Array mit den Daten, fangen wir das ab, indem wir den Wert auf eine leere Zeichenkette setzen.
Das war’s auch schon. Die vollständige LET-Formel lautet nun:
=LET(Array;A2:C9;
Defaults;{"Z";99;999};
D;WENN(SPALTEN(Defaults)>1;INDEX(Defaults;1;0);MTRANS(Defaults));
X;SEQUENZ(1;SPALTEN(Array));
Y;SEQUENZ(ZEILEN(Array);1;0);
WENN(Y<1;WENNFEHLER(INDEX(D;1;X);"");
INDEX(Array;Y;X)))
Hieraus lässt sich dann die LAMBDA-Funktion erstellen, die im Namensmanager unter dem Namen ARRAY.PUSH abgelegt wird.
=LAMBDA(Array;Defaults;
LET(D;WENN(SPALTEN(Defaults)>1;INDEX(Defaults;1;0);MTRANS(Defaults));
X;SEQUENZ(1;SPALTEN(Array));
Y;SEQUENZ(ZEILEN(Array);1;0);
WENN(Y<1;WENNFEHLER(INDEX(D;1;X);"");
INDEX(Array;Y;X))))
ARRAY.PULL
Die Formel für ARRAY.PULL ist zu der Formel für ARRAY.PUSH sehr ähnlich. Auch hier überprüfen wir die Angaben zu den Standardwerten und legen die zwei Zählvariablen X und Y fest. Der Unterschied ist hier, dass Y nicht mit 0 beginnt, sondern mit 2.
D=WENN(SPALTEN(Defaults)>1;INDEX(Defaults;1;0);MTRANS(Defaults))
X=SEQUENZ(1;SPALTEN(Array))
Y=SEQUENZ(ZEILEN(Array);1;2)
Dadurch werden alle Einträge im Array um eine Position nach oben gezogen. Bei der Berechnung fragen wir dann nicht ab, ob Y kleiner 1 ist, sondern ob Y größer der Zeilenanzahl im Array ist.
WENN(Y>ZEILEN(Array);WENNFEHLER(INDEX(D;1;X);"");INDEX(Array;Y;X))
Die LAMBDA-Funktion für ARRAY.PULL lautet dann:
=LAMBDA(Array;Defaults;
LET(D;WENN(SPALTEN(Defaults)>1;INDEX(Defaults;1;0);MTRANS(Defaults));
X;SEQUENZ(1;SPALTEN(Array));
Y;SEQUENZ(ZEILEN(Array);1;2);
WENN(Y>ZEILEN(Array);
WENNFEHLER(INDEX(D;1;X);"");
INDEX(Array;Y;X))))
Fazit
ARRAY.PUSH und ARRAY.PULL ergänzen meine Sammlung an LAMBDA-Array-Funktion um zwei weitere Hilfsfunktionen. ARRAY.PUSH wird später z.B. bei einer Formel zur Addition sehr großer Zahlen in Excel zum Einsatz kommen.
Happy Exceling :-)
