|
Incanta Пользователь Сообщений: 5 |
Добрый день! Подскажите как подсчитать среднее арифметическое, чтобы не учитывались пустые ячейки? http://office.microsoft.com/ru-ru/excel-help/HP005208995.aspx?CTT=3 Я работаю с excell 2010, у меня нет такого в меню Параметры, не понятно где искать чтобы поставить эту опцию. |
|
формула массива |
|
|
Serge  Пользователь Сообщений: 11308 |
{quote}{login=Incanta}{date=25.10.2011 04:09}{thema=Формула СРЗНАЧ без учета пустых ячеек}{post} 32,1 Кб. |
|
Incanta Пользователь Сообщений: 5 |
Михаил, спасибо, замечательная формула, прекрасно работает! Serge 007, про функцию срзначесли я знаю, но мне непонятно как в таком случае задать чтобы среднее считалось по всем, кроме «0» значений, как задать диапазон, исключив не равно нулю? И еще вопрос: как можно посчитать среднее без учета нулевых, если у меня в таблице чередуются столбцы план/факт, и мне нужно только для факта? ведь массив в этом случае не сработает. |
|
vikttur Пользователь Сообщений: 47199 |
Если хорошо попросить, то массив и здесь сработает =СРЗНАЧ(ЕСЛИ($B$4:$I$4=»факт»;ЕСЛИ(B5:I5;B5:I5))) |
|
{quote}{login=Incanta}{date=25.10.2011 04:09}{thema=Формула СРЗНАЧ без учета пустых ячеек}{post}Добрый день! Подскажите как подсчитать среднее арифметическое, чтобы не учитывались пустые ячейки? {/post}{/quote}Сразу не обратил внимание.., функция СРЗНАЗ() пустые ячейки и так не учитывает. Но суть понял — имеются ввиду нулевые значения. 58885 |
|
|
Михаил С. Пользователь Сообщений: 10514 |
#7 27.10.2011 18:09:34 {quote}{login=Incanta}{date=27.10.2011 03:52}{thema=Формула СРЗНАЧ без учета пустых ячеек}{post}Михаил, спасибо, замечательная формула, прекрасно работает! Serge 007, про функцию срзначесли я знаю, но мне непонятно как в таком случае задать чтобы среднее считалось по всем, кроме «0» значений, как задать диапазон, исключив не равно нулю? И еще вопрос: как можно посчитать среднее без учета нулевых, если у меня в таблице чередуются столбцы план/факт, и мне нужно только для факта? ведь массив в этом случае не сработает. =СРЗНАЧЕСЛИМН(B5:I5;B5:I5;»<>0″;$B$4:$I$4;»факт») Прикрепленные файлы
|
|
Среднее значение без учета ячеек с нулем |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
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 |
Нужна дополнительная помощь?
Функция среднего очень полезна для повседневной работы в Excel. В этой статье будет рассказано о том, как легко вычислить среднее значение для ячеек только со значениями (исключая пустые ячейки) в Excel.
- Среднее с игнорированием / исключением пустых ячеек с формулой
- Среднее для ячеек только со значениями (исключая пустые ячейки) с Kutools for Excel (всего 1 шаг)
Среднее с игнорированием / исключением пустых ячеек с формулой
Функция Average автоматически игнорирует действительно пустые ячейки. Однако, если вы отключили опцию Sкак ноль в ячейках с нулевым значением в диалоговом окне Параметры Excel (нажав Файл > Опции > Дополнительно > Параметры отображения для этого рабочего листа) некоторые пустые ячейки могут иметь нулевые значения в Excel. В этом случае функция Average не исключает эти пустые ячейки с нулевыми значениями.
На самом деле мы можем исключить ячейки с нулями, чтобы решить эту проблему. Например, вы хотите усреднить диапазон A1: D13 с игнорированием / исключением пустых ячеек и нулей, вы можете применить формулу = СРЗНАЧЕСЛИ (A1: D13; «<> 0») как показано ниже:
Среднее для ячеек только со значениями (исключая пустые ячейки) с Kutools for Excel
Microsoft Excel может автоматически усреднять выбранные ячейки и отображать вычисленные результаты в строке состояния. Если мы можем выбрать только непустые ячейки в диапазоне, мы можем получить среднее значение этого диапазона, исключая пустые ячейки. Kutools for Excel’s Выбрать непустые ячейки утилита может помочь вам сделать это сразу.
Выберите диапазон, в котором вы хотите усреднить, за исключением пустых ячеек, и щелкните значок Кутулс > Выберите > Выберите Un blank Cells.
Теперь в указанном диапазоне выбираются только ячейки со значениями, и вы получите среднее значение без пустых ячеек в строке состояния, как показано ниже:
Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия 30-день, кредитная карта не требуется! Get It Now
Демо
Рассчитывать специальные средние в Excel
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
- Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Комментарии (5)
Оценок пока нет. Оцените первым!
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