Средние значения в Excel: AVERAGE, AVERAGEA, AVERAGEIF и AVERAGEIFS

Ключевые варианты использования (поиск намерения)

• Как вычислить среднее арифметическое в Excel
• Отличия AVERAGE и AVERAGEA
• Среднее по условию: AVERAGEIF и AVERAGEIFS
• Советы по обработке пустых ячеек и ошибок
• Альтернативы: SUM/COUNT, SUBTOTAL, FILTER/AVERAGE

Что такое «среднее» в Excel — коротко

AVERAGE — арифметическое среднее: сумма чисел делится на количество чисел. Если в диапазоне есть пустые ячейки, текст или логические значения — AVERAGE их игнорирует. AVERAGEA учитывает логические значения и текст (как 0 для большинства текстов, 1 для TRUE, 0 для FALSE). AVERAGEIF и AVERAGEIFS сначала фильтруют данные по одному или нескольким критериям, затем берут среднее от отобранных чисел.

Важно: в формулах нельзя допускать ошибки в поддиапазонах (например, #DIV/0!, #N/A, #REF!), иначе функция вернёт ошибку.

Содержание

• Как много функций «AVERAGE» в Excel
• AVERAGE — синтаксис и примеры
• AVERAGEA — когда применять и как работает
• AVERAGEIF — среднее по одному условию
• AVERAGEIFS — среднее по нескольким условиям
• Альтернативы и приёмы: SUM/COUNT, SUBTOTAL, FILTER, массивы
• Частые ошибки и как их избегать
• Быстрые приёмы интерфейса (ленты) и локализация UI
• Чек-листы для ролей (аналитик, учитель, финансист)
• Руководство: SOP для надёжного расчёта среднего
• Дерево принятия решения (Mermaid)
• Критерии приёмки и тест-кейсы
• FAQ

Сколько функций AVERAGE существует в Excel?

В Excel обычно используют четыре функции с названием «AVERAGE» (или эквиваленты в локализованных версиях):

• AVERAGE — арифметическое среднее, игнорирует нечисловые значения и пустые ячейки.
• AVERAGEA — среднее, учитывает числа, текст и логические значения.
• AVERAGEIF — среднее по одному условию.
• AVERAGEIFS — среднее по нескольким условиям.

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

AVERAGE — как и когда использовать

Синтаксис:

=AVERAGE(A, B)

Где A — первый аргумент (число, ссылка на ячейку или диапазон), B — до 255 дополнительных аргументов.

Пример: у нас 7 студентов, их баллы в столбце B. Чтобы взять среднее для B2:B8:

=AVERAGE(B2:B8)

Важно:

• AVERAGE включает нули (0) в вычисление.
• AVERAGE игнорирует пустые ячейки, текст и логические значения (TRUE/FALSE).
• Если в диапазоне есть ошибка (например, #DIV/0!), результат будет ошибкой.

Практический приём: если нужно игнорировать нули, используйте сочетание SUM и COUNTIF, либо AVERAGEIF с условием “<>0” (см. раздел AVERAGEIF).

Простой альтернативный расчёт среднего (контроль):

=SUM(B2:B8)/COUNT(B2:B8)

Это даёт тот же результат, что и AVERAGE, при условии что в диапазоне только числа и пустые ячейки.

Быстрый способ в интерфейсе: выделите диапазон, перейдите на вкладку «Главная», в группе «Правка» нажмите на стрелку справа от кнопки Σ (Автосумма) и выберите “Среднее”. Результат появится в ячейке под или рядом с диапазоном.

Важно: локальные наименования вкладок зависят от версии Excel; в русской локали это “Главная” → группа “Правка” → Автосумма → “Среднее”.

AVERAGEA — когда текст и логика важны

Синтаксис совпадает с AVERAGE:

=AVERAGEA(A, B)

Отличие в том, что AVERAGEA учитывает:

• TRUE как 1;
• FALSE как 0;
• текст как 0 (если это не числовая строка, например “FOUR” считается 0);
• пустые ячейки игнорируются.

Пример: если в столбце оценок одна ячейка содержит FALSE (ученик пришёл, но отметка — FALSE) и одна ячейка пуста, AVERAGEA посчитает FALSE как 0 и включит его в знаменатель при делении.

=AVERAGEA(B2:B8)

Когда применять:

• Когда логические значения в ячейках несут смысл (например, TRUE = успешно = 1).
• Когда текстовые метки должны считаться как 0 (и вы это ожидаете).

Когда не применять:

• Если текст в ячейках — опечатки или метки, которые не должны влиять на среднее как 0; в этом случае лучше очистить данные или использовать фильтрацию/условие.

AVERAGEIF — среднее по одному условию

Синтаксис:

=AVERAGEIF(range, criteria, [average_range])

Где:

• range — диапазон, в котором Excel проверяет условие;
• criteria — условие (строка с оператором обычно в кавычках, например “>10”);
• average_range — необязательный диапазон значений, которые будут усреднены (если не указан, усредняется range).

Примеры:

1. Среднее по оценкам выше 10:

=AVERAGEIF(B2:B8, ">10")

2. Среднее по оценкам студентов старше 12 лет (в B — оценки, в C — возраст):

=AVERAGEIF(C2:C8, ">12", B2:B8)

Объяснение: AVERAGEIF сначала фильтрует диапазон C2:C8 по критерию “>12”, затем усредняет соответствующие значения из B2:B8.

Правила для criteria:

• Операторы: >, <, =, <=, >=, <>
• Подстановочные знаки: ? (один символ), (любая последовательность). Чтобы искать фактический ? или , используйте ~ перед символом: “~?” или “~*”.
• Для сравнения с текстом используйте строки в кавычках: “=Green” или просто “Green”.

Ограничения и замечания:

• AVERAGEIF игнорирует логические значения в average_range, если они не являются числами; при этом поведение зависит от версии Excel и формата диапазона.
• Если нет совпадений по условию, функция возвращает ошибку #DIV/0!.

Как обойти: используйте сочетание IFERROR, например:

=IFERROR(AVERAGEIF(C2:C8, ">12", B2:B8), "Нет совпадений")

AVERAGEIFS — среднее по нескольким условиям

Синтаксис:

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

Где average_range — диапазон, который усредняется; затем идут пары диапазон-критерий. Поддерживается до 127 пар диапазон/критерий.

Пример: усреднить оценки учеников из “Green” House старше 10 лет (B — оценки, C — дом, D — возраст):

=AVERAGEIFS(B2:B8, C2:C8, "Green", D2:D8, ">10")

Особенности:

• В критериях TRUE считается как 1, FALSE как 0 при усреднении.
• Подстановочные знаки и экранирование работают так же, как в AVERAGEIF.
• Если для всех условий нет подходящих строк — результат #DIV/0!.

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

Частые ошибки и как их исправить

• Ошибка #DIV/0!: нет подходящих значений для усреднения. Решение: проверить критерии, добавить IFERROR или условие для проверки количества совпавших строк.

• Формула учитывает 0, а вы этого не хотели. Решение: использовать AVERAGEIF(range, “<>0”) или SUM/COUNTIF.

• Текстовые числа: если числа хранятся как текст (например “10”), AVERAGE их игнорирует. Решение: привести к числовому типу (VALUE, умножить на 1 или использовать Text-to-Columns).

• Логические значения: AVERAGE игнорирует TRUE/FALSE, AVERAGEA учитывает; убедитесь, что выбираете нужную функцию.

• Скрытые/отфильтрованные строки: AVERAGE/AVERAGEA/AVERAGEIF/AVERAGEIFS учитывают данные в скрытых строках. Если нужно игнорировать отфильтрованные строки, используйте SUBTOTAL или функцию AGGREGATE в сочетании с дополнительными приёмами.

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

1. SUM/COUNT или SUM/COUNTIF (контрольный расчёт):

=SUM(B2:B8)/COUNT(B2:B8)
=SUM(B2:B8)/COUNTIF(B2:B8, "<>0")

2. SUBTOTAL — позволяют учитывать или игнорировать отфильтрованные строки. Номера функций SUBTOTAL:

• 1 — AVERAGE (включает скрытые строки);
• 101 — AVERAGE (игнорирует скрытые вручную строки и данные, скрытые фильтром).

Пример:

=SUBTOTAL(1, B2:B8)   // среднее, учитывая все видимые строки (вариант 1)
=SUBTOTAL(101, B2:B8) // среднее, игнорируя отфильтрованные строки (вариант 101)

3. FILTER + AVERAGE (Excel 365 / Excel 2021 и новее с динамическими массивами):

=AVERAGE(FILTER(B2:B8, C2:C8="Green"))

FILTER возвращает набор, удовлетворяющий условию, AVERAGE затем его усредняет. Это гибко и читаемо.

4. Массивные выражения (старые версии Excel):

=AVERAGE(IF(C2:C8="Green", B2:B8))

Вводится как формула массива (Ctrl+Shift+Enter) в старых версиях.

Разница между AVERAGE и AVERAGEA — таблица поведения

• Числа: оба учитываются.
• Пустые ячейки: оба игнорируют.
• Текст (нечисловой): AVERAGE игнорирует, AVERAGEA считает как 0.
• TRUE/FALSE: AVERAGE игнорирует, AVERAGEA считает TRUE=1, FALSE=0.

Практическое руководство: SOP для расчёта среднего (шаги)

1. Оцените данные: есть ли смешанные типы (текст, логика, пустоты)?
2. Решите, нужно ли включать логические значения или текст как 0.
3. Выберите функцию: AVERAGE / AVERAGEA / AVERAGEIF / AVERAGEIFS или комбинацию SUM/COUNT.
4. Тест: проверьте формулу на граничных наборах (все пустые, все нули, смешанные тексты).
5. Обработайте ошибки: добавьте IFERROR, если пользователю нужен дружественный вывод.
6. Документируйте формулу (комментарий в ячейке или отдельный лист с описанием).

Чек-лист перед публикацией файла:

• Все диапазоны фиксированы (если нужно использовать абсолютные ссылки).
• Учтены скрытые/отфильтрованные строки.
• Обработаны возможные ошибки (#DIV/0!, #VALUE!).
• Есть пояснение для нетривиальных критериев.

Дерево принятия решения (какую функцию выбрать)

flowchart TD
  A[Нужно вычислить среднее?] --> B{Данные только числа?}
  B -- Да --> C{Требуются критерии для фильтрации?}
  B -- Нет --> D{Логические/текстовые значения должны учитываться как 0/1?}
  C -- Нет --> E[Используйте AVERAGE]
  C -- Да --> F{Один критерий?}
  F -- Да --> G[Используйте AVERAGEIF]
  F -- Нет --> H[Используйте AVERAGEIFS]
  D -- Да --> I[Используйте AVERAGEA]
  D -- Нет --> J[Очистите данные или используйте AVERAGE после фильтрации]
  E --> K[Проверьте ошибки и пустые ячейки]
  G --> K
  H --> K
  I --> K
  J --> K

Критерии приёмки (что проверить перед сдачей отчёта)

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

Тест-кейсы

1. Набор: [10, 20, 30, пусто] → ожидание AVERAGE = 20.
2. Набор: [10, “text”, FALSE, пусто] → AVERAGE = 10; AVERAGEA учитывает FALSE как 0 → среднее = (10+0)/3 = 3.333…
3. AVERAGEIF на условие “>100” при отсутствии значений → #DIV/0! → обрабатывать через IFERROR.
4. AVERAGEIFS с двумя критериями, где одна пара диапазонов не совпадает по размерам → ошибка; убедитесь, что диапазоны одинаковой длины.

Роль‑ориентированные чек-листы

Аналитик данных:

• Понимает разницу между AVERAGE и AVERAGEA.
• Использует FILTER + AVERAGE в Excel 365.
• Документирует допущения о пустых и текстовых данных.

Учитель/преподаватель:

• Учитывает, что пропущенные тесты не равны нулю — выбирает AVERAGE.
• Если отсутствие оценки означает «неявка», учитывает это отдельно.

Финансист/бизнес-аналитик:

• Проверяет влияние выбросов; при необходимости использует TRIMMEAN или медиану.
• Учитывает фильтрацию по сегментам с AVERAGEIFS.

Советы по оптимизации и читаемости формул

• Используйте именованные диапазоны для сложных отчётов (например, Scores вместо B2:B1000).
• Разбивайте сложные условия на вспомогательные столбцы, чтобы формулы оставались читаемыми.
• Документируйте диапазоны и критерии в отдельной таблице параметров.

Совместимость и версия Excel

• AVERAGE и AVERAGEA доступны во всех распространённых версиях Excel.
• AVERAGEIF появился в более ранних версиях, AVERAGEIFS стал доступен начиная с Excel 2007 и во всех последующих релизах. Если используете очень старые версии, проверьте поддержку функций или используйте массивные выражения.

Примеры «когда это не работает» и обходные пути

1. Если данные содержат текст-числа (“10”), AVERAGE их игнорирует. Решение: привести тип с помощью VALUE или умножения на 1.
2. Если нужно исключить нули: AVERAGEIF(range, “<>0”).
3. Если нужно игнорировать отфильтрованные строки: используйте SUBTOTAL(101, range) для среднего по видимым строкам.

Короткая шпаргалка (cheat sheet)

• AVERAGE(range) — среднее по числам, игнорирует текст/пустые ячейки.
• AVERAGEA(range) — среднее, учитывает TRUE как 1, FALSE как 0, текст как 0.
• AVERAGEIF(range, criteria, [average_range]) — среднее по одному условию.
• AVERAGEIFS(average_range, criteria_range1, criteria1, …) — среднее по нескольким условиям.
• SUM/COUNT — контролируемый расчёт среднего.
• FILTER + AVERAGE — современный и гибкий при динамических массивах.

Быстрая справка по локализованному интерфейсу

В русской версии Excel:

• Вкладка «Главная» → группа «Правка» → значок Σ (Автосумма) → пункт “Среднее” для быстрого вставления формулы среднего.
• Локализованные названия функций: в русской локали функции называются по‑русски (СРЗНАЧ, СРЗНАЧА и т.д.). В англоязычной локали используйте AVERAGE, AVERAGEA, AVERAGEIF, AVERAGEIFS.

Примечание: при переносе файла между локализациями функции могут требовать конвертации (Excel автоматически конвертирует имена функций при открытии в другой локали, но при экспорте в текстовые формулы это важно учитывать).

Примеры формул — сборник для копирования

1. Среднее всех ненулевых значений:

=AVERAGEIF(B2:B100, "<>0")

2. Среднее по категории “Green” и возраст > 10:

=AVERAGEIFS(B2:B100, C2:C100, "Green", D2:D100, ">10")

3. Среднее видимых (неотфильтрованных) строк:

=SUBTOTAL(101, B2:B100)

4. Среднее с FILTER (Excel 365):

=AVERAGE(FILTER(B2:B100, (C2:C100="Green")*(D2:D100>10)))

FAQ

В: Чем отличается AVERAGE от AVERAGEA?

A: AVERAGE считает среднее только по числам и игнорирует текст и логические значения; AVERAGEA включает логические значения (TRUE=1, FALSE=0) и считает текст как 0.

В: Что делать, если AVERAGEIF возвращает #DIV/0!?

A: Это означает, что нет значений, соответствующих условию. Проверьте критерий или оберните формулу в IFERROR/IF(COUNTIF(…)=0, …).

В: Как исключить нули из среднего?

A: Используйте AVERAGEIF(range, “<>0”) или SUM/COUNTIF: SUM(range)/COUNTIF(range, “<>0”).

В заключение: выбор между AVERAGE, AVERAGEA, AVERAGEIF и AVERAGEIFS зависит от типа данных и требований к условиям. Всегда тестируйте формулы на граничных примерах и документируйте допущения в отчёте.

Краткое резюме и рекомендации:

• Используйте AVERAGE для обычного арифметического среднего по числам.
• Используйте AVERAGEA, если нужно учитывать логические значения и текст как часть расчёта.
• Для одного условия используйте AVERAGEIF, для нескольких — AVERAGEIFS.
• Для современных рабочих книг Excel 365 используйте FILTER + AVERAGE для большей гибкости.