Excel匹配地址与最近站点坐标的公式结果异常，求排查修正

核心问题定位 #

你的公式存在两个关键逻辑错误，这是结果偏差的主要原因：

• 行号引用错误：ROW(A$2:A$17986)返回的是Excel的绝对行号（比如第2行、第3行...），但INDEX的第二参数需要的是相对于数据区域A$2:A$17986的相对行号（从1开始计数），直接用绝对行号会导致INDEX取到错误的行。
• 多匹配场景失效：如果有多个站点和当前地址的距离同为最小值，SUMPRODUCT会把这些站点的行号相加，最终得到的数字根本不是有效的行号，必然返回错误结果。

分步排查与修正 #

1. 验证距离计算的准确性
   选一个地址（比如H2、I2），手动计算它和几个站点的距离（用公式SQRT((Bx - H2)^2 + (Cx - I2)^2)，x取具体行号），对比公式自动计算的结果，确认：

   • 经纬度列有没有搞混（比如把纬度和经度的位置写反）
   • 表1、表2的经纬度引用是否对应正确（有没有把B/C列和H/I列弄混）

2. 修正行号偏移问题
   把公式中的ROW(A$2:A$17986)改为ROW(A$2:A$17986)-ROW(A$2)+1，这样得到的是相对于数据区域起始行的相对行号，修正后的公式片段：

   INDEX(A$2:A$17986, SUMPRODUCT((MIN(SQRT((B$2:B$17986 - H2)^2 + (C$2:C$17986 - I2)^2)) = SQRT((B$2:B$17986 - H2)^2 + (C$2:C$17986 - I2)^2)) * (ROW(A$2:A$17986)-ROW(A$2)+1))
   

3. 替换为更可靠的匹配逻辑
   放弃SUMPRODUCT，改用MATCH配合MIN来定位，这个逻辑更清晰，且能正确处理多匹配场景（返回第一个符合条件的站点）：

   INDEX(A$2:A$17986, MATCH(MIN(SQRT((B$2:B$17986-H2)^2+(C$2:C$17986-I2)^2)), SQRT((B$2:B$17986-H2)^2+(C$2:C$17986-I2)^2), 0))
   

4. 优化计算性能
   因为开根号是冗余计算（平方的最小值等于开根号后的最小值），可以去掉SQRT减少计算量，公式更高效：

   INDEX(A$2:A$17986, MATCH(MIN((B$2:B$17986-H2)^2+(C$2:C$17986-I2)^2), (B$2:B$17986-H2)^2+(C$2:C$17986-I2)^2, 0))
   

5. 校验数据范围
   确认公式中引用的A$2:A$17986、B$2:B$17986等范围是否和实际的15000个站点数据完全对应，有没有多包含空白行或少包含有效数据行。

内容的提问来源于stack exchange，提问作者tmh1998