Excel: Datensätze säubern, vereinheitlichen und automatisch ausfüllen

Posted by on Nov 7, 2012 in News | 13 Comments

Ich hatte gestern einen Datensatz von Postleitzahlen und Gemeinden/Landkreisen/Städten (im Rohformat 8000 Zeilen) am Wickel, an dem ich einmal ein paar Aufräum-Arbeiten demonstrieren will – ich konnte so einer Kollegin mehrere Stunden Handarbeit sparen.

Aufbau des Datensatzes (Beispiel-Datensatz) ist relativ simpel: PLZ; Stadt/Landkreis; Stadtteil/Gemeinde.

Problematisch ist die letzte Spalte, in der die einzelnen Gemeinden mit Semikolon getrennt für eine Stadt zusammengefasst sind. Ich will aber für jede Gemeinde eine vollständig ausgefüllte Zeile haben:

Erster Schritt ist also, jeweils ein Semikolon durch eine neue Zeile zu ersetzen. Dafür markiere ich den entsprechenden Tabellenbereich und kopiere ihn in einen Texteditor und von dort nach Word (um Word daran zu hindern, aus der Zwischenablage ein echtes Tabellendokument zu machen).

Ich suche&ersetze das Semikolon durch ein ^p (steht für Paragraph, also neuen Absatz – und rücke die einzelnen Gemeinden noch um zwei ^t (Tabularoren) ein:

Suche ; – ersetze das durch ^p^t^t

Wenn ich diesen Text jetzt zurück zu Excel kopiere, sieht er so aus (um die fehlende “0″ am Anfang der PLZ kümmern wir uns später):

Jetzt hat der Datensatz insgesamt etwas über 14.000 Zeilen, die fehlenden Zellen händisch auszufüllen wäre utopisch, ich bediene mich hier einer einfachen WENN-Formel, in der ich Excel sage: Schaue nach,ob in der ersten Zelle etwas steht, wenn nicht, nimm den Inhalt der Zelle darüber und schreibe ihn in die leeren Felder. Sieht in Excel dann so aus:

=WENN(A3=0;E2;A3)

Diese Formel muss ich dann nur noch bis zur letzten Zeile herunterziehen und habe einen bereinigten Datensatz. Excel ist sehr undankbar, was den Umgang mit Postleitzahlen angeht – im “Zellen formatieren” gibt es unter “Sonderformate” den Punkt “Postleitzahl D”, der mir die Zahlen dann in einem verwertbaren Format darstellt.

Spart ne Menge Zeit…

 

13 Comments

  1. jmi
    2012-11-07

    Die Frage, die ich mir gerade stelle: Wie könnte ich ähnliches in einer CSV-Textdatei mittels Regular Expressions und ein bisschen Python o.ä. anstellen?

    Mag mir sie jemand beantworten?

  2. Mettwurstballett
    2012-11-08

    Der Umweg über den Texteditor ist nicht einmal nötig. In OpenOffice kann man einfach mit Strg+Shift+V einfügen, und dann auswählen, dass es Unformatierter Text sein soll. Ich meine, dass man in Word über “Inhalte einfügen” die Möglichkeit hat, beim “pasten” das Format zu wählen.

  3. maas
    2012-11-08

    Mettwurstballett hat recht – ich hab es gerade mal geprüft – für den mac und office-produkte ist die “inhalte einfügen” kombi “apfel dach v”, dann geht es auch. wie schön, selbst was gelernt ;-)

  4. @jbspeakr
    2012-11-30

    danke, konnte dieses kleine howto gut gebrauchen!
    für libreOffice ist der RegEx übrigens ntt

    cheers.

  5. Macsico
    2013-01-28

    Die Wandlung der Postleitzahlen a la Excel als Anzeigeformat hielt ich früher mal für gut – bis mich der mehrfach Hin- und Herschieben zwischen Excel und anderen Programmen eines Besseren belehrte.

    Wenn man nämlich später mal in Excel einen SVERWEIS benutzen will, muss der pure Inhalt und nicht das Anzeige-Format genutzt werden.

    Lösung dafür:
    1) Zusatzspalte in Excel rechts der PLZ-Spalte, ausdrücklich mit dem Format TEXT
    2) darin Formel (Überschriftenzeile vorausgesetzt): =WENN(LÄNGE(A2)=4;VERKETTEN(“0″;A2);A2)

    3) Ergebnis
    PLZ PLZ (bereinigt)
    1004 01004
    90445 90445

    4) Format TEXT, weil Excel sonst beim nächsten Hin und her kopieren wieder die führende NULL killt. Hat mich in Kunden-Projekten damals massivst genervt, bis ich zu obiger Lösung kam.

  6. Macsico
    2013-01-28

    Nachtrag:

    Deutsche PLZ selbst sind eine Sonderklasse an Daten für sich. Zitat aus meiner Abschluss-Dokumentation von damals (Stand 2009):

    ==================
    Folgende Eigenheiten treten auf:
    • eine Postleitzahl wird für bis zu ca. 60 verschiedene Orte verwendet (Bsp: PLZ 14913: 59x, PLZ 23936: 59x, PLZ 54597: 39x)
    • kleine Randgemeinden, Variante 1: die gleiche Postleitzahl ist gültig für zwei Orte in zwei Bundesländern (Bsp: 22113 Oststeinbek (Schleswig-Holstein) vs. 22113 Hamburg)
    • kleine Randgemeinden, Variante 2: Postleitzahl liegt im Nummernkreis der Großstadt und hat keine Überschneidung mit einer PLZ der Großstadt: 12529 Schönefeld (Brandenburg) liegt zwischen 12527 Berlin und 12555 Berlin)
    • Postleitzonen sind bundeslandübergreifend: Beispielswiese können Postleitzahlen beginnend mit einer „3“ in den Bundesländern Niedersachsen, Nordrhein-Westfalen, Hessen, Thüringen oder Sachsen-Anhalt liegen.
    • Postleitzahlen sind veränderlich: Auch die Deutsche Post AG aktualisiert ihre Daten mehrfach im Jahr, so dass auch hier Gebietsstandänderungen zu einem Verschwinden alter PLZ und zum Auftauchen neuer PLZ führen kann.

    Faustregel 1: Eine Postleitzahl ist niemals alleine zur zweifelsfreien Identifizierung einer deutschen Gemeinde ausreichend!

    Eine zusätzliche Besonderheit gibt es bei deutschen Ortsnamen. Es gibt in Deutschland mehrere hundert Orte, die mindestens zwei Mal unter gleichem Namen vorhanden sind, die aber räumlich weit voneinander entfernt sind. Da gemäß postalischer Vorgaben Ortsnamenszusätze entfallen können, weisen viele Portofolio-Datensätze mittlerweile nur noch nach dieser Vorgabe bereinigte Ortsnamen auf. Ein Beispiel hierfür ist Essen in Niedersachsen (PLZ 49632), welches einen Namensvetter in Nordrhein-Westfalen hat (PLZ 45127 bis PLZ 45359). Auch die vielen Orte namens „Neustadt“ fallen in diese Problemkategorie.

    Faustregel 2: Ein Ortsname ist niemals alleine zur zweifelsfreien Identifizierung einer deutschen Gemeinde ausreichend!
    ==================

    Soweit das Zitat.

    8.000 Ortsteile in DE erscheint mir verdächtig wenig. Meine DeStatis-Tabelle hatte alleine schon über 12.000 Gemeinden, und eine gekaufte GfK-Tabelle verfügte über knapp 18.000 PLZ/Orts-Kombinationen. Woher stammen die erwähnten Orts-Daten?

  7. marco
    2013-01-28

    ich hatte da keine komplette tabelle, es handelte sich bei dem datensatz um ausgewählte landstiche- von daher: nicht schlimm. deine hinweise sind aber gut und richtig – danke!

  8. Heiko
    2013-06-25

    Hallo Marco,

    die Tutorials von Michael Kreil stehen unter der Lizenz Creative Commons by 3.0. Unter welcher Lizenz steht dieses Tutorial hier ?

  9. maas
    2013-06-25

    moin heiko,
    lizenz ist hierfür auch cc by 3.0.

  10. ronny
    2013-07-15

    statt der Wenn-Formel: Markiere einfach die Spalte der PLZ, wähle dann (unter Windows Excel) Gehe zuInhalteLeerzellen. Dann sind nur noch die Leerzellen ausgewählt. Dann einfach eingeben: =+Pfeil oben+Strg+Enter.
    Damit wird in jede Leerzelle eine Formel geschrieben, die einen Verweis auf die obere Zelle enthält.

  11. derBustaa
    2013-10-09

    Dafür ist doch mit Sicherheit in den “Asap-Utilities” (ziemlich gute Excel AddIn Sammlung) was zu finden (Link gibt es über Google, will ja nicht des URL Spam’s bezichtigt werden ^_^).

    Grüße

  12. Dino Jakubovic
    2013-10-24

    Wenn man spezielle Anforderungen an Excel hat, kann man ja auch Anwendungen, die Excel-orientiert sind, nutzen. Diese lassen sich ja von Experten wie hier programmieren und beibringen. Meiner Meinung nach vernünftiger, als ewig lange Arbeitsprozesse aufzuwenden. Mit einer Anwendung kann man schließlich viele Funktionen in einem Befehl unterbringen und so schneller arbeiten.