Формула среднего значения в Excel

Эта заметка продолжает цикл материалов по использованию формул массива. Если ранее вы не сталкивались с формулами массива, рекомендую прочитать:

Excel. Транспонирование строк в столбцы (или столбцов в строки) с помощью формулы массива

Excel. Суммирование по диапазону, содержащему ошибку, с помощью формулы массива

После этого вы, вероятно, сможете оценить возможности и достоинства формул массива, и вам захочется узнать о них побольше. Если вас не нужно агитировать, и вы полны желания разобраться в этом мощном оружии Excel, но не знаете, с чего начать, прочитайте Введение в формулы массива и Некоторые примеры использования формул массива

Основное достоинство формул массивов состоит в том, что они позволяют выполнять очень широкий круг вычислений, который другими способами выполнить нельзя. К сожалению, формулы массивов — это наиболее сложное и непонятное средство Excel.

Если вы уже постигли азы, предлагаю вам продолжить знакомство с формулами массива вместе с Джоном Уокенбахом и его книгой MS Excel 2007. Библия пользователя. — М.: Издательский дом «Вильямс», 2008. — 816 с.

Уокенбах. Excel2007. Библия пользователя. Обложка

Скачать заметку в формате Word, примеры в формате Excel

Вычисление среднего, не учитывающего нулевые значения

На рис. 1 показан рабочий лист, на котором вычисляется средний объем продаж группы продавцов. Формула в ячейке В14 имеет вид: =СРЗНАЧ(Продажи). Она вычисляет среднее значений из диапазона ВЗ:В10, которому присвоено имя Продажи. Некоторые продавцы не работали, но они также учитывались при вычислении среднего. Функция СРЗНАЧ игнорирует пустые ячейки, но учитывает ячейки с нулевыми значениями.

Следующая формула массива, записанная в ячейке В15, возвращает среднее без учета ячеек, содержащих 0: {=СРЗНАЧ(ЕСЛИ(Продажи>0;Продажи))}. Эта формула создает виртуальный массив, содержащий только ненулевые значения из диапазона Продажи. Этот массив используется в качестве аргумента в функции СРЗНАЧ.

Тот же результат можно получить с помощью обычной формулы (не формулы массива), записанной в ячейке В16: =СУММ(Продажи)/СЧЁТЕСЛИ(Продажи;»>0″). Эта формула использует функцию СЧЁТЕСЛИ для определения числа ненулевых значений в заданном диапазоне, на которое затем делится сумма значений этого диапазона.

Если диапазон может содержать отрицательные значения, и по-прежнему необходимо подсчитать среднее, не учитывающее нулевые значения, формулу массива нужно немного модифицировать: {=СРЗНАЧ(ЕСЛИ(Продажи<>0;Продажи))}