Live-Forum - Die aktuellen Beiträge
Datum
Titel
26.04.2024 09:08:00
Export alle Tabellenblätter als csv - in bestimmten Ordner
26.04.2024 08:56:40
Über alle Spalten nur €-Werte zählen
26.04.2024 08:08:24
Pivot Vormonatsveränderung
26.04.2024 07:54:11
nur den ersten Eintrag in spalte zählen
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1092to1096
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1092to1096
1092to1096
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

PLZ Zuordnung

PLZ Zuordnung
joergstgt
Hallo zusammen,
folgendes problem: Ich möchte Postleitzahlen einem Bereich zuordnen. Der Bereich gliedert sich in MAD und BCN.
Der Aufbau der Tabelle ist folgender:
Tabelle1
Spalte A------Spalte B
PLZ Von-----------Bis
In Tabelle 2 werden per copy & Paste aus einer anderen Anwendung (Daher Standartformatierung notwendig) die Postleitzahlen in Spalte A eingefügt.
In Spalte B soll dann der zugeordnete Bereich (MAD od BCN) angezeigt werden.
Bisher habe ich folgende Formel versucht: =SVERWEIS(A1;Tabelle1!$A$1:$C$5004;3)
Leider funktioniert das Ganz gut wenn ich alles per Hand eingebe, nicht aber wenn ich mit dem copy & paste Prinzip aus der anderen Anwedung arbeite.
Die Tabelle habe ich mal angehängt
https://www.herber.de/bbs/user/63634.xls
Vielen Dank für eure Hilfe

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
AW: PLZ Zuordnung
05.08.2009 14:41:57
AndreasG
Hallo Joerg?,
vielleicht liegt das daran, dass die per copy und paste eingefügten Werte nicht als Zahl sondern als Text interpretiert werden. Versuch doch mal diese zuerst einzufügen, dann in eine leere Zelle eine 1 schreiben, dieses Datenfeld kopieren, dann die eingefügten PLZ markieren und den Befehl: Bearbeitn- Inhalte einfügen mit angehaktem "multiplizieren" auszuführen. Wandelt das den kopierten Text in Zahlen um und die Formel funktioniert dann?
Grüße
Andreas
AW: PLZ Zuordnung
05.08.2009 14:48:53
joergstgt
Danke für den Tip Andreas aber leider funktioniert das auch nicht.
AW: PLZ Zuordnung
05.08.2009 15:33:30
MichaV
dann hängt noch ein unsichtbares Zeichen dran. Ich nehme mal an, die hochgeladene Tabelle ist die mit den händisch eingegebenen Werten? Lade mal eine Tabelle hoch mit Werten die nicht funktionieren.
Gruß- Micha
Anzeige
AW: PLZ Zuordnung
05.08.2009 16:42:26
jockel
hi Andreas, deine formel kann meines erachtens auch nicht funzen...
Tabelle2 (2)
 ABC
2931000MADBCN
3033000MADMAD
312001BCNMAD

verwendete Formeln
Zelle Formel
B29 =SVERWEIS(A29;Tabelle1!$A$1:$C$5004;3)
C29 {=INDEX(Tabelle1!C:C;VERGLEICH(MIN(WENN((A29>=Tabelle1!$A$1:$A$31)*(A29<=Tabelle1!$B$1:$B$31);Tabelle1!$B$1:$B$31));Tabelle1!$B$1:$B$31;0))}
B30 =SVERWEIS(A30;Tabelle1!$A$1:$C$5004;3)
C30 {=INDEX(Tabelle1!C:C;VERGLEICH(MIN(WENN((A30>=Tabelle1!$A$1:$A$31)*(A30<=Tabelle1!$B$1:$B$31);Tabelle1!$B$1:$B$31));Tabelle1!$B$1:$B$31;0))}
B31 =SVERWEIS(A31;Tabelle1!$A$1:$C$5004;3)
C31 {=INDEX(Tabelle1!C:C;VERGLEICH(MIN(WENN((A31>=Tabelle1!$A$1:$A$31)*(A31<=Tabelle1!$B$1:$B$31);Tabelle1!$B$1:$B$31));Tabelle1!$B$1:$B$31;0))}
{} Matrixformel mit Strg+Umschalt+Enter abschließen

Tabellendarstellung in Foren Version 4.10



