arrays - EXCEL：使用 INDEX/MATCH 创建具有多个结果的数组公式

Question

我的目标是将包含不同项目、员工和每月工作时间的大型 Excel 表转换为每位员工的概览。应该可以显示员工参与的项目以及他每个月每个项目工作的小时数。原始工作表如下所示：

通过 INDEX/MATCH 函数过滤，我设法找到了 Person A 从事的项目。我将公式应用于列出员工并收到多个项目结果的整行。我的问题是如何将公式转换为更有效的公式，以将所有匹配的结果（项目）复制到一列中（参见1）。

这是我到目前为止所拥有的，如果与某个区域的员工姓名匹配；输出是他参与的项目的第一场比赛：

=INDEX(B2:J3;1;MATCH("Person A";Sheet1!B3:E3;0))

如何将其复制到底部单元格以复制所有匹配的结果？用这个创建一个数组公式有帮助吗？

标签： arraysexcelexcel-formula

Answer 1

您可以在单元格 B9 中使用以下公式：

=IFERROR(INDEX($2:$2,SMALL(IF($3:$3=$B$8,COLUMN($3:$3)-COLUMN(INDEX($3:$3,1,1))+1),ROWS(A$1:A1))),"")

它对第 2 行进行索引，并在第 3 行中查找与 B8 (=Person A) 中的值相等的第一个匹配项的列号。向下拖动时，它会寻找第二个匹配项ROWS(A$1:A1)将变为ROWS(A$1:A2)= 2。

对于人 B，您可以在单元格 B14 中使用此公式：

=IFERROR(INDEX($2:$2,SMALL(IF($3:$3=$B$13,COLUMN($3:$3)-COLUMN(INDEX($3:$3,1,1))+1),ROWS(A$1:A1))),"")

我希望这就是你要找的东西。

PS 如果您将以下公式粘贴到单元格 C9 中，您将获得 2019 年 10 月项目 XY ​​中人员 A 的总和结果： =IF(OR($B9="",C$8=""),"",SUMPRODUCT(($B$2:$K$2=$B9)*($B$3:$K$3=$B$8)*($A$4:$A$6=C$8),B4:K6))

注意：前提是单元格 C8 中的值等于单元格 A4 中的值。