Вычисляем среднее значение в Excel: от базовых формул до сложных условий

Чтобы быстро найти среднее арифметическое чисел в Excel, используйте функцию =AVERAGE(диапазон). Например, формула =AVERAGE(A1:A10) просуммирует все числа в ячейках от A1 до A10 и разделит результат на их количество. Пустые ячейки и текст при этом автоматически игнорируются. Если же нужно учесть только определенные значения (например, продажи выше 1000 рублей), применяются функции AVERAGEIF или AVERAGEIFS.

Базовый расчет: функция СРЗНАЧ (AVERAGE)

Это основной инструмент для получения средней величины. В русской версии Excel функция называется СРЗНАЧ, в английской — AVERAGE.

Синтаксис: =СРЗНАЧ(число1; [число2]; ...)

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

Примеры использования:

• Один диапазон: =СРЗНАЧ(B2:B20) — найдет среднее по столбцу B.
• Несколько диапазонов: =СРЗНАЧ(B2:B10; D2:D10) — усреднит данные из двух разных столбцов, пропуская пустоты между ними.
• Смешанный ввод: =СРЗНАЧ(B2:B10; 50) — посчитает среднее по диапазону и добавит к выборке число 50.

Условное среднее: СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИМН

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

1. Один критерий: СРЗНАЧЕСЛИ (AVERAGEIF)

Используется, когда условие применяется к одному диапазону, а усреднение проводится по другому (или тому же).

Формула: =СРЗНАЧЕСЛИ(диапазон_условия; условие; [диапазон_усреднения])

Практический пример: Нужно найти среднюю сумму продаж только для товаров категории "Ноутбуки".

• Столбец A: Категория
• Столбец B: Сумма продажи
• Формула: =СРЗНАЧЕСЛИ(A2:A100; "Ноутбуки"; B2:B100)

Условия можно задавать через операторы сравнения: ">1000", "<>0" (не равно нулю), "<"&C1 (меньше значения в ячейке C1).

2. Несколько критериев: СРЗНАЧЕСЛИМН (AVERAGEIFS)

Если нужно отфильтровать данные сразу по нескольким параметрам (например, "Ноутбуки" И "Продажи за 2025 год").

Формула: =СРЗНАЧЕСЛИМН(диапазон_усреднения; диапазон1; условие1; диапазон2; условие2; ...)

Важное отличие: Здесь первым аргументом всегда идет диапазон, по которому считается среднее, а затем пары "диапазон-условие".

Пример: Средняя зарплата менеджеров отдела "Продажи" со стажем более 3 лет. =СРЗНАЕСЛИМН(C2:C100; B2:B100; "Продажи"; D2:D100; ">3") Где: C — зарплаты, B — отделы, D — стаж.

Работа с фильтрами и скрытыми строками

Стандартные функции СРЗНАЧ и СРЗНАЧЕСЛИ считают среднее по всем данным в диапазоне, даже если строки скрыты вручную или отфильтрованы. Чтобы получить среднее только по видимым ячейкам после применения фильтра, используйте функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL).

Формула: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1; диапазон) или =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(101; диапазон)

• Цифра 1 включает в расчет вручную скрытые строки, но игнорирует отфильтрованные.
• Цифра 101 игнорирует и отфильтрованные, и вручную скрытые строки.

Это незаменимый инструмент для динамических отчетов и сводных таблиц.

Игнорирование ошибок в данных

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

1. Функция АГРЕГАТ (AGGREGATE) — самый современный метод. =АГРЕГАТ(1; 6; диапазон)

   • 1 — код функции СРЗНАЧ.
   • 6 — код игнорирования ошибок. Эта формула спокойно пропустит ячейки с ошибками и посчитает среднее по остальным числам.

2. Комбинация с ЕСЛИОШИБКА. Можно создать вспомогательный столбец, где формулой =ЕСЛИОШИБКА(исходная_ячейка; "") заменить ошибки на пустоту, а затем применить обычный СРЗНАЧ к этому новому столбцу.

Сравнение методов расчета среднего

Частые ошибки при вычислениях

• Несоответствие размеров диапазонов. В функциях СРЗНАЧЕСЛИ и СРЗНАЕСЛИМН все указанные диапазоны должны иметь одинаковое количество строк. Если один диапазон A1:A10, а другой B1:B11, формула вернет ошибку #ЗНАЧ!.
• Текст вместо чисел. Иногда числа хранятся в формате текста (часто бывает при выгрузке из 1С или банковских систем). Функция СРЗНАЧ их проигнорирует. Проверьте формат ячеек и при необходимости используйте "Текст по столбцам" для конвертации.
• Лишние пробелы в критериях. При поиске по тексту условие " Продажи " (с пробелами) не совпадет с ячейкой "Продажи". Используйте функцию СЖПРОБЕЛЫ для очистки данных или внимательнее проверяйте критерии.
• Путаница в аргументах СРЗНАЕСЛИМН. Помните: в этой функции диапазон для усреднения ставится первым, в отличие от СРЗНАЧЕСЛИ, где он последний.

FAQ

В чем разница между СРЗНАЧ и МЕДИАНА? СРЗНАЧ — это классическое среднее арифметическое (сумма делить на количество). Оно сильно чувствительно к выбросам (очень большим или маленьким значениям). МЕДИАНА находит значение, которое делит выборку ровно пополам. Если у вас есть аномальные выбросы (например, одна зарплата в миллион при остальных в 50 тысяч), медиана покажет более реалистичную картину "типичного" значения.

Как посчитать средневзвешенное значение? В Excel нет отдельной функции для этого, но она легко собирается из СУММПРОИЗВ и СУММ. Формула: =СУММПРОИЗВ(диапазон_цен; диапазон_весов) / СУММ(диапазон_весов). Например, средняя цена товара с учетом количества проданных единиц.

Можно ли использовать функцию СРЗНАЧ для дат? Да. Поскольку даты в Excel хранятся как числа, функция корректно вычислит среднюю дату. Результат может отобразиться как число (например, 45321). Просто примените к ячейке с результатом формат "Дата", чтобы увидеть читаемое значение.