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

问题描述

这是我的矩阵的一个示例:

Here's a sample of my matrix:

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

如您所见，尽管有五行，但由于其中一个缺少数据，我们仍对三个值进行了平均.

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

但是，由于它会将具有部分或全部值的任何行都视为= 0的值，因此得出的值为1.

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))),""))

必须使用 Ctrl Shift Enter

注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屋！