平均行总和而不在 Excel 中创建新列 [英] Average the sum of rows without a creating new column in Excel

问题描述

这是我的矩阵示例:

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

0   0   1   1   0
0   2           1

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

You can think of each row as a respondent and each column as an item on a questionnaire.

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

My goal is to take an average of the sum of each row (i.e. total score for each respondent) without creating a new column AND accounting for the fact that some or all of the entries in a given row are empty (e.g., some respondents
missed some items [see row 5] or didn't complete the questionnaire entirely [see row 3]).

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

The desired solution for this matrix = 1.67, whereby

[1+0+0+1+1 = 3] + [0+0+0+0+0 = 0] + [0+0+1+1+0 = 2]/3 == 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

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

As you can see, we have averaged over three values despite there being five rows because one has missing data.

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

I am already able to take an average of the sum of rows which are only summed for non-missing entries, e.g.,:

=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.

However, this results in a value of 1 because it treats any row with some or all values values as = 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

[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

是否有人对如何调整当前代码以平均非缺失值或实现所需结果的替代方法有任何想法?

Does anyone have any ideas about how to adapt the current code to average over non-missing values or an alternative way of achieving the desired result?

推荐答案

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

You can do this more concisely with an array formula, but the short answer to fix up your existing formula is, if you have a blank cell in your sheet somewhere (say it's F1) AVERAGE will ignore blank cells so change your formula to

=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 得到行的平均值.

This would be one array formula version of your formula - it uses OFFSET to pull out each row of the matrix then SUBTOTAL to see if every cell in that row has a number in it. Then it uses SUBTOTAL again to work out the sum of each row and AVERAGE to get the average of rows.

=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，因为它是易变的 - 您可以使用矩阵乘法代替，但它可能不太容易理解.

Note 1 - some people don't like using OFFSET because it is volatile - you can use matrix multiplication instead but it's arguably less easy to understand.

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

Note 2 - I used "" instead of referring to an empty cell. Interesting that the non-array formula needed an actual blank cell but the array formula needed an empty string.

你可以省略空字符串

=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)))))

这篇关于平均行总和而不在 Excel 中创建新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！