Что означают ошибки в формулах Excel и как исправить код на листе заказа

Сумма заказа вдруг превращается в #Н/Д, а итог отчёта — в #ЗНАЧ!, и непонятно, где искать причину. Найти её помогает сам код: по справке Microsoft каждое значение ошибки описывает свою причину — от узкого столбца до данных не того типа^[1]. Код в итоговой ячейке часто пришёл из первой сломанной формулы цепочки, поэтому смотреть нужно с начала.

Путь к исправлению короткий: найти ячейку, где ошибка появилась первой, ввести или открыть формулу и прочитать код, проверить данные и аргументы, исправить причину и протянуть исправленную формулу на свои данные. Ошибки в формулах Excel не исчезают при пересчёте: их убирает только правка данных или самой формулы.

Ключевые факты: ошибки в формулах Excel

• Ошибка #Н/Д означает, что функции или формуле недоступно значение, а #ЗНАЧ! — что в формуле данные не того типа^[1].
• ЕСЛИОШИБКА перехватывает 7 значений ошибок, включая #Н/Д, #ЗНАЧ! и #ДЕЛ/0!^[5].
• ЕСНД обрабатывает только #Н/Д, остальные коды остаются видимыми^[6].
• Справка Microsoft не рекомендует скрывать ошибки: код часто сигнал, что нужно исправить данные^[4].
• Оператор плюс отличается от СУММ тем, что на тексте выдаёт #ЗНАЧ!, а СУММ текст пропускает^[4].

Какие бывают ошибки в формулах Excel и что означает каждая

Ошибки в формулах Excel сообщают код причины, по которому видно, что сломалось. Справка Microsoft описывает восемь значений^[1], и у каждого своя типичная причина.

Обнаруженная ошибка помечается треугольником в левом верхнем углу ячейки^[1]. Последовательно пройти все ошибки листа можно командой «Формулы → Проверка ошибок», а фоновый поиск включается в параметрах, в разделе «Формулы → Поиск ошибок»^[1]. Справка честно предупреждает: правила не гарантируют исправление всех ошибок на листе. В Excel для Mac фоновая проверка ошибок включается через меню Excel, а страница справки относится к Excel 2016, 2019, 2021, 2024 и Microsoft 365^[1]. Ошибки в формулах Excel в браузерной и настольной версиях обозначаются одними и теми же кодами: #Н/Д, #ЗНАЧ!, #ДЕЛ/0!, #ССЫЛКА!, #ИМЯ?, #ЧИСЛО! и #ПУСТО!.

Все ошибки ниже разобраны на одном листе — заказе по прайс-листу.

В C2 стоит формула =B2*ВПР(A2;$F$2:$G$4;2;0), протянутая вниз. Чай посчитался, а две следующие строки дали две разные ошибки^[2]: #Н/Д у «Какао» и #ЗНАЧ! у кофе. Ошибки в формулах Excel на этом листе типичны для любой выгрузки — товара нет в справочнике, а количество записано словом.

Разобравшись с кодами, переходим к двум самым частым — #Н/Д и #ЗНАЧ!.

#Н/Д и #ЗНАЧ! по шагам: поиск и тип данных

Ошибка #Н/Д означает, что функция поиска не нашла значение в таблице. В строке 3 ищется «Какао», а в прайсе его нет. По справке Microsoft главная причина #Н/Д — функции поиска ВПР, ПРОСМОТРX, ПОИСКПОЗ и другие не нашли искомое^[3].

1. Найдите первую ячейку с #Н/Д и откройте её формулу.
2. Проверьте, есть ли искомое значение в справочнике.
3. Сравните типы: число не равно тому же числу, сохранённому как текст.
4. Уберите лишние пробелы функцией СЖПРОБЕЛЫ.
5. Проверьте последний аргумент ВПР: для точного поиска нужен 0.

ВПР выдаёт #Н/Д, хотя товар есть в прайсе, чаще всего из-за лишнего пробела в конце названия. На листе-примере «Чай » с пробелом в конце дал #Н/Д, а формула со СЖПРОБЕЛЫ нашла цену и вернула 750^[2]. Ловушки поиска подробно разобраны на странице о функции ВПР и точном поиске, а очистку данных описывает страница об удалении лишних пробелов.

