Live-Forum - Die aktuellen Beiträge
Datum
Titel
23.04.2024 11:26:57
Probleme mit tbl.Listrows.add
23.04.2024 10:55:14
Zeilen sortieren
23.04.2024 10:23:52
Tabelle 90° drehen + Filteroptionen
23.04.2024 10:11:03
Dateiname plus Wenn vorhanden dann "(1)" anhängen.
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1256to1260
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
1256to1260
1256to1260
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

PLZ suchen

PLZ suchen
dieterj
Hallo Forum.
da soll eine liste entstehen mit etwa 100 Zahlen/zeilen.
Diese Zahlen sind (zufällig) Postleitzahlen.
Wenn nun eine PLZ eingeben wird, soll die nächstliegende PLZ gefunden werden.
Das muss bei 33555 nicht logischerweise die 33556 sein, weil es die in meiner Tabelle vielleicht gar nicht gibt.
Wie würde die Formel aussehen müssen?
Wenn ich das Ergebnis (die PLZ) angezeigt bekomme, soll damit noch eine Adresse verknüpft werden.
(Beispiel ist die Abfrage vielleicht, "wo ist der nächste Mediamarkt", dazu gebe ich meine PLZ ein und mir wird die PLZ und Adresse verraten).
Danke für Eure Tipps

9
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
AW: PLZ suchen
14.04.2012 19:13:35
fcs
Hallo Dieter,
wenn die Liste nach der PLZ sortiert ist kann man mit INDEX und VERGLEICH nach der PLZ suchen.
Allerdings sollte man auch die beiden Nachbarn zur gefundenen PLZ auswerten.
Die PLZ ist aber nicht unbedingt das geeignete Kriterium, um nächstgelegene Orte zu einem Referenzort zu suchen. Dazu wären GPS-Koordinaten oder ein anderes Koordinaten-System besser geeignet mit deren Hilfe dann zumindest die Luftlinien-Entfernung auf den Minimum-Wert ausgewertet werden könnte.
Grüß
Franz
Telefonkosten

 ABCDEFG
1PLZOrtStrassePLZ gesucht:33300  
200000DummyDummy  PLZ01270
301234Ort 01Strasse 01  OrtOrt 10
401238Ort 02Strasse 02  StrasseStrasse 10
501242Ort 03Strasse 03    
601246Ort 04Strasse 04  PLZ44000
701250Ort 05Strasse 05  OrtOrt 15
801254Ort 06Strasse 06  StrasseStrasse 15
901258Ort 07Strasse 07    
1001262Ort 08Strasse 08  PLZ47000
1101266Ort 09Strasse 09  OrtOrt 16
1201270Ort 10Strasse 10  StrasseStrasse 16
1344000Ort 15Strasse 15    
1447000Ort 16Strasse 16    
1553000Ort 14Strasse 14    
1665000Ort 13Strasse 13    
1781823Ort 11Strasse 11    
1882452Ort 12Strasse 12    
1999999DummyDummy    

Formeln der Tabelle
ZelleFormel
G2=INDEX($A$2:$C$100;VERGLEICH($E$1;$A$1:$A$101;1)-1;1)
G3=INDEX($A$2:$C$100;VERGLEICH($E$1;$A$1:$A$101;1)-1;2)
G4=INDEX($A$2:$C$100;VERGLEICH($E$1;$A$1:$A$101;1)-1;3)
G6=INDEX($A$2:$C$100;VERGLEICH($E$1;$A$1:$A$101;1); 1)
G7=INDEX($A$2:$C$100;VERGLEICH($E$1;$A$1:$A$101;1); 2)
G8=INDEX($A$2:$C$100;VERGLEICH($E$1;$A$1:$A$101;1); 3)
G10=INDEX($A$2:$C$100;VERGLEICH($E$1;$A$1:$A$101;1)+1;1)
G11=INDEX($A$2:$C$100;VERGLEICH($E$1;$A$1:$A$101;1)+1;2)
G12=INDEX($A$2:$C$100;VERGLEICH($E$1;$A$1:$A$101;1)+1;3)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Anzeige
AW: PLZ suchen
15.04.2012 11:00:24
dieterj
Danke für Deine Arbeit,fcs.
>Die PLZ ist aber nicht unbedingt das geeignete Kriterium, um nächstgelegene Orte zu einem Referenzort zu suchen in diesem Falle betrachte ich die PLZ einfachmal als 5 stellige Zahl.
Und wenn ich in der Suchfunktion eine 5 stellige eingebe, soll mir die Zahl angezeigt werden, die meiner Eingabe am nächsten kommt-oder sie sogar trifft.
Braucht es dazu wirklich GPS daten?
AW: PLZ suchen
15.04.2012 14:11:20
fcs
Hallo Dieter,
GPS oder ähnliche geografische Information benötigst du nur wenn du den bezüglich Entfernung nächstgelegenen Ort suchst.
Wenn du aus einer Zahlenmenge die Zahl mit der kleinsten Differenz zur gesuchten Zahl suchst, dann kann man das per Formel machen.
Gruß
Franz
Tabelle1

 ABCDEFG
1PLZOrtStrassePLZ gesucht:45501  
265000Ort 01Strasse 13    
353000Ort 02Strasse 14ZeilePLZOrtStrasse
444000Ort 03Strasse 15547000Ort 04Strasse 16
547000Ort 04Strasse 16    
601262Ort 05Strasse 08    
701266Ort 06Strasse 09    

Formeln der Tabelle
ZelleFormel
D4{=MIN(WENN(ABS($E$1-$A$2:$A$100)=MIN(ABS($E$1-$A$2:$A$100)); ZEILE($A$2:$A$100); 10000))}
E4=INDEX(A:A;$D$4)
F4=INDEX(B:B;$D$4)
G4=INDEX(C:C;$D$4)
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Anzeige
AW: PLZ suchen
15.04.2012 14:23:56
Reinhard
Hallo Dieter,
Tabellenblatt: [Mappe1]!Tabelle1 │ A │ B │ C │ ---┼-------┼-------┼-------┼ 1 │ 6129 │ 47000 │ 46692 │ ---┼-------┼-------┼-------┼ 2 │ 12468 │ │ 48228 │ ---┼-------┼-------┼-------┼ 3 │ 16888 │ │ │ ---┼-------┼-------┼-------┼ 4 │ 18002 │ │ │ ---┼-------┼-------┼-------┼ 5 │ 20372 │ │ │ ---┼-------┼-------┼-------┼ 6 │ 25632 │ │ │ ---┼-------┼-------┼-------┼ 7 │ 27552 │ │ │ ---┼-------┼-------┼-------┼ 8 │ 30466 │ │ │ ---┼-------┼-------┼-------┼ 9 │ 33404 │ │ │ ---┼-------┼-------┼-------┼ 10 │ 34155 │ │ │ ---┼-------┼-------┼-------┼ 11 │ 38550 │ │ │ ---┼-------┼-------┼-------┼ 12 │ 46692 │ │ │ ---┼-------┼-------┼-------┼ 13 │ 48228 │ │ │ ---┼-------┼-------┼-------┼ 14 │ 55830 │ │ │ ---┼-------┼-------┼-------┼ 15 │ 58789 │ │ │ ---┼-------┼-------┼-------┼ 16 │ 62964 │ │ │ ---┴-------┴-------┴-------┴ Benutzte Formeln: C1 : =VERWEIS(B1;A1:A30) C2 : =MIN(INDIREKT("A"&VERGLEICH(C1;A1:A30;0)+1&":A1000")) A1:C16 haben das Zahlenformat: Standard
Gruß
Reinhard
Anzeige
AW: PLZ suchen
15.04.2012 15:03:50
dieterj
Ein dickes "DANKE" Reinhard und Alle, die mir auf die Sprünge helfen.
Frage noch (an Reinhard?) Ich möchte ja auch die zahl anzeigenlassen, die mit der Eingegebenen übereinstimmt....
AW: PLZ suchen
15.04.2012 15:52:46
Reinhard
Hallo Dieter,
meinst du das so:
Tabellenblatt: [Mappe1]!Tabelle1
│   A   │   B   │   C   │
---┼-------┼-------┼-------┼
1 │  6129 │ 18002 │ 18002 │
---┼-------┼-------┼-------┼
2 │ 12468 │       │ 18002 │
---┼-------┼-------┼-------┼
3 │ 16888 │       │       │
---┼-------┼-------┼-------┼
4 │ 18002 │       │       │
---┼-------┼-------┼-------┼
5 │ 20372 │       │       │
---┼-------┼-------┼-------┼
6 │ 25632 │       │       │
---┼-------┼-------┼-------┼
7 │ 27552 │       │       │
---┼-------┼-------┼-------┼
8 │ 30466 │       │       │
---┼-------┼-------┼-------┼
9 │ 33404 │       │       │
---┼-------┼-------┼-------┼
10 │ 34155 │       │       │
---┼-------┼-------┼-------┼
11 │ 38550 │       │       │
---┼-------┼-------┼-------┼
12 │ 46692 │       │       │
---┼-------┼-------┼-------┼
13 │ 48228 │       │       │
---┼-------┼-------┼-------┼
14 │ 55830 │       │       │
---┼-------┼-------┼-------┼
15 │ 58789 │       │       │
---┼-------┼-------┼-------┼
16 │ 62964 │       │       │
---┼-------┼-------┼-------┼
17 │ 63749 │       │       │
---┴-------┴-------┴-------┴
Benutzte Formeln:
C1 : =WENN(ZÄHLENWENN(A1:A1000;B1)>0;B1;VERWEIS(B1;A1:A1000))
C2 : =WENN(ZÄHLENWENN(A1:A1000;B1)>0;B1;MIN(INDIREKT("A"&VERGLEICH(C1;A1:A30;0)+1&":A1000")))
A1:C17
haben das Zahlenformat: Standard

Gruß
Reinhard
Anzeige
AW: PLZ suchen
15.04.2012 16:11:03
dieterj
ich meine: zeige mir 3 zahlen :
die übereinstimmunng
die nächst niedrigere
die nächst höhere.
(habe aber auch festgestellt: sortiere ich die Spalte1 absteigend, erhalte ich andere ergebnisse; was ja eigentlich nicht sein kann)
AW: PLZ suchen
15.04.2012 18:20:42
dieterj
Hallo Franz, wenn ich nach Spalte A aufsteigend sortiere, gibt er mir in D4 mit deiner Formel an, das es zwei Zeilen hat. Tatsächlich hab ich 8. Woran mag das liegen?
AW: PLZ suchen
15.04.2012 19:58:08
fcs
Hallo Dieter,
die Formel in D4 berechnet die Nummer der Zeile in der die gesuchte Zahl steht, nicht die Anzahl der Zeilen. Die Sortierung der Zahlen spielt dabei keine Rolle. Wenn die Zahlen sortiert sind, dann kannst du natürlich auch die weiteren in der nähe liegenden Zahlen ermitteln.
Die Formel in D4 ist eine Matrix-Formel. D.h., die Eingabe der Formel muss mit der Tasten-Kombination Strg+Shift+Enter abgeschlossen werden. Excel zeigt dann in der Formel die geschweiften Klammern {} als Kennzeichen für Matrixformel an.
Wenn du schnell einen Überblick über die in der Nähe der gesuchten Zahl liegenden Zahlen willst, dann füge in einer weiteren Spalte der Liste eine Formel ein, die die Differenz zwischen Zahl und gesuchterZahl berechenet. Wenn du die Liste nach Eingabe der gesuchten Zahl aufsteigend nach der Diferenz sortierst, dann steht die gesuchte Zahl in der 1. Zeile und die weiteren nächstgelegenen Zahlen folgen.
Gruß
Franz
Anzeige

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige