Функция СРЗНАЧ (на английском AVERAGE) считает среднее арифметическое: складывает числа и делит на их количество. =СРЗНАЧ(B2:B7) вернёт среднее по столбцу. Пустые ячейки и текст функция пропускает, а нули учитывает — из-за этого среднее бывает ниже, чем вы ожидаете. Среднее без нулей считает =СРЗНАЧЕСЛИ(B2:B7;"<>0").
| Раздел функций | Статистические |
| Название на английском | AVERAGE |
| Версии Excel | Все версии Excel, включая Microsoft 365 |
| Волатильность | Не волатильная |
| Похожие функции | СРЗНАЧЕСЛИ, СРЗНАЧЕСЛИМН, МЕДИАНА, СУММПРОИЗВ, ОКРУГЛ |
Синтаксис функции СРЗНАЧ
=СРЗНАЧ(число1;[число2];…)
Аргументов может быть до 255: отдельные числа, ячейки, диапазоны вперемешку. =СРЗНАЧ(B2:B7;D2:D7;100) посчитает среднее по двум столбцам и числу 100 вместе. Если быстро нужно просто взглянуть на среднее, формула не обязательна: выделите ячейки, и Excel покажет «Среднее» в строке состояния внизу окна.
Как СРЗНАЧ считает пустые ячейки, текст и нули
Это главное, что нужно понимать о функции. Ноль — полноценное число и участвует в расчёте. Пустая ячейка и текст в диапазоне — нет, их как будто не существует.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Менеджер | Продажи | Как считаем | Результат | |
| 2 | Иванов | 120 | СРЗНАЧ | 118,4=СРЗНАЧ(B2:B7) | |
| 3 | Петрова | 210 | без нулей | 148=СРЗНАЧЕСЛИ(B2:B7;"<>0") | |
| 4 | Сидоров | 0 | пустые как нули | 98,67=СУММ(B2:B7)/ЧСТРОК(B2:B7) | |
| 5 | Орлова | 164 | |||
| 6 | Ким | ||||
| 7 | Лебедев | 98 |
Сумма 592. СРЗНАЧ делит её на 5: пустая ячейка у Кима не считается, ноль у Сидорова считается. Без нулей — 592 / 4 = 148. Если пустые тоже считать нулями, получится 592 / 6 ≈ 98,67.
В английском Excel: =AVERAGE(B2:B7)
Какой вариант правильный, зависит от смысла данных. Если ноль у Сидорова — реальный результат месяца, его нужно учитывать. Если ноль означает «нет данных», честнее СРЗНАЧЕСЛИ без нулей. А вот если в ячейке стоит «нет данных» текстом, СРЗНАЧ уже сама его пропустит.
Среднее по условию: СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИМН
Чтобы посчитать среднее только по части строк, есть родственные функции. Синтаксис у них такой же, как у СУММЕСЛИ и СУММЕСЛИМН:
| Задача | Формула |
|---|---|
| Средний чек по менеджеру Иванову | =СРЗНАЧЕСЛИ(A2:A100;"Иванов";C2:C100) |
| Среднее без нулей | =СРЗНАЧЕСЛИ(C2:C100;"<>0") |
| Среднее по сделкам больше 10 000 | =СРЗНАЧЕСЛИ(C2:C100;">10000") |
| Средний чек Иванова в Москве | =СРЗНАЧЕСЛИМН(C2:C100;A2:A100;"Иванов";B2:B100;"Москва") |
Обратите внимание на порядок аргументов: у СРЗНАЧЕСЛИ усредняемый диапазон стоит последним, у СРЗНАЧЕСЛИМН — первым. Это та же ловушка, что и у пары СУММЕСЛИ / СУММЕСЛИМН.
Взвешенное среднее
Обычное среднее цены по трём товарам и средняя цена одной продажи — разные вещи. Если одного товара продали 50 штук, а другого 10, их цены должны весить по-разному. Это считают через СУММПРОИЗВ:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Товар | Цена | Продано, шт. | Среднее | Значение | |
| 2 | Чай | 120 | 50 | простое | 216,67=СРЗНАЧ(B2:B4) | |
| 3 | Кофе | 450 | 10 | взвешенное | 137=СУММПРОИЗВ(B2:B4;C2:C4)/СУММ(C2:C4) | |
| 4 | Сахар | 80 | 40 |
Выручка 120×50 + 450×10 + 80×40 = 13 700, продано 100 штук — средняя цена продажи 137. Простое среднее 216,67 завышено дорогим кофе, которого продали меньше всего.
В английском Excel: =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)
Округлить среднее и другие полезные приёмы
Среднее почти всегда получается с длинным хвостом после запятой. Если результат идёт дальше в расчёты, округляйте его функцией ОКРУГЛ, а не форматом ячейки: формат меняет только вид, а число остаётся прежним.
| Задача | Формула |
|---|---|
| Среднее до 1 знака | =ОКРУГЛ(СРЗНАЧ(B2:B100);1) |
| Среднее только видимых строк после фильтра | =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(101;B2:B100) |
| Среднее, не боящееся ошибок в данных | =АГРЕГАТ(1;6;B2:B100) |
| Среднее трёх лучших результатов | =СРЗНАЧ(НАИБОЛЬШИЙ(B2:B100;{1;2;3})) |
| Середина без влияния выбросов | =МЕДИАНА(B2:B100) |
Последняя строка — повод задуматься. Если в продажах один менеджер сделал 2 миллиона, а остальные по 100 тысяч, среднее покажет картину, которой нет ни у кого. Медиана в таких случаях честнее.
Почему СРЗНАЧ считает неправильно
- #ДЕЛ/0! — в диапазоне нет ни одного числа. Часто это значит, что числа сохранены как текст: СРЗНАЧ их пропускает. Признак — числа прижаты к левому краю ячейки, лечится преобразованием текста в числа.
- Среднее не сходится с ручным расчётом — часть чисел текстом, и функция посчитала только «настоящие». Сравните СЧЁТ(B2:B100) с количеством заполненных строк.
- Среднее ниже ожидаемого — в расчёт попали нули, которые по смыслу означают «нет данных».
- После фильтра среднее не меняется — СРЗНАЧ учитывает скрытые строки. Нужна ПРОМЕЖУТОЧНЫЕ.ИТОГИ(101;…).
- #Н/Д или #ЗНАЧ! — ошибка внутри диапазона «заражает» результат. Обойти можно через АГРЕГАТ с параметром 6.
Функция СРЗНАЧ — частые вопросы
Как посчитать среднее в Excel?
Функцией СРЗНАЧ: =СРЗНАЧ(B2:B100). Или выделите ячейки и посмотрите «Среднее» в строке состояния.
Как СРЗНАЧ называется на английском?
AVERAGE. Формула =СРЗНАЧ(B2:B100) в английском Excel — =AVERAGE(B2:B100).
Учитывает ли СРЗНАЧ пустые ячейки?
Нет, пустые ячейки и текст пропускаются. Нули учитываются.
Как посчитать среднее без нулей?
Формулой =СРЗНАЧЕСЛИ(B2:B100;"<>0").
Почему СРЗНАЧ выдаёт #ДЕЛ/0!?
В диапазоне нет ни одного числа — например, все числа сохранены как текст.
Самая коварная ошибка СРЗНАЧ — числа, сохранённые как текст: функция их молча пропускает, и среднее получается «почти правильным». !SEMTools превращает такие числа в настоящие прямо в исходных ячейках, во всём столбце сразу.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: функция СРЗНАЧ в Excel
| Задача | Формула |
|---|---|
| Среднее арифметическое | =СРЗНАЧ(B2:B100) |
| Без нулей | =СРЗНАЧЕСЛИ(B2:B100;"<>0") |
| По условию | =СРЗНАЧЕСЛИ(A2:A100;"Иванов";C2:C100) |
| Взвешенное | =СУММПРОИЗВ(B2:B100;C2:C100)/СУММ(C2:C100) |
| Округлённое | =ОКРУГЛ(СРЗНАЧ(B2:B100);1) |
| Только видимые строки | =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(101;B2:B100) |
Надеюсь, на этой странице вы нашли то, что искали, и среднее наконец сошлось с ожиданиями. Перед тем как доверять цифре, задайте себе два вопроса: что значат нули в данных и нет ли среди чисел текста. Ответ на них важнее выбора функции. Другие метрики — доли, накопительный итог, ABC-анализ — собраны в статье про статистические метрики в Excel.