Excel中如何匹配两组坐标最近点并获取对应行号？

阿华AIGC实验室

2026-5-12

嘿，这个坑我之前帮人踩过！分开匹配纬度和经度肯定会出问题——毕竟最近的纬度点和最近的经度点基本不可能刚好是同一个坐标对吧？这就是你那1/3无效条目的根源。下面给你几个靠谱的方案，兼顾准确性和内存效率：

核心问题复盘

之前的思路错在把纬度、经度的距离分开计算，但实际地理距离是二维（或球面）的综合值，单独取某一维度的最近点，必然会出现两者不在同一行的无效匹配。我们需要直接计算每个点到大列表中所有点的综合距离，再找最近的那个点。

方案1：Excel动态数组公式（适合365/2021版本，内存友好）

如果你用的是新版Excel，动态数组公式是最省心的选择，不需要下拉，自动批量计算，内存占用比传统数组公式低很多。

假设小列表在Sheet1（A=code，B=Latitude，C=Longitude），大列表在ALLStops（结构相同），在Sheet1的D2单元格输入以下公式（输入后自动溢出到所有行）：

=XLOOKUP(TRUE, BYROW(ALLStops!$B$2:$C$434561, LAMBDA(x, (x[Latitude]-B2)^2 + (x[Longitude]-C2)^2 = MIN(BYROW(ALLStops!$B$2:$C$434561, LAMBDA(y, (y[Latitude]-B2)^2 + (y[Longitude]-C2)^2))))), ALLStops!$A$2:$A$434561, "无匹配")

公式说明：

用BYROW遍历大列表每一行，计算当前小列表点与该行的平面距离平方（用平方避免开根号，节省计算资源）
MIN找到最小的距离平方值
再通过BYROW定位到距离最小的行，用XLOOKUP返回对应的code

如果你的Excel版本不支持LAMBDA，可以用旧版数组公式（输入后按Ctrl+Shift+Enter确认，下拉填充）：

=INDEX(ALLStops!$A$2:$A$434561, MATCH(MIN((ALLStops!$B$2:$B$434561-B2)^2 + (ALLStops!$C$2:$C$434561-C2)^2), (ALLStops!$B$2:$B$434561-B2)^2 + (ALLStops!$C$2:$C$434561-C2)^2, 0))

这个公式准确率没问题，但4000行的话计算量会比动态数组大一点，不过比之前的方法靠谱多了。

方案2：Power Query（大数据首选，内存占用极低）

Power Query是Excel内置的ETL工具，处理几十万行数据完全无压力，后台计算不会让Excel卡顿，内存占用远低于公式。

操作步骤：

把两个列表导入Power Query：
- 选中大列表（ALLStops）数据区域，点「数据」→「从表格/区域」，加载到编辑器
- 同样把小列表（Sheet1）导入Power Query
在小列表的查询中添加自定义列：
- 点「添加列」→「自定义列」，输入公式：
```
= Table.Min(ALLStops, (x) => (x[Latitude] - [Latitude])^2 + (x[Longitude] - [Longitude])^2)[code]
```
这个公式会遍历大列表所有行，计算距离平方，找到最近的点并返回对应的code
关闭编辑器，把结果上载到Excel即可

这个方案我强烈推荐，处理43万行+4000行完全不卡，结果是静态的，不会像公式那样每次编辑都重新计算。

方案3：优化版VBA函数（灵活准确，适合跨区域坐标）

如果需要更准确的球面地理距离（比如跨城市、跨国家的坐标），可以用VBA自定义函数，而且把数据读入数组后，速度和内存占用都会很优秀。

代码实现：

按Alt+F11打开VBA编辑器，插入模块，粘贴以下代码：

Function GetNearestCodeFast(lat As Double, lon As Double, rng As Range) As String
    Dim dataArr As Variant
    Dim minDist As Double
    Dim currentDist As Double
    Dim i As Long
    Dim targetCode As String
    Dim latRad As Double, lonRad As Double
    
    ' 把大列表数据读入数组，大幅减少单元格访问，提升速度
    dataArr = rng.Value
    latRad = lat * WorksheetFunction.Pi() / 180
    lonRad = lon * WorksheetFunction.Pi() / 180
    minDist = 1E+10 ' 初始化一个极大值
    
    ' 遍历数组计算球面距离（Haversine公式，更准确）
    For i = 2 To UBound(dataArr, 1)
        Dim currentLatRad As Double, currentLonRad As Double
        currentLatRad = dataArr(i, 2) * WorksheetFunction.Pi() / 180
        currentLonRad = dataArr(i, 3) * WorksheetFunction.Pi() / 180
        
        currentDist = Application.WorksheetFunction.Acos( _
            Sin(latRad) * Sin(currentLatRad) + _
            Cos(latRad) * Cos(currentLatRad) * Cos(lonRad - currentLonRad) _
        ) * 6371 ' 6371为地球半径，单位公里
        
        If currentDist < minDist Then
            minDist = currentDist
            targetCode = dataArr(i, 1)
        End If
    Next i
    
    GetNearestCodeFast = targetCode
End Function