☰

如何获取列表中数组公式结果的引用？

tag-icon • tag-icon microsoft-excel worksheet-function
如何获取列表中数组公式结果的引用？

我有两个数组，每个数组包含我们和竞争对手的商店 ID、x 坐标和 y 坐标。

我正在寻找 ABC 公司的每家商店以及 XYZ 公司列表中其最接近的竞争对手。

在此处输入图片描述

到目前为止，我已经设法使用 H 列中的数组公式获取了每个 ABC 商店与其最近的 XYZ 公司之间的距离：

'{MIN((sqrt((Power(B3-$F$3:$F$6;2)+(power(C3-$G$3:$G$6;2))))))}

我很难为每个 ABCi 找到与 H 中的计算值相对应的名称 XYZi（或线参考）。

我已经花了两个小时摆弄 Match() 和 Index() – 正如 SE 上各种类似问题所建议的那样 – 并且在 Google 上搜索了所有我能想到的答案，但无济于事。

（我似乎记得，对于椭球体上的 XY 坐标，地图上两点之间的距离公式可能与这里使用的不同，但对于我的实际生活目的来说，它已经足够了。）

答案1

我认为数组公式可能只是让事情变得复杂。实际的数组可以显示所有距离，=SQRT((E$2-$B4)^2+(E$3-$C4)^2)然后：

=CHOOSE(MATCH(MIN(E4:H4),$E4:$H4,0),E$1,F$1,G$1,H$1)

在每种情况下选择最接近的一个：

SU608775 示例

或者为了突出显示每行的最小值，可以使用带有公式的条件格式=E4=MIN($E4:$H4)。

答案2

从@pnuts 解决方案开始，我们可以跳过数组表示。

获取网格上两个坐标和集合之间两点之间的最小距离（数组公式ctrl++ Shift）Enter：

'={MIN((sqrt((Power(B3-$F$3:$F$6;2)+(power(C3-$G$3:$G$6;2))))))}

为了跳过数组显示，我们使用距离计算（不带MIN）作为的第二个参数 MATCH()。它将返回数组内值的索引位置。

然后CHOOSE获取列表中第 k 个成员E3:E6，即最小值的来源：

{CHOOSE(MATCH(MIN((sqrt((B3-$F$3:$F$6)^2+((C3-$G$3:$G$6)^2))))),
(sqrt(((B3-$F$3:$F$6)^2+((C3-$G$3:$G$6)^2)))),0),$E$3;$E$4;$E$5:$E$6)}

由于它是一个数组公式，因此必须使用ctrl++进行验证ShiftEnter

在此处输入图片描述

剩下唯一要做的事情就是找到一种方法来解决CHOOSE此范围之上的列表公式中 255 个单独值的限制。

（获取椭球体上两个 XY 坐标之间距离的公式与此处使用的不同，但对于 2/3 纬度、经度的小区域来说，它足够接近。）

相关内容