Nachdem ich ARRAY.SPLIT entwickelt hatte, kam mir die Idee, ob sich nicht eine Version entwickeln liesse, die einen einspaltigen Bereich nicht anhand eines Trennzeichens, sondern anhand vordefinierter Positionen aufteilt. Beispielsweise müsste eine solche Funktion in der Lage sein, den Text KA01A23Z25 in K, A01, A23 und Z25 aufzuteilen, also an den Positionen 2, 5 und 8.
Dieser Artikel stellt die LAMBDA-Funktion ARRAY.SPLIT.BYPOS und meinen Lösungsansatz vor. Wie so oft bei Excel-Formeln, ist aber sicherlich immer etwas Verbesserungspotential vorhanden. Falls jemand einen Vorschlag z.B. zur Optimierung der Lösung hat, freue ich mich über jeden Kommentar zu diesem Artikel.
Um die benutzerdefinierte Funktion anzulegen, werden dynamische Arrays und die kürzlich von Microsoft veröffentlichte Funktion LAMBDA verwendet. Zum jetztigen Zeitpunkt (20.12.2020) ist LAMBDA nur in der Excel-Beta-Version verfügbar, was sich hoffentlich bald ändern wird. Eine Einführung zu der LAMBDA-Funktion ist hier zu finden.
Anforderungen
Die Funktion ARRAY.SPLIT.BYPOS soll die Werte eines Bereichs auf einzelne Spalten verteilen und dabei eine Positionsliste berücksichtigen. Gleichzeitig müssen die Textwerte im Bereich nicht alle gleich lang sein, was heißt, dass wenn kürzere Texte im Bereich enthalten sind, berechnete Spalten durch einen Standardwert aufgefüllt werden sollen. In oberer Abbildung ist dies beispielsweise der Fall für ZX00, wo die Spalten D und E mit einem Sternchen als Standardwert aufgefüllt wurden. Die Syntax der Funktion wäre somit:
=ARRAY.SPLIT.BYPOS(Array;Positions;Default)
Einige Anwendungsbeispiele wären dann:
=ARRAY.SPLIT.BYPOS(A2:A5;B1:D1;"*")
=ARRAY.SPLIT.BYPOS(A2:A5;B2:B4;"*")
=ARRAY.SPLIT.BYPOS(A2:A5;{2.5.8};"-")
=ARRAY.SPLIT.BYPOS(A2:A5;{8;5;2};)
ARRAY.SPLIT.BYPOS
Neben den Eingangsparametern Array, Positions und Default werden in der Funktion auch sicherlich ein paar Zwischenberechnungen notwendig sein, wie beispielsweise das Erstellen von Array für die Positionsintervalle. Die Funktion LET ermöglicht es nicht nur eben solche Zwischenberechnungen durchzuführen, sondern auch als Variablen abzulegen und in weiteren Berechnungen wiederzuverwenden. Folgende Pseudoformel hat sich für mich nach einigen Versuchen ergeben:
=LET(Array;A2:A5;
Positions;"-";
Default;"*";
D;Validierung vom Argument zum Standardwert;
S;Validierung vom Argument für die Positionen;
F;Hilfsvariable zum Filtern von S;
A;Hilfsvariable zum Bereich;
L;Hilfsvariable zur Textlänge;
C;Hilfsvariable für die Spaltenanzahl;
P;Hilfsvariable für eine Positionsmatrix;
M;Hilfsvariable zum Füllen der Positionsmatrix;
X;Hilfsvariable für Indizes in F;
Y;Hilfsvariable für Indizes in F;
U;Hilfsvariable für die Startpositionen;
V;Hilfsvariable für die Endpositionen;
Berechnung vom Ergebnis)
Schauen wir uns zuerst die ersten zwei Hilfsvariablen zur Validierung der Argumente für den Standardwert und das Trennzeichen an. Die beiden Formeln sind recht einfach:
D=WENN(ISTLEER(Default);"";INDEX(Default;1;1))
S=WENN(SPALTEN(Positions)>1;MTRANS(INDEX(Positions;1;0));Positions)
Für den Standardwert Default wird geprüft, ob das übergebene Argument leer ist. Wenn ja, wird ein vorgebener Wert verwendet; in diesem Fall eine leere Zeichenkette.
Für die Angabe zu den Positionen wird geprüft, ob die Anzahl der Spalten größer eins ist, was bedeutet, dass mindestens ein mehrspaltiger Bereich übergeben wurde. In diesem Fall wird die erste Zeile extrahiert und in einen einspaltigen, vertikalen, Bereich transponiert. Ansonsten kann es sich nur um einen einspaltigen Bereich oder einen Einzelzelle bzw. Einzelwert handeln, der dann verwendet wird.
Nach der Überprüfung der Angaben zu den Positionen erfolgt eine weitere Überprüfung von S über die Variable F. Die Angabe zu den Positionen darf nicht die Zahl 1 enthalten und parallel werden Werte, die nicht einer Zahl entsprechen, ausgeschlossen. Duplikate sind ebenfalls nicht erlaubt. Und, die Zahlen sollten aufsteigend sortiert sein. Somit würde beispielsweise aus {8;2;1;"X";2;5} die Positionsreihe {2;5;8} entstehen. Die Formel für F lautet:
F=SORTIEREN(EINDEUTIG(FILTER(S;ISTZAHL(S)*(S>1))))
Die nächste Variable A ist eine Variable, die aus dem angebenenen Bereich zu den Textwerten die erste Spalte extrahiert. Die Formel ist somit sehr einfach:
A=INDEX(Array;0;1)
Kommen wir zur Variable L. Diese berechnet die maximale Länge der Textwerte und addiert 1 zu diesem Wert. Auch diese Formel ist sehr einfach aufgebaut:
L=MAX(LÄNGE(A))+1
Die Variable C berechnet die Anzahl der benötigten Spalten in der Ergebnismatrix. Die Anzahl entspricht der Anzahl der Zeilen in F, allerdings um 1 erhöht, denn z.B. 3 Positionen ergeben 4 einzelne Textteile. Beispiel: ein Aufteilen nach Position 2, 5 und 8 ergibt als Teilttexte die Texte von 1 bis 1, 2 bis 4, 5 bis 7 und 8 bis zum Textende.
C=ZEILEN(F)+1
Wie bei der Funktion ARRAY.SPLIT kann nun anhand der Funktion SEQUENZ eine Matrix erzeugt werden, die die Anzahl der errechneten Spalten und Zeilen berücksichtigt und die erzeugten Zellen einfach durchzählt.
Die Formel für P lautet somit:
P=SEQUENZ(ZEILEN(A);C)
Auch das Ausfüllen der in P generieren Matrix mit den Werten aus dem Bereich mit den Textwerten ist gleich zu dem Verfahren in ARRAY.SPLIT. Die Formel für M lautet dann:
M=INDEX(A;AUFRUNDEN(INDEX(P;0;0)/C;0);1)
Die Formel schreibt pro Zeile den entsprechenden Wert von A in die in P generierte Matrix, dupliziert also quasi die Werte aus A in P.
Schauen wir uns nun die Variablen X und Y an. Beide Variablen generieren jeweils eine Zahlenreihe an Indizes und unterscheiden sich nur dadurch, dass für X als Startwert eine Null und für Y eine Eins verwendet wird.
X=SEQUENZ(1;C;0)
Y=SEQUENZ(1;C;1)
Wenn C gleich 3 ist, würde somit X die Zahlenreihe 0, 1, 2, 3 und Y die Zahlenreihe 1, 2, 3, 4 enthalten. Die Variablen X und Y machen wir uns zunutze, indem wir in den nächsten Variablen U und V einzelne Werte aus der Angabe zu den Positionen ermitteln. Die Formeln für U und V sehen wie folgt aus:
U=WENN(X>0;INDEX(F;X;0);1)
V=WENN(Y>C-1;L;INDEX(F;Y;0))
Folgende Abbildung führt die Werte in U und V auf, wenn die Positionen in der Variable F als 2, 5 und 8 angegeben sind. U und V entsprechen somit Unter- und Obergrenzen der Positionsintervalle.
Damit sind alle Variablen definiert bzw. berechnet und es verbleibt nur noch, die Teiltexte in M anhand der Werte in U und V zu extrahieren. Die Formel für die finale Berechnnung lautet:
WENN(LÄNGE(M)>U;TEIL(M;U;V-U);D)
Es wird geprüft, ob der aktuelle Wert in der Matrix M eine größere Anzahl an Zeichen hat, als die Startposition in U ab der der Teiltext extarhiert werden soll. Wenn Ja, dann wird die Funktion TEIL verwendet, um den Text zwischen Position U und V zu ermitteln. Wenn nein, wird der Standardwert D verwendet.
Damit ist nun die LET-Formel vollständig:
=LET(Array;A2:A5;
Positions;"-";
Default;"*";
D;WENN(ISTLEER(Default);"";INDEX(Default;1;1));
S;WENN(SPALTEN(Positions)>1;MTRANS(INDEX(Positions;1;0));Positions);
F;SORTIEREN(EINDEUTIG(FILTER(S;ISTZAHL(S)*(S>1))));
A;INDEX(Array;0;1);
L;MAX(LÄNGE(A))+1;
C;ZEILEN(F)+1;
P;SEQUENZ(ZEILEN(A);C);
M;INDEX(A;AUFRUNDEN(INDEX(P;0;0)/C;0);1);
X;SEQUENZ(1;C;0);
Y;SEQUENZ(1;C;1);
U;WENN(X>0;INDEX(F;X;0);1);
V;WENN(Y>C-1;L;INDEX(F;Y;0));
WENN(LÄNGE(M)>U;TEIL(M;U;V-U);D))
Und wir können jetzt die LAMBDA-Funktion erstellen und diese im Namensmanager als ARRAY.SPLIT.BYPOS ablegen:
=LAMBDA(Array;Positions;Default;
LET(D;WENN(ISTLEER(Default);"";INDEX(Default;1;1));
S;WENN(SPALTEN(Positions)>1;MTRANS(INDEX(Positions;1;0));Positions);
F;SORTIEREN(EINDEUTIG(FILTER(S;ISTZAHL(S)*(S>1))));
A;INDEX(Array;0;1);
L;MAX(LÄNGE(A))+1;
C;ZEILEN(F)+1;
P;SEQUENZ(ZEILEN(A);C);
M;INDEX(A;AUFRUNDEN(INDEX(P;0;0)/C;0);1);
X;SEQUENZ(1;C;0);
Y;SEQUENZ(1;C;1);
U;WENN(X>0;INDEX(F;X;0);1);
V;WENN(Y>C-1;L;INDEX(F;Y;0));
WENN(LÄNGE(M)>U;TEIL(M;U;V-U);D)))
Fazit
ARRAY.SPLIT.BYPOS und ARRAY.SPLIT ergänzen sich insofern, als dass nun durch einen einfachen Funktionsaufruf die Möglichkeit besteht, einen einspaltigen Bereich mit Textwerten anhand eines Trennzeichens oder der Angabe von Positionen in Teiltexte aufzuteilen.
Happy Exceling :-)