Ошибка #ЗНАЧ! значит, что что-то не так с записью формулы или с ячейками, на которые она ссылается^[4]. В строке 4 количество записано словом «две», и умножение текста на цену невозможно. Операторы +, -, *, / на тексте дают #ЗНАЧ!, а функции вроде СУММ текст пропускают^[4]. Такие ошибки в формулах Excel особенно часты после выгрузок, где числа приходят текстом.

Результаты получены на листе^[2]. Ошибка пропала, но итог неверен: СУММ молча не учла «две». Правильное решение — исправить данные, заменив слово числом. Повторите поиск причины в тренажёре: урок подставляет новые данные и проверяет, что формула не сломалась.

Однако остальные коды устроены иначе: у них причина почти всегда в структуре листа.

Чем отличаются #ДЕЛ/0!, #ССЫЛКА!, #ИМЯ? и решётки

Ошибка #ДЕЛ/0! появляется при делении на ноль или на пустую ячейку. Справка Microsoft описывает её именно так: число делится на ноль или на ячейку без значения^[1]. На листе обе ситуации дали #ДЕЛ/0!^[2].

Проверить делитель заранее можно функцией ЕСЛИ: =ЕСЛИ(E2;D2/E2;0) при пустом или нулевом E2 вернула 0^[2]. Вместо нуля можно вывести пустую строку или подсказку «нет данных».

Все примеры таблицы дали на листе указанные коды^[2]. #ССЫЛКА! появилась и у ВПР с номером столбца 3 при двух столбцах прайса. #ИМЯ? чаще всего значит опечатку: Excel не распознаёт текст формулы^[1], например функцию «СУМ».

Ошибка расходится по цепочке: формула, которая ссылается на ячейку с #Н/Д, тоже покажет #Н/Д. Искать причину нужно в первой ячейке, где код появился. В Windows помогает команда «Формулы → Проверка формулы → Вычисление формулы» — она показывает расчёт по шагам; справка описывает её только для Windows^[1]. Подробнее об инструментах — на странице о проверке формул по шагам.

Остаётся понять, когда код можно скрыть, а когда это опасно.

Сколько кодов скрывает перехват: 7 значений и ЕСНД

ЕСЛИОШИБКА подменяет любой из семи кодов ошибки заданным значением, но причину не устраняет. Справка перечисляет все семь перехватываемых значений — те же коды, что в таблице первого раздела, кроме решёток^[5]. Ошибка поиска у отсутствующего товара — нормальный случай для перехвата: товара действительно нет.

Синтаксис: =ЕСЛИОШИБКА(значение;значение_если_ошибка). Пустую ячейку во втором аргументе функция считает пустой строкой^[5].

На листе ЕСНД заменила #Н/Д у «Какао» на «нет в прайсе», а на строке со словом «две» оставила #ЗНАЧ!; ЕСЛИОШИБКА скрыла обе ошибки^[2]. Разница видна только на строках, где сломаны данные, поэтому проверять перехват нужно именно на них. ЕСНД обрабатывает только #Н/Д и в остальных случаях возвращает результат формулы^[6]. Для поиска по справочнику ЕСНД безопаснее: отсутствие товара подписывается, а сломанные данные остаются заметными.

ТИП.ОШИБКИ даёт номер кода: #ПУСТО! — 1, #ДЕЛ/0! — 2, #ЗНАЧ! — 3, #Н/Д — 7^[7]. Номер пригодится, когда разные коды нужно подписать разным текстом: отсутствие товара — одной подписью, деление на пустую ячейку — другой. В Google Таблицах есть те же функции IFERROR и IFNA^[8].

Именно поэтому перехват ставят последним шагом, когда причина уже понятна.

Когда перехват не подойдёт: скрытые промахи и ложные нули

Перехват ошибок не подойдёт, когда код ошибки сообщает о неверных данных. Справка Microsoft прямо пишет, что скрывать ошибки не рекомендуется: код часто сигнал, что что-то нужно исправить^[4].

• Если сбой вызван текстом вместо числа, перехват спрячет сигнал, а итог останется неверным.
• При поиске по справочнику ЕСЛИОШИБКА не подойдёт: она скроет и текст в количестве, а ЕСНД перехватит только отсутствие товара^[6].
• Если формула ссылается на удалённые ячейки, замена кода нулём даст ложный ноль в отчёте, и никто не заметит пропавший прайс.
• Когда делитель пуст, ноль вместо кода исказит среднее; честнее вывести пустую строку и заполнить данные.
• Если пропуск нужен на диаграмме, код поиска не перехватывают: справка отмечает, что такие значения на диаграмме не отображаются^[3].

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