Среднее значение без учета нулей в excel


Функция СРЗНАЧЕСЛИ - Служба поддержки Майкрософт

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

Описание

Возвращает среднее значение (среднее арифметическое) всех ячеек в диапазоне, которые соответствуют данному условию.

Синтаксис

СРЗНАЧЕСЛИ(диапазон, условия, [диапазон_усреднения])

Аргументы функции СРЗНАЧЕСЛИ указаны ниже.

  • Диапазон.    Обязательный. Одна или несколько ячеек для вычисления среднего, включающих числа или имена, массивы или ссылки, содержащие числа.

  • Условие.     Обязательный. Условие в форме числа, выражения, ссылки на ячейку или текста, которое определяет ячейки, используемые при вычислении среднего. Например, условие может быть выражено следующим образом: 32, "32", ">32", "яблоки" или B4.

  • Диапазон_усреднения.    Необязательный. Фактическое множество ячеек для вычисления среднего. Если этот параметр не указан, используется диапазон.

Замечания

  • Ячейки в диапазоне, которые содержат значения ИСТИНА или ЛОЖЬ, игнорируются.

  • Если ячейка в "диапазоне_усреднения" пустая, функция СРЗНАЧЕСЛИ игнорирует ее.

  • Если диапазон является пустым или текстовым значением, то #DIV0! значение ошибки #ЗНАЧ!.

  • Если ячейка в условии пустая, "СРЗНАЧЕСЛИ" обрабатывает ее как ячейки со значением 0.

  • Если ни одна из ячеек в диапазоне не соответствует условиям, то #DIV/0! значение ошибки #ДЕЛ/0!.

  • В этом аргументе можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому одиночному символу; звездочка — любой последовательности символов. Если нужно найти сам вопросительный знак или звездочку, то перед ними следует поставить знак тильды (~).

  • Значение "диапазон_усреднения" не обязательно должно совпадать по размеру и форме с диапазоном. При определении фактических ячеек, для которых вычисляется среднее, в качестве начальной используется верхняя левая ячейка в "диапазоне_усреднения", а затем добавляются ячейки с совпадающим размером и формой. Например:

Если диапазон равен

И "диапазон_усреднения"

Обрабатываемые ячейки

A1:A5

B1:B5

B1:B5

A1:A5

B1:B3

B1:B5

A1:B4

C1:D4

C1:D4

A1:B4

C1:C2

C1:D4

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

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

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

  • Мода     — это число, наиболее часто встречающееся в данном наборе чисел. Например, модой для чисел 2, 3, 3, 5, 7 и 10 будет 3.

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

Примеры

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

Стоимость имущества

Комиссионные

100000

7000

200000

14000

300000

21000

400000

28000

Формула

Описание

Результат

=СРЗНАЧЕСЛИ(B2:B5;"<23000")

Среднее значение всех комиссионных меньше 23 000. Этому условию удовлетворяют три из четырех значений, сумма которых составляет 42 000.

14000

=СРЗНАЧЕСЛИ(A2:A5;"<250000")

Среднее среди всех значений стоимости имущества меньше 250 000. Этому условию удовлетворяет два из четырех значений, их сумма составляет 300 000.

150000

=СРЗНАЧЕСЛИ(A2:A5;"<95000")

Среднее среди всех значений стоимости имущества меньше 95 000. Так как количество значений стоимости имущества, удовлетворяющих этому условию, равно 0, функция СРЗНАЧЕСЛИ вернет ошибку #ДЕЛ/0! из-за попытки деления на 0.

#ДЕЛ/0!

=СРЗНАЧЕСЛИ(A2:A5;">250000";B2:B5)

Среднее значение всех комиссионных для стоимости имущества более 250 000. Этому условию удовлетворяют два значения, сумма которых составляет 49 000.

24500

Пример 2

Регион

Доходы (в тысячах)

Восточный

45678

Западный

23789

Северный

-4789

Южная (новое представительство)

0

Средний Запад

9678

