Excel-TippLücken und falsche Listeneinträge ausbessern
Ein häufiges Problem aus der Praxis: Sie erhalten eine Liste mit Daten, die auf den ersten Blick gut strukturiert aussieht – eine Überschrift, gefolgt von den entsprechenden Werten.
Doch beim genaueren Hinsehen bemerken Sie, dass einige Werte nicht in der richtigen Spalte stehen. Manche sind um eine oder zwei Spalten verrutscht – und das leider ohne ein klares Muster.
Wie kommt es zu falschen Einträgen in Excel-Tabellen?
Häufig liegt der Fehler in der ursprünglichen Datenquelle. Wenn die Liste aus einer CSV- oder Textdatei stammt, können unerwünschte Sonderzeichen wie Kommata oder Semikola enthalten sein.
Diese Zeichen führen dazu, dass Excel oder eine Datenbank die Daten falsch interpretiert und Spalten falsch anlegt. Das Ergebnis: eine fehlerhafte und schwer nutzbare Tabelle.
Natürlich könnte man das Problem händisch in Excel korrigieren, doch eine einfachere und schnellere Lösung bietet Power Query.
In der folgenden Abbildung sehen Sie ein Beispiel für das oben beschriebene Problem.
Beachten Sie: Damit die Liste mit allen Werten am Ende korrekt ist, dürfen die Einträge in der Ursprungstabelle verschoben sein. Sie müssen aber alle vorhanden sein. Fehlt in einer Zeile ein Eintrag wie der Straßenname komplett, werden die Daten nicht in die richtige Spalte verschoben. Fügen Sie dazu bei den fehlenden Einträgen ein Sonderzeichen oder einfach ein Leerzeichen (Leertaste/Blank) ein.
Fehlerhafte Tabelle in Power Query importieren
Diese Liste können Sie in Power Query entsprechend transformieren und aufbereiten. Zuerst müssen Sie die Daten in Power Query importieren.
Markieren Sie den kompletten Bereich, also alle Zellen, auf die sich die Daten verteilen.
Aktivieren Sie dann im Menüband die Befehlsfolge Registerkarte Daten > Befehlsgruppe Daten abrufen und transformieren > Befehl Aus Tabelle
Damit der Import vorgenommen werden kann, muss der Bereich zunächst in eine intelligente Tabelle umgewandelt werden. Es öffnet sich deshalb das Dialogfeld Tabelle erstellen.
Da die Liste keine Überschriften hat, deaktivieren Sie das Kontrollkästchen Tabelle hat Überschriften.
Bestätigen Sie Ihre Einstellungen, indem Sie das Dialogfeld durch Klick auf OK schließen.
Die Liste wird nun in Power Query geladen – perfekt für die Bereinigung!
Aus vielen Spalten eine Spalte mit allen Daten erzeugen
Markieren Sie alle Spalten, indem Sie diese bei gedrückter Strg-Taste alle nacheinander anklicken.
Aktivieren Sie dann im Menüband die Befehlsfolge Spalte hinzufügen > Spalten zusammenführen.
Es öffnet sich das Dialogfeld Spalten zusammenführen. Hier können Sie ein Trennzeichen für die einzelnen Spalten beim Zusammenführen festlegen.
Wählen Sie hier Benutzerdefiniert aus und wählen Sie anschließend ein seltenes Trennzeichen aus, zum Beispiel | (senkrechter Strich) oder ~ (Tilde), um die Werte zu trennen. Im Beispiel ist das Zeichen % als Trennzeichen gewählt.
Wichtig: Das Trennzeichen darf nicht in den Zellen vorkommen!
Bestätigen Sie Ihre Einstellung, indem Sie das Dialogfeld durch Klick auf OK schließen.
Excel fügt eine neue Spalte ein, in der sich alle Werte befinden, getrennt durch das %-Zeichen. Bei leeren Zellen kommen zwei %-Zeichen hintereinander.
Entfernen Sie im nächsten Schritt die anderen Spalten.
Klicken Sie mit der rechten Maustaste auf eine markierte Spaltenüberschrift und wählen Sie im Kontextmenü den Eintrag Spalten entfernen aus.
Daraufhin bleibt nur noch die neue Spalte mit den zusammengefügten Spalten übrig.
Daten in der Spalte bereinigen
Nun müssen Sie die Werte in dieser Spalte bereinigen. Doppelte Trennzeichen deuten auf eine Leerzelle hin, die entfernt werden muss. Ersetzen Sie daher ein doppeltes Trennzeichen (%%) durch ein einfaches Trennzeichen (%).
Klicken Sie hierzu mit der rechten Maustaste auf die Spaltenüberschrift und wählen Sie im Kontextmenü den Eintrag Werte ersetzen aus.
Es öffnet sich das Dialogfeld Werte ersetzen.
Ersetzen Sie hier %% durch % und bestätigen Sie Ihre Einstellung, indem Sie das Dialogfeld durch Klick auf OK schließen.
Wiederholen Sie den Vorgang mehrmals, bis es keine doppelten Trennzeichen mehr gibt.
Spalte nach Bereinigung wieder aufteilen
Gibt es nur noch hintereinander einmal vorkommende Trennzeichen, dann teilen Sie die Spalte wieder auf mehrere Spalten nach dem Trennzeichen % auf.
Klicken Sie hierzu in der Spaltenüberschrift mit der rechten Maustaste und wählen Sie im Kontextmenü den Eintrag Spalte teilen > Nach Trennzeichen aus.
Es öffnet sich das Dialogfeld Spalte nach Trennzeichen teilen. Wählen Sie hier das von Ihnen definierte Trennzeichen (%) aus.
Bei Aufteilen aktivieren Sie das Optionsfeld Bei jedem Vorkommen des Trennzeichens.
Bestätigen Sie anschließend Ihre Einstellungen, indem Sie das Dialogfeld durch Klick auf OK schließen.
Nun werden die Daten wieder in Spalten aufgeteilt – dieses Mal in der richtigen Ordnung!
Benennen Sie die Spalten noch sinnvoll um. Dann können Sie die bereinigte Liste wieder nach Excel laden: Datei > Schließen & laden > Schließen & laden in...
Mit dieser Methode können Sie in wenigen Schritten chaotische Listen in Power Query bereinigen und korrekt formatierte Tabellen erzeugen – ohne mühsame Handarbeit in Excel!
