СРЗНАЧЕСЛИМН (функция СРЗНАЧЕСЛИМН)

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

Описание

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

Синтаксис

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

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

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

  • Диапазон_условий1, диапазон_условий2, …    параметр "диапазон_условий1" — обязательный, остальные диапазоны условий — нет. От 1 до 127 интервалов, в которых проверяется соответствующее условие.

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

Замечания

  • Если "диапазон_усреднения" является пустым или текстовым значением, то функция СРЗНАЧЕСЛИМН возвращает значение ошибки #ДЕЛ/0!.

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

  • Ячейки в диапазоне, которые содержат значение ИСТИНА, оцениваются как 1; ячейки в диапазоне, которые содержат значение ЛОЖЬ, оцениваются как 0 (ноль).

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

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

  • Если ячейки в параметре "диапазон_усреднения" не могут быть преобразованы в численные значения, функция СРЗНАЧЕСЛИМН возвращает значение ошибки #ДЕЛ/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, а затем — клавишу Enter. При необходимости измените ширину столбцов, чтобы видеть все данные.

Студент

Первый

Второй

Последний

Тест

Тест

Экзамен

Оценка

Оценка

Оценка

Климов

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

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

Нужны дополнительные параметры?

Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.

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