Excel 数组：平均值以及是否组合多个条件

Question

我有一个评估工具的数据，该工具有大约 20 个类别和大约 200 个项目，得分为 0-3。该工具的前半部分是必选的，其他部分是可选的，因此有很多空白区域。4 个项目有 N/A 选项。

我目前使用数组来收集平均如果每个类别的多个列上的数据。我在另一个论坛上得到了帮助，下面也包括了公式。我使用了两个不同的公式，正如在一些数据范围内提到的那样，所有项目单元格都需要分数，而对于某些部分，它是可选的或根本不得分。在下面的示例网格中，我需要找到所有数据列组合的平均值（第一季度至第四季度) 基于一个评估类型乙和否. 注意：有些分数给出n/a选项

T   Q1  Q2  Q3  Q4
N   0   0   3   2
B   1   2   3   
N   1   1   0   0
N       
D   0   n/a 2   3
O1  0   1   1   0
O1  2   0   0   0
O2  2   n/a 0   0

我在这里粘贴的网格似乎显示不正确，我无法粘贴它的图像。下面它根本不像我的网格。因此，假设在列中A我有不同的评估类型代码。乙，德，否，O1，氧气. 在列中是我给每个问题都打了分。有些单元格一整行都是空白的，C 列中有几行标记为 N/A。所以第二行可能是： 否 0 1 3 0；第二行可能是B 0 不适用 2 2.第三排可能是乙（空白单元格）

当前使用一个标准的公式：

=AVERAGE(IF(ANSAt!H:H="B",ANSAt!J:AC))

并且不要在有大量空白的部分包含空白

=AVERAGE(IF(ANSAt!$H:$H="B", IF(XMod!G:Y<>"", XMod!G:Y)))

如果有人能帮我展示这个例子我将不胜感激:) 我甚至尝试使用截图工具。

Answer 1

如果您可以按“T”列排序，那么您可以使用以下命令：

=AGGREGATE(1,6,INDEX($B$2:$E$9,MATCH(H2,$A$2:$A$9,0),1):INDEX($B$2:$E$9,MATCH(H2,$A$2:$A$9,0)+COUNTIF($A$2:$A$9,H2)-1,4))

编辑：

当您尝试理解上述内容时，我想我会为您再添加一条。此公式不关心是否已排序：

=SUM(IF(ISERROR(INDEX(($A$2:$A$9=H2)*$B$2:$E$9,)),"",INDEX(($A$2:$A$9=H2)*$B$2:$E$9,)))/SUMPRODUCT(--(INDEX(($A$2:$A$9=H2)*ISNUMBER($B$2:$E$9),)))

这是一个数组公式。离开编辑模式时，需要使用 Ctrl-Shift-Enter 进行确认。如果操作正确，Excel 会将{}公式放在一边。

另外，如果您想用查找值对这些公式中的任何一个进行硬编码，请直接用所需的字符串替换 H2 引用；"N"。