cu jörg
ein feedback wär ganz reizend
Anzeige
PLZ Zuordnung - Fehler
05.08.2009 17:00:34
Erich
Hi Joerg und Jörg,
und das ist ein wesentlicher Grund dafür, dass der einfache SVERWEIS nicht funzt:
Blatt Tabelle1 ist nicht nach Spalte A aufsteigend sortiert.
Zitat aus der Excel-Hilfe zu SVERWEIS:
Wenn Bereich_Verweis WAHR ist, müssen die Werte in der ersten Spalte von Matrix
in aufsteigender Reihenfolge angeordnet werden: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSCH, WAHR;
andernfalls gibt SVERWEIS möglicherweise nicht den richtigen Wert zurück.
Wenn Bereich_Verweis FALSCH ist, muss Bereich_Verweis nicht sortiert werden.
Du (Joerg) hattest den 3. Parameter weggelassen - das entspricht dem WAHR.
Nach dem Sort reicht der einfache Verweis, wenn die PLZ-Bereiche in Tab.1 lückenlos sind.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: PLZ Zuordnung - Fehler
05.08.2009 17:20:11
jockel
hi Erich, das hatte ich auch gesehen, aber ich hatte meinen namensvetter so verstanden, dass er die sachen vorgegeben bekommt... und mit dieser vorgabe hatt ich "gearbeitet - und dort funzt auch meine variante...
mfg jörg
ohne array
05.08.2009 22:21:20
jockel
hi Joerg, hier noch eine alternative ohne array-formel (bei den leerzellen gibt es keinen bereich)...
Tabelle1
 ABC
110001999BCN
230003999BCN
370008999BCN
41200012999BCN
51700017999BCN
62000020999BCN
72200022999BCN
82500026999BCN
93000030999BCN
103100031600BCN
113500035999BCN
123800038999BCN
134300044999JOC
144600046999BCN
154800048999BCN
165000050999BCN
1720002999MAD
1840006999MAD
19900011999MAD
201300016999MAD
211800019999MAD
222100021999MAD
232300024999MAD
242700029999MAD
253200034999MAD
263600037999MAD
273900042999MAD
284500045999MAD
294700047999MAD
304900049999MAD



Tabelle2
 AB
18002BCN
28006BCN
38006BCN
48015BCN
58018BCN
68028BCN
78028BCN
88038BCN
98174BCN
108174BCN
118174BCN
128174BCN
138174BCN
148174BCN
158174BCN
168174BCN
178174BCN
188174BCN
198174BCN
208970BCN
2120018BCN
2220018BCN
2326006BCN
2431570BCN
2531570BCN
2631570BCN
2731570BCN
2831570BCN
2931620 
3031620 
3143120JOC
3243120JOC
3343120JOC
3443120JOC
3543120JOC
3643120JOC
3743120JOC
3843120JOC
3943120JOC
4043120JOC
4143120JOC
4243120JOC
4343120JOC
4443120JOC
4543120JOC
4643120JOC
4743120JOC
4843120JOC
4943120JOC
5043120JOC
5143120JOC
5243120JOC
5343120JOC
5443120JOC
5543120JOC
5643120JOC
5743120JOC
5843120JOC
5943120JOC
6043120JOC
6143120JOC
6243120JOC
6343120JOC
6443120JOC
6543120JOC
6643120JOC
6743120JOC
6843120JOC
6943120JOC
7043120JOC
7143120JOC
7243120JOC
7343120JOC
7443120JOC
7543120JOC
7643120JOC
7743120JOC
7843120JOC
7943120JOC
8043120JOC
8143120JOC
8243120JOC
8343120JOC
8443120JOC
8543120JOC
8643120JOC
8743120JOC
8843120JOC
8943120JOC
9043120JOC
9143120JOC
9243120JOC
9343120JOC
9443120JOC
9543710JOC
9646015BCN
9746021BCN
9848009BCN
9950014BCN
10050057BCN
10150059BCN
10250059BCN
10350059BCN
10450059BCN
10550059BCN
10650059BCN
10750059BCN
10850197BCN
10950197BCN
11050197BCN
11150197BCN
11250197BCN
11350197BCN
11450197BCN
11550197BCN
11650197BCN
11750197BCN
11850197BCN
11950197BCN
12050197BCN
12150197BCN
12250197BCN
12350197BCN
12450197BCN
12550197BCN
12650197BCN
12750197BCN
12849999MAD

verwendete Formeln
Zelle Formel
B1 =WENN(SUMMENPRODUKT((Tabelle1!$A$1:$A$30<=A1)*(Tabelle1!$B$1:$B$30>=A1)*Tabelle1!$A$1:$A$30)=0;"";INDEX(Tabelle1!C:C;VERGLEICH(SUMMENPRODUKT((Tabelle1!$A$1:$A$30<=A1)*(Tabelle1!$B$1:$B$30>=A1)*Tabelle1!$A$1:$A$30);Tabelle1!A:A;0)))

Tabellendarstellung in Foren Version 4.10



cu jörg
ein feedback wär ganz reizend
Anzeige
PLZ besser immer als Text
05.08.2009 16:47:05
Erich
Hi Jörg,
vielleicht ist es auch in diesem Fall nützlich, PLZen immer als Texte, nicht als Zahlen zu behandeln.
Schau dazu mal hier: http://de.wikipedia.org/wiki/Postleitzahl#Postleitzahlen_in_der_Datenverarbeitung
Die jetzt vorliegenden Zahlen kannst du leicht per Formel in Texte umwandeln: =TEXT(A1;"00000")
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: PLZ Zuordnung
06.08.2009 08:54:09
joergstgt
Hallo an alle,
vielen Dank für eure Hilfe. Es funktioniert nun einwandfrei.
Ich habe nun die Formel von Jockel eingebaut...super Sache.
Vielen Vielen Dank nochmal an alle....

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige