Dieser zweite Artikel zu meinen benutzerdefinierten Array-Funktionen stellt die zwei Funktionen ARRAY.REMOVE.FIRSTN und ARRAY.REMOVE.LASTN vor, die respektive von oben und von unten Einträge aus einem Array entfernen. Die Idee habe ich mir übrigens von den M-Funktionen in PowerQuery abgeguckt, wo die zwei Funktionen List.RemoveFirstN und List.RemoveLastN zur Verfügung stehen. Wäre auch mal interessant weitere Funktionen der Art in Excel zu implementieren.
Meine beiden benutzerdefinierten Funktionen basieren auf der Excel LAMBDA-Funktion. Eine Einführung zu der LAMBDA-Funktion ist hier zu finden.
Anforderungen
Beide Funktionen sollen jeweils eine anzugebende Zahl an Einträgen aus dem übergebenen Array bzw. Bereich entfernen und ein entsprechend gekürztes dynamisches Array zurückgeben. Die Funktion ARRAY.REMOVE.FIRSTN soll hierbei N Elemente am Anfang des Array entfernen und ARRAY.REMOVE.LASTN dann N Elemente am Ende des Array. Somit sind je zwei Parameter für die Funktionen erforderlich: der Bereich und die Anzahl der Elemente. Die Syntax wäre somit:
=ARRAY.REMOVE.FIRSTN(Array; Count)
=ARRAY.REMOVE.LASTN(Array; Count)
Gut wäre es wenn die angegebene Anzahl der Elemente auch insoweit überprüft würde, ob sich diese auch innerhalb der Anzahl von Zeilen des Arrays bewegt. Falls nicht, soll der Wert auf Null gesetzt werden und nichts passieren. Angenommen, die Zeilenanzahl des Arrays bzw. Bereichs ist mit R angegeben, lässt sich das dann mit folgender Formel lösen:
=WENN(ISTZAHL(Count);WENN(UND(Count>0;Count<R);Count;0);0)
ARRAY.REMOVE.FIRSTN
Schreiben wir die Formel als LET-Funktion, sieht diese wie folgt aus:
=LET(Array;$A$2:$C$9;
Count;4;
R;ZEILEN(Array);
C;SPALTEN(Array);
N;WENN(ISTZAHL(Count);WENN(UND(Count>0;Count<R);Count;0);0);
X;SEQUENZ(1;C);
Y;SEQUENZ(R-N;1;N+1);
INDEX(Array;Y;X))
Was passiert in der Formel?
Klar, die beiden Variablen Array und Count geben den Bereich an und die Anzahl der zu entfernenden Elemente. Die beiden Hilfsvariablen R und C ermitteln die Höhe und Breite des Bereichs. Diese Variablen sind zwar nicht zwingend notwendig, dienen aber der Übersichtlichkeit. Die Variable N führt die Überprüfung durch, ob die Anzahl der zu entfernenden Zeilen valide ist. X und Y erzeugen jeweils ein Array von Indizes, um anschließend in der INDEX-Funktion verwendet zu werden und die Einträge aus dem Bereich zu extrahieren. Zu beachten ist jedoch, dass die von Y generierte Zahlenreihe nicht bei 1 anfängt, sondern ab dem Element, das der Anzahl der zu entfernenden Elemente folgt. In unserem Beispiel erzeugt Y dann die Zahlenreihe {5;6;7;8}.
Eine benutzerdefinierte Funktion aus der Formel zu erstellen ist nun recht einfach: wir kapseln die Formel in die LAMBDA-Funktion. Dies sieht dann wie folgt aus:
=LAMBDA(Array;Count;
LET(R;ZEILEN(Array);
C;SPALTEN(Array);
N;WENN(ISTZAHL(Count);WENN(UND(Count>0;Count<R);Count;0);0);
X;SEQUENZ(1;C);
Y;SEQUENZ(R-N;1;N+1);
INDEX(Array;Y;X)))
Es verbleibt noch im Namensmanager den Namen ARRAY.REMOVE.FIRSTN anzulegen und diesem die Formel mit der LAMBDA-Funktion zuzuweisen.
ARRAY.REMOVE.LASTN
Ähnlich zu der Formel für ARRAY.REMOVE.FIRSTN verhält es sich mit der Formel für ARRAY.REMOVE.LASTN. Die LET-Funktion sieht in diesem Fall wie folgt aus:
=LET(Array;$A$2:$C$9;
Count;4;
R;ZEILEN(Array);
C;SPALTEN(Array);
N;WENN(ISTZAHL(Count);WENN(UND(Count>0;Count<R);Count;0);0);
X;SEQUENZ(1;C);
Y;SEQUENZ(R-N);
INDEX(Array;Y;X))
Der einzige Unterschied zur Formel für ARRAY.REMOVE.FIRSTN besteht darin, dass die für Y generierte Zahlenreihe bis zum letzten Zeile des Bereichs abzüglich der zu entfernenden Elemente geht. In diesem Beispiel ergibt Y dann die Reihe {1;2;3;4}.
Schreiben wir wieder die Formel als LAMBDA-Funktion, erhalten wir:
=LAMBDA(Array;Count;
LET(R;ZEILEN(Array);
C;SPALTEN(Array);
N;WENN(ISTZAHL(Count);WENN(UND(Count>0;Count<R);Count;0);0);
X;SEQUENZ(1;C);
Y;SEQUENZ(R-N);
INDEX(Array;Y;X)))
Es verbleibt dann auch hier Namensmanager den Namen ARRAY.REMOVE.LASTN anzulegen und diesem die Formel mit der LAMBDA-Funktion zuzuweisen.
Fazit
Mit diesem Artikel stehen mir dann drei neue Funktionen in Excel zur Verfügung: ARRAY.REMOVE.FIRSTN, ARRAY.REMOVE.LASTN und ARRAY.REVERSE aus einem vorangegangen Artikel.
Happy Exceling :-)
