Усреднить сумму строк без создания нового столбца в 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)))))

Answer 2

По сути, то, что вы описываете здесь для достижения желаемого результата, это =AVERAGEA()функция

_{Функция Microsoft Excel AVERAGEA возвращает среднее (среднее арифметическое) предоставленных чисел.Функция AVERAGEA отличается от функции AVERAGE тем, что она рассматривает TRUE как значение 1 и FALSE как значение 0.}

Имея это в виду, формула должна выглядеть следующим образом.

=SUM(AVERAGEA(A1:A4),AVERAGEA(B1:B4),AVERAGE(C1:C4),AVERAGEA(D1:D4),AVERAGEA(E1:E4))

Дает ожидаемый результат:

---

Обратите внимание, если вы хотите ROUND() результат до двух цифр, добавьте к нему следующую формулу:

=ROUND(SUM(AVERAGEA(A1:A4),AVERAGEA(B1:B4),AVERAGE(C1:C4),AVERAGEA(D1:D4),AVERAGEA(E1:E4)), 2)