查找与 X 和 Y 坐标相关的最接近/最近值 Excel

查找与 X 和 Y 坐标相关的最接近/最近值 Excel

几天来我一直在努力解决一个问题。我有一张包含大量欧洲机场数据的 Excel 表。为了简单起见,我将数据缩小到同一张表中的几列。

D 列 =国际航空运输协会代码(每个机场的“名称代码”,例如名称:埃因霍温机场 -> IATA:EIN

J 列 = 机场 (D)Y 坐标

K 列 = 机场 (D)X 坐标

现在我的目标是空白列(N)为我提供每个最近机场的 IATA 代码(D)。

例如:

值 (D2) =短暂性脑缺血发作

值 (J2) =41.4146995544

值(K2)=19.7206001282

值(N2)= 在这里我想找到与坐标相关的最近的机场

我已经尝试过:1.将坐标 Y [J] 和 X [K] 转换为单个值,然后使用函数

<=INDEX([IATA],MATCH(MIN(ABS(M3:M1045-M2)),ABS(M3:M1045-M2),0))>。

在此公式中,列 [M] 是坐标的单个值。我遇到的问题是

1.1 Y[J]和X[K]的单一值不准确

1.2 公式没有显示准​​确的结果

1.3 我仍然不知道如何在忽略同一行内的值的情况下应用公式。例如,显然,Excel 会说距离“EIN”最近的机场 =“EIN”,因为它是完全匹配!

  1. 我也看到很多人使用 LARGE 和 SMALL 函数,但我想找到最接近的机场,无论值是更高还是更低。

有人可以帮我解决这个难题吗:)

答案1

以下是如何快速生成所有距离的表格:

  1. 生成所有 XY 坐标的表格(我假设你已经有这个了)

在此处输入图片描述

  1. 创建一个表格,在标题和第一列列出所有机场

在此处输入图片描述

  1. 在 中B2插入以下公式:

    =SQRT((VLOOKUP($A2,$I$1:$K$4,2)-VLOOKUP(B$1,$I$1:$K$4,2))^2+(VLOOKUP($A2,$I$1:$K$4,3)-VLOOKUP(B$1,$I$1:$K$4,3))^2)
    
  2. 复制并粘贴公式以填充整个表格

在此处输入图片描述

获得该表格后,您可以使用以下公式来查找最近的机场:

=INDEX(A2:A4,MATCH(SMALL(OFFSET(A2:A4,0,MATCH(O2,B1:D1)),2),OFFSET(A2:A4,0,MATCH(O2,B1:D1)),0))

在此处输入图片描述

为了得到第二接近、第三接近等等,将公式部分的2参数更改为,等等。(注意:我把所有这些都放在一张纸上,但你可能不想这样做,根据需要调整范围)SMALL34

相关内容