В этой статье описаны синтаксис формулы и использование функции AVERAGEIFS в Microsoft Excel.
Описание
Возвращает среднее значение (среднее арифметическое) всех ячеек, которые соответствуют нескольким условиям.
Синтаксис
СРЗНАЧЕСЛИМН(диапазон_усреднения;диапазон_условий1;условие1;[диапазон_условий2;условие2];…)
Аргументы функции СРЗНАЧЕСЛИМН указаны ниже.
- Average_range Обязательно. Одна или несколько ячеек для вычисления среднего с числами или именами, массивами или ссылками, содержащими числа.
- Диапазон_условий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.
- Median , представляющий собой среднее число группы чисел; то есть половина чисел имеет значения, превышающие медиану, а половина чисел — значения, которые меньше медианы. Например, медианой для чисел 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 |
| Формула | Описание | Результат | |
| =AVERAGEIFS(B2:B5, B2:B5, ">70", B2:B5, "<90") | Средняя оценка за первый тест у всех студентов, которая находится в промежутке от 70 до 90 баллов (80,5). Оценка "Не выполнено" не учитывается при расчете, так как она не является числовым значением. | 75 | |
| =AVERAGEIFS(C2:C5, C2:C5, ">95") | Средняя оценка за второй тест у всех студентов, которая выше 95 баллов. Так как нет оценок выше 95 баллов, возвращается значение #ДЕЛ/0!. | #ДЕЛ/0! | |
| =AVERAGEIFS(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 | Нет |
| Формула | Описание | Результат | ||
| =AVERAGEIFS(B2:B7, C2:C7, "Bellevue", D2:D7, ">2", E2:E7, "Да") | Средняя цена дома в Омске как минимум с тремя спальнями и гаражом (397839) | 397839 | ||
| =AVERAGEIFS(B2:B7, C2:C7, "Issaquah", D2:D7, "<=3", E2:E7, "Нет") | Средняя цена дома в Иркутске не более чем с тремя спальнями без гаража | 230000 |