我有一个评估工具的数据，该工具有大约 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)))
如果有人能帮我展示这个例子我将不胜感激:) 我甚至尝试使用截图工具。
答案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"。
