Xtract: Vervielfältigen von Zeilen einer Liste, fortlaufende Nummerierung nach in Spalten hinterlegten Werten (von..bis). Besonderheit: Werte in Zellen für den Start- und Endwert enthalten teilweise einen alphanumerischen Präfix (z.B. X1), nur die Zahlen sollen hochgezählt werden.
Wissensstand: Level 3 ⇒ Excel GUT!, mindestens Basis-Kenntnisse in Power Query
Prolog
In einem Excel-Forum wurde die Frage gestellt, wie mittels Power Query eine mehrspaltige Liste derart umgestellt werden kann, dass die Anzahl der Zeilen so vervielfacht wird, dass die numerischen Werte der ersten beiden Spalten (von … bis) in 1er-Schritten aufgefüllt/hochgezählt werden. Prinzipiell ist das ja auch nichts außergewöhnliches, ein derartiger Vorgang wurde beispielsweise hier im Blog beschrieben. In diesem Fall ist aber eine Besonderheit gegeben, sehen Sie sich einfach einmal den Screenshot der (leicht vereinfachten) Liste an:
Kleine Demo-Tabelle als Muster
Die ersten beiden Zeilen sind exakt nach dem Muster wie in der oben verLinkten Datei problemlos lösbar. Die letzten beiden Einträge in den ersten beiden Spalten sind keine reinen Zahlen, sondern haben einen alphanumerischen Präfix. Um das gewünschte Ziel zu erreichen muss also Sorge dafür getragen werden, dass nur die numerischen Werte für das Erstellen einer Liste herangezogen werden. Und was für die Lösung noch relevant ist: Die Ergebnis-Tabelle soll eine exakte Kopie der jeweiligen Zeilen enthalten, also sollen auch die letzten beiden Zeilen der Basisdaten einschließlich des unveränderten alphanumerischen Präfix mehrfach numerisch hochgezählt dargestellt werden.
Als Muster-Datei biete ich Ihnen erst einmal die etwas umfangreichere Tabelle aus dem Forum hier zum Download an. Sie erkennen, dass das Prinzip zur oben als Bild dargestellten Tabelle identisch ist, es sind nur einige Datensätze mehr. Daneben hat der Fragesteller in den Spalten F:H ist auch schon die Wunschlösung aufgeführt. Und bis auf die Rahmenlinien zwischen den Ergebnis-Blöcken lässt sich das auch mit etwas mehr (aber einmaligem) Aufwand per Power Query realisieren. Ich habe auch in der Muster-Datei im Bereich Ist eine Zeile hinzugefügt, um nicht immer nur das Präfix Z und auch einmal 3‑stellige Werte in der 2. Spalte zu verwenden.
Da die Rohdaten seitens des Fragestellers bereits als Tabelle formatiert worden sind, ist ein direkter Import in Power Query möglich. Sie werden sich denken können, dass auch hier in PQ viele Wege nach Rom (sprich: mehrere Wege zum Ziel) führen. Ich versuche in diesem Blog einen Weg zu gehen, der möglichst verständlich ist; selbst wenn bei der Erstellung der Abfrage dadurch der eine oder andere Schritt mehr erforderlich ist. Und ich versuche auch Rücksicht auf jene User zu nehmen, die nicht die allerneueste Excel- bzw. PQ-Version auf dem Rechner haben. Und ich gehe in diesem Beitrag auch davon aus, dass maximal ein Buchstabe vor den Ziffern eingefügt ist.
In jeder PQ-Version realisierbar
Die ersten beiden Gedanken, die Länge des Zellinhalts als Argument zu verwenden oder durch Umwandlung des Datentyps in eine (echte) Zahl einen Error zu provozieren, habe ich rasch als nicht realisierbar oder deutlich zu aufwendig beiseite gelegt. Der nächst beste (hier auch vorgestellte) Weg ist auch nicht wirklich „kurz und knackig”, aber aus meiner Sicht auch nachvollziehbar, wenn sie gute Excel-Kenntnisse haben und auch der englischen Sprache etwas mächtig sind.
Unter dem Aspekt, dass im Zweifelsfall immer nur das erste Zeichen der Zelle entfernt werden muss, um zum Ziel des rein numerischen Ergebnisses zu gelangen, gehen Sie den Weg über Spalte hinzufügen und wählen dort im Menüband das Symbol Benutzerdefinierte Spalte. Im Dialog tragen sie bei Neuer Spaltenname von ein und im großen Feld Benutzerdefinierte Spaltenformel geben Sie diese Funktion ein:
= if Value.Is([Wert von], type text)
then
Number.From(Text.End([Wert von], Text.Length([Wert von])-1))
else
[Wert von]
Die etwas komplexere Formel zur Berechnung der numerischen Werte
In der Spalte Wert bis gehen Sie gleichermaßen vor, nur dass sie naturgemäß als Überschrift bis verwenden und die Spaltennamen [Wert bis] einsetzen. Damit ist eine solide Grundlage geschaffen, um die Liste mit den vervielfältigten Zeilen zu generieren. Was aber noch bleibt ist die Forderung, dass im Endergebnis die alphanumerischen Zeichen vor die fortlaufende Nummerierung eingebunden werden sollen. Siehe auch den Bereich F31:F36 in der Original-Tabelle. Sie brauchen also noch eine Spalte, wo bei reinen Zahlenwerten nichts (null), ansonsten das erste Zeichen des Textes enthalten ist. Vom Prinzip her ist das Vorgehen identisch zu den beiden bisherigen Formeln. Erstellen Sie also wiederum eine neue, Benutzerdefinierte Spalte, schreiben Präfix als Überschrift und tragen Sie dort diese Formel ein:
= if Value.Is([Wert von], type text)
then
Text.Start([Wert von], 1)
else
null
Nun sind wirklich alle Voraussetzungen zum Erreichen des Ziels gegeben. Erstellen Sie dazu wiederum eine Benutzerdefinierte Spalte, belassen sie die Überschrift gerne bei Benutzerdefiniert und die Spaltenformel ist ausnahmsweise einmal kurz und knackig:
{[von]..[bis]}
Power Query hat nach dem Klick auf OK die Spalte Benutzerdefiniert erstellt, der Inhalt jeder einzelnen der sieben Zeilen ist List. Ein Klick auf den Doppelpfeil 
und anschließend Auf neue Zeilen ausweiten wählen. Ruckzuck sind die gewünschten Zeilen generiert worden, die Abfrage umfasst nun 39 Zeilen.
Im nächsten Schritt geht es darum, dass die teilweise vorangestellten alphanumerischen Zeichen wieder integriert werden. Dazu markieren Sie zuerst die Überschrift Präfix, Strg und dann Benutzerdefiniert. Rechtsklick in eine der beiden markierten Überschriften und wählen Sie im Kontextmenü Spalten zusammenführen. Bei Trennzeichen belassen sie es bei –Keine– und bei Neuer Spaltenname (optional) geben Sie Wert ein.
Das bisherige Ergebnis sollte schon einmal überzeugen. Markieren Sie nun genau in dieser Reihenfolge die Überschriften Wert, Variable 1 und Variable 2, Rechtsklick in eine der markierten Überschriften und Andere Spalten entfernen.
Bingo, das sieht schon exakt so aus, wie es vom Fragesteller gewünscht war. Ein stichprobenweiser Vergleich wird Ihnen das auch bestätigen. Jetzt nur noch Datei | Schließen und laden in… | Bestehendes Arbeitsblatt und markieren Sie beispielsweise die Zelle J4, um die Abfrage in gleicher Höhe wie die Soll-Vorgabe des Fragestellers als Tabelle in das Arbeitsblatt einzutragen. Sollte die Möglichkeit Bestehendes Arbeitsblatt ausgegraut sein, einfach als Tabelle speichern und im Anschluss an die Wunschposition im gewünschten Arbeitsblatt verschieben.
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag Ihrerseits z.B. 2,50 € freuen …
Dieses Thema umfasst mehrere Teile:
| Teil 1 | Gilt für alle PQ-Versionen. Voraussetzung ist aber, dass ein vorhandenes Präfix maximal 1 Zeichen lang ist. |
| Teil 2 | Erfordert Excel >=2019 oder 365. Das Präfix hat eine beliebige Länge und kann recht problemlos separiert werden. |
| Teil 3 | Präfix hat beliebige Länge, beliebige PQ-Version. Deutlich aufwendiger als mit den neueren Versionen (siehe Teil 2) aber dennoch zielführend. |
| $/$$ Außer Konkurrenz |
Gleiche Voraussetzungen wie in Teil 3. Mehrere Möglichkeiten, auf kürzeren Wegen, mit weniger Schritten zum gewünschten Ziel zu gelangen. Viel M-Code. |