В этой статье описаны синтаксис формулы и использование функции СРЗНАЧЕСЛИМН в Microsoft Excel.
Описание
Возвращает среднее значение (среднее арифметическое) всех ячеек, которые соответствуют нескольким условиям.
Синтаксис
СРЗНАЧЕСЛИМН(диапазон_усреднения;диапазон_условий1;условие1;[диапазон_условий2;условие2];…)
Аргументы функции СРЗНАЧЕСЛИМН указаны ниже.
-
Диапазон_усреднения: обязательный. Одна или несколько ячеек для вычисления среднего с числами или именами, массивами или ссылками, содержащими числа.
-
Диапазон_условий1, диапазон_условий2, … параметр "диапазон_условий1" — обязательный, остальные диапазоны условий — нет. От 1 до 127 интервалов, в которых проверяется соответствующее условие.
-
Условие1, условие2, … Параметр "условие1" является обязательным, остальные условия — нет. От 1 до 127 условий в форме числа, выражения, ссылки на ячейку или текста, определяющих ячейки, для которых будет вычисляться среднее. Например, условие может быть выражено следующим образом: 32, "32", ">32", "яблоки" или B4.
Замечания
-
Если "диапазон_усреднения" является пустым или текстовым значением, то функция СРЗНАЧЕСЛИМН возвращает значение ошибки #ДЕЛ/0!.
-
Если ячейка в диапазоне условий пустая, функция СРЗНАЧЕСЛИМН обрабатывает ее как ячейку со значением 0.
-
Ячейки в диапазоне, которые содержат значение ИСТИНА, оцениваются как 1; ячейки в диапазоне, которые содержат значение ЛОЖЬ, оцениваются как 0 (ноль).
-
Каждая ячейка в аргументе "диапазон_усреднения" используется в вычислении среднего значения, только если все указанные для этой ячейки условия истинны.
-
В отличие от аргументов range и criteria в функции AVERAGEIF, в функции AVERAGEIFS каждый criteria_range должен иметь тот же размер и форму, что и average_range.
-
Если ячейки в параметре "диапазон_усреднения" не могут быть преобразованы в численные значения, функция СРЗНАЧЕСЛИМН возвращает значение ошибки #ДЕЛ/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, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Студент |
Первый |
Второй |
Последний |
---|---|---|---|
Тест |
Тест |
Экзамен |
|
Оценка |
Оценка |
Оценка |
|
Климов |
75 |
85 |
87 |
Покровская |
94 |
80 |
88 |
Жданов |
86 |
93 |
Не выполнено |
Быков |
Не выполнено |
75 |
75 |
Формула |
Описание |
Результат |
|
=СРЗНАЧЕСЛИМН(B2:B5; B2:B5; ">70"; B2:B5; "<90") |
Средняя оценка за первый тест у всех студентов, которая находится в промежутке от 70 до 90 баллов (80,5). Оценка "Не выполнено" не учитывается при расчете, так как она не является числовым значением. |
75 |
|
=СРЗНАЧЕСЛИМН(C2:C5;C2:C5;">95") |
Средняя оценка за второй тест у всех студентов, которая выше 95 баллов. Так как нет оценок выше 95 баллов, возвращается значение #ДЕЛ/0!. |
#ДЕЛ/0! |
|
=СРЗНАЧЕСЛИМН(D2:D5;D2:D5,"<>неудовлетворительно";D2:D5;">80") |
Средняя оценка за последний экзамен для всех студентов, которая выше 80 баллов (87,5). Оценка "Не выполнено" не учитывается при расчете, так как она не является числовым значением. |
87,5 |
Пример 2
Тип |
Цена |
Город |
Количество спален |
Гараж? |
---|---|---|---|---|
Коттедж |
230000 |
Иркутск |
3 |
Нет |
Теремок |
197000 |
Омск |
2 |
Да |
Вилла |
345678 |
Омск |
4 |
Да |
Два этажа роскоши |
321900 |
Иркутск |
2 |
Да |
Вилла Тюдор |
450000 |
Омск |
5 |
Да |
Колониальная классика |
395000 |
Омск |
4 |
Нет |
Формула |
Описание |
Результат |
||
=СРЗНАЧЕСЛИМН(B2:B7; C2:C7; "Омск"; D2:D7; ">2"; E2:E7; "Да") |
Средняя цена дома в Омске как минимум с тремя спальнями и гаражом (397839) |
397839 |
||
=СРЗНАЧЕСЛИМН(B2:B7; C2:C7; "Иркутск"; D2:D7; "<=3"; E2:E7; "Нет") |
Средняя цена дома в Иркутске не более чем с тремя спальнями без гаража |
230000 |