计算行总和的平均值，无需在 Excel 中创建新列

这是我的矩阵的示例：

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

有谁知道如何调整当前代码以平均非缺失值或实现所需结果的替代方法？

您可以使用数组公式更简洁地完成此操作，但修复现有公式的简短答案是，如果您的工作表中某处有一个空白单元格（假设是 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))),""))

Has to be entered as an array formula using 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)))))