Eine Anleitung zur Bereinigung unsauberer Daten in Excel, einschließlich der Entfernung von Duplikaten, des Kürzens von Leerzeichen und der Korrektur von Formatierungen.

FAQ — Welche Fragen werden in diesem Artikel beantwortet?

Q: Warum geben meine VLOOKUP- und SUMIF-Formeln Fehler aus, obwohl der entsprechende Wert eindeutig vorhanden ist?

A: Dies liegt in der Regel an unsichtbaren Zeichen. Die Zelle mit dem Eintrag „Anna Müller” (mit einem führenden Leerzeichen) stimmt nicht mit dem Eintrag „Anna Müller” in unseren Datensätzen überein. Wenn Sie die Funktion GLÄTTEN verwenden, werden diese Leerzeichen entfernt und die Formel funktioniert sofort.

Q: Wie lassen sich Duplikate in einer Liste am schnellsten entfernen?

A: Verwenden Sie die EINDEUTIG-Formel als dynamische Array/Bereich-Formel. Sie gibt in einem Schritt eine Liste der nicht doppelten Einträge zurück, aktualisiert sich automatisch, wenn sich Ihre Quelldaten ändern, und verändert die Originaldaten nicht. Wenn Sie doppelte Zeilen in einer Tabelle entfernen müssen, probieren Sie das Tool „Duplikate entfernen“ aus. Sie finden es unter „Daten“ > „Datenwerkzeuge“.

Q: Meine Zahlen sind als Text gespeichert und werden daher von der SUM-Funktion nicht erkannt. Wie kann ich das beheben?

A: Verwenden Sie die Funktion WERT, um Text in Zahlen umzuwandeln, oder multiplizieren Sie die Spalte mithilfe einer Array-Formel mit 1: Excel behandelt den Inhalt in beiden Fällen als Zahl. In diesem Artikel werden beide Methoden vorgestellt.

Q: Wie kann ich die uneinheitliche Verwendung von Großbuchstaben beheben, wenn manche Namen in Großbuchstaben und andere in Kleinbuchstaben geschrieben sind?

A: Verwenden Sie die Funktion GROSS2, um beliebigen Text in Titelschreibweise umzuwandeln (wobei der erste Buchstabe jedes Wortes großgeschrieben wird). Alternativ können Sie die Funktion GROSS verwenden, um alle Buchstaben in Großbuchstaben umzuwandeln, oder die Funktion KLEIN, um alle Buchstaben in Kleinbuchstaben umzuwandeln. In Kombination mit der Funktion GLÄTTEN können Sie sowohl die Leerzeichen als auch die Groß-/Kleinschreibung in einem Schritt korrigieren.

Q: Kann ich eine ganze Spalte auf einmal bereinigen, statt die einzelnen Zellen zu bearbeiten?

A: Ja, das ist möglich. Alle in diesem Artikel beschriebenen Bereinigungsfunktionen funktionieren in Excel 365 als Array-Formeln. Geben Sie sie dazu einfach in eine Zelle ein. Die Formel wird dann automatisch auf die gesamte Spalte angewendet. Die folgenden Beispiele zeigen Ihnen, wie das geht.

Oft brechen Excel-Workflows aufgrund von fehlerhaften Daten ab. Die Formel sollte eigentlich funktionieren, tut es aber nicht. In einer Pivot-Tabelle wird derselbe Name zweimal angezeigt, weil ein Eintrag ein abschließendes Leerzeichen enthält. Die SUMMME-Funktion gibt Null zurück, weil die Zahlen aus einem Export stammen und als Text gespeichert sind. Dies sind keine Probleme mit den Formeln, sondern mit den Daten. Für jedes dieser Probleme gibt es eine einfache Lösung.

In diesem Artikel wird erläutert, wie sich ein fehlerhafter HR-Export korrigieren lässt. Dazu werden alle Bereinigungstechniken der Reihe nach durchgesprochen, von der einfachsten bis zur leistungsstärksten.

Die Daten, mit denen wir derzeit arbeiten, werden als „Rohdaten” bezeichnet.

