arrays - Excel：将最近匹配的行号返回为两个值

Question

我有两个需要匹配的坐标列表。

一个非常大的列表（434,561 行）结构如下（在 Excel 中将列视为 A、B 和 C）：

code (ID) |  Latitude  |  Longitude
----------|------------|-----------
point455  |  4.513523  |  23.754457
point478  |  74.123523 |  83.757459
[...]

另一组具有相同结构的点的第二个列表（4000 行）。

我想将第二个列表中的每个点与第一个列表中最接近的坐标相匹配。差不多是这样的：

(second list)
code (ID) |  Latitude  |  Longitude  |  Closest point
----------|------------|-------------|--------------
tag165    |  4.513523  |  23.754457  |  point488
tag198    |  74.123523 |  83.757459  |  point124
[...]

我尝试分别匹配纬度和经度。下面的公式给出了单元格中包含最近长值的值：

=INDEX(ALLStops!B2:ALLStops!$B$434561, MATCH(MIN(ABS(ALLStops!$B$2:ALLStops!$B$434561-B2)), ABS(ALLStops!B2:ALLStops!$B$434561-B2), 0))

两列具有相同的公式，并且被独立处理。然后我用它=MATCH(E2,ALLStops!B2:B434561,0)来获取行。

但是大约 1/3 的条目具有最接近的坐标在不同行上的值，这显然是无用的。

所以匹配为最接近的两个坐标值需要在同一行，我猜计算必须压缩为一个。

任何人都可以提出一种限制这种情况的方法，或者一种更简洁（并且内存占用更少）的方法吗？

标签： arraysindexingexcel-formulamatchvlookup

Answer 1

编辑：根据您的评论，这是修改后的公式。

假设较长的列表在 A、B 和 C 列中可用，而较短的列表在 F 到 I 中可用，您可以使用此公式从大列表中获取与候选列表中的点最接近的匹配代码。

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

...使用 Ctrl+Shift+Enter 执行。希望这可以帮助