Live-Forum - Die aktuellen Beiträge
Datum
Titel
04.10.2025 05:06:02
Mahjong gratis: gioco di puzzle senza tempo
04.10.2025 05:04:16
Mahjong gratuito: gioco di puzzle senza tempo
03.10.2025 22:18:19
Dropdown
03.10.2025 20:19:48
Formatierung bei Zelländerung
Keine passende Antwort im Forumarchiv gefunden?
Stelle deine Frage im Live-Forum
Anzeige
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 Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

PLZ suchen

Forumthread: 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
Anzeige

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?
Anzeige
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?
Anzeige
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
;

Forumthreads zu verwandten Themen

Was ist dass Es erscheinen merkwürdige Makros ???
juergen

Hallo liebe Excel Gemeinde
Bei mir passiert etwas sehr mekwürdiges.Und zwar
Öffne ich Excel mit leerem Blatt und gehe dann in die VBA Umgebung ist alles sauber.
Öffne Ich die Exceldatei von Hajo.Ziplies "nur_mit_aktivierten_makros_zeit_loeschen.xls"
Und gehe dann auf Makros...
30.03.2009 19:21:05
Thread-ID: 1065401
8 Beiträge
Anzeige
Was ist eine "generic object variable"
Maria

Hallo Ihr Excel-Kundigen!Könnt Ihr mir vielleicht erklären, was unter "generic object variable" bzw. "any specific object variable" zu verstehen ist?Ich versuche aus Ressourcengründen bei einer For each... Next Schleife vom Variant-Variablentyp wegzukommen und die Excel-Hilfe gibt mir diese bei...
07.05.2002 18:19:55
Thread-ID: 119290
3 Beiträge
HILFE - was ist mit meiner ADOD.Connection los
Stefan

Ich bin echt am verzweifeln.
Folgendes hab ich versucht. Ich habe eine (funktionierende) Prozedur in ein neues Modul einer anderen Excel-Datei verschoben und wollte es dort ausführen. Dann bekam ich folgende Fehlermeldung:Fehler beim Kompilieren: Benutzerdefinierter Typ nicht definiertin Be...
21.05.2002 18:11:06
Thread-ID: 122490
2 Beiträge
PLZ zum Ort hinzu fügen
Katharina

Hallo zusammen,
ich hatte meine PLZ zum Ort Anfrage bereits im Forum und auch einige Antworten bekommen. Allerdings hat das mit dem SVERWEIS nicht geklappt. Kann ich jemandem meine zwei Listen per Mail zusenden und IHr schaut mal, ob Ihr den Fehler findet.
Danke und Grüße
Katharina<...
27.03.2013 08:38:25
Thread-ID: 1306121
9 Beiträge
Anzeige
Wortgruppe zu finden und Zahl dahinter auslesen
VAT

Hallo zusammen,
gibt es eine Möglichkeit, einen Text (in einer Zelle) nach einer bestimmten Wortgruppe zu durchsuchen zu durchsuchen.
Wenn die Wortgruppe gefunden wird, benötige ich die 9stellige Zahl dahinter.
Im Beispielfall ist die Wortgruppe: „Auftrag durch:“
und die auszu...
26.06.2014 11:52:28
Thread-ID: 1367582
6 Beiträge
Hilfe, was ist mit meinem Excel los
Stefan

Ich hab nun 8 Stunden damit verbracht eine Auswertung von Offerten einzugeben.Nun möchte ich die Summe einer Spalte errechnen, doch nun bleibt nur die Formel in der Zelle stehen.(Das Resultat bleibt verborgen)
Dies obwohl ich den Haken in der "Option-Ansicht-Formel" entfernt habe.Kann mir bi...
18.07.2002 16:31:13
Thread-ID: 138061
4 Beiträge
Ort zur PLZ suchen
walter mb
Hallo,
habe noch mal eine, vielleicht Kleinigkeit aber für
mich leider nicht lösbar.
Anbei die Musterdatei.
In der Datenbank steht der Courser z.b. in der Zelle "M4",
nun soll der dazugehörige Ort daneben also in "N4" geschrieben
werden.
Die PLZ und Ort stehen in der Tab...
23.08.2014 15:54:55
Thread-ID: 1377871
7 Beiträge
Daten sortieren - Was ist mit neuen Eintrag?
matthias

Hallo,
ich habe über den Makrorecoder einen Sortiervorgang mit drei Bedingungen aufgezeichnet.
Funktioniert auch toll.
Aber wenn ich zusätzliche Werte in meine Spalte eintrage, werden die verständlicherweise nicht mit sortiert.
Wie kann ich so ein Problem lösen?
20.03.2015 10:35:07
Thread-ID: 1416122
9 Beiträge
Zahlenkette finden und auflisten / Fortsetzung
erichm

Hallo,
leider kann ich mich erst jetzt melden. Der Originalthread kann leider nicht mehr beantwortet werden.
Also an der Diskussion kann ich mich irgendwie nicht beteiligen, da kenne ich mich zu wenig aus.
Als Ergebnis erfüllt diese Lösung aber alle Voraussetzungen:
https://www....
07.06.2015 13:13:59
Thread-ID: 1429712
10 Beiträge
Wortvergleich um gleichen Wortstamm zu finden
Kirk

