Массивы Excel: средние и при комбинировании нескольких критериев

Question

У меня есть данные для инструмента оценки, который имеет около 20 категорий и около 200 пунктов, набранных 0-3. Первая половина инструмента является обязательной, другие разделы являются необязательными, поэтому в них много пустых областей. 4 элемента имеют опцию N/A.

В настоящее время я использую массивы для сбора среднего значения данных по нескольким столбцам для каждой категории. Я получил помощь на другом форуме и включаю формулы ниже. Я использую два разных, как уже упоминалось в некоторых диапазонах данных, все ячейки элементов требуют оценки, а для некоторых разделов это необязательно или вообще не оценивается. В приведенной ниже примерной таблице мне нужно найти среднее значение для всех столбцов данных вместе (Q1-Q4) на основе одного типа оценки B и N. Примечание: некоторые оценки предоставлены для н / п

T   Q1  Q2  Q3  Q4
N   0   0   3   2
B   1   2   3   
N   1   1   0   0
N       
D   0   n/a 2   3
O1  0   1   1   0
O1  2   0   0   0
O2  2   n/a 0   0

Сетка, которую я вставил сюда, кажется, не отображается правильно, и я не могу вставить ее изображение. Ниже он совсем не похож на мою сетку. Итак, допустим, что в столбце А у меня есть разные коды для типа оценки. Б, Д, Н, О1, О2. В колонках BE у меня есть баллы по каждому вопросу. Некоторые ячейки не заполнены для одной целой строки, пара строк в столбце C помечена как N/A. Таким образом, вторая строка может быть: N 0 1 3 0 ; вторая строка может быть B 0 N/a 2 2. Строка три может быть B (пустые ячейки)

Текущие формулы для использования одного критерия:

=AVERAGE(IF(ANSAt!H:H="B",ANSAt!J:AC))

и не включать пробелы в разделы, которые имеют много пробелов

=AVERAGE(IF(ANSAt!$H:$H="B", IF(XMod!G:Y<>"", XMod!G:Y)))

Если кто-то может помочь мне показать пример, я был бы признателен :) Я даже пытался использовать инструмент Snip.

Answer 1

Если вы можете сортировать по столбцу "T", то вы можете использовать это:

=AGGREGATE(1,6,INDEX($B$2:$E$9,MATCH(H2,$A$2:$A$9,0),1):INDEX($B$2:$E$9,MATCH(H2,$A$2:$A$9,0)+COUNTIF($A$2:$A$9,H2)-1,4))

РЕДАКТИРОВАТЬ:

Пока вы пытаетесь понять вышесказанное, я подумал, что добавлю еще один для вас. Эта формула не заботится, отсортирована она или нет:

=SUM(IF(ISERROR(INDEX(($A$2:$A$9=H2)*$B$2:$E$9,)),"",INDEX(($A$2:$A$9=H2)*$B$2:$E$9,)))/SUMPRODUCT(--(INDEX(($A$2:$A$9=H2)*ISNUMBER($B$2:$E$9),)))

Это формула массива. Это необходимо подтвердить с помощью Ctrl-Shift-Enter при выходе из режима редактирования. Если все сделано правильно, Excel поместит {} вокруг формулы.

Кроме того, если вы хотите жестко закодировать любую из этих формул со значением поиска, непосредственно замените ссылку H2 на желаемую строку; "N" .