HERBERS Excel-Forum - das Archiv
Thema: Berechnung einer Luftlinie aus Koordinaten
Berechnung einer Luftlinie aus Koordinaten
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
AW: Berechnung einer Luftlinie aus Koordinaten
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
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
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
Christian
Hallo Uwe, danke das klappt schonmal
Christian
AW: Berechnung einer Luftlinie aus Koordinaten
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
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
Rudi
Hallo,
für doppelte Werte kenne ich keine Lösung.
Gruß
Rudi
Berechnung einer Luftlinie aus Koordinaten
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
AW: Berechnung einer Luftlinie aus Koordinaten
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
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
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
Christian
Hallo Uwe, danke das klappt schonmal
Christian
AW: Berechnung einer Luftlinie aus Koordinaten
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
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
Rudi
Hallo,
für doppelte Werte kenne ich keine Lösung.
Gruß
Rudi
Excel-Beispiele