Формула

Описание

Результат

=СРЗНАЧЕСЛИ(A2:A6;"=*Западная";B2:B6)

Среднее значение всех доходов для Западной и Средне-Западной областей.

16733,5

=СРЗНАЧЕСЛИ(A2:A6;"<>*(Новое представительство)";B2:B6)

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

18589

Примеры функций СРЗНАЧ и СРЗНАЧА для среднего значения в Excel

Функции СРЗНАЧ и СРЗНАЧА служат для того, чтобы вычислять среднее арифметическое значение интересующих аргументов в Excel. Среднее число вычисляется классическим путем – суммирования всех чисел и делением суммы на количество этих же чисел. Главное отличие этих функций заключается в том, что они по-разному обрабатывают не числовые типы значений, которые находятся в ячейках Excel. Далее обо всем поподробнее.

Примеры использования функции СРЗНАЧ в Excel

Функция СРЗНАЧ относится к группе «Статистические». Поэтому для вызова данной функции в Excel необходимо выбрать инструмент: «Формулы»-«Библиотека функций»-«Другие функции»-«Статистические»-«СРЗНАЧ». Или вызвать диалоговое окно «Вставка функции» (SHIFT+F3) и выбрать из выпадающего списка «Категория:» опцию «Статистические». После чего в поле «Выберите функцию:» будет доступен список категории со статистическими функциями где и находится СРЗНАЧ, а также СРЗНАЧА.

Если есть диапазон ячеек B2:B8 с числами, то формула =СРЗНАЧ(B2:B8) будет возвращать среднее значение заданных чисел в данном диапазоне:

Синтаксис использования следующий: =СРЗНАЧ(число1; [число2]; …), где первое число обязательный аргумент, а все последующие аргументы (вплоть до числа 255) необязательны для заполнена. То есть количество выбранных исходных диапазонов не может превышать больше чем 255:

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

Аргументы функции СРЗНАЧ могут быть представлены не только числами, но и именами или ссылками на конкретный диапазон (ячейку), содержащий число. Учитывается логическое значение и текстовое представление числа, которое непосредственно введено в список аргументов.

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

Результат выполнения функции в примере на картинке ниже – это число 4, т.к. логические и текстовые объекты игнорируются. Поэтому:

(5 + 7 + 0 + 4) / 4 = 4

При вычислениях средних значений необходимо учитывать разницу между пустой ячейкой и ячейкой, содержащей нулевое значение, особенно если в диалоговом окне Excel снят флажок «Показывать нули в ячейках, которые содержат нулевые значения». Когда флажок установлен, пустые ячейки игнорируются, а нулевые значения нет. Чтобы снять или установить этот флаг необходимо открыть вкладку «Файл», далее нажать на «Параметры» и выбрать в категории «Дополнительно» группу «Показать параметры для следующего листа», где возможно установить флажок:

Результаты еще 4-х задач сведены в таблицу ниже:

Как видно на примере, в ячейке A9 функция СРЗНАЧ имеет 2 аргумента: 1 - диапазон ячеек, 2 – дополнительное число 5. Так же могут быть указаны в аргументах и дополнительные диапазоны ячеек с числами. Например, как в ячейке A11.



Формулы с примерами использования функции СРЗНАЧА

Функция СРЗНАЧА отличается от СРЗНАЧ тем, что истинное логическое значение «ИСТИНА» в диапазоне приравнивается к 1, а ложное логическое значение «ЛОЖЬ» или текстовое значение в ячейках приравнивается к нулю. Поэтому результат вычисления функции СРЗНАЧА отличается:

Результат выполнения функции возвращает число в примере 2,833333, так как текстовые и логические значения приняты за нуль, а логическое ИСТИНА приравнено к единице. Следовательно:

(5 + 7 + 0 + 0 + 4 + 1) / 6 = 2,83

Синтаксис:

=СРЗНАЧА(значение1;[значение2];...)

Аргументы функции СРЗНАЧА подчинены следующим свойствам:

  1. «Значение1» является обязательным, а «значение2» и все значения, которые следуют за ним необязательными. Общее количество диапазонов ячеек или их значений может быть от 1 до 255 ячеек.
  2. Аргумент может быть числом, именем, массивом или ссылкой, содержащей число, а также текстовым представлением числа или логическим значением, например, «истина» или «ложь».
  3. Логическое значение и текстовое представление числа, введенного в список аргументов, учитывается.
  4. Аргумент, содержащий значение «истина», интерпретируется как 1. Аргумент, содержащий значение «ложь», интерпретируется как 0 (ноль).
  5. Текст, содержащийся в массивах и ссылках, интерпретируется как 0 (ноль). Пустой текст («») интерпретирован тоже как 0 (ноль).
  6. Если аргумент массив или ссылка, то используются только значения, входящие в этот массив или ссылку. Пустые ячейки и текст в массиве и ссылке — игнорируется.
  7. Аргументы, которые являются значениями ошибки или текстом, не преобразуемым в числа, вызывают ошибки.

Результаты особенности функции СРЗНАЧА сведены в таблицу ниже:

Скачать примеры функций СРЗНАЧ в Excel

Внимание! При вычислениях средних значений в Excel надо учитывать различия между пустой ячейкой и ячейкой, содержащей нулевое значение (особенно если в диалоговом окне «Параметры» снят флажок «Показывать нули в ячейках, которые содержат нулевые значения»). Когда флажок установлен, пустые ячейки не учитываются, а нулевые значения учитываются. Для установки флажка на вкладке «Файл» надо выбрать команду «Параметры», перейти в категорию «Дополнительно», где найди раздел «Показать параметры для следующего листа» и там установить флаг.

Игнорировать нули и пробелы при усреднении ячеек в Excel

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

Функция СРЗНАЧЕСЛИ в Excel

Функция СРЗНАЧЕСЛИ усредняет ячейки на основе предоставленных критериев в Excel в соответствии с ее синтаксисом. Синтаксис функции СРЗНАЧЕСЛИ следующий:

=СРЗНАЧЕСЛИ (диапазон, критерии, [средний_диапазон])

Здесь,

диапазон — этот аргумент содержит диапазон ячеек, по которым проверяются критерии.

критерии — этот аргумент содержит условие для определения, какие ячейки усреднить. Он может содержать числовые или текстовые значения, логическое выражение, ссылку на ячейку или другие функции в качестве условия для выполнения.

средний_диапазон — этот аргумент состоит из диапазона ячеек, содержащих числа, которые вы хотите усреднить. Это необязательный аргумент, и если вы опустите этот аргумент в функции, то функция усреднит ячейки, указанные в аргументе диапазона .

Игнорировать нули при нахождении среднего значения

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

В аргументе критерии необходимо проверить критерии «Не равно нулю» . Это делается с помощью логического выражения Не равно (<>) с нулем, заключенным в двойные кавычки ("") .

Игнорировать пробелы при нахождении среднего значения

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

В этом примере вам нужно среднее значение Excel, игнорирующее нули и пустые ячейки, и то, как оно отличается от простого среднего значения этих ячеек. Предположим, у вас есть диапазон ячеек A1:A11 , содержащий числа, нули и пустые ячейки. Используя функцию СРЗНАЧЕСЛИ с выражением критерия Не равно нулю («<>0») , вы усредните ячейки, игнорируя нулевые и пустые значения. Формула, в этом случае, будет;

=СРЗНАЧЕСЛИ(A1:A11;"<>0")

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

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

=СРЕДНЕЕ(A1:A11)

И

=СРЗНАЧЕСЛИ(A1:A11,"<>0")

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

Вам все еще нужна помощь с функцией усреднения? Ознакомьтесь с нашим исчерпывающим обзором руководств по функциям среднего значения здесь.

Альтернативная формула усреднения Excel, не оценивающая ноль

Альтернативная формула усреднения Excel, которая не оценивает ноль

Функция Excel AVERAGE() оценивает нулевые значения. Если вы хотите исключить нули, используйте эти альтернативы.

Мы можем получать компенсацию от поставщиков, которые появляются на этой странице, с помощью таких методов, как партнерские ссылки или спонсируемое партнерство. Это может повлиять на то, как и где их продукты будут отображаться на нашем сайте, но поставщики не могут платить за влияние на содержание наших отзывов. Для получения дополнительной информации посетите нашу страницу «Условия использования».

Функция СРЗНАЧ() в Excel учитывает нулевые значения в своих вычислениях, что, вероятно, вы ожидаете, но не обязательно то, что вам нужно. За прошедшие годы я видел много формул, которые игнорируют нулевое значение, и большинство из них более запутаны, чем должны быть.

Если вы используете Excel 2007 или 2010, решение будет намного проще, чем раньше, благодаря новой функции СРЗНАЧЕСЛИ(). Эта функция использует следующий синтаксис для возврата среднего значения на основе условия:

 СРЗНАЧЕСЛИ(  средний  ,  критерии  ,  средний диапазон  ) 

Обязательны первые два аргумента; среднее — это ссылка на ячейки, которые вы хотите усреднить, а критерий — это выражение, которому должны удовлетворять ссылочные значения. Чтобы исключить нулевые значения, вы должны использовать критерии <>0. В частности, функция в C6 =СРЗНАЧЕСЛИ(B2:B5,"<>0″) усредняет значения в B2:B5, только если они не равны 0. В столбце B используется традиционная функция СРЗНАЧ(), которая включает ноль. Столбец A использует формулу =(A2+A3+A4+A5)/4, чтобы найти среднее значение.

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

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

 =СУММ(диапазон  )/СЧЁТЕСЛИ(диапазон  ,"<>0") 

Есть и другие формулы, включая массив (мое наименее любимое решение). Ключ в том, чтобы найти правильное решение проблемы, и, как я показал, функции и формулы ведут себя нежелательно, поэтому будьте осторожны.

 

Сьюзан Харкинс

Опубликовано: Изменено: Увидеть больше Программное обеспечение Доля: альтернативная формула усреднения Excel, которая не оценивает ноль.
  • Программное обеспечение

Выбор редактора

  • Изображение: Rawpixel/Adobe Stock ТехРеспублика Премиум

    Редакционный календарь TechRepublic Premium: ИТ-политики, контрольные списки, наборы инструментов и исследования для загрузки

    Контент TechRepublic Premium поможет вам решить самые сложные проблемы с ИТ и дать толчок вашей карьере или новому проекту.

    Персонал TechRepublic

    Опубликовано: Изменено: Читать далее Узнать больше
  • Изображение: Nuthawut/Adobe Stock
  • Изображение: WhataWin/Adobe Stock Безопасность

    Основные угрозы кибербезопасности на 2023 год

    В следующем году киберпреступники будут как никогда заняты. Готовы ли ИТ-отделы?

    Мэри Шеклетт

    Опубликовано: Изменено: Читать далее Узнать больше Безопасность
  • Изображение: Разные фотографии/Adobe Stock Облако

    Salesforce дополняет свой технологический стек новыми интеграциями для Slack, Tableau

    Компания, которая в течение нескольких лет закупала лучшие в своем классе продукты, интегрирует платформы для создания синергии для скорости, понимания и совместной работы.

    Карл Гринберг

    Опубликовано: Изменено: Читать далее Увидеть больше Облако
  • физкес / iStock
  • Изображение: Bumblee_Dee, iStock/Getty Images Программное обеспечение

    108 советов по Excel, которые должен усвоить каждый пользователь

    Независимо от того, являетесь ли вы новичком в Microsoft Excel или опытным пользователем, эти пошаговые руководства принесут вам пользу.


    Learn more

Только новые статьи

Введите свой e-mail

Видео-курс

Blender для новичков

Ваше имя:Ваш E-Mail: