Excel – PLZ und Ort

In einer Adressliste gibt es in- und ausländische Adressen. Es gibt drei Vorschriften für diese Liste:

  1. Land Deutschland
    PLZ Ort erster  Buchstabe groß
  2. Bei Ländern einer Liste (USA, UK, Australien etc.)
    Ort in Großbuchstaben Staat PLZ
  3. alle übrigen Länder
    PLZ Ort in Großbuchstaben

Wir haben die Liste der Länder aus Punkt 2 als formatierte Tabelle angelegt mit dem Namen tbl_umgekehrt (weil PLZ und Stadt im Vergleich zu Deutschland umgedreht werden). Dann kann die Länderliste jederzeit ergänzt werden.

Verwendet wurden vier Excel-Funktionen:

  • WENN und ISTFEHLER
    damit starte ich die Abfrage
  • GROSS
    damit wird die Großschreibung der Orte erreicht
  • VERGLEICH
    sucht das Land in der vorgegebenen Liste

Schlussendlich sah die Formel so aus:

=WENN(D2=“Deutschland“;A2&“ „&B2;WENN(ISTFEHLER(VERGLEICH(D2;tbl_umgekehrt;0));A2&“ „&GROSS(B2);GROSS(B2)&“ „&C2&“ „&A2))

13.12.2018_0000

In Spalte G sehen Sie die formatierte Tabelle mit den bisher erfassten Ländern, für die PLZ und Ort umgedreht werden müssen. In A steht die PLZ, in B der Ort, in C der Staat – sofern vorhanden und notwendig -, in D das Land. In E stelle ich alles zusammen.

Die Formel Schritt für Schritt:

=WENN(D2=”Deutschland”;A2&” “B2;
Wenn in D2 das Land “Deutschland” steht, dann schreibe den Inhalt von A2 plus einen Leerschritt plus den Inhalt von B2 – wichtig ist hier: keine Großschreibung des Ortes

WENN(ISTFEHLER(
Wenn bei der folgenden Berechnungen ein Fehlerwert ausgegeben wird – z. B. #NV für “nicht vorhanden” – jetzt kommt die Berechnung, die eventuell einen Fehlerwert ausgibt

VERGLEICH(D2;tbl_umgekehrt;0))
Vergleiche den Wert aus D2 in der Tabelle tbl_umgekehrt und suche den exakten Wert; kommt der Wert nicht vor, wird eine Fehlermeldung #NV ausgegeben – darauf reagiert die Funktion ISTFEHLER.

A2&” “&GROSS(B2)
Wenn also der ISTFEHLER anspringt, weil das Land nicht in der Liste vorkommt, dann schreibe den Wert aus A2 plus einen Leerschritt plus den Inhalt aus B2 in Großbuchstaben

GROSS(B2)&“ „&C2&“ „&A2
Das letzte Argument ist das gemeinsame SONST aus beiden WENN: wenn es nicht Deutschland ist und wenn kein Fehlerwert produziert wurde, dann schreibe in Großbuchstaben den Inhalt aus B2 plus einen Leerschritt plus den Inhalt aus C2 plus den Inhalt aus A2.

Stören Sie die zwei Leerschritte, die entstehen, wenn eine Adresse keinen Eintrag in C2 hat? Dann schreiben Sie  im letzten Argument statt &C2& noch einmal ein WENN:

WENN(C2<>““;C2&“ „;““)&A2))
Wenn C2 nicht leer ist (<> heißt “nicht” und “” bedeutet “leer”), dann schreibe C2 plus einen Leerschritt, sonst schreibe nichts. Diese Formel sieht komplett so aus:

WENN(D2=“Deutschland“;A2&“ „&B2;WENN(ISTFEHLER(VERGLEICH(D2;tbl_umgekehrt;0));A2&“ „&GROSS(B2);GROSS(B2)&“ „&WENN(C2<>““;C2&“ „;““)&A2))

Kompliziert? Ja, wenn man sich die komplette, fertige Formel anschaut. Aber sie entsteht in Schritten. Ich  habe zuerst ausprobiert, ob ich mit VERGLEICH feststellen kann, ob ein Land in meiner Liste vorkommt. VERGLEICH liefert bei Vorkommen eine Zahl, bei Nichtvorkommen #NV. Ich kann also unterscheiden:

  • Liefert VERGLEICH #NV, dann schreibe PLZ und Ort in Großbuchstaben
  • sonst schreibe Ort in Großbuchstaben plus Staat plus PLZ

Ob #NV geliefert wird, kann ich mit ISTFEHLER erkennen. So ist der mittlere Teil entstanden.

Dann muss ich noch Deutschland abfragen – das kam ganz vorne hin. Und als gemeinsames Sonst alle anderen Länder.

Nein, das ist nicht in drei Minuten aus dem Ärmel geschüttelt. Da ist erst ein Problem, dann eine vage Idee, dann eine Lösung mit drei, vier Hilfsspalten, dann eine langsame  Reduzierung.

Nur Mut! Traut Euch!

Wer gucken will: Beispieltabelle

Werbeanzeigen

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden /  Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s