Wissensstand: Level 2 ⇒ Solides Basiswissen in Excel, etwas Erfahrung in PQ
Die Aufgabe
In einem Forum wurde die Frage gestellt, wie eine fortlaufende Zahlenreihe generiert werden kann, welche aus fünf einzelnen Blöcken besteht und mit Ausnahme des ersten Blocks stets zu einer definierten Anzahl hoch gezählt werden soll:
Die Nummerierung – Grenzen der Blöcke
Das Grundmuster und somit auch der erste Eintrag stellt sich so dar: 1.1.1.1.1 und geht entsprechend bis zum Wert 1.65.4.10.8 in der letzten Zeile. Im Forum wurde (bis zum Zeitpunkt der Erstellung dieses Beitrags) nur eine VBA-Lösung angeboten. Ich habe die Lösung mit Power Query erstellt, da in manchen Unternehmen VBA (aus gutem Grund) nicht gestattet bzw. auch nicht möglich ist und mich diese Aufgabe auch gereizt hat. Außerdem ist es eine schöne Übung für alle User, die ein wenig tiefer in die Materie des PQ einsteigen möchten. 😎
Lösungsmöglichkeiten
Ich stelle hier ganz bewusst für den Einstieg in den Lösungsweg zwei unterschiedliche Lösungsmöglichkeiten vor. Die erste ist er etwas für Spezies/Tüftler, hier wird ausschließlich mit Power Query gearbeitet, ohne dass eine Excel-Tabelle mit einem Startwert existiert bzw. genutzt wird. Zugegeben, das ist etwas puristisch aber aus meiner Sicht recht interessant. Direkt zu meinem Lösungsansatz geht’s hier: Klick
Deutlich einfacher und auch gewiss praxisnäher ist der Einstieg, wo in ein Excel-Arbeitsblatt beispielsweise in Zelle A1 der Startwert der ersten Gruppe geschrieben wird, also (nur) die 1. Diese Zelle wird dann für den Import in den Power Query-Editor verwendet und die weiteren Werte werden ergänzt bzw. berechnet. Klicken Sie hier, um direkt zu diesem Einstieg zu gelangen.
Ganz ohne Excel-Tabelle starten
Ich schlage vor, Sie erstellen eine neue, leere Excel-Arbeitsmappe. Derzeit befinden sie sich im Arbeitsblatt Tabelle1 und die Zelle A1 ist aktuell. Diese Vorgehensweise ist vielleicht etwas gewöhnungsbedürftig und gewiss auch mit mehr Umstand verbunden als der später beschriebene klassische Ablauf, aber vielleicht können Sie diesen Weg auch einmal bei einer anderen Gelegenheit beschreiten.
- Wechseln Sie zum Register Daten
- Klicken Sie auf das Symbol Daten abrufen und wählen Sie im Kontextmenü Power Query-Editor starten…
- Die meisten Menüpunkte sind ausgegraut und können deshalb nicht gewählt werden. In der Gruppe Neue Abfrage wählen Sie Neue Quelle | Andere Quellen | Leere Abfrage:
Der Weg zu einer neuen Abfrage…
- Tragen Sie in die komplett Leere Abfrage (Abfrage1) in die Eingabezeile die Ziffer 1 ein, denn die gesamte Spalte soll ja mit dieser Zahl beginnen:
In die Eingabezeile kommt nur die Zahl 1
- … Und bestätigen Sie mit der Taste Enter (Return, Zeilenschaltung). Der Editor stellt sich nun so dar:
Das Ergebnis ist eine Liste, keine Tabelle
Bei der Gelegenheit: Die Zeile unter den Gruppenbezeichnungen mit den zwei Symbolen ist eine von mir angelegte und genutzte Möglichkeit mit Schnellzugriffen.
- Sie Klicken auf das Symbol Zu Tabelle und das Bild wird sich Ihnen gewiss etwas vertrauter darstellen:
So werden Sie eine typische Abfrage kennen…
Damit ist die Basis geschaffen. Sie können die erste (und noch einzige) Spalte gerne nach Ihrem Geschmack umbenennen, aber das wäre nur der Optik wegen. In den weiteren Schritten wird dort zwar Bezug darauf genommen aber im letzten Schritt werden Sie sowieso eine Ihnen genehme Überschrift Festlegung generieren.
Die nächsten und zielführenden Schritte sind weiter unten nach der Beschreibung des Standard-Einstiegs beschrieben und unterscheiden sich nicht.
Standard: Excel-Tabelle für den Import
Wie weiter oben schon erwähnt werden sie vermutlich die erste Spalte der Abfrage in einem Excel-Arbeitsblatt erstellen. Dazu tragen Sie in A1 die Ziffer 1 ein und formatieren Sie diese Zelle auf beliebigem Wege zu einer „Intelligenten” Tabelle. Ich mache das meistens per StrgT oder StrgL. Über Daten | Aus Tabelle/Bereich importieren Sie diese Liste in den Power Query-Editor. Sie haben jetzt den gleichen Stand wie in der zuvor dargestellten Übung; eine Abfrage mit 1 Spalte und 1 Zeile. In der deutschen Excel Version wird der einzige Unterschied sein, dass die Überschrift hier Spalte1 ist und nicht Column1 wie bei der vorherigen Vorgehensweise.
Die 5 Gruppen generieren
Sie haben gewiss noch die Aufgabe im „Hinterkopf”: Es sollen ungefähr 20.000 Zahlengruppen generiert werden, die mit 1.1.1.1.1 beginnen und mit 1.65.4.10.8 enden. Da diese Zahlenfolge ja wegen der mehrfachen Punkte in jedem Fall ein String (eine Zeichenfolge) ist, kann weder Excel noch Power Query ohne Umwege und „Klimmzüge” jeweils an passender Stelle den Wert 1 addieren. Der einfachste und auch aus meiner Sicht einzig sinnvolle Weg ist, jede Gruppe erst einmal in einer einzelnen Spalte darzustellen, dort das Hochzählen der Werte vorzunehmen und später die Daten mit den jeweils trennenden Punkten zusammenzufügen.
Gruppe 1 (erste Spalte)
Es ist festgelegt, dass jeder der generierten Werte in der ersten Gruppe mit 1 beginnt. Da ist es logisch, dass diese Spalte nicht hochgezählt werden soll bzw. darf. Folglich bleibt diese Spalte so wie sie ist. Auch wenn das Endresultat aus mehreren Gruppen besteht wäre es nicht zweckmäßig, diese erste Spalte direkt so zu verändern, dass das Endresultat irgendwann nach ca. 20.000 Zeilen erreicht ist. Belassen Sie es bei dieser 1 Zeile, den Rest erledigt Power Query für Sie. 😉 Es ist wesentlich pragmatischer und im Endeffekt schneller, jede dieser fünf Gruppen in einer einzelnen Spalte zu generieren und im letzten Schritt die einzelnen Spalten jeweils durch einen Punkt separiert zusammen zu führen.
Gruppe 2 (zweite Spalte)
Es ist ja vorgegeben, dass die 2. Spalte (sprich die zweite Gruppe) die fortlaufenden Ziffern 1 bis 65 enthält. Auch wenn Sie sonst in Power Query sehr viel per Mausklick erledigen können, hier ist eine Formel gefragt. Wechseln Sie also zum Register Spalte hinzufügen und Klicken Sie dort auf Benutzerdefinierte Spalte. Im Dialog wählen/schreiben Sie als Neuer Spaltenname (Überschrift) beispielsweise Spalte2 oder auch nur die 2 und tragen Sie als Benutzerdefinierte Spaltenformel exakt diesen Wert ein: {1..65} (das sind die geschweiften Klammern)
Der Dialog für die Formel-Eingabe
… und bestätigen Sie mit OK. Danach sieht der Power Query-Editor zwar immer noch etwas spartanisch aus, aber es ist okay so:
Der Wert „List” ist hier absolut korrekt
Verfahren Sie mit den Gruppen/Spalten 3 bis 5 gleichermaßen. Natürlich werden sie die Überschriften und die Werte in den geschweiften Klammern entsprechend anpassen, also Spalte3 | {1..4}, Spalte4 | {1..10} und Spalte5 | {1..8}.
Nacheinander erweitern Sie nun die Überschriften Spalte2 bis Spalte5 durch einen Klick auf den Doppelpfeil 
rechts in der Überschrift, Auf neue Zeilen ausweiten und Sie erkennen, dass umgehend sehr viele neue Zeilen entstanden sind. 💡
Im letzten Schritt müssen die 5 separaten Spalten natürlich zu einer einzigen Spalte zusammengefügt werden, wo jede Gruppe durch einen Punkt von der folgenden getrennt wird. Dazu Klicken Sie als erstes in die Überschrift Spalte1, Shift und dann in Spalte5. Alle Spalten sind nun markiert. Wechseln Sie zum Menü Transformieren und wählen Sie in der Gruppe Textspalte Spalten zusammenführen. Im Dialog wählen Sie bei Trennzeichen zuerst –Benutzerdefiniert– und fügen dann in das leere Textfeld einen Punkt . ein. Als Neuer Spaltenname tragen Sie das ein, was als Überschrift künftig der kompletten Liste stehen soll, beispielsweise Warengruppe oder einen anderen passenden Text.
Über den Menüpunkt Datei | Schließen & laden oder Schließen & laden in… die Abfrage schließen und die über 20.000 Zeilen werden in ein neues Tabellenblatt oder an gewünschter Position in ein Arbeitsblatt geschrieben. Damit ist die Aufgabe erfüllt.
Epilog
Sollten Sie bei den Gruppennummern welche in den Spalten/Grupen zwei und vier ein- und zweistellig sein können bei den Zahlen 1–9 wegen beispielsweise besserer Sortiermöglichkeit eine führende Null verwenden wollen, so ist auch dieses mit Power Query durchaus machbar. Mit ein wenig tüfteln werden Sie vielleicht die Lösung finden. Sie können mich aber auch gerne anschreiben und gegen eine Spende von 2,00 Euro erstellt ich Ihnen die passende Lösung.
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag Ihrerseits z.B. 2,50 € freuen …