Ошибка #Н/Д в Excel: все реальные причины и как найти проблему за 3 минуты

Ошибка #Н/Д в Excel — причины и пошаговое решение. Почему в Excel появляется ошибка #Н/Д: несовпадение типов данных, скрытые пробелы, диапазоны, частичный поиск, ВПР и ПОИСКПОЗ. Полная диагностика + файл для практики.

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

#Н/Д. Она не “ломает” файл. Она просто говорит: “Я не нашёл совпадение”. Но проблема в том, что совпадение вроде есть. Разберём все реальные причины, а не поверхностные советы.

Файл с 10 сценариями #Н/Д лежит в Telegram — там можно пройтись по каждому случаю.

Мини-кейс

Формула:

=ВПР(A2;F2:G100;2;ЛОЖЬ)

В таблице код 10025 есть. Но результат — #Н/Д. Почему?

Причина 1. Текст и число

Код в поиске — число. В таблице — текст. Визуально одинаково. Excel считает разным.

Проверка

=ЕЧИСЛО(A2) и =ЕЧИСЛО(F2) Если результат отличается — типы разные.

Решение №1

Привести к числу: =ЗНАЧЕН(A2)

Решение №2

Привести к тексту: =ТЕКСТ(A2;"0")

Причина 2. Скрытые пробелы

Особенно если данные пришли из CRM или PDF.

Проверка

=ДЛСТР(A2) Сравнить с ожидаемой длиной.

Решение

=СЖПРОБЕЛЫ(A2) или =ПОДСТАВИТЬ(A2;" ";"")

Причина 3. Ищем не в том диапазоне

Формула может выглядеть корректно, но диапазон не включает нужную строку.

Проверка

Выделить диапазон мышкой — действительно ли нужная строка внутри?

Решение

Расширить диапазон или преобразовать данные в таблицу (Ctrl+T).

Причина 4. Ищем приблизительно вместо точно

Если используется ИСТИНА — возможны странные результаты или #Н/Д.

Решение

Использовать ЛОЖЬ.

Причина 5. Формат ячейки “Общий” с ведущими нулями

Код 00125 и 125 — разные значения.

Проверка

Выделить ячейку → посмотреть формат.

Причина 6. Частичный поиск

Если используется ПОИСКПОЗ без точного совпадения:

=ПОИСКПОЗ(A2;F2:F100;0) — ищет точно
=ПОИСКПОЗ(A2;F2:F100;1) — приблизительно

Неправильный тип совпадения даёт #Н/Д.

Причина 7. Неразрывный пробел (невидимый враг)

Данные из браузера часто содержат символ CHAR(160).

Решение

=ПОДСТАВИТЬ(A2;СИМВОЛ(160);"")

Причина 8. Ссылки “поехали” при копировании

Формула копируется вниз, диапазон смещается.

Проверка

F2 → проверить фиксированные ссылки ($).

Причина 9. Поиск в “грязном” столбце

Если столбец содержит формулы, возвращающие пустую строку (“”), это не пустая ячейка. Excel может не находить совпадение.

Причина 10. Данные в разных регистрах

Иногда критично при точном сравнении.

Универсальный алгоритм диагностики #Н/Д

Когда видите #Н/Д:

1. Проверить тип данных
2. Проверить длину строки
3. Проверить диапазон
4. Проверить режим поиска
5. Проверить пробелы
6. Проверить ссылки

Идти сверху вниз. Не менять всё сразу.

Контроль результата

После исправления:

1. Проверить 5 значений вручную.
2. Сравнить с фильтрацией.
3. Убедиться, что нет дубликатов.

В Telegram — файл с 10 сценариями ошибки #Н/Д:
— текст vs число
— пробелы
— неверный диапазон
— частичный поиск
— невидимые символы
— и ещё несколько реальных ловушек

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

Напишите: чаще у вас #Н/Д из-за типов данных или пробелов?

Подпишитесь на наш Телеграм, чтобы не пропускать выход новых гайдов, шаблонов и сек-листов.