时间戳

首页 > 解决方案 > Excel INDEX LOOKUP VLOOKUP HLOOKUP 组合

excel - Excel INDEX LOOKUP VLOOKUP HLOOKUP 组合

问题描述

我有一些由大地坐标定义的路径和一些也由坐标定义的点。我试图找到点所在或附近的确切或最近的路径，但没有成功。我的表是这样的：

                        Path-Start               Path-End                    
ID    vs      ID     East       North        East       North            Point    East      North
1-A    600    1-A    366,585    7,270,720    366,704    7,270,700        L-01    366,887    7,269,446
1-B    525    1-B    366,704    7,270,700    366,822    7,270,682        L-02    366,822    7,269,546
1-C    519    1-C    366,822    7,270,682    366,939    7,270,663        L-03    366,757    7,269,446
1-D    629,4  1-D    366,939    7,270,663    367,056    7,270,645        L-04    366,822    7,269,346
2      728,3    2    365,995    7,270,467    366,145    7,270,469        M-01    366,822    7,269,466
3      646,2    3    367,421    7,270,465    367,561    7,270,465        M-02    366,973    7,269,466
4      429,7    4    367,726    7,270,470    367,875    7,270,472        M-04    366,671    7,269,466
5      759,4    5    365,857    7,270,216    366,011    7,270,216        M-06    366,585    7,269,622
6,1    888,6    6,1  366,072    7,270,217    366,192    7,270,217        M-07    366,746    7,269,613
6,2    741,2    6,2  366,192    7,270,217    366,312    7,270,217        M-08    366,898    7,269,613
6,3    570,3    6,3  366,312    7,270,217    366,432    7,270,217        M-09    367,062    7,269,618
7,1    481,6    7,1  366,572    7,270,216    366,726    7,270,216        M-10    366,582    7,269,314
7,2    444,7    7,2  366,726    7,270,216    366,88    7,270,216        M-11    366,746    7,269,319
7,3    556    7,3    366,88    7,270,216    367,034    7,270,216        M-12    366,896    7,269,319
8,1    936,9    8,1  367,425    7,270,220    367,576    7,270,223        M-13    367,035    7,269,309
8,2    422,7    8,2  367,577    7,270,223    367,739    7,270,216        M-15    366,659    7,269,780
8,3    412,8    8,3  367,74    7,270,215    367,89    7,270,216        M-16    366,822    7,269,780
9      574,3    9    365,662    7,269,966    365,816    7,269,966        M-17    366,985    7,269,780
10     534,6    10    367,827    7,269,966    367,981    7,269,966        M-20    366,659    7,269,152
11,1   761,3   11,1    365,552    7,269,716    365,652    7,269,716        M-21    366,822    7,269,152
11,2    664,9  11,2    365,652    7,269,716    365,752    7,269,716        M-22    366,985    7,269,152
12    660,8    12    365,887    7,269,716    366,041    7,269,716        M-24    366,828    7,270,048
13    1156,5    13    366,252    7,269,716    366,406    7,269,716        M-25    366,816    7,268,884
14,1    524,9    14,1    366,522    7,269,716    366,676    7,269,716        S-03    366,661    7,269,259
14,2    411,1    14,2    366,676    7,269,716    366,83    7,269,716        S-04    366,983    7,269,259
14,3    395,3    14,3    366,83    7,269,716    366,984    7,269,716        S-05    366,399    7,269,635

该点（例如 L-01）的坐标位于其右侧。我必须根据它的坐标找到它所属的路径ID，然后写入路径的vs值。非常感谢任何帮助或想法。

标签： excelexcel-formula

解决方案

由于您的问题实际上是关于如何“找到确切或最近的路径”，因此我将重点关注这一点，而忽略计算该距离的机制。

修改问题以关注问题

我将用更容易理解的两点之间的笛卡尔距离替换从点到线或大地线的距离。因此，在我的隐喻表述中，我将在您的表格的重新制定版本中找到基于vs的最接近的vs，但将其放入 Excel 表格中以使其更具可读性和更易于理解。§[Point East, Point North][Path Start East, Path Start North][Path End East, Path End North]

在此处输入图像描述

我将用我基于毕达哥拉斯距离的过于简化的虚拟公式替换您对我未知的距离计算，该公式查看每个点到路径起点和路径终点的距离并将它们相加。所以，它实际上是：

SQRT( ([Pt E]-[Path.St E])^2 + ([Pt N] - [Path.St N])^2 ) +
SQRT( ([Pt E]-[Path.End E])^2 + ([Pt N] - [Path.End N])^2 )

解决查找问题

希望你有 Excel 365 - 否则，这必须以不同的方式完成，并使用辅助单元格......

我在表格末尾创建了一个列，其中包含基于最小“距离”的最近vs的结果。下面公式的重要部分是 INDEX 的使用，我认为这首先是您的问题 - INDEX 是最好的方法。

=LET( pythDist, SQRT( ([@[Point East]]-[Path-Start East])^2 + ([@[Point North]] - [Path-Start North])^2 ) 
                      + SQRT( ([@[Point East]]-[Path-End East])^2 + ([@[Point North]] - [Path-End North])^2 ),
       INDEX( [vs], MATCH( MIN( pythDist ), pythDist, 0 ) ) )

这首先将您的变量用于距离计算（Point East、Point North、[Path Start North]、[Path Start East]、[Path Start North]、[Path End East]）并计算我的假距离，称为pythDist。在这一行中，您需要输入您将要使用的距离的实际计算。最重要的一点是：

当您提到您的路径开始和结束时，请输入整个值范围 - 即整个列。例如，在我的工作表中，您的 Path-Start East 位于 D2:D27 -如果 您不使用 Excel 表或列名[Path-Start East] 如果您使用的是 Excel 表格。

当提及您的观点时，请参考使用 相对寻址的各个单元格。即我的工作簿中的 I2 用于 Point East 第一个值或[@[Point East]] 使用 Excel 表寻址。

因此pythDist实际上是针对所有路径变量评估每个点，以创建包含距离的结果列。这就是你在计算中需要完成的，用你的真实公式替换我的虚拟公式。

然后定位vs的过程现在简单地由 INDEX 完成。您将[vs]列（或锁定范围）放入 INDEX 的数组部分，然后使用 MATCH 在pythDist中查找最小距离值的位置。

笔记

如果你没有 Excel 365，你仍然可以这样做，但它很乱。如果您需要有关如何做到这一点的帮助 - 请在评论中说出来，我可以试一试。LET 让事情变得更干净。

§ - 要制作 Excel 表格，请确保您的标题行具有唯一且具有代表性的字段名称（即，将 [North] 更改为 [Path Start North] 等）。然后单击表格中的任意位置并按CTRL+ T。Excel 会询问您的表格是否有标题 - 说是。

推荐阅读