Как использовать AVERAGEIF и AVERAGEIFS в Excel

Быстрые ссылки

• Как использовать AVERAGEIF в Excel

• Как использовать AVERAGEIFS в Excel

Excel-функции AVERAGEIF и AVERAGEIFS помогают найти среднее значение в наборе данных, но в отличие от простой функции AVERAGE они позволяют включать или исключать значения по заданным условиям. В этой статье вы найдёте синтаксис, примеры, типичные ошибки, советы по локали и набор практических чек-листов и методик для разных ролей.

Как использовать AVERAGEIF в Excel

AVERAGEIF позволяет вычислить среднее значение в диапазоне, отфильтрованном по одному условию.

Синтаксис AVERAGEIF

=AVERAGEIF(*x*,*y*,*z*)

где

• x (обязательно) — диапазон для проверки по критерию,
• y (обязательно) — критерий для диапазона x,
• z (необязательно) — диапазон значений для усреднения, если критерий выполнен. Если z опущен, Excel усреднит значения из x.

Важно: в англоязычной версии Excel аргументы обычно разделяются запятыми. В русской локали часто используется точка с запятой. Например: =AVERAGEIF(Table1[Age]; “>40”; Table1[Salary]).

AVERAGEIF на примере

Предположим, в таблице Excel 12 человек с их именами, возрастом и зарплатой. Требуется найти среднюю зарплату людей старше 40 лет.

В этой задаче столбец B — это диапазон для проверки (аргумент x), критерий — “>40” (аргумент y), а столбец C — диапазон для усреднения (аргумент z).

=AVERAGEIF(Table1[Age],">40",Table1[Salary])

Если вы работаете в русской локали Excel, формула может выглядеть так:

=AVERAGEIF(Table1[Age];">40";Table1[Salary])

Разбор формулы:

• В примере используются структурированные ссылки Table1[Age] и Table1[Salary]. Это удобнее, чем явные диапазоны (например, B2:B13 и C2:C13), потому что при добавлении строк таблица автоматически расширится и формула учтёт новые значения.
• Критерии с логическими операторами (>, <, >=, <=) должны быть в кавычках.

На что обратить внимание при использовании AVERAGEIF

• Критерий (y) гибкий: можно использовать число, текст, шаблон с подстановочными символами, ссылку на ячейку.
• Пустые ячейки игнорируются при расчёте среднего. Ноль учитывается как значение.
• Если ни одна ячейка не удовлетворяет условию, функция вернёт ошибку #DIV/0!.

Примеры типов критериев:

(в таблице кавычки показаны как стандартные; в коде формул они присутствуют только для логических и текстовых критериев)

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

Как использовать AVERAGEIFS в Excel

Когда нужно фильтровать по нескольким условиям одновременно, используйте AVERAGEIFS. Функция учитывает только те значения, которые удовлетворяют всем заданным критериям.

Синтаксис AVERAGEIFS

=AVERAGEIFS(x, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

или, в более иллюстративной форме:

=AVERAGEIFS(*x*, *y¹*, *y²*, *z¹*, *z²*, ...)

где

• x — диапазон для усреднения (обязательно),
• y¹ — первый диапазон для проверки (обязательно),
• y² — критерий для y¹,
• z¹ — второй диапазон для проверки (необязательно),
• z² — критерий для z¹ (обязательно, если указан z¹).

Заметьте: порядок аргументов в AVERAGEIFS отличается от AVERAGEIF — первым всегда идёт диапазон значений для усреднения.

AVERAGEIFS на примере

Допустим, в таблице есть имя, возраст, пол и зарплата. Нужно найти среднюю зарплату мужчин старше 35 лет.

Формула выглядит так:

=AVERAGEIFS(Table2[Salary],Table2[Age],">35",Table2[Gender],"M")

или в русской локали:

=AVERAGEIFS(Table2[Salary];Table2[Age];">35";Table2[Gender];"M")

• Table2[Salary] — диапазон значений для усреднения;
• Table2[Age] и “>35” — первый диапазон и его критерий;
• Table2[Gender] и “M” — второй диапазон и его критерий.

На что обратить внимание при использовании AVERAGEIFS

• Все диапазоны критериев должны иметь одинаковый размер и выровнены по строкам (или по столбцам) с диапазоном для усреднения.
• Логические и текстовые критерии должны быть в кавычках.
• Учитываются только те ячейки, которые удовлетворяют всем условиям одновременно.
• Пустые ячейки игнорируются; ячейки со значением 0 включаются.
• Если ни одна ячейка не соответствует всем критериям, возвращается #DIV/0!.

Практические советы и подводные камни

• Разделители аргументов: в англоязычных версиях Excel используется запятая, в русской — часто точка с запятой. Если формула выдаёт ошибку, попробуйте заменить запятые на точки с запятой.
• Структурированные ссылки: при использовании таблиц (Insert > Table) удобно ссылаться на столбцы по заголовкам — формулы автоматически подхватят новые строки.
• Шаблоны подстановки: “*” соответствует любому набору символов, “?” — одному символу.
• Совместимость функций: AVERAGEIFS появилась в Excel 2007 и доступна во всех последующих версиях. Если вы работаете в старой программе, используйте альтернативы (см. ниже).

Частые ошибки

• Несовпадающие диапазоны по размеру — приведут к ошибке.
• Пропущенные кавычки вокруг текстовых критериев или логических выражений.
• Ожидание, что пустые строки считаются нулями (они игнорируются).

Альтернативные подходы и расширенные варианты

1. AVERAGE и AVERAGEA

• AVERAGE считает чисто арифметическое среднее для чисел.
• AVERAGEA учитывает логические значения и текст как числовые (TRUE = 1, FALSE = 0), что редко нужно для зарплат, но полезно при специфичных наборах данных.

2. Комбинация FILTER + AVERAGE (Excel 365/2021 и новее)

• В динамических версиях Excel можно сначала отфильтровать диапазон по правилам, а затем усреднить результат:

=AVERAGE(FILTER(Table1[Salary], (Table1[Age] > 40)))

В русской локали:

=AVERAGE(FILTER(Table1[Salary]; (Table1[Age] > 40)))

3. AGGREGATE и SUBTOTAL — для вычислений, игнорирующих скрытые строки или ошибки.

4. Формулы массива и SUMPRODUCT — альтернативы, когда нужно гибко комбинировать условия и выполнять более сложные вычисления.

Ментальная модель

• AVERAGE = среднее по всему набору.
• AVERAGEIF = фильтр по одному условию, затем среднее.
• AVERAGEIFS = фильтр по нескольким условиям, затем среднее.

При оценке: сначала представьте, какие строки должны остаться после фильтра; затем решите, какие столбцы усреднять.

Когда эти функции не подойдут

• Если нужно медиана или взвешенное среднее — используйте MEDIAN или SUMPRODUCT/SUM.
• Если данные содержат текст, который нужно интерпретировать как числа — предварительно преобразуйте формат.
• Если требуется игнорировать скрытые строки или агрегировать по группам — рассмотрите SUBTOTAL или сводные таблицы.

Небольшая методика по шагам

1. Проверьте исходные диапазоны: одинаковый размер и корректные заголовки.
2. Определите условия и протестируйте каждое отдельно (например, через фильтр или COUNTIF).
3. Вставьте формулу сначала с явными диапазонами, затем при переводе в таблицу замените на структурированные ссылки.
4. Проверяйте пограничные случаи: нули, пустые строки, нечисловые значения.
5. Если формула возвращает ошибку, пробуйте по одной убирать критерии, чтобы найти проблемный диапазон.

Чек-листы по ролям

• Для аналитика:

  • Убедиться, что диапазоны одинаковы по размеру.
  • Проверить формат чисел и наличие ошибок в диапазоне.
  • Протестировать формулу на подмножестве данных.

• Для HR (зарплаты и кадры):

  • Проверить дубликаты имён и неверный формат пола.
  • Убедиться, что пробелы и невидимые символы удалены (TRIM).
  • Сверить нулевые зарплаты с учётом контекста (отсутствие данных или реальный 0).

• Для финансового отдела:

  • Сверить агрегаты с суммами и средними за период.
  • Убедиться в корректной группировке по датам и округлении.
  • Документировать критерии расчёта для аудита.

Критерии приёмки

• Формула корректно рассчитывает среднее для тестового набора с известным результатом.
• Все используемые диапазоны совпадают по размеру.
• Обработаны пограничные случаи: пустые значения, нули, нечисловые данные.
• Формула документирована (комментарий к ячейке или вспомогательная таблица с критериями).

Тестовые случаи

• Один подходящий элемент и остальные пусты → возвращает значение этого элемента.
• Только нули в подходящих элементах → среднее положительное/нулевое согласно нулям.
• Ни одного подходящего элемента → #DIV/0!.
• Разные размеры диапазонов в AVERAGEIFS → ошибка.

Быстрый справочник по синтаксису для русской локали

• AVERAGEIF: =AVERAGEIF(диапазон;критерий;[диапазон_усреднения])
• AVERAGEIFS: =AVERAGEIFS(диапазон_усреднения;диапазон1;критерий1;[диапазон2;критерий2];…)

Факты и ограничения

• AVERAGEIFS поддерживает до 127 пар диапазон/критерий.
• Пустые ячейки игнорируются; нули включаются в расчёт.
• Функция доступна начиная с Excel 2007 и выше.

Диаграмма выбора функции

flowchart TD
  A[Нужно среднее?] --> B{Требуется фильтр по условию}
  B -->|Нет| C[AVERAGE]
  B -->|Да, одно условие| D[AVERAGEIF]
  B -->|Да, несколько условий| E[AVERAGEIFS]
  E --> F{Требуются сложные вычисления}
  F -->|Да| G[SUMPRODUCT / FILTER + AVERAGE]
  F -->|Нет| H[AVERAGEIFS]

Совместимость и миграция

• Если вы переносите файлы между локалями, проверьте разделители аргументов в формулах.
• При экспорте в CSV следите за тем, как записывается десятичный разделитель и чтобы структурированные ссылки были заменены на реальные диапазоны.
• Для старых версий Excel, где AVERAGEIFS недоступен, используйте комбинации AVERAGE и IF в виде формул массива или SUMPRODUCT.

Короткие советы по отладки

• Используйте функцию FORMULATEXT, чтобы увидеть текст формулы и понять, где ошибка.
• Вставьте вспомогательный столбец с логикой условия (например, =Table1[Age] > 40), затем проверьте, какие строки помечаются TRUE.

Краткое резюме

AVERAGEIF и AVERAGEIFS — это удобные средства для получения среднего значения в выборке, соответствующей одному или нескольким критериям. Они хорошо работают с таблицами и структурированными ссылками, но требуют внимательности к типу критериев, размерам диапазонов и локали Excel.

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