Здравствуйте. В статье о расчете среднего значения, я рассказал о работе функции СРЗНАЧ. Там же я уточнил, что функция игнорирует пустые и текстовые ячейки, а ноль – участвует в вычислении. Чем это чревато?
Посмотрите пример на картинке выше. Здесь ежедневная выработка служащих производственного цеха за 15 дней. В последних двух столбцах вычисляем общее количество произведенного товара, и среднедневное. Легко заметить, что каждый сотрудник после четырёх смен получает два выходных. В выходные дни его выработка, согласно таблице, равна нулю.
Теперь посмотрим на столбец «Средняя выработка». Очевидно, этот показатель занижен, ведь нули (выходные) участвуют в вычислении, а не должны. Как быть?
В Excel есть функция СРЗНАЧЕСЛИ, которая определяет среднее арифметическое на основании тех величин, которые соответствуют заданному условию. Записывается она так:
=СРЗНАЧЕСЛИ( массив_значений ; условие )
В этой функции массив, как правило, задается ссылкой на диапазон ячеек, а условие – логическое выражение, заключённое в кавычки.
В нашем случае, формула будет такой:
То есть, мы говорим программе: посчитай среднее значение всех чисел в диапазоне B3:P3, которые не равны нулю. Вы можете вручную пересчитать результат и убедиться, что Эксель проигнорировал нули, т.е. дни, когда работники были выходные. Вычисления, для данного случая, корректны.
Это всё, жду ваших вопросов и комментариев!
Добавить комментарий Отменить ответ
2 комментариев
За объяснение спасибо! Нужна еще помощь в том что у меня диапазон прерывистый, то есть мне нужно выбирать столбец через столбец, и мне выдает ошибку что слишком много значений. Как в этом случае быть
Здравствуйте. Обычно, в таких случаях, дробят выполняемые работы на части, с которыми удобно работать, но чтобы памяти хватало для комфортного процесса
Здравствуйте, подскажите пожалуйста, есть ряд числовых ячеек например их количество 40 в некоторых есть значения, а некоторые нулевые. Требуется сложить ячейки не нулевые и разделить на их количество, причем количество не нулевых ячеек постоянно разное. Огромное спасибо Вашему ресурсу узнал много нового.
Как я понимаю, речь идет о вычислении среднего значения по столбцу или по строке при условии, что ячейки содержащие нулевые значения не учитываются и их количество может изменяться. Классическое среднее значение равно сумма значений деленное на количество значений. С суммой все ясно, нулевые значения не влияют на сумму, а вот количество ненулевых значений будет постоянно изменяться. Чтобы их подсчитывать необходимо применить функцию СЧЁТЕСЛИ, которая позволяет подсчитать количество значений по заданному критерию в заданном диапазоне.
Если в заданном диапазоне числовые значения могут принимать положительные и отрицательные значения, то критерий отбора необходимо изменить с «Больше нуля» на «Неравно нулю» и формула будет выглядеть так.
А теперь попробуем еще усложнить задачу. Допустим нам надо найти среднее значение по диапазону с условием, что суммируются значения больше «19». В этом случае критерий отбора влияет не только на количество значений но и на сумму. Поэтому вместо функции СУММ() мы применим функцию СУММЕСЛИ() и формула будет выглядеть так.
Возможны и другие варианты задания условий отбора, которые можно реализовать, используя эту формулу.
Как посчитать в экселе среднее значение без учета нулевых значений
Как усреднить диапазон данных, игнорируя ноль в Excel?
Обычно функция «Среднее» может помочь вам рассчитать среднее значение диапазона, включая нули, в Excel. Но здесь вы хотите исключить нули при применении функции Average. Как можно игнорировать нули при вычислении среднего значения?
Усреднение диапазона данных без учета нулей с помощью формулы
Если вы используете Excel 2007/2010/2013, эта простая функция AVERAGEIF может помочь вам быстро и легко решить эту проблему.
1. Введите эту формулу = СРЗНАЧЕСЛИ (B2: B13; «<> 0») в пустой ячейке помимо ваших данных, см. снимок экрана:
2, Затем нажмите Enter key, и вы получите результат, исключающий нулевые значения. Смотрите скриншот:
Среднее / суммирование / подсчет диапазона данных без учета нулей с помощью Kutools for Excel
Если вы хотите суммировать / усреднять / подсчитывать, игнорируя нулевые ячейки, вы можете применить Kutools for Excel‘s Выбрать определенные ячейки утилита для выбора ненулевых ячеек, а затем просмотреть результат расчета в Строка состояния.
Kutools for Excel, с более чем 300 удобные функции, облегчающие вашу работу.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Выберите диапазон, который вы хотите усреднить, нажмите Kutools > Выберите > Выбрать определенные ячейки. Смотрите скриншот:
3. Нажмите Ok, и все ячейки, которые больше 0, были выбраны, и вы можете просмотреть среднее значение, сумму, результат подсчета в строке состояния. Смотрите скриншот:
Наконечник. Если вы хотите получить бесплатную пробную версию Выбрать конкретную ячейкуфункция s, пожалуйста, перейдите к бесплатной загрузке Kutools for Excel сначала, а затем перейдите к применению операции в соответствии с вышеуказанными шагами.
Как посчитать в экселе среднее значение без учета нулевых значений
Функция среднего очень полезна для повседневной работы в Excel. В этой статье будет рассказано о том, как легко вычислить среднее значение для ячеек только со значениями (исключая пустые ячейки) в Excel.
Функция Average автоматически игнорирует действительно пустые ячейки. Однако, если вы отключили опцию Sкак ноль в ячейках с нулевым значением в диалоговом окне Параметры Excel (нажав Файл > Параметры > Advanced > Параметры отображения для этого рабочего листа) некоторые пустые ячейки могут иметь нулевые значения в Excel. В этом случае функция Average не исключает эти пустые ячейки с нулевыми значениями.
На самом деле мы можем исключить ячейки с нулями, чтобы решить эту проблему. Например, вы хотите усреднить диапазон A1: D13 с игнорированием / исключением пустых ячеек и нулей, вы можете применить формулу = СРЗНАЧЕСЛИ (A1: D13; «<> 0») как показано ниже:
Microsoft Excel может автоматически усреднять выбранные ячейки и отображать рассчитанные результаты в строке состояния. Если мы можем выбрать только непустые ячейки в диапазоне, мы можем получить среднее значение этого диапазона, исключая пустые ячейки. Kutools для Excel Выбрать непустые ячейки утилита может помочь вам сделать это сразу.
Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!
Выберите диапазон, в котором вы хотите усреднить, за исключением пустых ячеек, и щелкните значок Kutools > Выберите > Выберите Un blank Cells.
Теперь в указанном диапазоне выбираются только ячейки со значениями, и вы получите среднее значение без пустых ячеек в строке состояния, как показано ниже:
Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Усреднение диапазона данных без учета нулей с помощью формулы