Stellen Sie sich den folgenden Export von Mitarbeiterarbeitszeiten vor, der aus einem externen System in die Spalten A bis E eingefügt wurde:

In diesen Daten sind folgende Probleme erkennbar:

• Einige Buchstaben sind großgeschrieben, andere nicht. Beispiele sind „hr“, „HR“, „Sales“, „sales“, „ACTIVE“ und „active“.
• Es gibt führende und nachfolgende Leerzeichen bei „Sara Becker“ und „TOM FISCHER“.
• Eine Zeile, die kopiert wurde. Anna Müller sieht in beiden Einträgen gleich aus.
• Ein Textwert in einer Zahlenspalte. Weitere Informationen finden Sie im Abschnitt „see note“.
• Dies könnte ein Duplikat sein und es könnte ein Datenkonflikt vorliegen, da Sara Becker zweimal aufgeführt ist, mit unterschiedlichen Stundenwerten.

Dies ist ein typischer Export aus der Praxis. Nun werden wir das Problem Schritt für Schritt beheben.

Erster Schritt: Entfernen Sie mithilfe der GLÄTTEN-Funktion alle überflüssigen Leerzeichen am Anfang oder Ende eines Satzes.

Die GLÄTTEN-Funktion entfernt alle führenden und nachfolgenden Leerzeichen aus einem Dokument, um so für eine einheitliche Schreibweise zu sorgen. Außerdem wandelt sie aufeinanderfolgende Leerzeichen innerhalb eines Satzes in ein einzelnes Leerzeichen um. Dies sollten Sie als Erstes tun, wenn Sie Text in Ihre Datenbank importieren.

Wenn Sie die GLÄTTEN Funktion nicht verwenden, erhalten Sie eine Fehlermeldung, wenn Sie nach „Sara Becker“ suchen. Excel weist Sie nicht auf den Fehler hin, sondern liefert ein falsches Ergebnis.

Einzelne Zelle:

=GLÄTTEN(A2)

Gesamte Spalte als Spill-Array (Excel 365):

=GLÄTTEN(A2:A8)

Geben Sie dies in eine freie Spalte ein und es wird automatisch für jede Zeile dieser Spalte ausgefüllt. Sie müssen es nicht kopieren oder ziehen.

Die Funktion GLÄTTEN entfernt keine geschützten Leerzeichen (Unicode-Zeichen 160). Diese kommen häufig in Daten vor, die von Websites oder bestimmten ERP-Systemen kopiert wurden. Kombinieren Sie in solchen Fällen GLÄTTEN mit WECHSELN:

=GLÄTTEN(WECHSELN(A2, ZEICHEN(160), " "))

Über die gesamte Spalte verteilt:

=GLÄTTEN(WECHSELN(A2:A8, ZEICHEN(160), " "))

Im zweiten Schritt wird die Funktion SÄUBERN verwendet, um alle nicht druckbaren Zeichen zu entfernen.

Daten, die aus Datenbanken oder älteren Systemen exportiert wurden, enthalten häufig unsichtbare Steuerzeichen (z. B. Zeilenumbrüche, Nullzeichen, Tabulatorzeichen), die von der Funktion GLÄTTEN nicht entfernt werden. Die Funktion SÄUBERN entfernt hingegen alle nicht druckbaren Zeichen, d. h. die ASCII-Codes 0–31.

=SÄUBERN(A2)

Die beiden Funktionen werden fast immer zusammen verwendet. Die richtige Reihenfolge ist, die Datei zuerst mit SÄUBERN zu bereinigen und anschließend mit GLÄTTEN zu trimmen. Denn bei der Bereinigung können an Stellen, an denen sich zuvor Steuerzeichen befanden, zusätzliche Leerzeichen zurückbleiben.

=GLÄTTEN(SÄUBERN(A2))

Als Überlauf-Array:

=GLÄTTEN(SÄUBERN(A2:A8))

Stellen Sie zunächst für jede Textspalte sicher, dass Sie dies tun, bevor Sie mit anderen Schritten fortfahren.

Der dritte Schritt besteht darin, die Großschreibungsregeln GROSS2, GROSS und KLEIN anzuwenden.

