Berechnung einer Luftlinie aus Koordinaten

Forumthread: Berechnung einer Luftlinie aus Koordinaten

15.02.2013 14:09:18

Christian

Hallo alle zusammen,
bitte helft mir, habe auch eine Datei angehangen. Ich weiß das wird nicht einfach, aber ich hoffe jemand kann mir helfen.
habe eine Datei angehangen, in der in Tabelle 1 x und y Koordinaten von 10 Städten stehen.
Ich suche nun ein paar Formeln, die mir helfen würden, das ganze weiter zu füllen
Zuerst zu der Matrix in Tabelle 2:
Hoffe das lässt sich in eine Excel Formel zusammenfassen:
Wenn Zahl in Zeile 1 größer als Zahl in Spalte A (das ganze als Matrix gesehen), dann berechne die Entfernung in Luftlinie zwischen den beiden Städten nach Satz des Pythagoras und gib diese aus. Ansonsten lass die Zelle leer. Das soll bewirken, dass ich nachher nicht einen Wert zwischen der Stadt 5 und 8 habe und dann nochmal einen Wert zwischen der Stadt 8 und 5.
Das heißt z.B. in C2 soll dann die Entfernung zwischen den Städten 1 und 2 stehen.
In Tabelle 3 stehen dann in Spalte A KKLeinste Formeln um die kürzesten Entfernungen herauszufinden. Ich suche dann noch Formeln um in den Spalten B und C die beiden Städte auszugeben. Wenn 2 Verbindungen zwischen unterschiedlichen Städten dieselbe Entfernung haben (da es insgesamt über 700 Städte sind, ist das nicht unwahrscheinlich) sollen alle Verbindungen auch ausgegeben werden. Nicht bei 3mal selbe Entfernung 3mal selbe Verbindung.
Hat da jemand eine Idee?
Gruß und vielen Dank
Christian
https://www.herber.de/bbs/user/83927.xlsx

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Berechnung einer Luftlinie aus Koordinaten

15.02.2013 14:11:16

Rudi

Hallo,
ohne mir die Datei angeschaut zu haben:
http://www.excelformeln.de/formeln.html?welcher=463
Gruß
Rudi

AW: Berechnung einer Luftlinie aus Koordinaten

15.02.2013 14:13:24

Christian

Hallo Rudi, danke für den Hinweis, aber es geht um ein Koordinatensystem mit x und y Achse, nicht um Geokoordinaten.
Gruß
Christin

AW: Berechnung einer Luftlinie aus Koordinaten

15.02.2013 14:34:51

UweD

Hallo
hier schon mal was für Tabelle2
Tabelle2

	A	B	C	D	E	F	G	H	I	J	K
1		1	2	3	4	5	6	7	8	9	10
2	1		1114	1594	1560	1403	513	527	1511	2186	1534
3	2			1306	881	571	607	621	794	1704	1429
4	3				567	833	1415	1492	636	607	247
5	4					319	1212	1275	89	825	788
6	5						987	1039	230	1143	1018
7	6							81	1145	1952	1437
8	7								1205	2024	1517
9	8									913	846
10	9										769
11	10

verwendete Formeln
Zelle	Formel	Bereich
B2	=WENN(B$1>$A2;WURZEL((SVERWEIS($A2;Tabelle1!$A:$C;2;0)-SVERWEIS(B$1;Tabelle1!$A:$C;2;0))^2+(SVERWEIS($A2;Tabelle1!$A:$C;3;0)-SVERWEIS(B$1;Tabelle1!$A:$C;3;0))^2);"")

Tabellendarstellung in Foren Version 5.49

Gruß UweD

AW: Berechnung einer Luftlinie aus Koordinaten

15.02.2013 14:40:16

Christian

Hallo Uwe, danke das klappt schonmal
Christian

AW: Berechnung einer Luftlinie aus Koordinaten

15.02.2013 14:53:43

Rudi

Hallo,
aufbauend auf Uwes Lösung

	A	B	C
1	Entf.	Stadt 1	Stadt 2
2	80,8949937	6	7
3	88,8144132	4	8
4	230,234663	5	8
5	246,64144	3	10
6	319,048586	4	5
7	512,562191	1	6
8	526,983871	1	7

Zelle	Formel
A2	=KKLEINSTE(Tabelle2!$B$2:$K$11;ZEILE(A1))
B2	=INDEX(Tabelle2!$A$2:$A$11;SUMMENPRODUKT((Tabelle2!$B$2:$K$11=A2)*(ZEILE($1:$10))))
C2	=INDEX(Tabelle2!$B$1:$K$1;SUMMENPRODUKT((Tabelle2!$B$2:$K$11=A2)*(SPALTE(A:J))))

Gruß
Rudi

AW: Berechnung einer Luftlinie aus Koordinaten

15.02.2013 15:12:37

Christian

