Live-Forum - Die aktuellen Beiträge
Datum
Titel
24.04.2024 00:20:14
Bestimmte Spalten als xls exportieren per Makro
23.04.2024 22:08:10
Auflisten in Spalte VBA
23.04.2024 21:33:46
Nur farbige/nicht farbige Zellen markieren
23.04.2024 21:29:56
Bestimmte Zellen auf andere Arbeitsblätter kopieren
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1408to1412
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
1408to1412
1408to1412
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

kürzeste Entfernung zwischen Städten

kürzeste Entfernung zwischen Städten
23.02.2015 20:24:35
Jenny
Hallo ihr lieben Excel-Helfer,
würde mich freuen, wenn sich einer von euch meinem Problem annimmt
https://www.herber.de/bbs/user/95938.xlsx
habe hier einen Ausschnitt aus meiner Entfernungstabelle,
suche eine Formel, die mir zu jeder Stadt in Tabelle 1 in den Spalten G-J den Namen der 4 nächstgelegenen Städte schreibt, Entfernungen stehen in Tabelle2.
Bitte berücksichtigt auch, dass dies nur ein Auszug aus der Originaltabelle ist, das Original hat 330 Städte, demnach geht die Entfernungstabelle von B2 bis LS331.
Danke für eure Hilfe
Jenny

15
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Luftlinienentfernung über Koordinaten
23.02.2015 20:58:41
Jenny
Hallo WF,
die Formel um das zu berechnen kenne ich, hab sie nur aus der Originaltabelle genommen weil die Berechnungszeit zu lange war.
Die Frage war welche Formel die 4 kürzesten Entfernungen aus der Entfernungstabelle auslesen kann.
LG
Jenny

AW: kürzeste Entfernung zwischen Städten
23.02.2015 20:56:14
Daniel
Hi
zunächsteinmal musst du die Tabelle spiegeln, so dass auch der untere Teil (unterhalb der 0-Diagonalen) ausgefüllt ist.
dann kopierst du die Orte aus der Tabelle2 Spalte A in die Tabelle 1 (bei dir ohne Überschrift, dh AAchen wandert aus Zeile 2 nach Zeile 1)
wichtig ist, dass die Reihenfolge in beiden Listen gleich ist.
dann kommt folgende Formel in G1, diese kannst du dann bis Spalte J und nach unten ziehen: 
=INDEX(Tabelle2!$1:$1;1;VERGLEICH(KKLEINSTE(Tabelle2!2:2;SPALTE(A1));Tabelle2!2:2;0))
Gruß Daniel

Anzeige
AW: kürzeste Entfernung zwischen Städten
23.02.2015 20:59:42
Jenny
Hallo Daniel,
spiegeln ist kein Problem, aber steht dann nicht jede Entfernung 2mal in der Tabelle?
LG
Jenny

AW: kürzeste Entfernung zwischen Städten
23.02.2015 21:05:32
Daniel
Ja, das ist richtig.
Aber dann stehen auch ALLE Entfernungen zu einer Stadt in einer Zeile, was dein Formelwerk vereinfachst.
Ansonsten müsstest du ja erstmal die Entfernungen die in der Ortszeile stehen mit den Entfernungen aus der Ortsspalte zusammenbringen und dann dort die 4 kleinsten Finden und dann mit diesen jeweils wieder rückwärts herausfinden, ob sie aus der Zeile oder der Spalte stammen um den passenden Ort dazu auszulesen.
Das könnte ziemlich komplex werden.
Gruß Daniel

Anzeige
AW: kürzeste Entfernung zwischen Städten
23.02.2015 21:08:52
Jenny
ok, dann probier ichs erstmal so,
das Problem ist dann nur dass die Mappe auch noch aus einer Tabelle besteht, in der die 5000 kürzesten Verbindungen insgesamt stehen zusammen mit den jeweils beiden Städten, die Formeln funktionieren dann nicht mehr, wenn in der Entfernungstabelle alles doppelt steht, brauche also 2 Entfernungstabellen.
LG
Jenny

