Мне нужно рассчитать среднее значение для нескольких ячеек, и я хочу исключить значения = 0.

Ячейки не последовательные, поэтому я не могу использовать AVERAGEIF(A1:A10, "> 0")

Однако мне нужно отдельно выбрать ячейки, я хочу скопировать формулу по нескольким столбцам, поэтому не хочу исключать какие-либо (вручную)

Как мне это сделать?

Пример данных можно увидеть здесь:

Я хочу рассчитать среднее значение по всем строкам для уровня 1, уровня 2 и CBD (исключая 0)

3 ответа3

5

Используйте AVERAGEIFS, который позволяет вам выбрать критерии для усреднения:

= AVERAGEIFS(A3:Z3, A $ 2:Z $ 2, "= Уровень 1", A3:Z3, "> 0")

Затем, при указании диапазона для критериев, убедитесь, что вы заблокировали номер строки. (A $ 2:Z $ 2, а не просто указание диапазона как A2:Z2.) В противном случае, когда вы распространите значение для последующих средних значений строки, Excel попытается (правильно) заполнить его как A3:Z3 и выбросит ошибку DIV/0.

2

В любой версии. Вы можете использовать этот тип формулы для СРЕДНЕГО несмежных ячеек без нулей (принимает только положительные значения)

=SUM(A2,F3,Z5)/MAX(1,INDEX(FREQUENCY((A2,F3,Z5),0),2))

где A2, F3 и Z5 - клетки для усреднения

Формула возвращает ноль, если нет ненулевых значений

Вы можете назвать свой диапазон несмежных клеток, если хотите, а затем использовать

=SUM(Range1)/MAX(1,INDEX(FREQUENCY(Range1,0),2))

1

Это выглядит ужасно, а также требует Excel 2007 или выше:

=SUM(A2,D2,G2,J2,M2,P2,S2,V2)/COUNTIFS(A2,"<>0",D2,"<>0",G2,"<>0",J2,"<>0",M2,"<>0",P2,"<>0",S2,"<>0",V2,"<>0")

но он будет усреднять выбранные ячейки (для SUM не нужны никакие критерии, так как добавление нуля или нет не влияет на общее количество)

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .