excel - 平均行总和而不在 Excel 中创建新列

Question

这是我的矩阵示例:

A   B   C   D   E
1   0   0   1   1
0   0   0   0   0

0   0   1   1   0
0   2           1

您可以将每一行视为一个受访者，将每一列视为问卷中的一个项目。

我的目标是取每行总和的平均值(即每个受访者的总分) 没有创建新列并且考虑到给定行中的部分或全部条目为空的事实 (例如，一些受访者
遗漏了一些项目 [见第 5 行] 或没有完全完成问卷 [见第 3 行])。

该矩阵的所需解 = 1.67，其中

[1+0+0+1+1 = 3] + [0+0+0+0+0 = 0] + [0+0+1+1+0 = 2]/3 == 5/3 = 1.67

如您所见，尽管有五行，但我们已经平均了三个值，因为其中一行缺少数据。

我已经能够对仅针对非缺失条目求和的行的总和进行平均，例如:

=AVERAGE(IF(AND(A1<>"",B1<>"",C1<>"",D1<>"",E1<>""),SUM(A1:E1)),IF(AND(A2<>"",B2<>"",C2<>"",D2<>"",E2<>""),SUM(A2:E2)),IF(AND(A3<>"",B3<>"",C3<>"",D3<>"",E3<>""),SUM(A3:E3)),IF(AND(A4<>"",B4<>"",C4<>"",D4<>"",E4<>""),SUM(A4:E4)),IF(AND(A5<>"",B5<>"",C5<>"",D5<>"",E5<>""),SUM(A5:E5)))

但是，这会导致值为 1，因为它将具有部分或全部值的任何行视为 = 0。

它执行以下操作:

[1+0+0+1+1 = 3] + [0+0+0+0+0 = 0] + [0+0+0+0+0 = 0] + [0+0+1+1 +0 = 2] + [0+0+0+0+0 = 0]/4 == 5/5 = 1

有没有人对如何调整当前代码以平均非缺失值或实现预期结果的替代方法有任何想法？

Answer 1

您可以使用数组公式更简洁地执行此操作，但修复现有公式的简短答案是，如果您的工作表中某处有一个空白单元格(比如 F1)，AVERAGE 将忽略空白单元格，因此将您的公式更改为

=AVERAGE(IF(AND(A1<>"",B1<>"",C1<>"",D1<>"",E1<>""),SUM(A1:E1),F1),IF(AND(A2<>"",B2<>"",C2<>"",D2<>"",E2<>""),SUM(A2:E2),F1),IF(AND(A3<>"",B3<>"",C3<>"",D3<>"",E3<>""),SUM(A3:E3),F1),IF(AND(A4<>"",B4<>"",C4<>"",D4<>"",E4<>""),SUM(A4:E4),F1),IF(AND(A5<>"",B5<>"",C5<>"",D5<>"",E5<>""),SUM(A5:E5),F1))

这将是您的公式的一个数组公式版本 - 它使用 OFFSET 拉出矩阵的每一行，然后 SUBTOTAL 来查看该行中的每个单元格中是否都有一个数字。然后它再次使用 SUBTOTAL 来计算每行的总和，并使用 AVERAGE 来获得行的平均值。

=AVERAGE(IF(SUBTOTAL(2,OFFSET(A1,ROW(A1:A5)-ROW(A1),0,1,COLUMNS(A1:E1)))=COLUMNS(A1:E1),SUBTOTAL(9,OFFSET(A1,ROW(A1:A5)-ROW(A1),0,1,COLUMNS(A1:E1))),""))

必须使用 CtrlShiftEnter 作为数组公式输入

注 1 - 有些人不喜欢使用 OFFSET，因为它是易变的 - 您可以使用矩阵乘法，但可以说它不太容易理解。

注 2 - 我使用“”而不是指空单元格。有趣的是，非数组公式需要一个实际的空白单元格，但数组公式需要一个空字符串。

您可以省略空字符串

=AVERAGE(IF(SUBTOTAL(2,OFFSET(A1,ROW(A1:A5)-ROW(A1),0,1,COLUMNS(A1:E1)))=COLUMNS(A1:E1),SUBTOTAL(9,OFFSET(A1,ROW(A1:A5)-ROW(A1),0,1,COLUMNS(A1:E1)))))