Как найти среднее значение в Excel для столбца, групп и весов цен

Средний чек, средняя оценка, средняя цена закупки — показатель кажется простым, пока в таблице не появляются нули, пустые строки и группы разного размера. Справка Microsoft предупреждает, что нули СРЗНАЧ учитывает, а пустые ячейки диапазона пропускает^[1]. Из-за этой разницы два отчёта с одинаковыми числами могут показать разный результат.

Работа с функцией идёт по простому пути: найти ячейку под числами и выбрать на вкладке «Главная» пункт «Среднее» у кнопки «Автосумма», ввести или поправить формулу СРЗНАЧ, проверить, какие ячейки попали в расчёт, исправить нули, пустые строки и ошибку #ДЕЛ/0!, затем протянуть формулу на свои данные. Среднее значение в Excel после этого пересчитывается при каждой правке чисел.

Ключевые факты: среднее значение в Excel

• Среднее значение в Excel считает СРЗНАЧ: функция возвращает среднее арифметическое аргументов и принимает до 255 чисел, ссылок или диапазонов^[1].
• Для ряда 10, 7, 9, 27 и 2 формула =СРЗНАЧ(A2:A6) возвращает 11^[1].
• Нули функция учитывает, а пустые ячейки, текст и логические значения из диапазона пропускает^[1].
• СРЗНАЧЕСЛИ возвращает #ДЕЛ/0!, если ни одна ячейка не подходит под условие^[2].
• Средневзвешенная цена отличается от простого среднего тем, что общую стоимость делят на общее количество^[3].

Как посчитать среднее значение функцией СРЗНАЧ

СРЗНАЧ возвращает среднее арифметическое своих аргументов — сумму чисел, делённую на их количество^[1]. Синтаксис: =СРЗНАЧ(число1;[число2];…), обязателен только первый аргумент, а всего их может быть до 255^[1].

Справка показывает функцию на ряде 10, 7, 9, 27 и 2 в A2:A6 и строке 10, 15, 32 в A2:C2^[1]:

Так среднее значение в Excel легко проверить вручную: 10 + 7 + 9 + 27 + 2 = 55, делим на 5 и получаем 11. Вторая формула добавляет к ряду ещё одно число, поэтому делитель становится равен шести.

Быстрый путь без набора формулы описан в справке так^[3]:

1. Выберите ячейку под числами или справа от них.
2. На вкладке «Главная» в группе «Редактирование» нажмите стрелку рядом с кнопкой «Автосумма».
3. Выберите «Среднее» и нажмите Enter.

Для несмежных ячеек формулу пишут вручную и перечисляют диапазоны через точку с запятой: =СРЗНАЧ(A2:A4;A7)^[3]. Если среднее нужно лишь для проверки, достаточно выделить числа: строка состояния по умолчанию показывает Среднее, Количество и Сумму^[4]. Такое значение не сохраняется в ячейке, зато сверить с ним формулу удобно.

Результат часто получается с длинной дробью, как у простого среднего цен в примере ниже; два знака оставит вложенная функция: =ОКРУГЛ(СРЗНАЧ(F2:F4);2). Подробности о разрядах — на странице об округлении до нужного числа знаков. Посчитайте средний балл в тренажёре: урок меняет оценки и проверяет, что формула не делит сумму на число строк вручную.

Разобравшись с формулой, переходим к тому, что именно попадает в расчёт, — здесь кроется большинство расхождений.

Чем ноль отличается от пустой ячейки для СРЗНАЧ

СРЗНАЧ отличается поведением с нулём и пустой ячейкой: ноль входит в среднее как число, а пустая ячейка из диапазона пропускается^[1]. Текст и логические значения в диапазоне функция тоже пропускает^[1].

Пример на пяти ячейках:

СРЗНАЧ делит 210 на три числа и получает 70. Формула с делением на пять вручную даёт 42: она считает пустую ячейку и текст, которых функция не видит. Без нулей результат равен 105 — так справка предлагает исключать нулевые значения условием «<>0»^[3]. Поэтому среднее значение в Excel по одному и тому же столбцу меняется, если пустые ячейки заменить нулями или наоборот. Перед расчётом стоит решить, что означает пустота в ваших данных: «нет продаж» или «ещё не заполнено».

Аргументы-ошибки и текст, который нельзя превратить в число, приводят к ошибке всей формулы^[1]. В Google Таблицах логика та же: AVERAGE пропускает текст, а AVERAGEA считает его нулём^[5].

Однако иногда нули и пустые строки нужно не обсуждать, а отбросить по условию — для этого есть отдельная функция.

Среднее значение в Excel по условию: синтаксис СРЗНАЧЕСЛИ

СРЗНАЧЕСЛИ усредняет только ячейки, которые подходят под условие: =СРЗНАЧЕСЛИ(диапазон;условие;[диапазон_усреднения])^[2]. Если третий аргумент не указан, усредняется сам диапазон с условием^[2].

Если ни одна ячейка не подходит под условие, функция возвращает #ДЕЛ/0!^[2]. В примере региона «Запад» в таблице нет, поэтому вторая формула ломается. Пустые ячейки диапазона усреднения функция пропускает, а в условии понимает подстановочные знаки: вопросительный знак заменяет один символ, звёздочка — любую последовательность^[2].

Справка разбирает функцию на комиссиях 7000, 14000, 21000 и 28000: среднее комиссий меньше 23000 равно 14000, а средняя комиссия по объектам дороже 250000 — 24500^[2]. Для нескольких условий есть вариант с окончанием МН, он упомянут в той же справке^[1]. Так среднее значение в Excel считают по региону, менеджеру или месяцу без вспомогательных столбцов. Обе функции работают в Excel 2016, Excel 2019, Excel 2021, Excel 2024 и Microsoft 365, в том числе на Mac^[1].

Чтобы ошибка #ДЕЛ/0! не пугала читателя отчёта, формулу оборачивают в ЕСЛИОШИБКА с понятным текстом. Сначала стоит проверить само условие: лишний пробел в «Север » тоже даст ноль совпадений. Условие удобно держать в отдельной ячейке и ссылаться на неё — тогда один и тот же расчёт работает для любого региона из списка.

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

Средневзвешенное и «среднее средних»: пример с ценами и отделами

Средневзвешенное значение учитывает вес каждого числа: общую стоимость делят на общее количество, а не складывают цены^[3]. Справка показывает это на трёх заказах^[3]:

Простое значение по ценам равно 26,67, а взвешенное — 24,66, потому что дешёвых единиц продано больше и настоящая цена за единицу ниже.

Похожая ловушка — итог по готовым итогам групп. Два отдела: в первом зарплаты 100 и 200, во втором — 300, 400, 500 и 600.

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

Именно поэтому выбор показателя требует отдельного разбора: среднее подходит не для каждого набора данных.

Когда СРЗНАЧ не подойдёт: выбросы, текст и разные группы

СРЗНАЧ не подойдёт, если в данных есть выбросы, группы разного размера или текст, который нужно считать нулём. Справка Microsoft отмечает, что при смещённом распределении среднее, медиана и мода могут сильно различаться^[1].

• Если одна крупная сделка тянет показатель вверх, он перестаёт описывать типичный случай: для ряда 2, 3, 3, 5, 7, 10 оно равно 5, а серединное значение — 4^[1].
• При группах разного размера сведение готовых итогов даёт ошибку, как 300 вместо 350 в примере выше.
• Если текст и логические значения нужно считать, функция их не учтёт: для этого служит СРЗНАЧА^[1].
• Когда пустые ячейки на самом деле означают ноль, впишите 0 — иначе строка выпадет из расчёта.
• Если под условие СРЗНАЧЕСЛИ не попала ни одна строка, вместо числа появится #ДЕЛ/0!^[2].

Среднее значение в Excel — один из базовых показателей листа, а другие функции для расчётов собраны в каталоге формул Excel по типам задач.