как посчитать среднее значение без учета нулей

Среднее значение ненулевых ячеек

Здравствуйте, подскажите пожалуйста, есть ряд числовых ячеек например их количество 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 Выбрать определенные ячейки утилита для выбора ненулевых ячеек, а затем просмотреть результат расчета в Строка состояния.

После бесплатная установка Kutools for Excel, сделайте следующее:

1. Выберите диапазон, который вы хотите усреднить, нажмите Kutools > Выберите > Выбрать определенные ячейки. Смотрите скриншот:
как посчитать среднее значение без учета нулей. Смотреть фото как посчитать среднее значение без учета нулей. Смотреть картинку как посчитать среднее значение без учета нулей. Картинка про как посчитать среднее значение без учета нулей. Фото как посчитать среднее значение без учета нулей

3. Нажмите Ok, и все ячейки, которые больше 0, были выбраны, и вы можете просмотреть среднее значение, сумму, результат подсчета в строке состояния. Смотрите скриншот:
как посчитать среднее значение без учета нулей. Смотреть фото как посчитать среднее значение без учета нулей. Смотреть картинку как посчитать среднее значение без учета нулей. Картинка про как посчитать среднее значение без учета нулей. Фото как посчитать среднее значение без учета нулей

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

Источник

Как посчитать среднее значение без учета нулей

Среднее значение без учета ячеек с нулем

Kutools for Excel, с более чем 300 удобные функции, облегчающие вашу работу.
AleksFinДата: Среда, 07.06.2017, 11:27 | Сообщение № 1
как посчитать среднее значение без учета нулей. Смотреть фото как посчитать среднее значение без учета нулей. Смотреть картинку как посчитать среднее значение без учета нулей. Картинка про как посчитать среднее значение без учета нулей. Фото как посчитать среднее значение без учета нулей

Собираю отчет, столкнулся с такой задачкой, когда нужно чтобы среднее значение в ячейке выводилось без учета ячеек с нулями.

Если есть 3 ячейки и в 2-х из них стоят нули, то эксель делит на 3, а нужно чтобы не делил на 3, а делил только на заполненные больше нуля цифры.

Пример не прилагаю, думаю он не нужен. Если нужен, приложу.

Собираю отчет, столкнулся с такой задачкой, когда нужно чтобы среднее значение в ячейке выводилось без учета ячеек с нулями.

Если есть 3 ячейки и в 2-х из них стоят нули, то эксель делит на 3, а нужно чтобы не делил на 3, а делил только на заполненные больше нуля цифры.

Пример не прилагаю, думаю он не нужен. Если нужен, приложу. AleksFin

Собираю отчет, столкнулся с такой задачкой, когда нужно чтобы среднее значение в ячейке выводилось без учета ячеек с нулями.

Если есть 3 ячейки и в 2-х из них стоят нули, то эксель делит на 3, а нужно чтобы не делил на 3, а делил только на заполненные больше нуля цифры.

Источник

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *