Berechnung des Abstandes zwischen Punkten (Microsoft Excel)
Mike verfolgt Breiten- und Längengrade in einem Excel-Arbeitsblatt. Da dies im Wesentlichen Punkte in einem Raster sind, möchte Mike die Entfernung zwischen zwei beliebigen Breiten- / Längenpunkten berechnen.
Wenn die Breiten- und Längengrade wirklich nur Punkte auf einem Gitter wären, wäre die Berechnung des Abstands zwischen ihnen einfach. Das Problem ist, dass es sich wirklich um Punkte auf einer Kugel handelt, was bedeutet, dass Sie keine Flachgitterberechnungen verwenden können, um die Entfernung zu bestimmen. Darüber hinaus gibt es viele Möglichkeiten, Entfernungen zu berechnen: kürzeste Oberflächenentfernung, optimale Flugbahn („Luftlinie“), Entfernung durch die Erde, Fahrstrecke usw.
Dies könnte natürlich eine komplizierte Frage sein. In dem verfügbaren Raum werde ich einige Möglichkeiten untersuchen, um die Entfernung des Großkreises („in Luftlinie“) zu bestimmen, und dann einige Referenzen für zusätzliche Informationen zu den anderen Arten von Berechnungen bereitstellen.
Als erstes müssen Sie herausfinden, wie der Breiten- und Längengrad jedes Punkts in Excel dargestellt wird. Es gibt verschiedene Möglichkeiten, wie es dargestellt werden könnte. Sie können beispielsweise die Grade, Minuten und Sekunden in einzelne Zellen eingeben. Oder Sie können sie in einer einzelnen Zelle als DD: MM: SS haben. Jeder Weg ist akzeptabel, aber sie müssen in Ihren Formeln unterschiedlich behandelt werden. Warum? Wenn Sie Breiten- und Längengrade als DD: MM: SS eingeben, konvertiert Excel diese intern in einen Zeitwert, und Sie müssen diese Konvertierung nur berücksichtigen.
Was Sie tun müssen, ist, Ihren Breiten- und Längengrad in einen Dezimalwert im Bogenmaß umzuwandeln. Wenn Sie eine Koordinate in drei separaten Zellen (Grad, Minuten und Sekunden) haben, können Sie die Konvertierung in einen Dezimalwert im Bogenmaß mit der folgenden Formel durchführen:
=RADIANS((Degrees3600+Minutes60+Seconds)/3600)
Die Formel verwendet benannte Bereiche für Ihre Grade, Minuten und Sekunden. Diese drei Werte werden in einen einzelnen Wert konvertiert, der die Gesamtgrade darstellt, und anschließend mit der RADIANS-Funktion in Radiant konvertiert.
Wenn Sie mit einem Wert von 32 Grad, 48 Minuten und 0 Sekunden beginnen, sieht die Formel folgendermaßen aus:
=RADIANS((323600+4860+0)/3600) =RADIANS((115200+2880+0)/3600) =RADIANS(118080/3600) =RADIANS(32.8) =0.572467995
Wenn Sie Ihre Koordinaten im Format DD: MM: SS in einer einzelnen Zelle (in diesem Beispiel Zelle E12) speichern, können Sie die folgende Formel verwenden, um sie im Bogenmaß in einen Dezimalwert umzuwandeln:
=RADIANS((DAY(E12)86400+HOUR(E12)3600+MINUTE(E12)*60+SECOND(E12))/3600)
Angenommen, Zelle E12 enthält 32:48:00, sieht die Formel folgendermaßen aus:
=RADIANS((186400+83600+48*60+0)/3600) =RADIANS((86400+28800+2880+0)/3600) =RADIANS(118080/3600) =RADIANS(32.8) =0.572467995
Mit Ihren Koordinaten im Bogenmaß können Sie eine trigonometrische Formel verwenden, um die Entfernung entlang der Oberfläche einer Kugel zu berechnen. Es gibt viele solcher Formeln, die verwendet werden könnten; Die folgende Formel wird für unsere Zwecke ausreichen:
=ACOS(SIN(Lat1)SIN(Lat2)+COS(Lat1)COS(Lat2)COS(Lon2-Lon1))180/PI()*60
In dieser Formel muss jede der Koordinaten für Breite (Lat1 und Lat2) und Länge (Lon1 und Lon2) ein Dezimalwert im Bogenmaß sein, wie bereits erläutert. Die Formel gibt einen Wert in Seemeilen zurück, auf den Sie dann verschiedene Formeln anwenden können, um sie nach Bedarf in andere Maßeinheiten umzurechnen.
Sie sollten sich darüber im Klaren sein, dass die Werte, die Sie mithilfe einer beliebigen Formel zur Berechnung des Abstands auf der Oberfläche einer Kugel ermitteln, zu leicht fehlerhaften Ergebnissen führen. Warum? Weil die Erde keine perfekte Kugel ist. Daher sollten die Entfernungen nur als ungefähr angesehen werden. Wenn Sie etwas genauer werden möchten, können Sie die folgenden Formeln verwenden, um Ihre Seemeilen zu bestimmen:
=ACOS(SIN(Lat1)SIN(Lat2)+COS(Lat1)COS(Lat2)COS(Lon2-Lon1))3443.89849
Diese Formel ersetzt den Radius einer Kugel (180 / PI () * 60 oder 3437.746771) durch den Radius der Erde (3443.89849 Seemeilen). In jedem Fall sollte die Antwort immer noch als ungefähr angesehen werden.
Wie Sie sehen können, ist die Formel zur Berechnung von Entfernungen ziemlich lang. Möglicherweise fällt es Ihnen leichter, eine eigene benutzerdefinierte Funktion zu entwickeln, die die Berechnung für Sie übernimmt. Die folgende Funktion nimmt vier Werte an (die beiden Breiten- und Längengrade in Grad) und gibt dann ein Ergebnis in Seemeilen zurück:
Function CrowFlies(dlat1, dlon1, dlat2, dlon2) Pi = Application.Pi() earthradius = 3443.89849 'nautical miles lat1 = dlat1 Pi / 180 lat2 = dlat2 Pi / 180 lon1 = dlon1 Pi / 180 lon2 = dlon2 Pi / 180 cosX = Sin(lat1) Sin(lat2) + Cos(lat1) _ Cos(lat2) * Cos(lon1 - lon2) CrowFlies = earthradius * Application.Acos(cosX) End Function
Wenn Sie eine eingehendere Diskussion der Breiten- und Längengrade sowie der damit verbundenen Mathematik wünschen, finden Sie auf dieser Website eine gute Auswahl an Artikeln:
http://mathforum.org/library/drmath/sets/select/dm_lat_long.html
Mit der Mathematik können Sie sich verschiedene Formeln ansehen, die Sie verwenden können. Es gibt eine interessante in VBA auf dieser Webseite:
http://www.freevbcode.com/ShowCode.asp?ID=5532
Eine gute allgemeine Diskussion finden Sie auch auf der Website von Chip Pearson, hier:
http://www.cpearson.com/excel/LatLong.aspx
_Hinweis: _
Wenn Sie wissen möchten, wie die auf dieser Seite (oder auf einer anderen Seite der ExcelTips-Websites) beschriebenen Makros verwendet werden, habe ich eine spezielle Seite vorbereitet, die hilfreiche Informationen enthält.
ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.
Dieser Tipp (9512) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365. Eine Version dieses Tipps für die ältere Menüoberfläche von Excel finden Sie hier: