Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
976to980
976to980
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Postleitzahlen Werte aus Tabelle zuweisen... ?!?

Forumthread: Postleitzahlen Werte aus Tabelle zuweisen... ?!?

Postleitzahlen Werte aus Tabelle zuweisen... ?!?
17.05.2008 12:45:53
DerBasti83
Hi,
ich zerbreche mir jetzt schon seit mehreren Tagen an einem Problem den Kopf... Ich hoffe ihr habt da eine Lösung für mich. Vielleicht kennt der eine oder andere ja auch ein ähnliches Problem...
In einer Exceldatei habe ich ein Arbeitsblatt, welches wie folgt aussieht:
A B C
von PLZ bis PLZ Relation
1 1067 1071 A101
2 1075 1081 A103
3 1085 1539 A101
4 1540 A102
5 1602 A104
Es gibt also die Spalten "A = von PLZ" ; "B = bis PLZ" ; C = Relation"!
Auf einem 2 Arbeitsblatt in der gleichen Datei gibt es nun alle Postleitzahlen einzeln in Spalte A untereinander geschrieben.
Nun das Problem:
In Spalte B auf dem Arbeitsblatt mit den einzelnen Postleitzahlen soll die entsprechende Relation eingetragen werden, die auf dem 1. Arbeitsblatt dem entsprechenden Bereich zugeteilt ist.
Am Ende soll das Ergebnis dann folgendermaßen aussehen:
A B
PLZ Relation
1 1067 A101
2 1068 A101
3 1069 A101
4 1070 A101
5 1071 A101
6 1075 A103
7 1076 A103
8 1077 A103
So, leider sehe ich gerade in der vorschau das das Ganze nicht gerade als Tabelle zu erkennen ist, aber ich denke ihr könnt euch denken wie das gemeint ist.
Um hinter die aufgeschlüsselten Postleitzahlen die Relationen in eine Spalte zu schreiben muss es doch eine Funktion geben, oder? Irrgentwie muss man doch die Postleitzahl einem Bereich aus dem ersten Arbeitsblatt zuordnen können und so den die Relation die daneben steht auch neben die einzelne Postleitzahl des zweiten Arbeitsblattes übernehmen können, ODER?
Ich beisse mir jetzt schon Tagelang die Zähne daran aus. Hoffemtlich habt ihr da eine Lösung für mich...
PS: Falls ihr euch fragt warum ich das nicht mal eben manuell mache, das Beispiel oben soll auch nur als Beispiel dienen. In der reichtigen Exceldatei hat das arbeitsblatt mit den einzelnen Postleitzahlen genau 8233 Zeilen, eben soviele wie es Postleitzahlen in Deutschland gibt, und jeder einzelnen muss aus der "von PLZ bis PLZ"-Liste eine Relation bekommen!!!
VIELEN DANK SCHONMAL FÜR EURE MÜHE !!!!!

Anzeige

13
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Postleitzahlen Werte aus Tabelle zuweisen... ?
17.05.2008 13:48:04
Reinhard
Hi Basti,
=SVERWEIS(A1;Tabelle1!$A:$C;3;1)
Gruß
Reinhard

AW: Postleitzahlen Werte aus Tabelle zuweisen... ?
17.05.2008 13:58:00
DerBasti83
Funktioniert leider nicht. Ich verstehe deine Formal aber auch nicht ganz. Wie soll die Formel in Tabelle 2 an Platz B1 beispielsweise erkennen das er für Relation 1068 die rechts daneben stehende Realtion einträgt ?
Schonmal Danke

Anzeige
und wie ermittelst Du 1068 als A101
17.05.2008 14:00:00
Matthias
Hi
Hatte eben auch ein Beispiel geschrieben, eigentlich genau wie Dein Vorschlag.
Nur müsste man eben dieses von bis berücksichtigen. Es muß also mE eine Wenn Formel mit rein.
Also wenn die PLZ >=A1 oder Ich denke mal das ist was für die Matrixexperten hier.
Gruß Matthias