AW: kürzeste Entfernung zwischen Städten
23.02.2015 21:16:10
Daniel
Vielleicht kannst du die Funktion ja dahingehend anpassen, dass sie die 10.000 kürzesten Verbindungen ausgibt, aber von diesen nur jede 2.
Gruß Daniel

AW: kürzeste Entfernung zwischen Städten
23.02.2015 21:19:38
Jenny
wenn du mir dabei hilfst, die Formeln sind
=KKLEINSTE(Tabelle2!$B$2:$LS$331;ZEILE(A1))


=INDEX(Tabelle2!A:A;MAX((A1=Tabelle2!B$2:LS$331)*ZEILE($2:$331)))


=INDEX(Tabelle2!$1:$1;MAX((A1=Tabelle2!B$2:LS$331)*SPALTE(B:LS)))
die letzten beiden Matrixformeln,
wenn du mir da helfen kannst...
lg
Jenny

Anzeige
AW: kürzeste Entfernung zwischen Städten
23.02.2015 21:25:14
Daniel
Hi
jede Entfernung kommt jetzt doppelt vor.
deswegen brauchst du nur jede zweite zu ermitteln:
=KKLEINSTE(Tabelle2!$B$2:$LS$331;ZEILE(A1)*2)
die Matrixformeln können so bleiben, es sollte ja egal sein, welche Stadt der Paarung zuerst gefunden wird.
Gruß Daniel

AW: kürzeste Entfernung zwischen Städten
23.02.2015 22:12:01
Jenny
aber Daniel tut mir leid, aber deine Formel funktioniert nicht
ich bekomme in der Stadt Abbeville (Tabelle 1 Zeile 5 bzw. Tabelle2 Zeile 6) als nächstgelegene Stadt Amsterdam aus Tabelle2!I4 angezeigt, die mit einer Entfernung von rund 330 km deutlich weiter weg ist als Amiens aus Tabelle2!I3 mit rund 40 km.
LG
Jenny

Anzeige
AW: kürzeste Entfernung zwischen Städten
23.02.2015 22:15:14
Daniel
Auf welche Formeln beziehst du dich jetzt?
die zum Ermitteln der 5000 kürzesten Entfernungen oder die für die 4 nächsten Städte?
kannst du die Datei mit den Formeln hochladen?
das macht die Fehlersuche einfacher.
Gruß Daniel

AW: kürzeste Entfernung zwischen Städten
23.02.2015 21:11:14
Jenny
Also Daniel,
das scheint zu funktionieren, danke.
Erklär mir bitte nur noch, warum das Ergebnis in der Schriftart Arial Unicode MS ausgegeben wird obwohl die Standardschriftart Calibri ist...
LG
Jenny

AW: kürzeste Entfernung zwischen Städten
23.02.2015 21:14:14
Daniel
Das Ergebnis wird in der Schriftart ausgegeben, die du für diese Zelle eingestellt hast.
Gruß Daniel

Anzeige
AW: kürzeste Entfernung zwischen Städten
23.02.2015 21:43:34
Franiska
Hallo Jenny und alle anderen Excelianer,
hatte mal vor langer langer Zeit diese Datei ( siehe Anhang ), um Entfernungen zu berechnen.
War damals echt super toll! Nicht nur, dass die Entfernung in der Tabelle zu sehen war, auch die Möglichkeit, das auch gleich online über google anzusehen.
Aber leider in die Jahre gekommen und ich finde auch keine passende Lösung.
War von vba-excel.de, vielleicht kann ja jemand das so gestalten, dass Jenny damit arbeiten könnte.
Hier mal die Datei: https://www.herber.de/bbs/user/95939.xls
es grüßt
Franziska

Anzeige
AW: kürzeste Entfernung zwischen Städten
23.02.2015 22:07:10
Jenny
Hallo Franziska,
danke für deine Mühe. Ich sehe leider 2 Probleme,
einmal hab ich ein 64bit Office deine Makros funktionieren also leider nicht.
Und sicherlich ist es auch mal interessant die realen Entfernungen in der Tabelle zu haben, jedoch variieren die bei Google je nach Tageszeit und Staulage inzwischen.
Außerdem hat meine Tabelle einen ganz bestimmten Sinn, für den ich die Luftlinie brauche.
Aber trotzdem danke
Jenny

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige