Мне нужно рассчитать среднее значение для нескольких ячеек, и я хочу исключить значения = 0.
Ячейки не последовательные, поэтому я не могу использовать AVERAGEIF(A1:A10, "> 0")
Однако мне нужно отдельно выбрать ячейки, я хочу скопировать формулу по нескольким столбцам, поэтому не хочу исключать какие-либо (вручную)
Как мне это сделать?
Пример данных можно увидеть здесь:
Я хочу рассчитать среднее значение по всем строкам для уровня 1, уровня 2 и CBD (исключая 0)
Используйте AVERAGEIFS, который позволяет вам выбрать критерии для усреднения:
= AVERAGEIFS(A3:Z3, A $ 2:Z $ 2, "= Уровень 1", A3:Z3, "> 0")
Затем, при указании диапазона для критериев, убедитесь, что вы заблокировали номер строки. (A $ 2:Z $ 2, а не просто указание диапазона как A2:Z2.) В противном случае, когда вы распространите значение для последующих средних значений строки, Excel попытается (правильно) заполнить его как A3:Z3 и выбросит ошибку DIV/0.
В любой версии. Вы можете использовать этот тип формулы для СРЕДНЕГО несмежных ячеек без нулей (принимает только положительные значения)
=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))
Это выглядит ужасно, а также требует 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 не нужны никакие критерии, так как добавление нуля или нет не влияет на общее количество)