Das Portal zum Thema Excel-Formeln
Gruppe: Sonstiges
Beitrag: Dynamisches Array erstellen
Aufgabe
Es soll ein eindimensionales Array aus Werten erstellt werden.
Die Werte stehen in B2:B12, die jeweils gewünschte Anzahl in A2:A12. Es dürfen auch weniger Einträge vorhanden sein (im Bild sind es nur 3):
Lösung
Seit xl365:
=TEXTTEILEN(GLÄTTEN(TEXTKETTE(WIEDERHOLEN(B2:B12&" ";A2:A12)));;" ")Die einzelnen Werte – egal ob Zahlen oder Text – werden von TEXTTEILEN als "Text" zurückgegeben. Wenn es ausschließlich Zahlen sind (wie im Beispiel), kann man diese "Textzahlen" mit einer mathematisch neutralen Operation in echte Zahlen umwandeln – im Folgenden nutzen wir dazu das Doppelminus --
=--TEXTTEILEN(GLÄTTEN(TEXTKETTE(WIEDERHOLEN(B2:B12&" ";A2:A12)));;" ")Wenn es Text und Zahlen sind, oder man nicht genau weiß, ob Text oder Zahlen oder beides, kann man wie folgt formulieren:
=LET(x;TEXTTEILEN(GLÄTTEN(TEXTKETTE(WIEDERHOLEN(B2:B12&" ";A2:A12)));;" ");WENNFEHLER(--x;x))Damit bleiben Texte Texte, aber Textzahlen werden in echte Zahlen umgewandelt. Seit xl2019 geht das mit XMLFILTERN:
=XMLFILTERN("<x><y>"&WECHSELN(GLÄTTEN(TEXTKETTE(WIEDERHOLEN(B2:B12&" ";A2:A12)));" ";"</y><y>")&"</y></x>";"//y")
Die Syntax der XML-Schreibweise muss man nicht beherrschen, um die Formel anzuwenden. Der einzig variable Formelteil sind die beiden Bereiche (hier: A2:A12 und B2:B12).
XMLFILTERN gibt es seit xl2013 – aber TEXTKETTE erst seit xl2019, weshalb die Formel erst ab xl2019 einsetzbar ist. Hier muss man nicht nach Text und Zahlen unterscheiden – die Funktion gibt jeweils den korrekten Datentyp zurück.
Da es in Excelversionen vor 365 das Spill-Prinzip noch nicht gibt, wird dort das gesamte XMLFILTERN-Array nur "intern" erzeugt und ist nicht "sichtbar". Da es aber natürlich vorhanden ist, lassen sich daraus mit umrandenden Funktionen aggregierte Ergebnisse ermitteln (MAX, MIN, STABWN, SUMME, ANZAHL, ANZAHL2 etc. etc.).
Einzelne Elemente aus dem Array lassen sich wie folgt auslesen (beispielhaft das 6. Element):
=XMLFILTERN("<x><y>"&WECHSELN(GLÄTTEN(TEXTKETTE(WIEDERHOLEN(B2:B12&" ";A2:A12)));" ";"</y><y>")&"</y></x>";"//x/y[6]")
oder mit INDEX:
=INDEX(XMLFILTERN("<x><y>"&WECHSELN(GLÄTTEN(TEXTKETTE(WIEDERHOLEN(B2:B12&" ";A2:A12)));" ";"</y><y>")&"</y></x>";"//y");6)
Die Formeln sind ne Gemeinschaftsproduktion von uns, Harry HS(V) und Ralf (RPP63neu).
Erläuterung
Der Clou in beiden Varianten ist die Erzeugung eines leerzeichengetrennten Strings mit allen Werten. Der dafür verantwortliche Formelteil ist:
GLÄTTEN(TEXTKETTE(WIEDERHOLEN(B2:B12&" ";A2:A12)))
Er liefert – bezogen auf obiges Beispiel - diesen Ergebnisstring:
"20 20 20 20 20 11 11 11 11 11 11 11 16 16"
Die Funktion TEXTTEILEN zerlegt den String dann anhand der Leerzeichen in einzelne Elemente.
Für XMLFILTERN muss man den String noch ein wenig bearbeiten (das erledigt WECHSELN in Kombination mit Verkettungen diverser XML-Tags), damit man daraus am Ende mit "//y" das gesamte Ergebnisarray erhält.
Beispiel