公式

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相比，JavaScript(x，y)而不是Atan2(y，x)在Excel中定义为背靠背。

你需要颠倒这两个论点的顺序：-

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

请参阅this

所以

=haversine(59.3293371,13.4877472,59.3225525,13.4619422)

给出

1.65 km

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

Answer 2

很棒的工具！只要强调结果将以为单位，那么如果您想要miles，则将结果乘以0.62137。