Усредните числовые значения, когда столбец идентификатора соответствует условию в Excel

Question

В Excel 2007 у меня есть таблица с номерами в столбцах F, G, H, I и J. В столбце B есть идентификатор.

Мне нужно среднее значение всех чисел в столбцах F, G, H, I и J, где идентификатор в столбце B = A14 (т. Е. Значение в A14).

Я попробовал следующее:

=AVERAGEIFS('Hours-Backup'!F3:J1048576,'Hours-Backup'!B:B,'Group Dashboard'!A14)

Это приводит к # #VALUE! ошибка.

В конечном итоге я хочу сделать так, чтобы функция игнорировала все нечисловые или пустые ячейки. Другими словами, я не хочу предполагать, что пустые ячейки равны 0 для усреднения.

Я не могу понять, в чем проблема.

Answer 1

Две проблемы.  (Или одна проблема с двумя головами.)  AVERAGEIFS говорит:

• В отличие от аргументов диапазона и критериев в функции AVERAGEIF, в AVERAGEIFS каждый критерий_рассылки должен иметь тот же размер и форму, что и сумма_диапазона.

Ну, это опечатка: «sum_range» должно быть «medium_range».  Но, в любом случае, это ваша проблема: ваш диапазон критериев равен 1 × ∞ (весь столбец), а ваш средний диапазон равен 5 × 1048574.  Таким образом, вы можете решить одну из проблем, изменив B:B на B3:B1048576 , сделав диапазон 1 × 1048574.

Вы можете решить вторую проблему, реплицируя столбец B в столбцы C , D , E и F , а затем используя B3:F1048576 качестве вашего критерия_предела.  Но это безумие.  Разумный способ - определить вспомогательный столбец (скажем, V) как среднее значение столбцов F J в текущей строке:

• Установите V3 в AVERAGE(F3:J3) ,
• и перетащите вниз.

Затем получите свое условное среднее, усредняя средние:

=AVERAGEIFS('Hours-Backup'!V3:V1048576,'Hours-Backup'!B3:B1048576,'Group Dashboard'!A14)
(changes)                  ^  ^                        ^  ^^^^^^^

Answer 2

Мой подход к подобным ситуациям - пара вспомогательных столбцов и несколько формул массива. Надеюсь, я настроил этот лист игрушек так же, как ваш:

Здесь я установил A16 в качестве ячейки, чтобы установить, какие отфильтрованные строки вы хотите сохранить. B - столбец значений для фильтрации, C G - столбцы со значениями, которые могут отсутствовать. Я считаю количество значений, присутствующих в каждой строке в столбце H , и суммирую значения, присутствующие в столбце I Ячейки H2 и I2 суммируют соответствующие столбцы «Count» и «Sum», а затем полученное среднее значение вычисляется в K2 . Обратите внимание, что формулы в столбцах H и I хорошо подходят как для текста, так и для значений ошибок в таблице.

Я не уверен в совместимости версий формул SUM(IF(...)) в формулах массива. Он явно работает в Excel 2010 и, вероятно, должен также работать в Excel 2007 и Excel 2013.