Excel: Postleitzahlen aufschlüsseln und Relation zuweisen...

[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 !!!!!

 

[uglybartender]

Was genau meinst du mit Relation

 

[DerBasti83]

Eine Relation ist einfach eine Zusammenfassung der Gebiete... Ist für die Fnktion nicht so wichtig. Die Spalte heisst einfach so. also mehrere Postleitzahlen sind zu einer Relation zusammengefasst.

 

[uglybartender]

hmmm... und wie werden die zusammengefasst, ich meine rechnerisch oder logisch oder wie auch immer. Sind relationen festgelegt oder kann man da variabel agieren.

 

[DerBasti83]

Die Relationen müssen nur den einzelnen PLZ zugewiesen sein... Das wird nicht weiter in Excel bearbeitet. Später wird das Ganze dann in eine GIS Software importiert und da werden dann Gebietskarten gezeichnet.

Das Programm kann nur mit den einzelnen Postleitzahlen arbeiten und nicht mit "von - bis" deswegen muss die Tabelle eben so aufgeschlüsselt werden...

Alles was ich also brauche, ist das die Relationen dort hingeschrieben werden wo sie hingehören und das kann anhand der ersten Tabelle ausgelesen werden...

Aber das muss doch irrgentwie gehn...

 

[uglybartender]

Dann sollte es eigentlich reichen wenn du mit einer WENN-formel und größer/kleiner eingrenzt

wenn(und(a1<1081;a1>1075);"A101";wenn(und(a1<1539;a1>1085);"A103".... usw

 

[Mr. Snoot]

Es lassen sich aber nur 7 Wenn-Bedingungen verknüpfen, könnte also knapp werden.

Eigentlich sollte sich das ganze elegant mit SVERWEIS lösen lassen.


In deiner Relation-Spalte auf Tabelle 2 (B1) nimmst du folgende Formel:

=SVERWEIS(A1;Tabelle1!$A$1:Tabelle1!$C$5000;3)

A1: die PLZ in Tabelle 2, die in den Daten in Tabelle 1 gesucht werden soll
Tabelle1!$A$1:Tabelle1!$C$5000: der Datenbereich mit allen Postleitzahlen und zugehöriger Relation
3: Ausgabe des zur jeweiligen PLZ gehörigen Wertes aus Spalte 3 (also Spalte C) des davor angegeben Datenbereichs

Die Formel kannst du dann einfach für alle Zeilen nach unten übernehmen.

 

[uglybartender]

es ist allerdings möglich wenn-formel (mit jeweils 7 bedingungen) mit einem einfachen "+" miteinander zu verketten.

http://xltreff.de/beispiele/43.htm

 

[Mr. Snoot]

Dann wünsch ich dir schonmal viel Spaß, wenn man da mal was korrigieren muss

 

[DerBasti83]

Also soweit funktioniert das schonmal ganz gut, ABER:

Nach der 46. Relation übernimmt er nurnoch immer die selbe Relation, fragt also nicht mehr ab.

Woran kann das liegen???

PS: Habe das Problem eingkreis, die Relationen werden nicht mehr geändert, ab der spalte wo die Postleitzahlen von 4 Stellen auf 5 Stellen übergeht

 

[Mr. Snoot]

Hast du in der Formel die Matrix auch mit den $-Zeichen angegeben? Andernfalls stimmen die Bezüge nicht, wenn du die Formel nach unten übernimmst.


Des Weiteren gehe ich davon aus, dass es keine PLZ in deiner Liste doppelt gibt!? Sonst gibts auch Probleme.

 

[DerBasti83]

Nein, Postleitzahlen sind nicht doppelt. Ich habe die Formel exakt konpiert in in Excel eingefügt. Und nur die Namen des Arbeitsblatts der ersten Tabelle geändert...

PS: Was gibt eigentlich die 5000 in der Formel an?

 

[Mr. Snoot]

Ah

5000 ist die 5000. Zeile, also A1 bis C5000.

Wenn du mehr Zeilen hast, musst du das natürlich vergrößern.

 

[DerBasti83]

Tja, leider ist es das nicht. Die Tabelle mit den von bis PLZ geht nur bis ca. 800.

Irrgentwie stört mich das, das es genau da passiert, wo die Postleitzahlen 5stellig werden. DENN:

vorher waren die 4stelligen Postleitzahlen auch 5stellig mit einer 0 davor. Also beispielsweise PLZ 01067. Da funktionierte die Formel auch nicht. Nachdem ich allerdings die 0 weggemacht habe, ging es. Natürlich musste ich dafür dann auch die PLZ in der 2. Tabelle ändern auf 4 Stellen...

Kann excel das einfach nicht berechnen oder wie?

 

[Mr. Snoot]

Die vorangestellte 0 ignoriert Excel. Berechnet werden muss da nichts, von daher ist es egal ob du eine Ziffer oder 27 in der Zelle stehen hast.

Überprüf mal, ob die PLZ an der Stelle, wo es nicht mehr stimmt, in beiden Tabellen korrekt sind. Wenn da etwas durcheinander gekommen ist, stimmt natürlich die Zuordnung nicht mehr.


edit: du kannst die Exceldatei hier auch mal hochladen (als Zip-Archiv), falls es nach wie vor nicht klappt. Kannst ja sonst alles rauschlöschen, falls das irgendwelche privaten Daten sind.

 

[uglybartender]

Denkst du es würde funktionieren wenn man die Postleizahlen auch als solche formatiert... sind sie das überhaupt??

 

[Mr. Snoot]

Das spielt keine Rolle, die Zelle kann auch als Text formatiert sein. Lediglich der Inhalt ist wichtig.