Excel-TippUnterschiedliche Daten aus einer Spalte als Tabelle aufbereiten

So machen Sie mit Power Query aus einer einspaltigen Excel-Liste mit unterschiedlichen Daten eine Tabelle, in der alle gleichen Daten in einer Spalte korrekt angeordnet sind. Mit diesem Vorgehen lassen sich beispielsweise Adressdaten übersichtlich anordnen für eine weitere Bearbeitung.

Bei der Übernahme von Daten nach Excel stehen Sie häufig vor dem Problem, dass die Informationen untereinander in einer Spalte angeordnet sind. Ein klassisches Beispiel dafür ist eine Liste von Kontaktinformationen, in der sich Namen, Straßen, Postleitzahlen, Orte und Telefonnummern abwechseln.

Für eine bessere Verarbeitung möchten Sie diese Daten in eine fünfspaltige Tabelle umwandeln, mit den Spalten „Name“, „Straße“, „PLZ“, „Ort“ und „Telefonnummer“.

In diesem Beitrag zeigen wir Ihnen, wie Sie mit Power Query (auch bekannt als „Daten abrufen und transformieren“) dieses Ziel in wenigen Schritten erreichen.

Ausgangspunkt: Eine einspaltige Liste

Stellen Sie sich vor, Ihre Daten sehen wie folgt aus:

Tabelle mit unterschiedlichen Daten in einer einzigen Spalte (Auszug)

Das Ziel ist eine Tabelle in diesem Format:

Tabelle mit korrekt angeordneten Kontaktdaten

Schritt-für-Schritt-Anleitung für die Datenaufbereitung mit Power Query in Excel

1. Daten in Power Query laden

Markieren Sie die Daten in Ihrer Excel-Datei.

Spalte mit Daten markieren/ auswählen (Auszug)

Aktivieren Sie im Menüband die Befehlsfolge Registerkarte Daten > Befehlsgruppe Daten abrufen und transformieren > Befehl Aus Tabelle/Bereich

Es öffnet sich das Dialogfeld Tabelle erstellen. Wenn Sie gefragt werden, ob die Tabelle einen Header (Überschrift) hat, dann deaktivieren Sie das Kontrollkästchen, da die Ausgangsliste keine Spaltenüberschrift besitzt.

Aus der Ausgangsliste eine intelligente Tabelle erstellen (Auszug)

2. Daten vorbereiten

Es öffnet sich der Power-Query-Editor und Sie bekommen die importierten Ausgangsdaten wie im Tabellenblatt dargestellt.

Import der unstrukturierten Daten in Power Query (Auszug)

Ihre Liste besteht aus wiederkehrenden Blöcken von fünf Zeilen (Name, Straße, PLZ, Ort, Telefon). Um diese zu trennen, fügen Sie nun zunächst eine neue Indexspalte hinzu:

Aktivieren Sie im Menüband die Befehlsgruppe Start > Spalte hinzufügen > Indexspalte > Von 0.

Daraufhin wird eine neue Spalte (Indexspalte) eingefügt.

Erstellen Sie nun eine benutzerdefinierte Spalte, um die Datensätze (Zeile) zu identifizieren. Sie wissen: Jeder Datensatz besteht aus fünf Zeilen in der Ausgangsliste.

Aktivieren Sie im Menüband die Befehlsfolge Spalte hinzufügen > Benutzerdefinierte Spalte.

Benutzerdefinierte Spalte in Power Query hinzufügen

Erfassen Sie die folgende Formel im sich öffnenden Dialogfeld Benutzerdefinierte Spalte:

=Number.RoundDown([Index]/5)

Hierdurch wird der Wert der Indexspalte durch 5 dividiert und anschließend abgerundet. Sie erhalten somit für jeden Datensatz (5 Zeilen) eine gleiche ganze Zahl in der benutzerdefinierten Spalte.

Würde die Ausgangsliste aus einer anderen Blockgröße bestehen, dann müssen Sie den Divisor entsprechend anpassen. Besteht der Block zum Beispiel nur aus drei Zeilen (Name, Straße, Ort) dann müssen Sie als Divisor die Zahl 3 verwenden.

Daraufhin wird eine Hilfsspalte eingefügt, in welcher die jeweiligen Adressblöcke gekennzeichnet werden.

Neue Spalte mit einer Nummer zu jedem Kontaktdatensatz (jeweils 5 Zeilen)

Erstellen Sie eine weitere benutzerdefinierte Spalte, um den Typ des Inhalts einer Zeile zu identifizieren:

Gehen Sie auf Spalte hinzufügen > Benutzerdefinierte Spalte.

Erfassen Sie die folgende Formel:

=if Number.Mod([Index], 5) = 0 then "Name" else if Number.Mod([Index], 5) = 1 then "Straße" else if Number.Mod([Index], 5) = 2 then "PLZ" else if Number.Mod([Index], 5) = 3 then "Ort" else "Telefon"

Die Indexzahl umwandeln in den Typ des Zelleninhalts

Die Indexzeile wird hier auch durch 5 dividiert. Abhängig vom verbleibenden Rest der Division wird dann der entsprechende Typ zugewiesen:

0 = Name
1 = Straße
2 = PLZ
3 = Ort
4 = Telefon

Hinweis: Indem Sie Spalte1 und Spalte vergleichen, erkennen Sie, ob Ihre Zuordnung von Zelleninhalt und Typ des Inhalts korrekt ist. Anne Meier ist der Name, Musterstraße 12 ist die Straße etc.

Wenn Sie andere Daten importieren und aufbereiten wollen, müssen Sie die letzten Schritte und die Bezeichnungen entsprechend anpassen. Das Verfahren ist aber identisch.

Entfernen Sie nun die Hilfsspalte Index. Markieren Sie die Spalte, indem Sie mit der linken Maustaste auf den Spaltennamen klicken und anschließend im Menüband die Befehlsfolge Start > Spalten entfernen anklicken.

Nach diesem Schritt sieht die Tabelle in Power Query wie folgt aus.

Ergebnis nach Bearbeitung der Tabelle in Power Query (Auszug)

3. Pivotieren der Daten

Markieren Sie die neue benutzerdefinierte Spalte „Spalte“ und aktivieren Sie die Befehlsfolge Transformieren > Spalte pivotieren.

Es öffnet sich das Dialogfeld Spalte pivotieren. Wählen Sie als Werte-Spalte die ursprüngliche Spalte (welche die Namen, Straßen, PLZ, Orte und Telefonnummern enthält) aus.

Klicken Sie anschließend auf Erweiterte Optionen und wählen Sie hier bei Aggregatwertfunktion den Eintrag Nicht aggregieren aus.

Einstellungen zum Pivotieren einer Spalte

Bestätigen Sie, indem Sie das Dialogfeld durch Klick auf OK schließen. Ihre Tabelle ist nun in fünf Spalten aufgeteilt.

Ergebnis nach dem Pivotieren einer Spalte

4. Daten bereinigen

Entfernen Sie die Spalte Datensatz, da sie nicht mehr benötigt wird und verschieben Sie die Spalten nach der gewünschten Ausrichtung.

Geben Sie den Spaltennamen eventuell noch andere Namen, falls gewünscht. Außerdem können Sie den Datentyp der Spalte anpassen (Text).

5. Ergebnis in Excel laden

Klicken Sie im Menüband auf die Befehlsfolge Datei > Schließen & Laden

Mit Power Query aufbereitete Kontaktdaten

Die aufbereiteten Daten werden daraufhin in ein neues Tabellenblatt geschrieben.