几天来我一直在努力解决一个问题。我有一张包含大量欧洲机场数据的 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”,因为它是完全匹配!
- 我也看到很多人使用 LARGE 和 SMALL 函数,但我想找到最接近的机场,无论值是更高还是更低。
有人可以帮我解决这个难题吗:)
答案1
以下是如何快速生成所有距离的表格:
- 生成所有 XY 坐标的表格(我假设你已经有这个了)
- 创建一个表格,在标题和第一列列出所有机场
在 中
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)
复制并粘贴公式以填充整个表格
获得该表格后,您可以使用以下公式来查找最近的机场:
=INDEX(A2:A4,MATCH(SMALL(OFFSET(A2:A4,0,MATCH(O2,B1:D1)),2),OFFSET(A2:A4,0,MATCH(O2,B1:D1)),0))
为了得到第二接近、第三接近等等,将公式部分的2
参数更改为,等等。(注意:我把所有这些都放在一张纸上,但你可能不想这样做,根据需要调整范围)SMALL
3
4