Как исправить ошибки #Н/Д, #ССЫЛКА!, #ЗНАЧ! в формулах
Практика4 минРедакция Innovialabs
Коротко
Каждая ошибка в таблице указывает на конкретный тип проблемы: #Н/Д — значение не найдено, #ССЫЛКА! — ссылка на удалённые ячейки, #ЗНАЧ! — неверный тип данных; найдите источник инструментами проверки формул и только потом решайте, нужно ли перехватывать ошибку.
Короткий ответ
Ошибка в ячейке — не сбой, а подсказка: код сообщает, что именно пошло не так. Порядок действий всегда один: понять код → найти ячейку-источник → исправить данные или формулу. Перехватывать ошибку через ЕСЛИОШИБКА стоит только тогда, когда она ожидаема.
В русском Excel ошибки показываются по-русски, в Google Таблицах и английском Excel — по-английски. Ниже указаны оба варианта.
Справочник ошибок
| Ошибка | Что значит | Типичные причины |
|---|---|---|
| #Н/Д (#N/A) | Значение не найдено | ВПР или ПОИСКПОЗ не нашли ключ; число и текст; лишние пробелы |
| #ССЫЛКА! (#REF!) | Ссылка на несуществующие ячейки | Удалили строку, столбец или лист; номер столбца в ВПР больше диапазона |
| #ЗНАЧ! (#VALUE!) | Неверный тип аргумента | Текст в арифметике; диапазоны разного размера в СУММЕСЛИМН; дата в виде текста |
| #ДЕЛ/0! (#DIV/0!) | Деление на ноль | Делитель равен нулю или пустой ячейке |
| #ИМЯ? (#NAME?) | Неизвестное имя | Опечатка в названии функции; текст без кавычек; функции нет в этой версии |
| #ЧИСЛО! (#NUM!) | Недопустимое число | Корень из отрицательного числа; итерационная функция не сошлась; слишком большое значение |
| #ПУСТО! (#NULL!) | Пустое пересечение диапазонов | Пробел вместо точки с запятой или двоеточия между ссылками (только Excel) |
| #ПЕРЕНОС! (#SPILL!) | Массиву некуда «разлиться» | Ячейки под формулой динамического массива заняты (Excel 365) |
| #ERROR! | Формула не разобрана | Ошибка синтаксиса: скобки, кавычки, разделители (Google Таблицы) |
Отдельно: ##### — это не ошибка, а слишком узкий столбец или отрицательная дата. Расширьте столбец.
Как исправлять самые частые ошибки
#Н/Д. Проверьте, что искомое значение действительно есть в таблице и совпадает побуквенно. Сравните ячейки формулой =A2=Справочник!A5. Уберите пробелы функцией СЖПРОБЕЛЫ, приведите типы: если ключ числовой, а хранится как текст, поможет ЗНАЧЕН. И проверьте, что в ВПР стоит точное совпадение — ЛОЖЬ в четвёртом аргументе.
#ССЫЛКА! Откройте формулу — в ней будет написано #ССЫЛКА! вместо адреса. Отмените удаление через Ctrl+Z, если это было только что, или перепишите ссылку. Чтобы ошибка не повторялась, используйте ИНДЕКС+ПОИСКПОЗ или ПРОСМОТРX вместо номеров столбцов и не удаляйте листы, на которые ссылаются другие.
#ЗНАЧ! Найдите аргумент неправильного типа. Частый случай — числа с пробелами или с точкой вместо запятой после выгрузки из другой системы. Используйте ЕЧИСЛО, чтобы проверить, число ли в ячейке, и сверьте размеры всех диапазонов в функциях с условиями.
#ДЕЛ/0! Проверяйте делитель явно:
=ЕСЛИ(B2=0; ""; A2/B2)
#ИМЯ? Проверьте название функции и кавычки вокруг текста. Важно: в русском Excel функции пишутся по-русски и с «;», а формулы, скопированные из англоязычных инструкций, нужно адаптировать.
ЕСЛИОШИБКА и ЕСНД
- ЕСЛИОШИБКА (IFERROR) перехватывает любую ошибку:
=ЕСЛИОШИБКА(A2/B2; 0). - ЕСНД (IFNA) перехватывает только #Н/Д:
=ЕСНД(ВПР(A2; Товары!A:C; 3; ЛОЖЬ); "Нет в каталоге").
Правило простое: для поиска используйте ЕСНД. ЕСЛИОШИБКА скроет и #ССЫЛКА!, и #ИМЯ?, то есть настоящие поломки формулы, и вы получите тихо неверный отчёт. Не возвращайте 0 там, где «не найдено» и «ноль» означают разное: это исказит суммы и средние.
Инструменты проверки формул
Excel, вкладка «Формулы»:
- Влияющие ячейки и Зависимые ячейки — стрелки показывают, откуда формула берёт данные и куда они уходят.
- Вычислить формулу — пошаговый разбор: видно, на каком шаге появляется ошибка.
- Проверка наличия ошибок — обходит ошибки на листе и предлагает варианты исправления.
- Показать формулы (Ctrl+`) — формулы вместо значений во всех ячейках.
- Окно контрольного значения — следит за ключевыми ячейками с других листов.
Google Таблицы:
- наведите курсор на ячейку с ошибкой — появится пояснение с причиной;
- выделите ячейку и нажмите F2: ссылки в формуле подсвечиваются цветными рамками;
- Вид → Показать формулы (Ctrl+`);
- пошагового вычислителя нет, поэтому длинную формулу удобно разбить на части во вспомогательных ячейках и найти, какая часть даёт ошибку.
Ошибка «распространяется»: если исходная ячейка даёт #Н/Д, все зависимые формулы покажут то же самое. Поэтому идите от итога к источнику по цепочке влияющих ячеек, пока не найдёте первую ошибку.