Hallo Rudi,
tut mir leid, aber das funktioniert leider so nicht.
Ich habe in angehangener Tabelle den Wert in K4 nach K5 kopiert, um 2 identische Werte zu erzeugen, demnach hätten die Formeln in Tabelle 3 Spalten B und C die Kombinationen 3 - 10 und 4 - 10 stehen sollen, stattdessen steht in Spalte B die Zahl 7, die und in Spalte C Bezug.
Kannst du bitte nochmal schaun?
Christian
https://www.herber.de/bbs/user/83928.xlsx

AW: Berechnung einer Luftlinie aus Koordinaten

15.02.2013 16:43:22

Rudi

Hallo,
für doppelte Werte kenne ich keine Lösung.
Gruß
Rudi

Infobox / Tutorial

Berechnung einer Luftlinie aus Koordinaten

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Vorbereitung der Daten: Stelle sicher, dass Deine Tabelle zwei Spalten mit den x- und y-Koordinaten enthält. Diese sollten in Tabelle1 angeordnet sein.
Matrix erstellen: In Tabelle2 erstelle eine Matrix, in der die Entfernungen zwischen den Städten angezeigt werden. Die Spaltenüberschriften sollten die Städtenummern darstellen.
Formel für die Entfernung: In der Zelle B2 von Tabelle2 kannst Du folgende Formel verwenden:
```
=WENN(B$1>$A2;WURZEL((SVERWEIS($A2;Tabelle1!$A:$C;2;0)-SVERWEIS(B$1;Tabelle1!$A:$C;2;0))^2+(SVERWEIS($A2;Tabelle1!$A:$C;3;0)-SVERWEIS(B$1;Tabelle1!$A:$C;3;0))^2);"")
```
Diese Formel berechnet die Luftlinie zwischen den Städten basierend auf dem Satz des Pythagoras.
Ziehen der Formel: Ziehe die Formel in die restlichen Zellen der Matrix, um die Entfernungen zwischen allen Städten zu berechnen.
Kürzeste Entfernungen finden: In Tabelle3 kannst Du die kürzesten Entfernungen mit der KKLEINSTE-Funktion ermitteln. Beispiel:
```
=KKLEINSTE(Tabelle2!$B$2:$K$11;ZEILE(A1))
```
Städte identifizieren: Verwende INDEX und SUMMENPRODUKT, um die Städte für die kürzesten Entfernungen auszugeben.

Häufige Fehler und Lösungen

Fehler: Falsche Werte angezeigt: Überprüfe die Referenzen in Deiner Formel. Achte darauf, dass die Koordinaten korrekt verknüpft sind.
Lösung: Doppelte Werte: Wenn Du identische Entfernungen hast und die Städte nicht korrekt angezeigt werden, stelle sicher, dass die Formeln in Tabelle3 richtig eingestellt sind.
Fehler: Leere Zellen: Wenn Zellen leer bleiben, kann es daran liegen, dass die Bedingung in der WENN-Formel nicht erfüllt ist. Überprüfe die Werte in den Zeilen und Spalten.

Alternative Methoden

Wenn Du die Entfernung zwischen Koordinaten berechnen möchtest, kannst Du auch Geokoordinaten verwenden und eine Berechnung basierend auf der Haversine-Formel durchführen. Hierfür gibt es spezialisierte Add-Ins oder VBA-Skripte.

Praktische Beispiele

Beispiel: 10 Städte: Angenommen, Du hast die Koordinaten von 10 Städten. Mit der oben beschriebenen Methode kannst Du schnell die Luftlinientfernung zwischen allen Städten berechnen.
Visualisierung: Du kannst die Ergebnisse in einem Diagramm darstellen, um die Entfernungen zwischen den Städten besser zu visualisieren.

Tipps für Profis

Nutze die SVERWEIS-Funktion effizient, um Daten aus großen Tabellen schnell abzurufen.
Experimentiere mit bedingter Formatierung, um die kürzesten Entfernungen hervorzuheben.
Wenn Du mit großen Datensätzen arbeitest, erwäge die Verwendung von Excel-Tabellen, um die Leistung zu verbessern.

FAQ: Häufige Fragen

1. Wie berechne ich die Entfernung zwischen zwei Koordinaten in Excel?
Du kannst die Entfernung zwischen Koordinaten in Excel mit Hilfe des Satzes des Pythagoras berechnen. Verwende dazu die WURZEL-Funktion in Kombination mit den Koordinaten.

2. Gibt es eine Formel, um die Luftlinie zwischen zwei Städten zu berechnen?
Ja, die Formel zur Berechnung der Luftlinie zwischen zwei Städten lautet:

=WURZEL((x2 - x1)^2 + (y2 - y1)^2)

Hierbei sind x1, y1 die Koordinaten der ersten Stadt und x2, y2 die der zweiten Stadt.

Beliebteste Forumthreads (12 Monate)

Berechnung einer Luftlinie aus Koordinaten

Forumthread: Berechnung einer Luftlinie aus Koordinaten

7 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Berechnung einer Luftlinie aus Koordinaten

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

7
Beiträge zum Forumthread

Beiträge zu diesem Forumthread