Функция AVERAGEIF в Excel — среднее по условию

Что делает AVERAGEIF

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

Короткая однострочная дефиниция: AVERAGEIF усредняет значения по одному критерию.

Синтаксис

=AVERAGEIF(range, criteria, [average_range])

• range — диапазон, в котором Excel проверяет условие.
• criteria — условие (число, выражение, текст или дата). Примеры: “>5”, “<>Apple”, “Apple”.
• average_range (необязательно) — диапазон значений, которые нужно усреднить. Если не задан, усредняется сам range.

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

Быстрый пример синтаксиса

=AVERAGEIF(A1:A7, ">5")
=AVERAGEIF(A1:A7, "Apple", B1:B7)
=AVERAGEIF(A1:A7, "<>Apple", B1:B7)
=AVERAGEIF(A1:A7, ">01.03.2021", B1:B7)

Совет: в международных или совместных книгах лучше писать условие для даты как “>” & DATE(2021,3,1) или использовать ссылку на ячейку с датой.

Примеры и пошаговые разъяснения

Пример с числом

Дано: в A1:A7 значения 10, 3, 4, 6, 7, 2, 11. Нужно усреднить только числа больше 5.

Формула:

=AVERAGEIF(A1:A7, ">5")

Логика: функция выберет 10, 6, 7, 11 и вернёт их среднее 8.5.

Текстовый критерий — пример 1

Есть список категорий в A1:A7 и числа в B1:B7. Нужно среднее для строк, где в A содержится “Apple”.

Формула:

=AVERAGEIF(A1:A7, "Apple", B1:B7)

Excel найдёт строки с “Apple” (например A2, A3, A7) и усреднит соответствующие значения в B2, B3, B7.

Текстовый критерий — пример 2 (исключение)

Усреднить значения в B1:B7, где в A1:A7 НЕ “Apple”:

=AVERAGEIF(A1:A7, "<>Apple", B1:B7)

Результат — среднее по всем строкам, кроме тех, где A = “Apple”.

Критерий по дате

Если нужно среднее для дат после 1 марта 2021 года (1 марта включительно или нет — укажите > или >=):

Ненадёжный вариант (зависит от локали):

=AVERAGEIF(A1:A7, ">01.03.2021", B1:B7)

Надёжный вариант:

=AVERAGEIF(A1:A7, ">" & DATE(2021,3,1), B1:B7)

Или указывайте ссылку на ячейку с датой, например:

=AVERAGEIF(A1:A7, ">" & $D$1, B1:B7)

Где в D1 записана дата 01.03.2021.

Полезные приёмы и шаблоны формул

• Подстановочные знаки для текста: “Ap“ — все значения, начинающиеся с “Ap”; “ple” — все значения, заканчивающиеся на “ple”; “?” — один символ.
• Логические операторы: “>”, “<”, “=”, “<>” можно комбинировать с числами и датами.
• Ссылки на ячейки: используйте “&” для конкатенации, например “>” & C1.
• Игнорирование TRUE/FALSE: AVERAGEIF не учитывает логические значения.

Когда AVERAGEIF даёт ошибку и как её обработать

• #DIV/0!: возникает, если ни одна ячейка не соответствует критерию (деление на ноль). Обработка:

=IFERROR(AVERAGEIF(range, criteria, [average_range]), "Нет совпадений")

• Пустой диапазон: если range пуст, вернётся ошибка. Проверяйте диапазон через COUNT или COUNTA.

=IF(COUNT(range)=0, "Нет данных", AVERAGEIF(range, criteria, average_range))

• Локаль дат: если формула с датой даёт неожиданный результат, используйте DATE или ссылку на ячейку.

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

• AVERAGEIFS — когда нужно усреднять по нескольким условиям.

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

• FILTER + AVERAGE (Excel 365 и Excel 2021) — когда нужна гибкая фильтрация с динамическим массивом:

=AVERAGE(FILTER(B1:B100, (A1:A100="Apple") * (C1:C100>0)))

Этот подход удобен для сложных логических комбинаций и кастомной обработки ошибок.

• AVERAGE + IF в старых версиях Excel (как формула массива):

{=AVERAGE(IF(A1:A7="Apple", B1:B7))}

(вставить как формулу массива в старых Excel: Ctrl+Shift+Enter)

Ментальные модели и эвристики

• Подумайте в две фазы: фильтрация (какие строки взять) и агрегация (что с ними сделать). AVERAGEIF покрывает обе фазы одной командой для простых сценариев.
• Всегда проверяйте, что диапазоны совпадают по размеру, если используется average_range — иначе получите ошибку.
• Для однотипных задач используйте AVERAGEIFS, чтобы не смешивать логику в одной формуле.

Проверка и критерии приёмки

Критерии приёмки для шаблона с AVERAGEIF:

• Формула возвращает ожидаемое среднее при наличии совпадений.
• При отсутствии совпадений формула не выдаёт #DIV/0!, а возвращает понятное сообщение или пустую ячейку.
• Даты обрабатываются однозначно: используются DATE или ссылки на ячейки.
• Размеры диапазонов average_range и range совпадают, если average_range задан.

Тестовые кейсы:

• Набор с явными совпадениями (числа > 5).
• Набор без совпадений → ожидаемая обработка ошибки.
• Текст с подстановочными знаками.
• Даты до и после граничной даты.

Рекомендации для разных ролей

Аналитик:

• Используйте именованные диапазоны; добавьте документацию к формуле.
• Для отчётов предпочтительнее AVERAGEIFS с явными условиями.

Бухгалтер:

• Проверяйте пустые ячейки и логические значения.
• Оборачивайте формулы в IFERROR, чтобы отчёт не ломался.

Разработчик отчётов:

• Для динамических панелей используйте FILTER+AVERAGE в Excel 365.
• Тестируйте на разных локалях для дат.

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

• AVERAGEIF присутствует в Excel 2007 и новее. AVERAGEIFS — с Excel 2007.
• FILTER доступен в Microsoft 365 / Excel 2021 и новее. При переносе книг учитывайте поддержку функций у пользователей.

Примеры распространённых ошибок и их исправление

• Ошибка: разные размеры диапазонов (range и average_range). Исправление: выровнять диапазоны по количеству строк.
• Ошибка: использование числовых критериев без кавычек при сравнении (корректно: “>5”, а не >5 в аргументе criteria).
• Ошибка: ожидание учёта TRUE/FALSE — AVERAGEIF их игнорирует; предварительно преобразуйте их в числа, если нужно.

Decision flow — какую функцию выбрать

flowchart TD
  A[Нужно среднее по условию?] --> B{Один критерий?}
  B -- Да --> C[AVERAGEIF]
  B -- Нет --> D{Несколько критериев?}
  D -- Да --> E[AVERAGEIFS]
  D -- Нет --> F{Excel 365 доступен?}
  F -- Да --> G[FILTER + AVERAGE]
  F -- Нет --> H[AVERAGE + IF 'формула массива']

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

• AVERAGEIF удобна для простых сценариев с одним критерием.
• Для нескольких критериев используйте AVERAGEIFS; для динамики — FILTER + AVERAGE.
• Всегда учитывайте локаль дат и выравнивайте размеры диапазонов.

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

Конец статьи.