excel 取百度经纬度，再用经纬度计算两点间距离

最新推荐文章于 2024-09-06 09:54:49 发布

学习白痴

最新推荐文章于 2024-09-06 09:54:49 发布

阅读量882

点赞数

文章标签：百度

本文链接：https://blog.csdn.net/weixin_37981475/article/details/122133915

版权

宏程序版本

Sub 经纬度提取()
    Url = "http://api.map.baidu.com/geocoder?address="
    With CreateObject("MSXML2.XMLHTTP")
        mr = [a65536].End(3).Row
        For m = 2 To [a65536].End(3).Row
            Cells(m, 6) = "": Cells(m, 7) = ""
            .Open "GET", Url & Cells(m, 1), False
            .send
            V = .responseText
            Cells(m, 7) = Split(Split(V, "at>")(1), "<")(0)
            Cells(m, 6) = Split(Split(V, "g>")(1), "<")(0)
        Next
    End With
End Sub

在这里插入图片描述
表格样式
计算到仓距离

=6371004*ACOS((SIN(RADIANS(C$1))*SIN(RADIANS(F2))+COS(RADIANS(C$1))*COS(RADIANS(F2))*COS(RADIANS(E2-B$1))))/1000*150%

计算到首点距离

=6371004*ACOS((SIN(RADIANS(F$1))*SIN(RADIANS(F2))+COS(RADIANS(F$1))*COS(RADIANS(F2))*COS(RADIANS(E2-E$1))))/1000*150%

最后一个点到仓的距离就是回程的距离

线路系数
=6371004*ACOS((SIN(RADIANS(C$1))*SIN(RADIANS(F2))+COS(RADIANS(C$1))*COS(RADIANS(F2))*COS(RADIANS(E2-B$1))))/1000距离

距离<=100 *150
距离<=200 *130
距离>200 *105