Ab und zu kommt es in der Praxis vor, dass Werte aus einem einspaltigen Bereich anhand eines Trennzeichens auf einzelne Spalten aufgeteilt werden müssen, beispielsweise um Kodierungen von Artikel- oder Materialnummern zu trennen.
Excel bietet für solche Aufgaben verschiedene Lösungsmöglichkeiten. So kann z.B. der Assistent Text in Spalten verwendet werden, der im Menüband unter dem Reiter Daten zu finden ist. Oder es kann Power Query verwendet werden, wo über eine Abfrage der Text ebenfalls recht einfach teilbar ist. Und natürlich ginge das auch über VBA.
Dieser Artikel stellt eine Methode vor, wie dies anhand der LAMBDA-Funktion und dynamischen Arrays machbar ist. Eine Einführung zu der LAMBDA-Funktion ist hier zu finden.
Anforderungen
Die Funktion ARRAY.SPLIT soll die Werte eines Bereichs auf einzelne Spalten verteilen. Hierzu muss der Funktion der Bereich sowie das Trennzeichen übergeben werden. Die Funktion ARRAY.SPLIT soll aber auch berücksichtigen, dass die Werte innerhalb des Bereichs unterschiedlich oft das Trennzeichen enthalten können. Dadurch kann sich wiederrum ergeben, dass einige Spalten leer sind. Ein Zusatzargument soll ermöglichen, dass diese leeren Werte durch einen Standardwert aufgefüllt werden können. Des Weiteren soll die Funktion leere Argumente berücksichtigen und dann einen fest definierten Wert verwenden. Die Syntax der Funktion wäre somit:
=ARRAY.SPLIT(Array;Separator;Default)
Einige Anwendungsbeispiele wären dann:
=ARRAY.SPLIT(A2:A5;"-";"*")
=ARRAY.SPLIT(A2:A5;"-";"")
=ARRAY.SPLIT(A2:A5;;)
ARRAY.SPLIT
Über die Funktion LET definieren wir in einem ersten Schritt die Eingangsparameter. Zwischenberechnungen dienen zur Validierung der Eingangswerte, Berechnen der Positionen des Trennzeichens pro Wert im Bereich und Ermitteln des Teiltextes. Eine Pseudoformel sieht somit wie folgt aus:
=LET(Array;A2:A5;
Separator;"-";
Default;"*";
D;Validierung vom Argument zum Standardwert;
S;Validierung vom Argument zum Trennzeichen;
A;Hilfsvariable zum Bereich;
C;Hilfsvariable zur Berechnung der Spaltenanzahl;
P;Hilfsvariable für eine Positionsmatrix;
M;Hilfsvariable zum Füllen der Positionsmatrix;
T;Hilfsvariable zum Trennzeichen;
V;Hilfsvariable zum Austausch des Trennzeichens;
X;Hilfsvariable zur Position - Start;
Y;Hilfsvariable zur Position - Ende;
Berechnung vom Ergebnis)
Nebenbei, die Aufteilung der Hilfsvariablen habe ich mir nicht mal eben so ausgedacht, sondern dies hat sich im Laufe der Entwicklung der Funktion ergeben. Bis ich zu diesem Ergebnis gekommen bin, hat es schon einige Zeit gebraucht. Und, Excel-Formeln sind natürlich nicht statisch – heißt, es ist nicht ausgeschlossen, dass es Verbesserungs- oder Optimierungsmöglichkeiten zu dieser Formel gibt.
Das Schöne an LAMBDA-Funktionen ist zudem, dass diese wie eine Blackbox agieren. Sofern nicht die Eingangsparameter verändert werden, kann die Formel im Hintergrund problemlos durch eine verbesserte Version ausgetauscht werden.
Schauen wir uns 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(ISTLEER(Separator);" ";INDEX(Separator;1;1))
Es wird über eine WENN-Funktion geprüft, ob das übergebene Argument leer ist. Ist dies der Fall, wird ein vorgebener Wert verwendet: ein Leerzeichen für das Trennzeichen und eine leere Zeichenkette für den Standardwert. Wurden Werte für das Trennzeichen bzw. für den Standardwert übergeben, wird die Übergabe in die INDEX-Funktion gekapselt. Das stellt sicher, dass im Fall der Übergabe eines Bezuges nur die erste Zelle im Bezug verwendet wird. Handelt es sich bei der Übergabe nicht um einen Bezug, sondern um einen einfachen Text, liefert auch da INDEX ein korrektes Ergebnis.
Die nächste Variable A ist eine Variable, die zwei Funktionen erfüllt. Erstens stellt diese sicher, dass nur die erste Spalte des an ARRAY.SPLIT übergebenen Bereiches verwendet wird. Zweitens wird das Trennzeichen jedem Wert vorangestellt, was wir später noch brauchen werden. Die Formel sieht wie folgt aus:
A=S&INDEX(Array;0;1)
Eine Anforderung an ARRAY.SPLIT ist, dass die Werte eine unterschiedliche Anzahl an Trennzeichen beinhalten können. Daher müssen wir die maximale Anzahl an zu erzeugenden Spalten berechnen. Dazu tauschen wir das Trennzeichen pro Wert im Bereich durch nichts aus, berechnen die Länge des verbleibenden Textes und ziehen diese von der Ursprungslänge ab. Das Maximum der ermittelten Werte ist dann die Anzahl der Spalten. Die Formel lautet:
C=MAX(LÄNGE(A)-LÄNGE(WECHSELN(A;S;"")))
Es wird in der Formel Bezug auf A genommen. Enthält also beispielsweise die erste Zelle im Bereich den Wert K-03-001, errechnet sich die erste Zelle in A als -K-03-001, was dann als Spaltenanzahl eine 3 ergibt.
Nun können wir anhand der SEQUENZ-Funktionen eine Matrix generieren, die die Anzahl der ermittelten Spalten berücksichtigt und einfach nur die Zellen durchzählt.
Obere Abbildung zeigt das Ergebnis der Formel für P:
P=SEQUENZ(ZEILEN(A);C)
Setzen wir nun statt der Zahlen die Werte des Bereichs ein. Auch diese Formel ist nicht allzu komplex und sieht wie folgt aus:
M=INDEX(A;AUFRUNDEN(INDEX(P;0;0)/C;0);1)
Die Formel schreibt nun pro Zeile den Wert von A – wir erinnern uns, A beinhaltet die Werte des Eingangsbereichs plus dem vorangestelltem Trennzeichen – in die Zellen der generierten Matrix. Über die Funktion AUFRUNDEN erreichen wir, dass pro Spalte immer die passende Zeile aus A genommen wird. So ergibt z.B. 3 / 5 = 0,6 = 1 aufgerundet.
Jetzt wird ein bißchen komplizierter, denn nun müssten die Teiltexte innerhalb der Matrix so extrahiert werden, dass pro Spalte die Aufteilung nach Trennzeichen stattfindet. In B2 müsste dann der Wert K stehen, in C2 der Wert 03 und in D2 der Wert 001. E2 und F2 wären leer bzw. mit dem Standardwert zu belegen.
Die Excel-Funktion WECHSELN erlaubt es, ein Zeichen an einer bestimmten Position auszutauschen. Das funktioniert auch mit Arrays, sofern Excel dynamische Arrays kennt. Generieren wir uns somit zunächst ein Array, was den Austauschtext eindeutig pro Spalte festlegt. Eine solche Formel ist:
T=ZEICHEN(1)&SEQUENZ(1;C)&ZEICHEN(1)
Das Ergebnis dieser Formel ist in nachfolgender Abbildung zu sehen. Es wird einfach eine Zahl hochgezählt und diese mit einem Zeichen umschlossen, sodass eine eindeutige Kennung entsteht. Statt dem Zeichen 1 könnte man auch ein anderes Zeichen verwenden, z.B. ein Dollar-Zeichen, aber dann muss sichergestellt sein, dass eine Kombination des Zeichens mit einer Zahl im Text nicht vorkommt. Bei dem Zeichen 1 ist die Wahrscheinlichkeit sehr gering.
Ersetzen wir nun die Trennzeichen in der Matrix M durch die Werte in T. Es ergibt sich unsere Variable V mit folgende Formel:
V=WECHSELN(M;S;T;SEQUENZ(1;C))&S
Wie nachfolgend zu sehen, wurden die Trennzeichen nur an ihren entsprechenden Positionen ausgetauscht. Zusätzlich habe ich das Trennzeichen selbst nochmals an das Ende drangehängt. Das dient später dazu, die Position des letzten Zeichens zu ermitteln.
Es ist fast geschafft. Wir müssen nur noch die Positionen – Start und Ende – der Trennzeichen bestimmen und können dann anhand der Funktion TEIL die Teiltexte extrahieren. Zur Bestimmung der Positionen dienen die Variablen X und Y. Die Formel für X sieht wie folgt aus:
X=WENNFEHLER(LÄNGE(INDEX(T;1;SEQUENZ(1;C)))+
FINDEN(INDEX(T;1;SEQUENZ(1;C));V);0)
Die Funktion FINDEN sucht in V nach T aus der entsprechenden Spalte und liefert die Startposition zurück. Beispiel: in C3 würde nach der Zahl 2 gesucht, die von ZEICHEN(1) umschlossen ist. Ist die Suche erfolglos, wird Null zurückgegeben. Das Ergebnis ist in folgender Abbildung zu sehen.
In ähnlicher Form berechnen wir Y. Hierbei suchen wir wieder nach dem Original-Trennzeichen, und zwar ab der Stelle, die und X geliefert hat. Die Formel für Y lautet somit:
Y=WENN(X>0;WENNFEHLER(FINDEN(S;V;X)-1;0);0)
Eine Suche ist natürlich nur dann sinnvoll, wenn X auch größer als Null ist. Und, es wird die Position des Zeichens vor dem ermittelten Wert zurückgegeben. Da übrigens das Trennzeichen in V auch an die Werte von M drangehängt wurde, findet die Formel immer das letzte Zeichen.
Sind einmal die Werte von X und Y gefunden, bleibt noch das Extrahieren der Teiltexte zwischen X und Y, was dann die abschließende Berechnung darstellt:
WENN(Y>0;TEIL(V;X;Y-X+1);D))
Damit ist nun die LET-Formel vollständig:
=LET(Array;A2:A5;
Separator;"-";
Default;"*";
D;WENN(ISTLEER(Default);"";INDEX(Default;1;1));
S;WENN(ISTLEER(Separator);" ";INDEX(Separator;1;1));
A;S&INDEX(Array;0;1);
C;MAX(LÄNGE(A)-LÄNGE(WECHSELN(A;S;"")));
P;SEQUENZ(ZEILEN(A);C);
M;INDEX(A;AUFRUNDEN(INDEX(P;0;0)/C;0);1);
T;ZEICHEN(1)&SEQUENZ(1;C)&ZEICHEN(1);
V;WECHSELN(M;S;T;SEQUENZ(1;C))&S;
X;WENNFEHLER(LÄNGE(INDEX(T;1;SEQUENZ(1;C)))+
FINDEN(INDEX(T;1;SEQUENZ(1;C));V);0);
Y;WENN(X>0;WENNFEHLER(FINDEN(S;V;X)-1;0);0);
WENN(Y>0;TEIL(V;X;Y-X+1);D))
Und wir können nun die LAMBDA-Funktion erstellen sowie diese im Namensmanager als ARRAY.SPLIT ablegen:
=LAMBDA(Array;Separator;Default;
LET(D;WENN(ISTLEER(Default);"";INDEX(Default;1;1));
S;WENN(ISTLEER(Separator);" ";INDEX(Separator;1;1));
A;S&INDEX(Array;0;1);
C;MAX(LÄNGE(A)-LÄNGE(WECHSELN(A;S;"")));
P;SEQUENZ(ZEILEN(A);C);
M;INDEX(A;AUFRUNDEN(INDEX(P;0;0)/C;0);1);
T;ZEICHEN(1)&SEQUENZ(1;C)&ZEICHEN(1);
V;WECHSELN(M;S;T;SEQUENZ(1;C))&S;
X;WENNFEHLER(LÄNGE(INDEX(T;1;SEQUENZ(1;C)))+
FINDEN(INDEX(T;1;SEQUENZ(1;C));V);0);
Y;WENN(X>0;WENNFEHLER(FINDEN(S;V;X)-1;0);0);
WENN(Y>0;TEIL(V;X;Y-X+1);D)))
Natürlich liesse sich der Funktion ARRAY.SPLIT auch ein deutscher Name vergeben. Ich hatte mich bei dieser Funktion, wie bei vorangegangen Artikel, jedoch für einen englischen Namen entschieden.
Fazit
ARRAY.SPLIT ergänzt meine Bibliothek an Array-Funktionen, die ich in vorangegangen Artikel bereits vorgestellt habe. Derzeit kann ARRAY.SPLIT nur mit einem Trennzeichen umgehen. Eine Version zu erstellen, die mehrere Trennzeichen berücksichtigt, ist mir noch nicht gelungen. Momentan hakt es da noch daran, dass ein verschachteltes Array ensteht, was anscheinend nicht mehr adressierbar ist. Ich bleibe jedenfalls dran.
Happy Exceling :-)