Ошибка — это диагноз, а не проблема
Худшее, что можно сделать с ошибкой, — спрятать её. Ячейка с #Н/Д честно сообщает: «этого артикула нет в справочнике». Если накрыть её ЕСЛИОШИБКА(...;0), сообщение исчезнет, а проблема останется — и уедет в итог отчёта в виде нуля.
Поэтому порядок такой: сначала понять причину, потом решить, что показывать пользователю. Никогда наоборот.
ЕСЛИОШИБКА перехватывает любую ошибку — и «нет в справочнике», и «здесь текст вместо числа», и «формула ссылается на удалённый столбец». Вы думаете, что обработали отсутствующие значения, а на деле замазали и настоящие поломки.
Семь ошибок и что они означают
#Н/Д#ЗНАЧ!#ДЕЛ/0!#ССЫЛКА!#ИМЯ?#ЧИСЛО!#ПУСТО!##### в этом списке нет намеренно — это не ошибка. Так Excel говорит, что число не помещается в ширину столбца. Двойной клик по границе заголовка — и всё. Данные при этом целы.
#Н/Д — самая полезная ошибка
Появляется, когда ВПР или ПОИСКПОЗ не нашли искомое. В 90% случаев значение на самом деле есть, но Excel считает его другим. Три причины по частоте.
- Число записано как текстАртикул «1024» в одном файле — число, в другом — текст. Визуально одинаковы. Признак: текст липнет к левому краю, число — к правому.
- Лишние пробелы«Иванов » с хвостовым пробелом не равен «Иванов». Классика выгрузок из 1С и CRM.
- Значения правда нетЕдинственный случай, когда #Н/Д работает как задумано: новая позиция ещё не заведена в справочник.
=ЕТЕКСТ(A2) ← ИСТИНА = число записано текстом
=ДЛСТР(A2) ← длина больше ожидаемой = есть пробелы
=A2=Прайс!A5 ← ЛОЖЬ при визуально одинаковых = типы разные
СЖПРОБЕЛЫ.Как обрабатывать правильно
Когда причина найдена и остаётся честное «значения нет», используйте ЕСНД, а не ЕСЛИОШИБКА. Она ловит только #Н/Д и оставит остальные ошибки видимыми.
=ЕСЛИОШИБКА(ВПР($A2; Прайс; 2; 0); 0) ← спрячет вообще всё
=ЕСНД(ВПР($A2; Прайс; 2; 0); "нет в прайсе") ← поймает только #Н/Д=ЕСЛИ(ЕНД(формула); "нет"; формула).Отчёт без ошибок — не тот, где их спрятали.
В модуле «Продвинутые навыки» разбираем диагностику формул на реальных файлах: где ломается, почему и как чинить, а не замазывать.
Открыть доступ на 48 часов →#ЗНАЧ! и #ДЕЛ/0! — ошибки данных, а не формул
#ЗНАЧ! — где-то текст вместо числа
Формула =C2*D2 падает, если в C2 лежит «1 024 шт.» или пробел, который выглядит как пустота. Excel не умеет умножать текст.
Формулы → Вычислить формулу → Вычислить
→ Excel пошагово покажет, на каком аргументе всё сломалось
Формулы → Влияющие ячейки
→ стрелками покажет, откуда формула берёт данные
#ДЕЛ/0! — знаменатель пустой или нулевой
Считаете конверсию =Заказы/Визиты, а визитов в этот день не было. Это нормальная жизненная ситуация, и её нужно обработать осмысленно.
=ЕСЛИ([@Визиты]=0; ""; [@Заказы]/[@Визиты])
← пусто честнее нуля: конверсия не «ноль», её просто нет
Разница принципиальная. «Конверсия 0%» означает «люди приходили и не покупали». «Конверсии нет» означает «людей не было». Если подменить одно другим, средняя конверсия за месяц окажется заниженной — и решения будут приниматься по кривой цифре.
#ССЫЛКА! и #ИМЯ? — ошибки, которые вы сделали сами
#ССЫЛКА! — необратимая
Формула ссылалась на столбец D, столбец D удалили. Excel не знает, чем заменить ссылку, и пишет #ССЫЛКА! прямо внутрь формулы — исходный адрес теряется навсегда.
Отменить удаление сразу. Если файл сохранён и закрыт, восстановить адрес автоматически невозможно: придётся вспоминать, на что ссылалась формула, и писать заново. Это главный аргумент за то, чтобы не удалять столбцы в чужих файлах не глядя.
#ИМЯ? — опечатка или чужая версия
Две причины. Первая — банальная опечатка: =СУМА(A1:A9). Вторая интереснее: функция существует, но не в вашей версии Excel.
ХПР (XLOOKUP)#ИМЯ?УНИК, ФИЛЬТР, СОРТЕСЛИМН, ОБЪЕДИНИТЬОтсюда правило: файл, который уходит наружу, лучше собирать на функциях, доступных в старых версиях. Красивая ХПР превратится у получателя в #ИМЯ? по всему столбцу — подробнее в разборе ВПР против ИНДЕКС+ПОИСКПОЗ.
Три кнопки, которые находят причину за минуту
- Формулы → Проверка ошибокПройдёт по листу и покажет все проблемные ячейки списком, с подсказкой по каждой. Начинать стоит отсюда.
- Формулы → Вычислить формулуРазбирает длинную формулу пошагово: видно, на каком именно аргументе появляется ошибка. Незаменимо для вложенных ЕСЛИ.
- Формулы → Влияющие / Зависимые ячейкиРисует стрелки: откуда формула берёт данные и кто зависит от неё. Показывает, что сломается, если тронуть ячейку.
Ещё один приём: выделите диапазон и нажмите F5 → Выделить → Формулы → снимите все галочки кроме «Ошибки». Excel выделит только ячейки с ошибками — удобно, когда их надо найти на листе в 5 000 строк.
Разворачиваете строку — читаете ответ
1Так использовать ЕСЛИОШИБКА вообще нельзя?−
ЕСНД вместо ковровой ЕСЛИОШИБКА.