Anzeige
AW: und wie ermittelst Du 1068 als A101
17.05.2008 14:15:20
Reinhard
Hi Matthias,
okay, dann halt so
=WENN(A1
Gruß
Reinhard

und 1540 -> A102 usw. fehlt aber noch ;o)
17.05.2008 18:47:22
Matthias
Hi Reinhard
Ich denke es muß noch ne Abfrage rein ob A und B gefüllt sind
Wenn A und B gefüllt, Deine Formel (perfekt)
sonst
wenn nur A gefüllt ist
Sverweis nur auf A beziehen und Wert aus der Spalte3 holen
ansonsten Leerstring.
Gruß Matthias

Anzeige
AW: und 1540 -> A102 usw. fehlt aber noch ;o)
17.05.2008 19:03:00
DerBasti83
Leider funktioniert die Formel nur soweit die Postleitzahlen 4stellig sind. Sobald die Postleitzahlen in den 5stelligen Bereich gehen, bleibt die Zelle leer...!
Woran kanns liegen?

welche ? -> Formel , Wo ? = Fehler ...
17.05.2008 19:49:00
Matthias
Hi
Und wer soll jetzt wissen welche Formel Du nun benutzt.
Du hast den Beitrag ja an meinen Beitrag angehängt.
Eine Alternative wäre (so hätte ich es jedenfalls gelöst)
alle PLZ in Spalte A die Relation in Spalte B
Dann hast Du auch überhaupt kein Problem mehr mit der SverweisFormel.
Wofür ist das Ganze denn überhaupt notwendig?
Da hat man jede Menge Zeilen zur Verfügung und nutzt sie nicht.
Dadurch machst Du es Dir doch nur selber schwer.
Gruß Matthias

Anzeige
AW: welche ? -> Formel , Wo ? = Fehler ...
17.05.2008 20:40:25
DerBasti83
Wieso mach ich es mir selber schwer?
Ich brauche die Tabelle mit einzelnen PLZ, Orten und Relationen für den Datenimport in eine GIS-Software...
Die andere Tabelle entsteht durch einen Export aus unserer Firmensoftware, diese kann keine einzelnen PLZ auswerfen, sondern nur anhand von Bereichseinteilungen....

Anzeige
AW: welche ? -> Formel , Wo ? = Fehler ...
17.05.2008 22:05:57
Matthias
Hi,
Sorry, ich wollte Dich nicht verärgern. Trotzdem kannst Du am Anfang ein Hallo schreiben und am Ende einen Gruß
Mir war ja nicht bekannt, das die Tabelle durch eine Firmensoftware erstellt wird.
Ich war der Überzeugung das Du es selbst so erstellst.
Ich möchte die Leistung des Entwicklers dieser Firmware nicht untergraben. Also entschuldige es bitte, wenn es falsch rübergekommen ist. ok ?
Ich werde mir nochmal Gedanken über eien Formel machen.
In meinem letzten Beitrag waren ja meine Gedanken aufgezeigt, nur bin ich eben kein Formelspezialist.
In dem Sinne, man liest sich nochmal ;o)
Gruß Matthias

Anzeige
AW: welche ? -> Formel , Wo ? = Fehler ...
17.05.2008 23:59:00
WalterK
Hallo,
hier meine Variante:
die Formeln von E2 bis G2 einfach hinunterkopieren!
Tabelle1

 ABCDEFG
1    Hilfsspalte  
210671071A101 11067A101
310751078A103 61068A101
410851086A102 101069A101
51540A102  121070A101
61602A104  131071A101
718001804A105 141075A103
81890A106  191076A103
91920A107  201077A103
101930A108  211078A103
111935A109  221085A102
1219402300A110 231086A102
1324002500A111 3841540A102
14     1602A104
15     1800A105
16     1801A105
17     1802A105
18     1803A105
19     1804A105
20     1890A106

