Частые ошибки формул в Excel и как их избежать

Коды ошибок: что каждый из них значит

#ЗНАЧ! — неверный тип данных

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

Как избежать: проверьте, что данные в столбце действительно распознаются Excel как числа — выровненные по левому краю значения в числовом столбце обычно и есть текстовые «числа». Функция ЗНАЧЕН принудительно преобразует текст в число, если формат позволяет.

#Н/Д — значение не найдено

Чаще всего встречается у ВПР, ПОИСКПОЗ и похожих функций поиска: искомое значение не найдено в указанном диапазоне.

Как избежать: прежде чем чинить формулу, стоит проверить сами данные — искомое значение действительно есть в диапазоне поиска, написано без лишних пробелов и в том же регистре и формате (число как число, а не как текст)? Отдельная частая причина — диапазон поиска не захватывает нужные строки из-за неправильно заданных границ.

#ССЫЛКА! — ссылка стала недействительной

Возникает после удаления строки, столбца или листа, на который ссылалась формула — ссылаться больше не на что.

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

#ДЕЛ/0! — деление на ноль

Появляется при делении на ячейку, которая содержит 0 или пуста. Часто встречается в расчёте процентов или средних значений, когда знаменатель ещё не заполнен.

Как избежать: обернуть формулу в проверку — например, ЕСЛИ(B2=0;"";A2/B2) — чтобы при пустом или нулевом знаменателе формула не выдавала ошибку, а показывала пустую ячейку или заданное значение.

#ИМЯ? — Excel не узнаёт часть формулы

Чаще всего причина — опечатка в названии функции, отсутствующие кавычки вокруг текста внутри формулы или ссылка на именованный диапазон, который был удалён или переименован.

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

Логические ошибки: когда формула «работает», но считает неверно

Это более коварная категория — Excel не показывает никакого кода ошибки, потому что формально формула построена правильно, но результат неверный по смыслу.

• Относительные ссылки вместо абсолютных. При копировании формулы в другие ячейки диапазон сдвигается вместе с ней, если нужная ссылка не закреплена знаком доллара ($A$1) — из-за этого одна и та же формула в соседних ячейках может считать по разным диапазонам;
• ВПР находит первое совпадение, а не то, что нужно. Если в диапазоне поиска есть повторяющиеся значения, функция вернёт данные по первому найденному, даже если нужна была другая строка с тем же ключом;
• Округление ячейки — это не то же самое, что округление значения. Изменение количества отображаемых знаков после запятой (формат ячейки) меняет только то, что видно на экране; в расчётах по-прежнему участвует полное значение — из-за этого сумма округлённых на вид чисел может «не сходиться»;
• Дата принята за текст или наоборот. Если дата введена в формате, который Excel не распознал как дату (день и месяц перепутаны, нестандартный разделитель), с ней нельзя будет производить вычисления как с датой — а визуально ячейка при этом выглядит нормально.

Как проверять формулы, а не только их результат

• используйте инструмент «Формулы → Показать формулы» (или Ctrl+`), чтобы увидеть все формулы книги сразу, а не по одной;
• «Формулы → Вычислить формулу» показывает пошаговое вычисление сложной формулы — удобно, когда непонятно, на каком этапе результат становится неверным;
• сверяйте контрольные суммы — например, сумму по столбцу, посчитанную формулой, с ручным пересчётом на небольшом фрагменте данных.

Часто задаваемые вопросы

Что дальше

Если формулы в шаблоне часто ломаются от неаккуратного ввода — возможно, дело не в самих формулах, а в отсутствии защиты полей. Как это исправить — в статье «Как защитить данные в Excel-шаблоне от случайных ошибок».