Dass Daten aus externen Quellen oft in einem Format vorliegen, das für eine Weiterverarbeitung in Excel ungeeignet ist, kennen vermutlich viele Anwender: von unnötigen Leerzeilen oder -spalten, Zwischensummen oder Zwischenüberschriften hat man alles schon gesehen.
Besonders fies ist eine Datenstruktur, die gelegentlich bei Exporten aus einfachen Adressenprogrammen vorkommt. Alle Daten einer Adresse werden in eine einzige Spalte untereinander geschrieben anstatt zeilenweise in mehreren Spalten mit je einer vollständigen Adresse pro Zeile.
Eine Lösungsmöglichkeit mit Power Query zeigt der heutige Artikel.
Beispieldatei herunterladen
Die Ausgangslage
Die besagte Adressendatei sieht im Originalzustand wie im folgenden Bild aus:
Die sehr ungünstig aufgebaute Adressenliste
Jede Adresse besteht aus 6 untereinander liegenden Feldern, danach kommt direkt die nächste Adresse. Zur Verdeutlichung habe ich rot gestrichelte Linien eingefügt.
Für diesen Fall hatte ich im Artikel Wie transponiert man mehrzeilige Datensätze in ein vernünftiges Tabellenformat? unter anderem zwei Formellösungen vorgestellt. Heute greifen wir stattdessen auf Power Query zurück.
Dazu stellen wir die aktive Zelle an den Anfang der Liste in Zelle A1 und rufen das Menü auf: „Daten | Aus Tabelle/Bereich“. Excel wandelt anschließend die Liste in eine formatierte Tabelle um. Da unsere Liste auch keine Überschriften enthält, wird die entsprechende Option auch nicht angewählt
Die Liste wird nach Power Query geladen
Excel hat die Tabelle in Power Query geladen und automatisch eine generische Überschrift mit den Namen „Spalte1“ erzeugt:
Die einspaltige Liste in Power Query
Als nächstes fügen wir eine Index-Spalte dazu: „Spalte hinzufügen | Indexspalte“
Eine Indexspalte wird hinzugefügt
Im nächsten Schritt kommt eine Modulo-Spalte dazu (die Index-Spalte muss dazu weiterhin markiert sein): „Spalte hinzufügen | Standard | Modulo“
Eine Modulo-Spalte wird hinzugefügt
Als Wert verwenden wir 6, da unsere Adressen aus jeweils 6 Zeilen bestehen. Dabei wird die Index-Spalte durch 6 dividiert und der Rest dieser Division wird zurückgegeben:
Jede Adresse hat 6 Felder, daher der Wert 6
Die neue Modulo-Spalte
Wie man im Bild oben erkennt, hat jetzt jedes Adressenfeld immer einen Wert von 0 bis 5. Nun können wir unsere Tabelle nach der Modulospalte pivotieren:
Die Modulo-Spalte wird pivotiert
Als Wertespalte wird dabei „Spalte1“ übernommen und in den erweiterten Optionen „Nicht aggregieren“ ausgewählt:
Die neuen Spaltenwerte sollen nicht aggregiert werden
Die Modulo-Werte 0 bis 5 sind jetzt zu Spaltenüberschriften geworden und die einzelnen Adressenfelder befinden sich nun schon in verschiedenen Spalten, wenn auch noch nicht in einer einzigen Zeile:
Die Adressenfelder stehen jetzt in verschiedenen Spalten
Jetzt werden die Spalten 1 bis 5 markiert (dazu einfach die Umschalt-Taste gedrückt halten) und die Transformation „Ausfüllen | Nach oben“ ausgewählt:
Spalten 1 bis 5 nach oben ausfüllen
Ergebnis nach dem Ausfüllen
Sieht schon gar nicht so schlecht aus, oder? Jetzt kommen nur noch ein paar Bereinigungsarbeiten. In der Spalte 0 werden alle Null-Werte ausgefiltert, damit bleibt nur noch eine Zeile pro Adresse übrig:
Nullwerte werden ausgefiltert
Die Index-Spalte wird noch gelöscht und wir können für die verbliebenen Spalten noch vernünftige Bezeichnungen eingeben:
Index-Spalte entfernt, Überschriften geändert
Fertig! Jetzt können wir die Abfrage zurück nach Excel laden und haben unsere transformierte Adressentabelle:
Transformierte Adressentabelle im Arbeitsblatt
Womit wieder einmal zwei Dinge bewiesen wären:
- Viele Wege führen in Excel zu einer Lösung
- Es lohnt sich unbedingt, sich mit Power Query zu beschäftigen
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Eine wunderbare Lösung. Vielen Dank. Ich stehe aktuell vor dem gleichen Problem, jedoch sind mal mehr, mal weniger Zeilen gefüllt.
Gibt es dafür ebenfalls eine Lösung? Vorab herzlichen Dank. Ihre Artikel sind seit Jahren wirklich fantastisch. Bitte weiter so.
Grüße aus Bremen
Moin.
Mir würde spontan ein Umweg vorschweben durch den du evtl. die Daten vorher noch automatisiert anpassen lassen musst…
Gibt es ein „widerkehrendes Muster“ dabei?
Sprich startet alles mit Namen, PLZ whatever?
Anhand dieses Kriteriums könntest du vielleicht etwas basteln was es dir erlaubt, das ganze aufzuteilen.
Liebe Grüße
Eloar
Hallo Gary,
vielen Dank für das tolle Feedback.
Was deine Frage angeht, da kann ich mich dem Kommentar von Eloar nur anschließen. Man bräuchte ein entsprechendes Muster, anhand dessen man eindeutig erkennen kann, wo ein neuer Datensatz beginnt.
Schöne Grüße,
Martin
Hallo Martin,
„Zeilen zu Spalten“, wieder ein interessanter Beitrag von dir. Das werde ich mal versuchen nachzustellen.
Learning by Doing ist immer besser als nur lesen.
Und, es wird Zeit, dass ich mich endlich auch mal mit Power Query beschäftige.
Beste Grüße
Gerhard
Hallo Gerhard,
vielen Dank! Ja, man muss die Dinge wirklich ausprobieren. Und die Arbeit mit Power Query lohnt sich auf jeden Fall!
Schöne Grüße,
Martin
Hallo Martin
Wieder ein sehr wertvoller Tipp. Ich wünschte, ich hätte solche Sachen gewusst, als ich noch in der Arbeitswelt war. Aber auch jetzt finde ich deine Website wahnsinnig interessant und deine Tipps sehr hilfreich!
Liebe Grüsse aus der Schweiz
Lisa
Hallo Lisa,
vielen Dank für dein tolles Feedback! Und es ist einfach so: Man lernt nie aus 🙂
Schöne Grüße in die Schweiz,
Martin
Ich habe mir eine Abfrage nach obiger Anleitung erstellt (sehr smarte Lösung). Da ich noch mehrere txt Dateien habe, auf die ich die gleiche Abfrage anwenden möchte, habe ich mir gedacht, daß ich die bestehende nun kopieren und einfügen kann (alternativ dupliziereren) und dann in der jeweils neuen die Datenquelle einfach ändere.
Ziel soll sein, das ich für meine 15 txt Dateien eine Kopie der Abfrage nutze, um jeweils andere Datenquellen zu hinterlegen. Somit könnte ich von Excelsheet zu Excelsheet klicken und die nun neu geordneten txt Inhalte betrachten.
Mit Excel (2016) ändert Excel jedoch jedesmal einheitlich auf die neu geänderte Datenquelle. Es ist nicht möglich das Excel die Datenquelle differenziert. Was mache ich noch falsch?
Hallo tomtom,
ich keine jetzt deine Abfragen nicht. Aber grundsätzlich gibt es keinen Grund (auch nicht in Excel 2016), warum man eine vorhandene Abfrage nicht mehrmals kopieren und dann jeweils im ersten Schritt „Quelle“ nicht auf eine andere Datenquelle verweisen könnte.
Wichtig ist nur, dass du die Abfragen wirklich duplizierst, und nicht nur einen Verweis auf die Ursprungsabfrage anlegst. Wenn es echte Duplikate sind, kannst du auch in jeder einzelnen Kopie die Quelle ändern.
Oder habe ich etwas missverstanden?
Schöne Grüße,
Martin
Starke Lösung. Danke Dir.
Gern geschehen!
Schöne Grüße,
Martin