Formeln der Tabelle
ZelleFormel
E2=WENN(A2="";"";WENN(ZEILE()=2;1;WENN(UND(C1="";C2=""); E1+1;WENN(UND(C1="";C2<>""); E1+1;(B1-A1+1)+E1))))
F2=WENN(ISTFEHLER(INDEX(A:A;VERGLEICH(ZEILE(A1); E:E;0))); F1+1;INDEX(A:A;VERGLEICH(ZEILE(A1); E:E;0)))
G2=WENN(INDEX(C:C;VERGLEICH(F2;A:A;2))=0;INDEX(B:B;VERGLEICH(F2;A:A;2)); INDEX(C:C;VERGLEICH(F2;A:A;2)))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Servus, Walter

Anzeige
AW: Postleitzahlen Werte aus Tabelle zuweisen... ?!?
18.05.2008 00:35:00
Josef
Hallo Basti,
noch eine Möglichkeit.
Tabelle1

 ABCDEFGHI
1von PLZbis PLZRelation   PLZRelation 
210671071A101   1067A101 
310751081A103   1068A101 
410851539A101   1069A101 
51540 A102   1070A101 
61602 A104   1071A101 
7      1075A103 
8      1076A103 
9      1077A103 
10      1082#ZAHL! 
11      1087A101 
12      1538A101 
13      1540A102 
14      1541#ZAHL! 
15      1602A104 
16         

Formeln der Tabelle
ZelleFormel
H2{=INDEX($C$2:$C$100;KKLEINSTE(WENN(((A$2:A$100<=G2)*((A$2:A$100>=G2)+(B$2:B$100<>"")))*((B$2:B$100>=G2)+(B$2:B$100="")); ZEILE($1:$99)); 1))}
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen
Excel Tabellen im Web darstellen  Excel Jeanie HTML

Gruß Sepp



Anzeige
AW: Postleitzahlen Werte aus Tabelle zuweisen... ?
19.05.2008 17:24:00
DerBasti83
Also liebe Leute,
schonmal VIELEN DANK für eure großen Bemühungen !!!
Das gröbste sollte geschafft sein, ich habe jetzt Dank euch folgende recht kurze und funkionierende Formel im Einsatz:
=SVERWEIS(A2;Vorgabe!$A$1:Vorgabe!$C$5000;3)
Das einzige Problem was leider noch besteht, ist das wenn man z.B. einen Bereich von 100 - 200 und einen Bereich von 300 - 400 hat, sollte zwischen 200 und 300 am Besten eine Fehlermeldung kommen oder das Feld einfach frei bleiben.
Leider nimmt die oben genannte Formel bei dem hier genannten Beispiel auf den nächstkleineren Wert, greift also auf das Gebiet zwischen 100 und 200 zurück...
Kann man die Formel noch ein bisschen verändern/einschränken sodass dieses Problem behoben wird?
Andere Formel funktionierten in meinen Tabellen leider nur sehr fehlerhaft, hörten mitten in der Tabelle auf oder sonstiges...
Also nochmals Vielen Dank für bisherige und zukünftige Hilfe
und bis später
DerBasti83
Anzeige
;
Anzeige
Anzeige

Infobox / Tutorial

Postleitzahlen in Excel zuordnen: So klappt's!

Schritt-für-Schritt-Anleitung

  1. Vorbereitung der Daten:

    • Erstelle in einem Arbeitsblatt eine Tabelle mit den Spalten "von PLZ", "bis PLZ" und "Relation". Diese Tabelle dient als Grundlage für die Zuordnung der Postleitzahlen.
    • In einem zweiten Arbeitsblatt listest du die einzelnen Postleitzahlen untereinander in Spalte A.

  2. Formel zur Zuordnung:

    • In der Zelle B1 des Arbeitsblattes mit den einzelnen Postleitzahlen gib folgende Formel ein: 
      =WENN(UND(A1>=Tabelle1!$A$1;A1<=Tabelle1!$B$1);Tabelle1!$C$1;"")
    • Diese Formel überprüft, ob die PLZ in A1 zwischen "von PLZ" und "bis PLZ" liegt. Falls ja, wird die entsprechende Relation eingetragen.

  3. Formel nach unten kopieren:

    • Ziehe das Ausfüllkästchen von B1 nach unten, um die Formel auf alle PLZ anzuwenden.

  4. Erweiterte Formel:

    • Um die Formel für mehrere Zeilen in der ersten Tabelle zu erweitern, kannst du die Formel anpassen: 
      =WENNFEHLER(INDEX(Tabelle1!$C$1:$C$5000;VERGLEICH(1;(A1>=Tabelle1!$A$1:$A$5000)*(A1<=Tabelle1!$B$1:$B$5000);0));"")
    • Diese Matrixformel muss mit STRG + SHIFT + ENTER abgeschlossen werden, um korrekt zu funktionieren.