In unseren Daten reichen die Namen von „Anna Müller“ bis „Klaus Weber“. Die Namen der Abteilungen sind als „hr“, „HR“, „sales“ und „Sales“ geschrieben. Dadurch funktionieren die Gruppierung in Pivot-Tabellen und die Filterung in dynamischen Array-Formeln nicht.

Die Funktion GROSS2 wandelt jeden Text in Titelschreibweise um, das heißt, der erste Buchstabe jedes Wortes wird großgeschrieben und der Rest erscheint in Kleinbuchstaben.

=GROSS2(A2)

„anna müller“ wird zu „Anna Müller“. „KLAUS WEBER“ wird zu „Klaus Weber“. „HR“ wird zu „Hr“ – was eine Einschränkung darstellt: GROSS2 erkennt nicht, dass „HR“ eine Abkürzung ist. Für Abkürzungen ist ein nachfolgender WECHSELN-Befehl erforderlich. Für die meisten Spalten mit Namen und Abteilungen reicht GROSS2 allein aus.

Kombination von GLÄTTEN, SÄUBERN und GROSS2 zu einer einzigen Spill-Formel, die die gesamte Namensspalte in einem Schritt bereinigt:

=GROSS2(GLÄTTEN(SÄUBERN(A2:A8)))

Dies ist die Standardformel, die Sie verwenden sollten, wenn Sie Textspalten mit Namen oder Bezeichnungen importieren.

Für Statusspalten wie active, „ACTIVE" und „Active", bei denen Sie einen einheitlichen Wert wünschen, ist KLEIN besser geeignet als GROSS2.

=KLEIN(GLÄTTEN(E2:E8))

Im vierten Schritt werden Textzahlen mithilfe der Funktion „WERT“ in reelle Zahlen umgewandelt.

In der Spalte „Stundenlohn“ sind die Zahlen 32,5, 28, 35, „see note“, 28 sowie eine Textzeichenfolge enthalten. Zudem werden Zahlen, die aus externen Systemen importiert werden, oft als Text gespeichert, selbst wenn sie wie Zahlen aussehen. Sie erkennen dies daran, dass die Zelle linksbündig ausgerichtet ist oder in der oberen linken Ecke ein grünes Dreieck angezeigt wird.

Die Funktion WERT wandelt eine als Text dargestellte Zahl in eine reelle Zahl um.

=WERT(D2)

Enthält Ihre Spalte eine Mischung aus Zahlen und Text, beispielsweise „see note“, verwenden Sie die Funktion WENNFEHLER, um Zellen ohne Zahlen zu behandeln.

=WENNFEHLER(WERT(D2), 0)

Als über die Spalte verteiltes Array, wobei nicht-numerische Werte durch Null ersetzt werden.

=WENNFEHLER(WERT(D2:D8), 0)

Wenn Sie problematische Zellen lieber kennzeichnen möchten, anstatt sie durch Null zu ersetzen,.

=WENNFEHLER(WERT(D2:D8), "prüfen")

Eine Alternative zu WERT für rein numerische Spalten ist die Multiplikation mit 1, wodurch eine Typkonvertierung erzwungen wird.

=D2:D8 * 1

Bei großen Datensätzen ist dies etwas schneller, aber WENNFEHLER(WERT(...) ) ist übersichtlicher und behandelt Fehler direkt.

Der fünfte Schritt besteht darin, Duplikate mithilfe der Funktion EINDEUTIG zu finden und zu löschen.

In unseren Daten gibt es zwei identische Zeilen für Anna Müller. Es gibt zwei Einträge für Sara Becker mit unterschiedlichen Stundenwerten. Dies ist ein Datenkonflikt, aber kein echtes Duplikat.

Wenn Sie die Funktion EINDEUTIG verwenden, gibt sie eine Liste zurück, aus der doppelte Zeilen entfernt wurden. Wenn Sie die Funktion auf die gesamte Tabelle anwenden, nachdem diese bereinigt wurde.

=EINDEUTIG(A2:E8)

Dies gibt jede Zeile zurück, die nur einmal vorkommt, sowie eine Kopie jeder Zeile, die mehr als einmal vorkommt. In unserem Fall ist die zweite Zeile mit „Anna Müller” verschwunden.

Um jedoch nur Zeilen zurückzugeben, die genau einmal vorkommen, und damit zu zeigen, dass Sara Becker in einen Konflikt verwickelt ist, statt heimlich eine Kopie beizubehalten, muss die Abfrage entsprechend angepasst werden.

=EINDEUTIG(A2:E8, FALSCH, WAHR)

Das dritte Argument, das auf „WAHR“ gesetzt ist, weist das Programm an, Zeilen zurückzugeben, die genau einmal vorkommen. Anna Müller verschwindet aus beiden Listen, da sie ein Duplikat ist. Sara Becker verschwindet ebenfalls, da sie ein Problem darstellt. Beide Fälle müssen von einer Person überprüft werden. Dies ist der sicherere Ansatz für Personaldaten.

Um herauszufinden, welche Namen mehr als einmal vorkommen, bevor Sie etwas entfernen.

=FILTER(A2:A8, ZÄHLENWENN(A2:A8, A2:A8) > 1)

Dies gibt eine Liste aller Namen zurück, bei denen mindestens ein Duplikat vorhanden ist. Auf diese Weise lassen sich gezielt nur die problematischen Zeilen ermitteln, bevor entschieden wird, wie mit ihnen verfahren werden soll.

Der sechste Schritt besteht darin, alle Informationen in einer Tabelle zusammenzufassen, die bereits das richtige Format hat.

Anstatt die Originaldaten in Excel 365 zu ändern, empfiehlt es sich, in einem separaten Bereich eine bereinigte Version zu erstellen. Dabei wird für jede Spalte eine eigene Formel verwendet. Für die Spalte „Mitarbeiter“ gilt:

=EINDEUTIG(GROSS2(GLÄTTEN(SÄUBERN(A2:A8))))

Mit dieser Formel werden Leerzeichen bereinigt, Steuerzeichen entfernt, die Groß- und Kleinschreibung vereinheitlicht und Duplikate gelöscht – und das alles in einem Schritt. Das Ergebnis wird automatisch übernommen und aktualisiert sich, sobald sich die Quelldaten ändern.

Um eine Tabelle ordnungsgemäß zu bereinigen, verwenden Sie für jede Spalte die entsprechende Formel.

• Textspalten (Namen, Abteilungen):
  =GROSS2(GLÄTTEN(SÄUBERN(Quellbereich)))
• Statusspalten: =KLEIN(GLÄTTEN(Quellbereich))
• Zahlenspalten: =WENNFEHLER(WERT(Quellbereich), „Überprüfen“)
• Vollständige Duplikatsbereinigung der Tabelle: =EINDEUTIG(bereinigter_Bereich)

Die Funktionen im Überblick

Zusammenfassung

Uneinheitliche Daten korrigieren sich nicht von selbst und es ist Zeitverschwendung, zu versuchen, fehlerhafte Daten mit Formeln zu korrigieren. Die hier behandelten Bereinigungsfunktionen GLÄTTEN, SÄUBERN, GROSS2, WERT und EINDEUTIG sind schnell, können zusammen verwendet werden und funktionieren alle als dynamische Array-Formeln in Excel 365: Wenn Sie sie auf einen anderen Ausgabebereich anwenden, bleiben die Originaldaten unverändert und Ihre nachgelagerten Formeln, Pivot-Tabellen und Berichte funktionieren ab diesem Zeitpunkt zuverlässig.

Wenn Sie Excel-Tabellen als Datenquelle verwenden, können Sie den bereinigten Ausgabebereich direkt in eine Tabelle übernehmen. Diese Tabelle können Sie dann für Funktionen wie FILTER und SORT sowie für alle weiteren Analysen verwenden, die Sie durchführen möchten. Lesen Sie auch So verwenden Sie Excel-Tabellen: Warum jeder Datensatz im Tabellenformat vorliegen sollteund wie Sie den nächsten Schritt strukturieren.