excel - VBA 半正弦公式

Question

我正在尝试将 Haversine 公式实现到 excel 函数中。它看起来像这样:

Public Function Haversine(Lat1 As Variant, Lon1 As Variant, Lat2 As Variant, Lon2 As Variant)
Dim R As Integer, dlon As Variant, dlat As Variant, Rad1 As Variant
Dim a As Variant, c As Variant, d As Variant, Rad2 As Variant

R = 6371
dlon = Excel.WorksheetFunction.Radians(Lon2 - Lon1)
dlat = Excel.WorksheetFunction.Radians(Lat2 - Lat1)
Rad1 = Excel.WorksheetFunction.Radians(Lat1)
Rad2 = Excel.WorksheetFunction.Radians(Lat2)
a = Sin(dlat / 2) * Sin(dlat / 2) + Cos(Rad1) * Cos(Rad2) * Sin(dlon / 2) * Sin(dlon / 2)
c = 2 * Excel.WorksheetFunction.Atan2(Sqr(a), Sqr(1 - a))
d = R * c
Haversine = d
End Function

但是当我测试它时，我得到了错误的距离......我不明白为什么。对于本主题中使用的坐标: Function to calculate distance between two coordinates shows wrong
我得到 20013,44 作为输出。有人知道这里有什么问题吗？找不到我的错误...

Answer 1

与 JavaScript 相比，Atan2 在 Excel 中从前向后定义，即 Atan2(x,y) 而不是 Atan2(y,x)。

您需要颠倒两个参数的顺序:-

c = 2 * Excel.WorksheetFunction.Atan2(Sqr(1 - a), Sqr(a))

见 this

所以

=haversine(59.3293371,13.4877472,59.3225525,13.4619422)

给

1.65 km

这是乌鸦飞翔时的正确距离。