Hallo Experten,
ich brauche mal wieder einen kleinen Tipp. Ich möchte gerne Wörter vergleichen,
z.b. Tisch, Tische, Tischs,
... und herausfinden, wenn der Wortstamm (Tisch..) gleich ist. Ich dacht an eine CODE mit LIKE, aber hier scheitere ich schon, weil mein VBA ?, # oder * gar...
22.02.2017 09:15:38
Thread-ID: 1543158
3 Beiträge
Was ist hier falsch? - eine Zelle suchen in Spalte
Tobi

Hallo zusammen,
was ist hier falsch?
Ich möchte den Wert (könnte so aussehen: H-L112-13-2110-0-0-30a)aus der Zelle "i9" von der "Tabelle 1", in der "Tabelle 2" in der ganzen Spalte "Y" suchen. Wenn etwas gefunden wird, soll "Grafik 1" eingeblendet werden wenn nicht soll "Grafik 2" einge...
25.07.2017 10:10:20
Thread-ID: 1570686
11 Beiträge
Was ist das für ein VBA UserForm Control?
Klaus

Hallo an Alle,
Ich versuche ein UserForm zu bauen, indem ein Control mit mehreren Zeilen und Spalten bestimmte Symbole aus einem bestimmten Zeichensatz anzeigt. Das Füllen mit den Symbolen ist kein Problem für mich. Habe es mit ListBox und ComboBox ausprobiert.
Aber leider schaffe ich...
11.03.2018 19:55:33
Thread-ID: 1613645
7 Beiträge
Entdecke relevante Threads

Schau dir verwandte Threads basierend auf dem aktuellen Thema an

Alle relevanten Threads mit Inhaltsvorschau entdecken
Anzeige
Anzeige

Infobox / Tutorial

PLZ suchen und zuordnen in Excel

Schritt-für-Schritt-Anleitung

  1. Daten vorbereiten: Erstelle eine Tabelle mit den Postleitzahlen (PLZ) in einer Spalte (z.B. Spalte A) und den dazugehörigen Orten in der nächsten Spalte (z.B. Spalte B).

    A       | B
--------|-------
01234   | Ort 1
01238   | Ort 2
01242   | Ort 3
01246   | Ort 4

  2. Suchfeld einrichten: Füge in einer anderen Zelle ein Suchfeld ein, wo Du die PLZ eingeben kannst (z.B. E1).

  3. Formel zur Suche: Verwende die folgende Formel, um die nächstgelegene PLZ zu finden:

    =INDEX(A:A;VERGLEICH(MIN(ABS(E1-A:A));ABS(E1-A:A);0))

    Diese Formel sucht die PLZ mit der kleinsten Differenz zur eingegebenen PLZ.

  4. Ort zuordnen: Um den dazugehörigen Ort anzuzeigen, kannst Du die INDEX- und VERGLEICH-Funktionen kombinieren:

    =INDEX(B:B;VERGLEICH(MIN(ABS(E1-A:A));ABS(E1-A:A);0))

  5. Ergebnisse anzeigen: Die Ergebnisse (nächste PLZ und Ort) werden nun angezeigt, sobald Du eine PLZ eingibst.

Häufige Fehler und Lösungen

  • Fehler: #NV: Dies kann auftreten, wenn die PLZ nicht in der Liste vorhanden ist. Stelle sicher, dass die PLZ korrekt eingegeben wurde und dass die Liste entsprechend sortiert ist.

  • Lösung: Formel anpassen: Achte darauf, dass die Formel korrekt eingegeben wurde und die Zellenbereiche die richtigen Daten enthalten.

  • Fehler: Falsche Ergebnisse: Wenn die Daten nicht sortiert sind, kann dies zu falschen Ergebnissen führen. Sortiere die PLZ-Spalte aufsteigend.

Alternative Methoden

  • GPS Koordinaten verwenden: Wenn Du eine genauere Suche nach dem nächstgelegenen Ort benötigst, kannst Du GPS-Koordinaten verwenden. Damit kannst Du die Distanz zwischen verschiedenen PLZ ermitteln.

  • PLZ Suche mit VBA: Du kannst auch ein VBA-Skript schreiben, um die PLZ Suche zu automatisieren und komplexere Abfragen durchzuführen.

Praktische Beispiele

  1. Beispiel: PLZ 01234: Wenn Du "01234" eingibst, zeigt die Tabelle den entsprechenden Ort "Ort 1" an.

  2. Beispiel: PLZ umkreissuche: Um PLZ innerhalb eines bestimmten Umkreises zu finden, erstelle eine Liste der PLZ und vergleiche diese mit der eingegebenen PLZ.

Tipps für Profis

  • Verwende Datenüberprüfung: Setze eine Datenüberprüfung für das Suchfeld, um sicherzustellen, dass nur gültige PLZ eingegeben werden können.

  • Erstelle eine Dropdown-Liste: Erstelle eine Dropdown-Liste mit häufig verwendeten PLZ, um die Suche zu erleichtern.

  • Nutze bedingte Formatierung: Markiere die nächstgelegene PLZ in der Liste, um sofortige visuelle Hinweise zu erhalten.

FAQ: Häufige Fragen

1. Was ist meine PLZ?
Deine PLZ ist die Postleitzahl, die Deinem Wohnort zugeordnet ist. Du kannst sie auch online ermitteln.

2. Wie finde ich den Ort zu einer PLZ?
Du kannst die Excel-Funktionen INDEX und VERGLEICH verwenden, um den Ort zu einer bestimmten PLZ zu finden.

3. Kann ich mehrere PLZ gleichzeitig suchen?
Ja, aber dafür benötigst Du eine angepasste Formel oder ein Makro, um mehrere Suchanfragen zu verarbeiten.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige