Что такое диапазон усреднения

Функция СРЗНАЧЕСЛИ() — Вычисление в EXCEL среднего по условию (один ЧИСЛОвой критерий)

Диапазон — диапазон ячеек, в котором ищутся значения соответствующие аргументу Условие . Диапазон может содержать числа, даты, текстовые значения или ссылки на другие ячейки. В случае, если другой аргумент — Диапазон_усреднения — опущен, то аргумент Диапазон должен содержать числа.

Условие — критерий в форме числа, выражения или текста, определяющий, какие ячейки должны участвовать в вычислении среднего. Например, аргумент Условие может быть выражен как 32, «яблоки» или «>32».

Диапазон_усреднения — диапазон ячеек содержащий числа, участвующие в вычислении среднего, в случае если соответствующие им ячейки, указанные в аргументе Диапазон соответствуют аргументу Условие . Диапазон_усреднения — необязательный аргумент. Если он опущен, то вычисление среднего будет производиться по диапазону ячеек, указанному в аргументе Диапазон .

Примеры

Рассмотрим случай, когда аргумент Диапазон_усреднения опущен. В этом случае вычисление среднего будет производиться по диапазону ячеек, указанному в первом аргументе Диапазон (т.е. он должен содержать числа). В нем же будет производиться поиск значений соответствующих аргументу Условие , которые затем и будут участвовать в вычислении среднего. Пусть это будет диапазон A5:A15 , см. Файл примера .

найти среднее всех чисел менее или равных заданному (10): =СРЗНАЧЕСЛИ(A5:A15;» .
найти среднее всех положительных чисел в диапазоне: =СРЗНАЧЕСЛИ(A5:A15;»>0″) . Другой вариант с использованием функции СУММПРОИЗВ() : =СУММПРОИЗВ((A5:A15)*(A5:A15>0))/ СЧЁТЕСЛИ(A5:A15;»>=10″)

Форма задания критерия достаточно гибка. Например, в файле примера в формуле =СРЗНАЧЕСЛИ(A5:A15;C7&C8) критерий >=10 задан через ссылку C7&C8 : в С7 содержится текстовое значение >= , а в С8 – число 10 . Пользователь может легко изменить критерий.

СОВЕТ: В статье Вычисление среднего по условию (один Текстовый критерий) рассмотрен более общий случай, когда критерий применяется к соседнему диапазону, содержащему текстовые значения, а суммирование производится только соответствующих ячеек из числового диапазона.

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

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

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

Описание

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

Синтаксис

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

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

Диапазон. Обязательный. Одна или несколько ячеек для вычисления среднего, включающих числа или имена, массивы или ссылки, содержащие числа.
Условие. Обязательный. Условие в форме числа, выражения, ссылки на ячейку или текста, которое определяет ячейки, используемые при вычислении среднего. Например, условие может быть выражено следующим образом: 32, «32», «>32», «яблоки» или B4.
Диапазон_усреднения. Необязательный. Фактическое множество ячеек для вычисления среднего. Если этот параметр не указан, используется диапазон.

Замечания

Ячейки в диапазоне, которые содержат значения ИСТИНА или ЛОЖЬ, игнорируются.
Если ячейка в «диапазоне_усреднения» пустая, функция СРЗНАЧЕСЛИ игнорирует ее.
Если диапазон является пустым или текстовым значением, то #DIV0! значение ошибки #ЗНАЧ!.
Если ячейка в условии пустая, «СРЗНАЧЕСЛИ» обрабатывает ее как ячейки со значением 0.
Если ни одна из ячеек в диапазоне не соответствует условиям, то #DIV/0! значение ошибки #ДЕЛ/0!.
В этом аргументе можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому одиночному символу; звездочка — любой последовательности символов. Если нужно найти сам вопросительный знак или звездочку, то перед ними следует поставить знак тильды (~).
Значение «диапазон_усреднения» не обязательно должно совпадать по размеру и форме с диапазоном. При определении фактических ячеек, для которых вычисляется среднее, в качестве начальной используется верхняя левая ячейка в «диапазоне_усреднения», а затем добавляются ячейки с совпадающим размером и формой. Например:

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

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

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

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

Среднее значение — это среднее арифметическое, которое вычисляется путем сложения набора чисел с последующим делением полученной суммы на их количество. Например, средним значением для чисел 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, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

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

Функция СРЗНАЧЕСЛИМН среднее значение в Excel по условию

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

Данная функция упрощает процедуру выборки данных из таблицы для последующего расчета среднего арифметического с учетом определенных критериев.

Как сделать выборку средних значений по условию в Excel

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

Пример 1. Студенты, изучавшие некоторый предмет, на протяжении семестра зарабатывали баллы (до 100 баллов). При этом те, кто набрал 49 и менее баллов считается не сдавшим предмет. Определить средний балл для студентов, которые набрали 50 и более баллов (сдали).

Вид таблицы данных:

Пример 1.

Для определения искомого значения используем формулу:

B3:B17 – диапазон усреднения (оценки всех студентов);
C3:C17 – диапазон, на основе данных которого будет проверяться условие;
«сдал» — условие (проверка каждой ячейки на соответствие хранящихся в ней данных заданной текстовой строке).

В результате получим:

СРЗНАЧЕСЛИМН.

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

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

Пример 2. Перед продавцами магазина стоят следующие задачи по продажам:

количество товаров в чеке – не менее 3 единиц;
средняя цена в чеке – 40 у. е.;
обязательная продажа продукции фирмы Adidas.

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

Вид таблицы данных:

Пример 2.

Для расчетов используем следующую формулу:

B3:B13 – диапазон ячеек с ценами в чеке;
A3:A12 – с номерами продавцов;
C15 – ячейка с номером продавца как критерий выбора;
C3:C12 – с количеством единиц товаров в чеке;
«> *»&C18&»*» – критерий выбора по названию бренда (так как наименования перечислены через запятую, для нахождения подстроки в строке используем символы «*» для указания любого числа любых символов в начале и в конце исследуемой строки).

Результат вычислений для первого продавца:

Результат 1.

Для второго продавца:

Результат №2.

Как видно, оба продавца справились с поставленной задачей.

Правила работы с функцией СРЗНАЧЕСЛИМН в Excel

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

=СРЗНАЧЕСЛИМН( диапазон_усреднения;диапазон_условий1;условие1; [диапазон_условий2;условие2];…)

диапазон_усреднения – обязательный для заполнения, принимает ссылку на одну ячейку или диапазон, которые содержат числа или данные другого типа, которые могут быть преобразованы в числовые. Среднее арифметическое будет рассчитано на основе данных из данного диапазона с учетом соответствующих критериев.
диапазон_условий1 – обязательный для заполнения, принимает ссылку на одну ячейку или диапазон, которые содержат данные, на основе которых будет выполнен отбор числовых данных (первый аргумент) для расчета среднего арифметического. Последующие аргументы (диапазон_условий2…) являются необязательными для заполнения и имеют тот же смысл (для более тонкой настройки отбора данных).
условие1 – обязательный для заполнения, принимает числовые, текстовые, ссылочные данные, имена и логические выражения, используемые в качестве критерия для отбора данных. Последующие аргументы (условие2…) необязательны для заполнения, имеют тот же смысл. Примеры:

2 – выбрать значения из диапазона, которые равны числу 2.
«2» — то же самое (текст будет преобразован в число).
«>5» — рассчитать среднее арифметическое для чисел из диапазона, значения которых больше 5.
«аккумулятор» — рассчитать среднее арифметическое для числовых данных, сопоставимых с категорией аккумулятор.
A2 – использовать для проверки данные, хранящиеся в ячейке A2 (может хранить числа или данные, преобразуемые в число, а также выражения).

Пустые ячейки, переданные в качестве аргументов условиеX преобразуются в значения 0.
Ошибка #ДЕЛ/0! возникает в случае, если диапазон_усреднения содержит нечисловые данные, которые не могут быть преобразованы в числа.
Логические данные (ЛОЖЬ, ИСТИНА) автоматические преобразуются в соответствующие числовые значение 0 и 1.
Размеры и формы диапазонов условий и усреднения должны совпадать.
Результат работы рассматриваемой функции будет являться кодом ошибки #ДЕЛ/0!, если в результате проверки условий не были найдены данные, которые им соответствуют.
В логическом выражении, используемом в качестве аргумента для указания критериев поиска данных (условиеX) можно использовать подстановочные знаки («*» — любое количество любых символов, «?» — любой одиночный символ).

Excel Formula Examples
Создать таблицу
Форматирование
Функции Excel
Формулы и диапазоны
Фильтр и сортировка
Диаграммы и графики
Сводные таблицы
Печать документов
Базы данных и XML
Возможности Excel
Настройки параметры
Уроки Excel
Макросы VBA
Скачать примеры