Почему формула среднего значения в Excel выдает ошибку

Иван Корнев·12.04.2026·⏱4 мин

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

Для быстрого исправления оберните диапазон в функцию очистки ошибок или используйте условное усреднение. Ниже подробный разбор ситуаций и готовые формулы.

Краткий ответ: Ошибка #ЗНАЧ! появляется, если в диапазоне есть ячейки с ошибками вычислений. Неверное число получается, если текст воспринимается как ноль или если учтены скрытые строки. Используйте =СРЗНАЧ(ЕСЛИОШИБКА(...)) для игнорирования проблемных ячеек.

Основные причины сбоя расчетов

Понимание природы данных — ключ к решению. Функция СРЗНАЧ ведет себя специфично в разных ситуациях:

Наличие ошибок в ячейках. Это самая частая причина появления #ЗНАЧ!. Если в диапазоне A1:A10 ячейка A5 содержит ошибку (например, результат деления на ноль #ДЕЛ/0!), то СРЗНАЧ(A1:A10) тоже вернет ошибку.
Числа, записанные как текст. Иногда цифры импортируются из других систем с лишними пробелами или апострофами ('100). Функция СРЗНАЧ их просто игнорирует, из-за чего среднее значение становится заниженным (так как знаменатель уменьшается), но ошибка не возникает.
Пустые ячейки против нулей. СРЗНАЧ игнорирует пустые ячейки, но учитывает нули. Если вы визуально видите пустоту, а там стоит 0, это исказит результат в меньшую сторону.
Скрытые строки и фильтры. Обычная формула считает все ячейки диапазона, даже если строки скрыты фильтром. Для учета только видимых данных нужна функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

Как диагностировать проблему

Прежде чем переписывать формулу, проверьте содержимое диапазона.

Поиск ошибок: Выделите диапазон и нажмите F5 → «Выделить» → «Формулы» → «Ошибки». Excel подсветит проблемные ячейки.
Проверка типов данных: Используйте формулу =ЕЧИСЛО(A1). Если она возвращает ЛОЖЬ для ячейки, которая должна содержать число, значит, там текст или ошибка.
Подсчет значений: Сравните =СЧЁТ(диапазон) (считает только числа) и =СЧЁТЗ(диапазон) (считает все непустые). Если первое число значительно меньше второго, в диапазоне много текста.

Лайфхак с зеленым уголком: Если в левом верхнем углу ячейки виден зеленый треугольник, нажмите на него и выберите «Преобразовать в число». Это быстро исправит числа, сохраненные как текст.

Способы исправления и надежные формулы

В зависимости от типа проблемы используйте один из следующих подходов.

1. Игнорирование ошибок в диапазоне

Если в столбце есть ячейки с #Н/Д или #ЗНАЧ!, стандартная формула не сработает. Нужно отфильтровать их «на лету».

Для современных версий Excel (365, 2021+): Используйте динамические массивы и функцию ФИЛЬТР:

=СРЗНАЧ(ФИЛЬТР(A2:A100; ЕЧИСЛО(A2:A100)))

Эта формула создает временный массив только из чисел и считает среднее по нему.

Для старых версий Excel: Используйте комбинацию с ЕСЛИОШИБКА (вводится как формула массива через Ctrl+Shift+Enter в старых версиях):

=СРЗНАЧ(ЕСЛИОШИБКА(A2:A100; ""))

Или более строгий вариант, исключающий и текст, и ошибки:

=СРЗНАЧ(ЕСЛИ(ЕЧИСЛО(A2:A100); A2:A100))

2. Учет только видимых ячеек (при фильтрах)

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

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1; A2:A100)

Где цифра 1 соответствует функции СРЗНАЧ. Эта формула автоматически игнорирует скрытые фильтром строки.

3. Работа с весовым средним

При расчете взвешенного среднего (СУММПРОИЗВ / СУММ) ошибки в весах или значениях также ломают результат.

Безопасная формула:

=СУММПРОИЗВ(--ЕЧИСЛО(B2:B10); A2:A10; B2:B10) / СУММПРОИЗВ(--ЕЧИСЛО(B2:B10); B2:B10)

Здесь мы умножаем на логическое условие ЕЧИСЛО, которое превращается в 1 (истина) или 0 (ложь), тем самым обнуляя вклад ошибочных строк в сумму.

Таблица выбора функции

Ситуация	Рекомендуемая формула	Примечание
Обычный расчет	`=СРЗНАЧ(A1:A10)`	Игнорирует текст и пустоты, ломается на ошибках
В диапазоне есть ошибки (#Н/Д)	`=СРЗНАЧ(ЕСЛИОШИБКА(A1:A10;""))`	Требует ввода как формула массива в старых Excel
Применен фильтр	`=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1; A1:A10)`	Учитывает только видимые строки
Числа записаны как текст	`=СРЗНАЧ(--A1:A10)`	Принудительное преобразование (формула массива)
Современный Excel (365)	`=СРЗНАЧ(ФИЛЬТР(A1:A10; ЕЧИСЛО(A1:A10)))`	Самый надежный и читаемый вариант

Частые ошибки пользователей

Замена ошибок на ноль. Частая практика — использовать =ЕСЛИОШИБКА(ячейка; 0). Это опасно: ноль участвует в расчете среднего и сильно занижает результат. Лучше исключать такие ячейки из расчета полностью.
Неверный диапазон. При копировании формулы вниз диапазон может «поехать», если не закреплены ссылки (знак $), либо захватывать итоговую строку, создавая циклическую ссылку.
Разделители в формулах. В русской локали Excel аргументы функций разделяются точкой с запятой ;, а не запятой ,. Использование западного синтаксиса вызовет ошибку #ИМЯ?.

FAQ

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

Вопрос: Как посчитать среднее, исключая конкретное значение (например, нули)? Ответ: Используйте СРЗНАЧЕСЛИ: =СРЗНАЧЕСЛИ(A1:A10; "<>0"). Эта формула проигнорирует все нули в диапазоне.

Вопрос: Формула возвращает #ДЕЛ/0!. Что делать? Ответ: Это значит, что в диапазоне нет ни одного числа для усреднения (все пусто, текст или ошибки). Оберните формулу в проверку: =ЕСЛИОШИБКА(СРЗНАЧ(...); "Нет данных").