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

Question

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

15

如果范围 A1:A4 中的数据如下：

Apple
Banana
Orange
Strawberry

那么INDEX可以被用来单独返回列表中的任何一个值，例如：

= INDEX(A1:A4,3)

会返回橙色。

是否存在类似的Excel函数或函数组合，可以有效地让您执行类似这样的操作：

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

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

这是否可能（最好不使用VBA），如果可以，如何实现？即使使用辅助单元格，我仍然很难弄清楚如何完成。

如果数据是数字（使用MMULT），我可以想出一个相对复杂的解决方案，但数据是文本，这使我感到困扰，因为MMULT无法与文本一起使用。

- ImaginaryHuman072889

2

简短回答，不。 - Scott Craner

@ScottCraner的长答案是，“是的，需要使用VBA吗？” - ImaginaryHuman072889

1

这很可能是正确的，但如果您能更详细地解释您的起点和终点，我们也许可以找到另一种选择。 - Scott Craner

2

那是因为我误解了你的要求。我以为你想能够选择列表的索引并返回一个可以在公式中使用的数组。如果我知道你想要连续的单元格，那么我会给出那个公式。但是，按照我的理解，你想要有选择地挑选，例如=INDEX(A:A,{1,3})并返回{"Apple","Orange"}，这是不可能的。是的，你可以使用INDEX来设置范围的开始和结束，我经常在回答问题时使用它。但是，这不是你所要求的。这就是为什么我试图从你那里获取更多信息的原因。 - Scott Craner

2

@ScottCraner：如果你像我在答案的第二部分中所做的那样引用它，那么使用INDEX返回非连续单元格是可能的。尽管如此，我可能不会使用它，因为OFFSET更方便。 - jeffreyweir

显示剩余5条评论

4个回答

3

你可以不使用数组公式来实现这种行为。例如，在单元格D1中。

=IFERROR(INDEX($A$1:$A$4,CHOOSE(ROWS($1:1),2,3)),"")

并复制下来。请注意，2,3 被嵌在 CHOOSE() 函数中。

您可以用任何一组索引替换 2,3。

- Gary's Student

0

正确

更新3

您应该使用数组公式：

对于= INDEX(A1:A4,{2;3})，请写成= INDEX(A1:A4,ROW($A$2:$A$3))

要使用数组公式，(1) 选择您想要返回结果的单元格范围，垂直地（例如B1:B2），然后按下(2) F2并输入上述公式，然后(3) 按下 Ctrl + Shift + Enter。

更新

解释：

您可以使用数组公式控制INDEX的row_num和column_num部分，以返回更特定的所需结果。

有两种方法返回数组公式结果：

(I)
1. 选择您想要显示返回结果的范围。
2. 按下 F2
3. 键入您的数组公式。
4. 然后按下 Ctrl + Shift + Enter。

示例：

=INDEX($A$1:$A$4,SMALL(IF($B$2=$A$1:$A$4,ROW($A$1:$A$4)-ROW($A$1)+1),ROW($A:$A)))

(II)
1. 在您想要显示返回结果的第一个单元格中输入数组形式公式（然后按下Ctrl + Shift + Enter），然后扩展公式。

示例：

=INDEX($A$1:$A$4,SMALL(IF($B$2=$A$1:$A$4,ROW($A$1:$A$4)-ROW($A$1)+1),ROW(A1)))

结论

INDEX公式可以以数组形式工作。

您需要在数组中输入row_num或column_num。为此，请使用适当的数组公式，如：IF、SMALL、CHOOSE。请注意，MATCH不是数组形式公式。

临时样本文件（30天有效期）：book.xlsx

- mgae2m

我已经更新了答案，并提供了更好的细节纠正。请审查我的答案并评估其价值和可接受性。 - mgae2m

下投票将保留。你的回答第一部分完全没有回答我的问题。更糟糕的是，这个公式甚至都不起作用。你的公式= INDEX(A1:A4,ROW($A$2:$A$3))只返回A2中的值，如果它被输入为一个数组公式，并且问题的重点是使用一个独立的索引而不是简单地取一个单元格范围的ROW。你的公式是多余的，因为= INDEX(A1:A4,ROW($A$2:$A$3))基本上是写公式= $A$2:$A$3的复杂方式。此外，你的SMALL函数缺少参数。 - ImaginaryHuman072889

再次强调，您的公式= INDEX(A1:A4,ROW($A$2:$A$3))仅返回Banana而不是{Banana;Orange}，即使作为数组公式输入也是如此。SMALL函数需要两个参数，而您的公式只包含一个参数。 - ImaginaryHuman072889

1

@ImaginaryHuman072889，我给你插入了一个样本文件和图片。你是对的，我错了。我的公式（包含SMALL函数）有误。在给你发送样本文件和图片之前，我已经进行了更正和测试。希望我的回答能够满足并得到你的接受、投票，并解决你的问题。如果还有任何问题，请随时提出。请原谅我最初的错误回答。 - mgae2m

@imaginaryHuman072889，如果我的答案正确并解决了问题，请点赞或接受。谢谢。 - mgae2m

显示剩余5条评论

0

由于超级英雄XOR发现的基于“去引用”的晦涩技术（如@jeffreyweir所提到的），我已经成功地使VLOOKUP()在CSE数组公式中工作（可以说INDEX(MATCH())或LOOKUP()也可以做到这一点，是的...）。

在下面的3个示例中，目标始终是对列$B:$B（2）中通过VLOOKUP()函数检索和返回的值求和。

示例1：

仅在列$A:$A中查找文本值。

=SOMME(RECHERCHEV(T(SI({1};$A$1:$A$3));$A$1:$B$3;2;0))
=SUM(VLOOKUP(T(IF({1},$A$1:$A$3)),$A$1:$B$3;2;0))

示例2：

仅在列$A:$A中查找数值。

=SOMME(RECHERCHEV(N(SI({1};$A$1:$A$3));$A$1:$B$3;2;0))
=SUM(VLOOKUP(N(IF({1},$A$1:$A$3)),$A$1:$B$3,2;0))

例子 3：

在列 $A:$A 中查找任何类型的值（数字/文本）

=SOMME(CNUM(RECHERCHEV(T(SI({1};TEXTE($A$1:$A$3;"@")));TEXTE($A$1:$B$3;"@");2;0)))
=SUM(VALUE(VLOOKUP(T(IF({1},TEXT($A$1:$A$3,"@"))),TEXT($A$1:$B$3,"@"),2,0)))

注意1：虽然使用INDEX(MATCH())解决方案时不需要数组常量{1}（标量1就可以了（或者True等等），但是对于这个VLOOKUP()解决方案，向量形式似乎是必需的。

注意2：据我所知，N()和T()是唯一允许“取消引用”范围并将其转换为类似VBA的字符串/数值数组的两个函数。这可能与这种奇怪的行为有关：

确实，像任何CSE数组公式一样，预期结果应该分别为{A,B,C}和{1,2,3}...

- hymced

1

如果您使用支持新功能“动态数组公式”的Office版本，则不再需要创建CSE公式，Excel现在会自动检测数组输入。因此，正常输入的公式=SUM(VLOOKUP($A$1:$A$3,$A$1:$B$3;2;0))返回6，而{=SUM(VLOOKUP($A$1:$A$3,$A$1:$B$3;2;0))}错误地返回1。 - hymced

你提到了一个 INDEX(MATCH()) 的解决方案，但只展示了 VLOOKUP - 我无法使用 INDEX(MATCH()) 让它工作。你有一个可行的例子吗？ - Alex M

当然，这里有一个：{=SUM((INDEX($D$1:$D$2;N(IF({1};MATCH($A$1:$A$5;$C$1:$C$2;0))))*($A$1:$A$5=F1)))}。正如我在之前的评论中所说，现在你甚至不需要将公式验证为数组公式，它也可以作为普通公式工作。https://i.stack.imgur.com/B02WC.png - hymced

假设您在A列中有某些数据，每个数据元素都有相应的值，在蓝色表格中给出。您可以立即对符合特定条件的所有元素的相应值求和，这里是第1行中的“A”元素，然后是第2行中的“B”元素： - hymced

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- jeffreyweir · Accepted Answer

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，因为OFFSET是不稳定的，意味着它会在任何时候重新计算，并强制其下游的任何公式也这样做。更多信息请参见我的文章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。该Elements区域告诉A8:B8中的公式应显示Data区域中的这五个数字中的哪些。

如果您查看A8:B8中的公式，您会发现它是一个数组输入的INDEX函数：{=INDEX(Data,Elements)}。该公式表示：“根据用户在元素范围内选择的任何元素，转到数据范围并从中获取元素。”

在这种特定情况下，用户请求其中的第五个和第二个项目。确实，这就是INDEX获取到A8:B8单元格中对应值的方式：50和20。

但是，请注意，如果您将完好无损的INDEX函数放入SUM中，如A11所示，会出现错误的结果：50 + 20不等于50。Excel中的20去哪了？

由于某种原因，虽然`=INDEX(Data,Elements)`很高兴地从某个地方获取不同的元素，然后将这些数字分别返回到一个范围中，但如果您要求它将这些数字提供给另一个函数，它却相当不愿意遵从。事实上，它非常不愿意，以至于它仅将第一个元素传递给函数。

因此，如果想要对其进行其他操作，似乎只能首先将`=INDEX(Data,Elements)`的结果返回到网格中。这很繁琐。但几乎每个Excel专业人士都会告诉您，没有解决方法...那就是这样，您别无选择。

Buuuuuuuut，他们错了。在文章http://excelxor.com/2014/09/05/index-returning-an-array-of-values/中，神秘的公式超级英雄XOR概述了两种相当简单的方法来“解除”索引，以便您可以直接在其他公式中使用其结果；其中一种方法如上面的A18所示。事实证明，如果您稍微修改INDEX函数，通过添加额外的位来包含那些Elements参数，INDEX就会获得成功。你需要做的就是像我下面所做的那样将那些Elements参数包含起来：

N(IF({1},Elements))

有了这个想法，你最初不正确的公式：

=SUM(INDEX(Data,Elements))

…变成了这个复杂但举止优雅的宝贝：

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