как посчитать среднее значение без учета нулей
Среднее значение ненулевых ячеек
Здравствуйте, подскажите пожалуйста, есть ряд числовых ячеек например их количество 40 в некоторых есть значения, а некоторые нулевые. Требуется сложить ячейки не нулевые и разделить на их количество, причем количество не нулевых ячеек постоянно разное. Огромное спасибо Вашему ресурсу узнал много нового.
Как я понимаю, речь идет о вычислении среднего значения по столбцу или по строке при условии, что ячейки содержащие нулевые значения не учитываются и их количество может изменяться. Классическое среднее значение равно сумма значений деленное на количество значений. С суммой все ясно, нулевые значения не влияют на сумму, а вот количество ненулевых значений будет постоянно изменяться. Чтобы их подсчитывать необходимо применить функцию СЧЁТЕСЛИ, которая позволяет подсчитать количество значений по заданному критерию в заданном диапазоне.
Если в заданном диапазоне числовые значения могут принимать положительные и отрицательные значения, то критерий отбора необходимо изменить с «Больше нуля» на «Неравно нулю» и формула будет выглядеть так.
А теперь попробуем еще усложнить задачу. Допустим нам надо найти среднее значение по диапазону с условием, что суммируются значения больше «19». В этом случае критерий отбора влияет не только на количество значений но и на сумму. Поэтому вместо функции СУММ() мы применим функцию СУММЕСЛИ() и формула будет выглядеть так.
Возможны и другие варианты задания условий отбора, которые можно реализовать, используя эту формулу.
Среднее значение без учёта нулевых в Эксель
Здравствуйте. В статье о расчете среднего значения, я рассказал о работе функции СРЗНАЧ. Там же я уточнил, что функция игнорирует пустые и текстовые ячейки, а ноль – участвует в вычислении. Чем это чревато?
Посмотрите пример на картинке выше. Здесь ежедневная выработка служащих производственного цеха за 15 дней. В последних двух столбцах вычисляем общее количество произведенного товара, и среднедневное. Легко заметить, что каждый сотрудник после четырёх смен получает два выходных. В выходные дни его выработка, согласно таблице, равна нулю.
Теперь посмотрим на столбец «Средняя выработка». Очевидно, этот показатель занижен, ведь нули (выходные) участвуют в вычислении, а не должны. Как быть?
В Excel есть функция СРЗНАЧЕСЛИ, которая определяет среднее арифметическое на основании тех величин, которые соответствуют заданному условию. Записывается она так:
=СРЗНАЧЕСЛИ( массив_значений ; условие )
В этой функции массив, как правило, задается ссылкой на диапазон ячеек, а условие – логическое выражение, заключённое в кавычки.
В нашем случае, формула будет такой:
То есть, мы говорим программе: посчитай среднее значение всех чисел в диапазоне B3:P3, которые не равны нулю. Вы можете вручную пересчитать результат и убедиться, что Эксель проигнорировал нули, т.е. дни, когда работники были выходные. Вычисления, для данного случая, корректны.
Это всё, жду ваших вопросов и комментариев!
Добавить комментарий Отменить ответ
2 комментариев
За объяснение спасибо!
Нужна еще помощь в том что у меня диапазон прерывистый, то есть мне нужно выбирать столбец через столбец, и мне выдает ошибку что слишком много значений. Как в этом случае быть
Здравствуйте. Обычно, в таких случаях, дробят выполняемые работы на части, с которыми удобно работать, но чтобы памяти хватало для комфортного процесса
Excel. Использование формулы массива для вычисления среднего, не учитывающего нулевые значения
Эта заметка продолжает цикл материалов по использованию формул массива. Если ранее вы не сталкивались с формулами массива, рекомендую прочитать:
После этого вы, вероятно, сможете оценить возможности и достоинства формул массива, и вам захочется узнать о них побольше. Если вас не нужно агитировать, и вы полны желания разобраться в этом мощном оружии Excel, но не знаете, с чего начать, прочитайте Введение в формулы массива и Некоторые примеры использования формул массива
Основное достоинство формул массивов состоит в том, что они позволяют выполнять очень широкий круг вычислений, который другими способами выполнить нельзя. К сожалению, формулы массивов – это наиболее сложное и непонятное средство Excel.
Если вы уже постигли азы, предлагаю вам продолжить знакомство с формулами массива вместе с Джоном Уокенбахом и его книгой MS Excel 2007. Библия пользователя. – М.: Издательский дом «Вильямс», 2008. – 816 с.
Скачать заметку в формате Word, примеры в формате Excel
Вычисление среднего, не учитывающего нулевые значения
На рис. 1 показан рабочий лист, на котором вычисляется средний объем продаж группы продавцов. Формула в ячейке В14 имеет вид: =СРЗНАЧ(Продажи). Она вычисляет среднее значений из диапазона ВЗ:В10, которому присвоено имя Продажи. Некоторые продавцы не работали, но они также учитывались при вычислении среднего. Функция СРЗНАЧ игнорирует пустые ячейки, но учитывает ячейки с нулевыми значениями.
Следующая формула массива, записанная в ячейке В15, возвращает среднее без учета ячеек, содержащих 0: <=СРЗНАЧ(ЕСЛИ(Продажи>0;Продажи))>. Эта формула создает виртуальный массив, содержащий только ненулевые значения из диапазона Продажи. Этот массив используется в качестве аргумента в функции СРЗНАЧ.
Тот же результат можно получить с помощью обычной формулы (не формулы массива), записанной в ячейке В16: =СУММ(Продажи)/СЧЁТЕСЛИ(Продажи;»>0″). Эта формула использует функцию СЧЁТЕСЛИ для определения числа ненулевых значений в заданном диапазоне, на которое затем делится сумма значений этого диапазона.
Если диапазон может содержать отрицательные значения, и по-прежнему необходимо подсчитать среднее, не учитывающее нулевые значения, формулу массива нужно немного модифицировать: <=СРЗНАЧ(ЕСЛИ(Продажи<>0;Продажи))>
Рис. 1. Формула массива, вычисляющая среднее без учета нулевых значений
Как посчитать среднее значение без учета нулей
Как усреднить диапазон данных, игнорируя ноль в Excel?
Обычно функция «Среднее» может помочь вам рассчитать среднее значение диапазона, включая нули, в Excel. Но здесь вы хотите исключить нули при применении функции Average. Как можно игнорировать нули при вычислении среднего значения?
Усреднение диапазона данных без учета нулей с помощью формулы
Усреднение диапазона данных без учета нулей с помощью формулыЕсли вы используете Excel 2007/2010/2013, эта простая функция AVERAGEIF может помочь вам быстро и легко решить эту проблему.
1. Введите эту формулу = СРЗНАЧЕСЛИ (B2: B13; «<> 0») в пустой ячейке помимо ваших данных, см. снимок экрана:
2, Затем нажмите Enter key, и вы получите результат, исключающий нулевые значения. Смотрите скриншот:
Среднее / суммирование / подсчет диапазона данных без учета нулей с помощью Kutools for Excel
Если вы хотите суммировать / усреднять / подсчитывать, игнорируя нулевые ячейки, вы можете применить Kutools for Excel‘s Выбрать определенные ячейки утилита для выбора ненулевых ячеек, а затем просмотреть результат расчета в Строка состояния.
