利用excel计算两经纬度之间距离及角度
1. 按ALT+F11进入VBA编译器
2. 在
工程
路基工程安全技术交底工程项目施工成本控制工程量增项单年度零星工程技术标正投影法基本原理
管理器中插入“模块”，然后将下面代码粘
贴到模块里
'计算两经纬度之间距离=cal_long_lat(经度1,纬度1,经度2,纬度2)
Public Function Cal_Long_Lat(ByVal long1 As Double, ByVal lat1 As Double, ByVal long2 As
Double, ByVal lat2 As Double) As Double
Const PI As Double = 3.1415926535
Dim AngleLong1, AngleLat1, AngleLong2, AngleLat2 As Double
AngleLong1 = long1 * PI / 180
AngleLat1 = lat1 * PI / 180
AngleLong2 = long2 * PI / 180
AngleLat2 = lat2 * PI / 180
Dim sinX, cosX As Double
sinX = Sin(AngleLat1) * Sin(AngleLat2)
cosX = Cos(AngleLat1) * Cos(AngleLat2) * Cos(AngleLong2 - AngleLong1)
x = sinX + cosX
On Error Resume Next
ax = Atn(-x / Sqr(-x * x + 1)) + 2 * Atn(1)
Cal_Long_Lat = 6368.16 * ax
End Function
'计算两经纬度之间角度=cal_bearing(经度1,纬度1,经度2,纬度2)
Public Function Cal_bearing(ByVal long1 As Double, ByVal lat1 As Double, ByVal long2 As
Double, ByVal lat2 As Double) As Double
Const PI As Double = 3.1415926535
Dim AngleLong1, AngleLat1, AngleLong2, AngleLat2 As Double
AngleLong1 = long1 * PI / 180
AngleLat1 = lat1 * PI / 180
AngleLong2 = long2 * PI / 180
AngleLat2 = lat2 * PI / 180
y = Sin(AngleLong1 - AngleLong2) * Cos(AngleLat2)
x = Cos(AngleLat1) * Sin(AngleLat2) - Sin(AngleLat1) * Cos(AngleLat2) *
Cos(AngleLong1 - AngleLong2)
Cal_bearing = 360 - (Atan2(y, x) * 180 / PI + 360) Mod 360
End Function
Public Function Atan2(ByVal y As Double, ByVal x As Double) As Double
Const PI As Double = 3.1415926535
If y > 0 Then
If x >= y Then
Atan2 = Atn(y / x)
ElseIf x <= -y Then
Atan2 = Atn(y / x) + PI
Else
Atan2 = PI / 2 - Atn(x / y)
End If
Else
If x >= -y Then
Atan2 = Atn(y / x)
ElseIf x <= y Then
Atan2 = Atn(y / x) - PI
Else
Atan2 = -Atn(x / y) - PI / 2
End If
End If
End Function
3. 关闭VBA编译器，然后在单元格中使用公式
Cal_Long_Lat(经度1，纬度1，经度2，纬度2)
即可计算两经纬度之间的距离，公式Cal_bearing
(经度1，纬度1，经度2，纬度2)可计算两经纬
度之间的角度。
浙公网安备 33021202002483