arrays - Excel的INDEX函数可以返回数组吗？

Question

如果A1:A4范围内的数据如下:

Apple
Banana
Orange
Strawberry

然后 INDEX 可用于单独返回该列表中的任何值，例如

= INDEX(A1:A4,3)

将返回橙色。

是否有类似的 Excel 函数或函数组合可以有效地允许您执行以下操作:

= INDEX(A1:A4,{2;3})

哪个会返回一个数组{Banana;Orange}？

这可能吗(最好不用 VBA)，如果可以，如何实现？即使使用辅助细胞，我也很难弄清楚如何实现这一目标。

如果数据是数字(使用 MMULT)，我可以想出一个有点复杂的解决方案，但数据是文本的事实让我困惑，因为 MMULT 确实不适用于文本。

Answer 1

OFFSET 可能是您想要的功能。

=OFFSET(A1:A4,1,,2)

但是回答你的问题，INDEX确实可以用来返回数组。或者更确切地说，两个 INDEX 函数之间有一个冒号:

=INDEX(A1:A4,2):INDEX(A1:A4,3)

这是因为 INDEX 实际上返回一个单元格引用或一个数字，而 Excel 根据您所询问的上下文来确定您需要其中的哪一个。如果您在两个 INDEX 函数中间放置一个冒号，Excel 会显示“嘿，冒号...通常其中一个函数的每一侧都有一个单元格引用”，因此会将 INDEX 解释为这样。您可以在 http://www.excelhero.com/blog/2011/03/the-imposing-index.html 阅读更多相关内容。

实际上，我更喜欢 INDEX 而不是 OFFSET，因为 OF​​FSET 是不稳定的，这意味着它会随时重新计算，然后强制其下游的任何公式执行相同的操作。有关这方面的更多信息，请阅读我的帖子 https://chandoo.org/wp/2014/03/03/handle-volatile-functions-like-they-are-dynamite/

实际上，您可以仅使用一个 INDEX 并返回一个数组，但它很复杂，并且需要称为取消引用的东西。以下是我正在为此撰写的一本书中的一些内容:

此屏幕截图中的工作表有一个名为 Data 的命名范围，分配给顶部的范围 A2:E2。该范围包含数字 10、20、30、40 和 50。它还有一个名为 Elements 的命名范围，分配给范围 A5:B5。该元素范围告诉 A8:B8 中的公式要显示数据范围中的五个数字中的哪一个。

如果您查看 A8:B8 中的公式，您会发现它是一个数组输入的 INDEX 函数:{=INDEX(Data,Elements)}。该公式表示:“转到数据范围并根据用户在元素范围中选择的任何元素从中获取元素。”在这种特殊情况下，用户已向其请求第五项和第二项。果然，这正是 INDEX 提取到单元格 A8:B8 中的内容:50 和 20 的对应值。

但是看看如果您使用完美的 INDEX 函数并尝试在它周围放置一个 SUM 会发生什么，如 A11 所示。您得到的结果不正确:50+20 不等于 50。20 发生了什么，Excel？

出于某种原因，虽然 =INDEX(Data,Elements) 会很乐意从某处获取不同的元素，然后将这些数字分别返回到一个范围，但如果您要求它，它会相当不愿意遵守而是将这些数字交给另一个函数。事实上，它非常不情愿，以至于只将第一个元素传递给函数。

因此，如果您想用它做其他事情，您似乎被迫首先将 =INDEX(Data,Elements) 函数的结果返回到网格。乏味。但几乎每个 Excel 专业人士都会简单地告诉您没有解决方法...事情就是这样，您别无选择。

但是，他们错了。在帖子http://excelxor.com/2014/09/05/index-returning-an-array-of-values/神秘公式 super 英雄 XOR 概述了两种相当简单的方法来“取消引用”INDEX，以便您可以直接在其他公式中使用其结果；其中一种方法如上面的 A18 所示。事实证明，如果您通过添加额外的位来封装 Elements 参数来稍微修改 INDEX 函数，INDEX 就会发挥作用。您需要做的就是将 Elements 参数括起来，就像我在下面所做的那样:

N(IF({1},元素))

考虑到这一点，你最初的不当行为公式:

=SUM(INDEX(Data,Elements))

...成为这个复杂但有礼貌的宠儿:

=SUM(INDEX(Data, N(IF({1},Elements))))