Häufige Fehler und Lösungen

  • Postleitzahl mit 0 fehlt:

    • Wenn du bei der Zuordnung von Excel Postleitzahlen mit führenden Nullen Probleme hast, stelle sicher, dass die Zellen als Text formatiert sind.

  • Formel funktioniert nicht für 5-stellige Postleitzahlen:

    • Überprüfe, ob die PLZ-Tabelle auch für 5-stellige Postleitzahlen korrekt eingerichtet ist. Möglicherweise sind die "bis PLZ" Werte nicht vollständig.

  • Leere Zellen bei falscher Zuordnung:

    • Nutze die WENNFEHLER-Funktion, um leere Zellen zu vermeiden, wenn keine passende Relation gefunden wird.

Alternative Methoden

  • Pivot-Tabellen:

    • Eine Pivot-Tabelle kann helfen, um eine Übersicht über die Postleitzahlen und ihre Relationen zu erhalten. Diese Methode eignet sich besonders für die Analyse großer Datensätze.

  • Power Query:

    • Mit Power Query kannst du Daten transformieren und die Zuordnung von Postleitzahlen automatisieren. Diese Funktion ist besonders hilfreich, wenn du alle Postleitzahlen in Deutschland Excel aufbereiten möchtest.

Praktische Beispiele

  1. Beispiel für eine PLZ-Zuordnung:

    • Angenommen, du hast folgende Daten: 
      von PLZ | bis PLZ | Relation
1067    | 1071   | A101
1075    | 1081   | A103
    • Für die PLZ 1068 würde die Formel in Spalte B die Relation A101 zurückgeben.

  2. Vollständige PLZ-Liste:

    • Wenn du eine PLZ-Tabelle mit allen Postleitzahlen in Deutschland hast, kannst du die oben genannten Formeln für die komplette Liste verwenden, um eine vollständige Zuordnung zu erstellen.

Tipps für Profis

  • Verwendung von Named Ranges:

    • Benenne deine Tabellenbereiche, um die Formeln klarer und einfacher zu gestalten. Anstelle von Tabelle1!$A$1:$C$5000 könntest du PLZ_Bereich verwenden.

  • Datenvalidierung:

    • Setze Datenvalidierungen ein, um Eingabefehler bei der PLZ zu vermeiden. Nutze Listen oder Dropdowns für die Eingabe.

  • Dynamische Arrays:

    • Wenn du Excel 365 verwendest, nutze die dynamischen Array-Funktionen, um die Zuordnung effizienter zu gestalten.

FAQ: Häufige Fragen

1. Wie kann ich Postleitzahlen in Excel suchen? Du kannst die SVERWEIS-Funktion oder die FILTER-Funktion nutzen, um gezielt nach bestimmten Postleitzahlen zu suchen.

2. Was mache ich, wenn ich eine Postleitzahl nicht finde? Stelle sicher, dass die PLZ in den eingestellten Bereichen korrekt eingetragen ist. Prüfe auch, ob die PLZ-Formate übereinstimmen (z.B. 5-stellig).

3. Wie kann ich eine PLZ-Tabelle für andere Länder erstellen? Die Methode bleibt die gleiche, lediglich die Postleitzahl-Bereiche und Relationen müssen entsprechend angepasst werden.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige