`AGGREGATE` 和 `INDEX` 函数中的数组

Question

场景:

所以，今天上类时，我的电脑运行了 MS Office 更新。然后，大约 30 分钟后，有人向我提出了一个看似简单的要求:不要使用 VLOOKUP。为了只返回第一个匹配项，他们需要 的分隔列表全部 火柴。 (例如“这个用户本周访问了哪些办公室”？)看起来很简单。

因此，使用一些垃圾数据(见下文)，我迅速编写了以下快速演示行:

=TEXTJOIN(", ", TRUE, IFERROR(INDEX(B$1:B$11, AGGREGATE(15, 6, ROW(A$1:A$11)/--(A$1:A$11=E1), ROW(A$1:A$11))),""))

按 Enter，然后... 6 .不太符合我的预期( 6, 35, 19, 56, 47)，所以我说“对不起”，回到单元格，然后按 Ctrl+Shift+Enter:
6 ， 再次。

所以，运行“评估公式”，结果是 ROW(A$1:A$11)在 AGGREGATE 的第 k 个位置(最后一个参数)正在返回 1而不是 {1,2,3,4,5,6,7,8,9,10,11} ，无论您使用的是普通公式还是数组公式。

所以，我手动输入数组:

=TEXTJOIN(", ", TRUE, IFERROR(INDEX(B$1:B$11, AGGREGATE(15, 6, ROW(A$1:A$11)/--(A$1:A$11=E1), {1,2,3,4,5,6,7,8,9,10,11})),""))

最终结果: 6 .我再次评估公式， AGGREGATE正在丢弃除我数组中的第一个值之外的所有内容。如果我放弃 INDEX ，然后运行 ​​ TEXTJOIN在 AGGREGATE结果，它工作正常。同样， =SUM(ROW(B1:B11))工作正常，但 =SUM(INDEX(B1:B11, ROW(B1:B11), 1))才不是。

问题:

现在，我是 95% 确定 我曾经能够得到这个 INDEX(.., AGGREGATE(..))和 SUM(INDEX(..))正常工作，而不是只返回第一个值 - 这是否改变了某些函数链如何与数组一起使用，或者我只是记错了？

(对于那些感兴趣的人，最后我们使用了数组公式 =TEXTJOIN(", ", TRUE, IF(A$1:A$11=E1, B$1:B$11, "")) 代替)

测试中使用的垃圾数据:

A |  6
A | 35
A | 19
B | 33
B | 46
B | 72
A | 56
C | 41
C | 20
B | 52
A | 47

Answer 1

尽管您发现添加 IF 而不是长的 INDEX/AGGREGATE 是可行的。

它不起作用的原因是 INDEX 通常没有设置为将数组作为行号。

但是我们可以强制它使用 N(IF({1},...))

=TEXTJOIN(", ", TRUE, IFERROR(INDEX(B$1:B$11, AGGREGATE(15, 6, ROW(A$1:A$11)/--(A$1:A$11=E1), N(IF({1},ROW(A$1:A$11))))),""))

这是一个数组公式，退出编辑模式时需要使用 Ctrl-Shift-Enter 确认，而不是 Enter。



但是，当一个简单的 IF()会做需要做的事。