Excel среднее значение ненулевых ячеек - Word и Excel - помощь в работе с программами

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

AleksFin

Дата: Среда, 07.06.2017, 11:27 |
Сообщение № 1

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 117

Репутация:
0
±

Замечаний:
0% ±

Excel 2010

Здравствуйте!

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

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

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

Ответить

Pelena

Дата: Среда, 07.06.2017, 11:31 |
Сообщение № 2

Группа: Админы

Ранг: Местный житель

Сообщений: 18797

Репутация:
4284
±

Замечаний:
±

Excel 2016 & Mac Excel

Здравствуйте.
Функция СРЗНАЧЕСЛИ() должна помочь

«Черт возьми, Холмс! Но как??!!»
Ю-money 41001765434816

Ответить

_Boroda_

Дата: Среда, 07.06.2017, 11:31 |
Сообщение № 3

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16618

Репутация:
6465
±

Замечаний:
0% ±

2003; 2007; 2010; 2013 RUS

СРЗНАЧЕСЛИ
СУММ/СЧЁТ

Скажи мне, кудесник, любимец ба’гов…
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

amfor

Дата: Среда, 07.06.2017, 11:32 |
Сообщение № 4

Группа: Проверенные

Ранг: Обитатель

Сообщений: 250

Репутация:
75
±

Замечаний:
0% ±

Excel 2016

Здравствуйте.
= СРЗНАЧЕСЛИ

Семь бед — один RESET.

Ответить

AleksFin

Дата: Среда, 07.06.2017, 11:39 |
Сообщение № 5

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 117

Репутация:
0
±

Замечаний:
0% ±

Excel 2010

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

К сообщению приложен файл:

333.xlsx
(8.4 Kb)

Ответить

_Boroda_

Дата: Среда, 07.06.2017, 11:43 |
Сообщение № 6

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16618

Репутация:
6465
±

Замечаний:
0% ±

2003; 2007; 2010; 2013 RUS

Код

=СРЗНАЧЕСЛИ(B11:B13;»<>0″)

Или формула массива

Код

=СУММ(B11:B13)/СЧЁТ(1/B11:B13)

К сообщению приложен файл:

333_1.xlsx
(8.7 Kb)

Ответить

buchlotnik

Дата: Среда, 07.06.2017, 11:45 |
Сообщение № 7

Группа: Заблокированные

Ранг: Участник клуба

Сообщений: 3442

Репутация:
929
±

Замечаний:
20% ±

2010, 2013, 2016 RUS / ENG

Код

=СРЗНАЧЕСЛИ(B11:B13;»>0″)

К сообщению приложен файл:

333-1-.xlsx
(8.9 Kb)

Ответить

and_evg

Дата: Среда, 07.06.2017, 11:47 |
Сообщение № 8

Группа: Проверенные

Ранг: Обитатель

Сообщений: 416

Репутация:
72
±

Замечаний:
0% ±

Excel 2007

AleksFin, так вам ведь говорили:

Код

= СРЗНАЧЕСЛИ(B11:B13;»<>0″;B11:B13)

Сообщение отредактировал and_evg — Среда, 07.06.2017, 11:48

Ответить

AleksFin

Дата: Среда, 07.06.2017, 12:20 |
Сообщение № 9

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 117

Репутация:
0
±

Замечаний:
0% ±

Excel 2010

Спасибо за ответы.

В оригинальную форму когда вставляю, выдает «слишком много аргументов».

Зажимаю ctrl и подбираю по одному нужные мне ячейки, это можно обойти как-нибудь?

Ответить

_Boroda_

Дата: Среда, 07.06.2017, 12:31 |
Сообщение № 10

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16618

Репутация:
6465
±

Замечаний:
0% ±

2003; 2007; 2010; 2013 RUS

Можно. Для этого нужно показать пример оригинальной формы

Ответить

AleksFin

Дата: Среда, 07.06.2017, 12:35 |
Сообщение № 11

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 117

Репутация:
0
±

Замечаний:
0% ±

Excel 2010

Ок

Ответить

_Boroda_

Дата: Среда, 07.06.2017, 12:46 |
Сообщение № 12

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16618

Репутация:
6465
±

Замечаний:
0% ±

2003; 2007; 2010; 2013 RUS

Не, не так. Нужно понимать, почему Вы выбрали именно ячейки из 11,13,13,17 и 20 строк. Не нужно придумывать примеры, просто отрубите кусок своего файла, уберите конф. информацию, положите сюда и объясните как-то примерно так: «Если в столбце А находятся значения ХХХ, то по таким строкам нужно вычислить среднее значение по столбцу В, но не учитывая нулевые значения»
Предположу, что Вам нужно что-то примерно вот такое

Код

=СРЗНАЧЕСЛИМН(B11:B21;B11:B21;»<>0″;A11:A21;»ХХХ»)

Или, если нужно для любого заполнения столбца А, то

Код

=СРЗНАЧЕСЛИМН(B11:B21;B11:B21;»<>0″;A11:A21;»*»)

=========
А вот это

Код

=СРЗНАЧЕСЛИ(B11;B13;B15;B17;B20;»>0″)

работать не будет. Почитайте справку по функции СРЗНАЧЕСЛИ и все сами поймете

Ответить

Sancho

Дата: Среда, 07.06.2017, 12:58 |
Сообщение № 13

Группа: Проверенные

Ранг: Обитатель

Сообщений: 279

Репутация:
19
±

Замечаний:
0% ±

2007, 2010, 2013

AleksFin, не понятно, из несвязанного диапазона надо выдернуть среднее значение среди ненулевых значений? тогда

Код

=СУММ(B11;B13;B15;B17;B20)/СУММПРОИЗВ((B11>0)+(B13>0)+(B15>0)+(B17>0)+(B20>0))

Ответить

AleksFin

Дата: Среда, 07.06.2017, 13:13 |
Сообщение № 14

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 117

Репутация:
0
±

Замечаний:
0% ±

Excel 2010

Выкладываю файлик, посмотрите пжл. Описание внутри.

К сообщению приложен файл:

1103380.xlsx
(10.3 Kb)

Ответить

_Boroda_

Дата: Среда, 07.06.2017, 13:18 |
Сообщение № 15

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16618

Репутация:
6465
±

Замечаний:
0% ±

2003; 2007; 2010; 2013 RUS

Так?

Код

=СРЗНАЧЕСЛИМН(Данные!F9:F18;Данные!F9:F18;»<>0″;Данные!D9:D18;B8&»*»)

На случай, если все нули

Код

=ЕСЛИОШИБКА(СРЗНАЧЕСЛИМН(Данные!F9:F18;Данные!F9:F18;»<>0″;Данные!D9:D18;B8&»*»);)

=
Вот теперь файл хороший и правильный.
В следующий раз тоже примерно так и делайте, тогда решение сразу получите, а не через 2 часа, как сейчас
=
И да, для цены <> не обязательно, достаточно просто >, как у Михаила

Ответить

buchlotnik

Дата: Среда, 07.06.2017, 13:20 |
Сообщение № 16

Группа: Заблокированные

Ранг: Участник клуба

Сообщений: 3442

Репутация:
929
±

Замечаний:
20% ±

2010, 2013, 2016 RUS / ENG

как-то так, наверное

Код

=СРЗНАЧЕСЛИМН(Данные!F9:F18;Данные!D9:D18;B8&»*»;Данные!F9:F18;»>0″)

К сообщению приложен файл:

1168525.xlsx
(10.7 Kb)

Ответить

Sancho

Дата: Среда, 07.06.2017, 13:40 |
Сообщение № 17

Группа: Проверенные

Ранг: Обитатель

Сообщений: 279

Репутация:
19
±

Замечаний:
0% ±

2007, 2010, 2013

AleksFin, то ли пример неудачный, то ли я чего то не понимаю? как может быть цена на изделие рассчитываться как среднее значение. в вашем варианте да, изделие из одной детали, а если из двух? одна деталь 15 другая 1, Средняя цена 8? че за бред какой то? если сравнить односоставное изделие со средней ценой 15 и двухсоставное изделие с ценой 8… в чем «соль»? какой вывод из этого следует?

Сообщение отредактировал Sancho — Среда, 07.06.2017, 13:43

Ответить

AleksFin

Дата: Среда, 07.06.2017, 14:18 |
Сообщение № 18

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 117

Репутация:
0
±

Замечаний:
0% ±

Excel 2010

Всем спасибо, очередной вопрос как всегда без сомнений решен. hands

Ответить

AleksFin

Дата: Среда, 07.06.2017, 14:39 |
Сообщение № 19

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 117

Репутация:
0
±

Замечаний:
0% ±

Excel 2010

Ребята, извините, я не полностью задачу обрисовал. :'(

Гляньте во вложении, я вторую строку добавил

К сообщению приложен файл:

7091850.xlsx
(10.8 Kb)

Ответить

Sancho

Дата: Четверг, 08.06.2017, 12:55 |
Сообщение № 20

Группа: Проверенные

Ранг: Обитатель

Сообщений: 279

Репутация:
19
±

Замечаний:
0% ±

2007, 2010, 2013

AleksFin, вы ищете машину, которой на листе «данные» нет.

Ответить

Источник

Ребята! Help me, плизз. Не могу найти на форуме решение этой задачи. Всё, что нашел — не работает. Может чего неправильно делаю? Как рассчитать среднее значение в столбце при этом не учитывая ячейки со значением «0»?
Файл удален — велик размер — [МОДЕРАТОРЫ]

webley Пользователь Сообщений: 1991 Регистрация: 01.01.1970	=СУММ(C6:C9)/СЧЁТЕСЛИ(C6:C9;»<>0″)

webley Пользователь Сообщений: 1991 Регистрация: 01.01.1970	или если есть еще пустые ячейки, то =СУММ(C6:C9)/(СЧЁТЕСЛИ(C6:C9;»<>0″)-СЧИТАТЬПУСТОТЫ(C6:C9))

MCH Пользователь Сообщений: 3875 Регистрация: 22.12.2012	Формула массива (вводить нажатием ctrl+shift+enter) =СРЗНАЧ(ЕСЛИ(диапазон;диапазон))

Спасибо ОГРОМНОЕ!
А возможен ли расчет среднего значения в прилагаемом файле в столбце «J» (Наличие POSM) только тех ячеек, которые соответствуют заполненным ячейкам (без учета «0» значений) в столбце «B» (Наименование и адрес т.т.)?
Заранее благодарен.

GIG_ant Пользователь Сообщений: 3102 Регистрация: 24.12.2012	=СУММЕСЛИ(B4:B153;»<>0″;J4:J153)/СЧЁТЕСЛИ(B4:B153;»<>0″)

	Спасибо! Работает! Я просто таки счастлив, что нашел Ваш сайт!

Дмитрий Гость	#8 17.03.2012 09:15:45 Для того, чтобы пользоваться средними величинами, неплохо бы разобраться, что они обозначают и для чего используются. Вот здесь неплохая подборка статей на тему средних. http://statanaliz.info/teoriya-i-praktika/8-srednie/6-srednie-velichiny-vvedenie.html

Источник

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще…Меньше

В этой статье описаны синтаксис формулы и использование функции С AVERAGE в Microsoft Excel.

Описание

Возвращает среднее арифметическое аргументов. Например, если диапазон A1:A20 содержит числа, формула =СПБ(A1:A20) возвращает среднее из этих чисел.

Синтаксис

СРЗНАЧ(число1;[число2];…)

Аргументы функции СРЗНАЧ описаны ниже.

Число1 Обязательный аргумент. Первое число, ссылка на ячейку или диапазон, для которого требуется вычислить среднее значение.
Число2… Необязательный. Дополнительные числа, ссылки на ячейки или диапазоны, для которых нужно вычесть среднее значение, не более 255.

Замечания

Аргументы могут быть числами, именами или ссылками на диапазоны или ячейки, содержащие числа.
Логические значения и текстовые представления чисел, которые непосредственно введите в список аргументов, не учитываются.
Если аргумент является ссылкой на диапазон или ячейку, содержащую текст или логические значения, или ссылкой на пустую ячейку, то такие значения игнорируются; однако ячейки, которые содержат нулевые значения, учитываются.
Аргументы, являющиеся значениями ошибок или текстом, которые не могут быть преобразованы в числа, вызывают ошибки.
Если логические значения и текстовые представления чисел необходимо учитывать в расчетах, используйте функцию СРЗНАЧА.
Если требуется вычислить среднее значение только для тех значений, которые удовлетворяют определенным критериям, используйте функцию СРЗНАЧЕСЛИ или СРЗНАЧЕСЛИМН.

Примечание: Функция СРЗНАЧ вычисляет среднее значение, то есть центр набора чисел в статистическом распределении. Существует три наиболее распространенных способа определения среднего значения, описанных ниже.

Среднее значение — это среднее арифметическое, которое вычисляется путем сложения набора чисел с последующим делением полученной суммы на их количество. Например, средним значением для чисел 2, 3, 3, 5, 7 и 10 будет 5, которое является результатом деления их суммы, равной 30, на их количество, равное 6.
Медиана — это число, которое является серединой множества чисел, то есть половина чисел имеют значения большие, чем медиана, а половина чисел имеют значения меньшие, чем медиана. Например, медианой для чисел 2, 3, 3, 5, 7 и 10 будет 4.
Мода — это число, наиболее часто встречающееся в данном наборе чисел. Например, модой для чисел 2, 3, 3, 5, 7 и 10 будет 3.

При симметричном распределении множества чисел все три значения центральной тенденции будут совпадать. При ассиметричном распределении множества чисел они могут отличаться.

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

Местонахождение флажка Показывать нули в ячейках, которые содержат нулевые значения

Откройте вкладку Файл , а затем нажмите кнопку Параметры и в категории Дополнительно найдите группу Показать параметры для следующего листа.

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Данные
10	15	32
7
9
27
2
Формула	Описание	Результат
=СРЗНАЧ(A2:A6)	Среднее значение чисел в ячейках A2–A6.	11
=СРЗНАЧ(A2:A6;5)	Среднее значение чисел в ячейках A2–A6 и числа 5.	10
=СРЗНАЧ(A2:C2)	Среднее значение чисел в ячейках A2–C2.	19

Нужна дополнительная помощь?

Источник

You can use the following formula to calculate the average in Excel while ignoring zeros and blank cells:

=AVERAGEIF(B2:B14, "<>0")

This particular formula calculates the average value in the range B2:B14 and ignores cells that are equal to zero or blank.

Note: Excel ignores blank cells by default when calculating an average.

Thus, we only need to specify in the formula that cells should not be equal ( “<>0”) to zero as well.

The following example shows how to use this formula in practice.

Suppose we have the following dataset in Excel that shows the total sales made by various employees at a company:

If we simply used the AVERAGE() formula, we would find the average sales for all of the employees who had a non-blank value:

The average sales per employee who had a non-blank value is 3.

However, suppose we wanted to only calculate the average for employees who had a sales value that was not blank and not equal to zero.

We could type the following formula into cell D2:

=AVERAGEIF(B2:B14, "<>0")

The following screenshot shows how to use this formula in practice:

The average sales for employees who had greater than zero sales was 4.125.

This formula calculated the average by only using the values that were not blank and not equal to zero.

We can confirm that this is correct by manually calculating the average of all values that are not blank or equal to zero:

Average of Values Greater than Zero: (10+4+4+3+2+1+4+5) / 8 = 4.125.

This matches the value calculated by our formula.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

How to Calculate Average If Cell Contains Number in Excel
How to Use AVERAGEIF with Multiple Ranges in Excel
How to Calculate Average Excluding Outliers in Excel

Источник

Обычно функция «Среднее» может помочь вам рассчитать среднее значение диапазона, включая нули, в Excel. Но здесь вы хотите исключить нули при применении функции Average. Как можно игнорировать нули при вычислении среднего значения?

Усреднение диапазона данных без учета нулей с помощью формулы

Усреднение/суммирование/подсчет диапазона данных без учета нулей с помощью Kutools for Excel

Усреднение диапазона данных без учета нулей с помощью формулы

Если вы используете Excel 2007/2010/2013, эта простая функция AVERAGEIF может помочь вам быстро и легко решить эту проблему.

1. Введите эту формулу = СРЗНАЧЕСЛИ (B2: B13; «<> 0») в пустой ячейке помимо ваших данных, см. снимок экрана:

Внимание: В приведенной выше формуле B2: B13 — это данные диапазона, которые вы хотите усреднить, исключая нули, вы можете изменить его по своему усмотрению. Если в диапазоне есть пустые ячейки, эта формула также усредняет данные, исключая пустые ячейки.

2, Затем нажмите Enter key, и вы получите результат, исключающий нулевые значения. Смотрите скриншот:

Примечание: Эта формула не вступит в силу в Excel 2003, если у вас Excel 2003, вы должны объединить Сумма и СЧЕТЕСЛИ функция, примените следующую формулу: =SUM(B2:B13)/COUNTIF(B2:B13,»>0″).

Усреднение/суммирование/подсчет диапазона данных без учета нулей с помощью Kutools for Excel

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

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

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

2. В появившемся диалоговом окне отметьте Ячейка , а затем выберите Не равный из первого раскрывающегося списка в Конкретный тип раздел и перейдите в правое текстовое поле, чтобы ввести 0. Смотрите скриншот:

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

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

Данные «Среднее / Счетчик / Сумма» игнорируют нули

Связанная статья:

Как рассчитать среднее значение без максимальных и минимальных значений в Excel?

Лучшие инструменты для работы в офисе

Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%

Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!