Ошибка #Н/Д в Excel и Google Sheets: Полное руководство

Кратко

Параметр	Описание
Ошибка	#Н/Д
Что означает	Значение не найдено (Not Available).
Чаще всего возникает при	ВПР, ПОИСКПОЗ, ПРОСМОТРX, ГПР
Сложность	★★☆☆☆
Исправляется	Да

Где встречается

Ошибка #Н/Д появляется во всех современных версиях электронных таблиц:

✓ Excel 365
✓ Excel 2024
✓ Excel 2021
✓ Excel 2019
✓ Excel 2016
✓ Google Sheets

Функции, вызывающие ошибку, работают одинаково на всех платформах. Различия в решениях — минимальны (отмечены отдельно в разделе «Ошибка #Н/Д в Google Sheets»).

Самые частые причины

В 90% случаев ошибка #Н/Д возникает из-за одной из этих пяти проблем:

✓ Значение отсутствует в таблице — самая частая причина, искомого элемента просто нет в источнике.
✓ Число хранится как текст — «123» и 123 для Excel разные сущности.
✓ Лишние пробелы — невидимые символы в начале или конце ячейки (« Иванов » вместо «Иванов»).
✓ Неразрывные пробелы — выгрузки из 1С, SAP и других ERP-систем часто содержат символ с кодом 160 вместо обычного пробела.
✓ Неверный четвертый аргумент ВПР — пропущен или равен ИСТИНА, а данные не отсортированы.

Функции, которые вызывают #Н/Д

Функция	Может вернуть #Н/Д	Примечание
ВПР (VLOOKUP)	Да	Классический источник ошибки при поиске с точным совпадением
ГПР (HLOOKUP)	Да	То же, что ВПР, но для горизонтального поиска
ПОИСКПОЗ (MATCH)	Да	Часто используется внутри ИНДЕКС + ПОИСКПОЗ
ПРОСМОТРX (XLOOKUP)	Да	Современная замена ВПР с встроенным обработчиком ошибки
ИНДЕКС + ПОИСКПОЗ (INDEX + MATCH)	Да	ПОИСКПОЗ внутри этой связки и дает #Н/Д
FILTER	Иногда	Может возвращать #Н/Д или другой результат в зависимости от версии и настроек формулы.

Почему возникает ошибка?

#Н/Д (Not Available) — это честный ответ Excel на запрос: «Я проверил весь диапазон, но не нашел то, что ты просил». В отличие от #ССЫЛКА! или #ЗНАЧ!, эта ошибка часто не свидетельствует о поломке формулы, а сигнализирует об отсутствии данных.

Причины делятся на три группы:

А. Значение действительно отсутствует
Функция поиска проверила каждую строку, но совпадение не обнаружено.
Пример: ВПР ищет «Груши» в столбце, где есть только «Яблоки» и «Сливы».

Б. Несовпадение типов данных (скрытая причина)
Визуально значения выглядят одинаково, но Excel воспринимает их как разные сущности.

• Число 123 vs текст '123 (с апострофом).
• Текст «Москва» vs текст «Москва » (с пробелом в конце).

В. Ошибка в аргументах функции

• Четвертый аргумент ВПР (интервальный просмотр) равен ИСТИНА или пропущен, а столбец поиска не отсортирован. Excel пытается искать приблизительно и «промахивается».
• Номер столбца в ВПР указан верно, но выходит за границы диапазона (это дает #ССЫЛКА!, а не #Н/Д — важное различие, см. Пример 4).
• Как диагностировать ошибку (Алгоритм)

Используйте этот пошаговый чек-лист, чтобы найти корень проблемы:

1. Поиск глазами и Ctrl+F.
   Откройте таблицу-источник и попробуйте найти искомое значение вручную. Не нашли? Переходите к шагу 5 (чистая #Н/Д).
2. Тест на типы данных.
   Выберите две визуально одинаковые ячейки (искомое и то, что нашли в источнике). В отдельной ячейке введите =A2=D10. Если результат ЛОЖЬ — это проблема типов или пробелов.
3. Проверка длины строки.
   Функция =ДЛСТР(A2) покажет истинное количество символов. Если видите 5 букв, а функция возвращает 6 — внутри есть невидимый символ.
4. Ревизия аргументов ВПР.
   Откройте формулу и проверьте: какой номер столбца указан? Не выходит ли он за правый край диапазона? Стоит ли ;0 (или ЛОЖЬ) четвертым аргументом?
5. Признание отсутствия.
   Если значение не найдено и тесты на шагах 2-4 не выявили проблем, значит данных действительно нет. Нужно заменить ошибку на понятный текст через ЕСЛИОШИБКА или аналоги.

Разбор реальных примеров и пошаговые способы решения

Пример 1: Классический случай — значения нет в таблице

Задача: Подтянуть цену по артикулу «Арт-999».
Формула: =ВПР("Арт-999"; B:C; 2; 0)
Результат: #Н/Д.
Диагностика: Ctrl+F в столбце B показывает ноль вхождений. Данных действительно нет.

Решение:
Оберните формулу в ЕСЛИОШИБКА (IFERROR), чтобы вместо ошибки выводилось понятное сообщение или пусто.

=ЕСЛИОШИБКА(ВПР("Арт-999"; B:C; 2; 0); "Товар не найден")

Если нужно скрывать только #Н/Д, используйте ЕСЛИНД (IFNA).
Если нужно скрывать любые ошибки — ЕСЛИОШИБКА (IFERROR).

Пример 2: Число как текст (зеленые треугольники)

Задача: Найти сумму по коду клиента 1234.
Формула: =ВПР(1234; F:G; 2; 0)
Результат: #Н/Д.
Визуально: Код 1234 в столбце F присутствует, но прижат к левому краю. В строке формул видно '1234.
Почему ошибка: Вы ищете число, а в таблице текст.

Способ А (Конвертация источника — правильный путь):

1. Выделите столбец F.
2. Перейдите: «Данные» → «Текст по столбцам».
3. Ничего не меняя, нажмите «Готово». Текстовые цифры станут числами.

Способ Б (Правка формулы — если источник трогать нельзя):

=ВПР(ТЕКСТ(1234; "0"); F:G; 2; 0)

Пример 3: Невидимые пробелы (обычные и неразрывные)

Задача: Найти менеджера «Иванов».
Формула: =ПОИСКПОЗ("Иванов"; A:A; 0)
Результат: #Н/Д.
Диагностика: =ДЛСТР(A4) показывает 7 при видимых 6 буквах. =A12="Иванов" возвращает ЛОЖЬ.

Решение для обычных пробелов:
Используйте функцию СЖПРОБЕЛЫ (TRIM) для очистки искомой ячейки или всего столбца.

=СЖПРОБЕЛЫ(A4)

Массовая очистка столбца: Выделите диапазон, Ctrl+H, в «Найти» пробел, «Заменить» — пусто. (Но это уберет и пробелы внутри текста, будьте аккуратны). Лучше использовать формулу СЖПРОБЕЛЫ рядом и скопировать результат как значения.

Решение для неразрывных пробелов (символ 160, частый гость из 1С):

=ПОИСКПОЗ(ПОДСТАВИТЬ(A4; СИМВОЛ(160); ""); A:A; 0)

Надежная комбинация для полной очистки:
=СЖПРОБЕЛЫ(ПОДСТАВИТЬ(A4; СИМВОЛ(160); " "))

Пример 4: Ошибка диапазона ВПР

⚠️ ВАЖНОЕ УТОЧНЕНИЕ:
Частая путаница у новичков. При указании номера столбца больше, чем ширина диапазона, возвращается #ССЫЛКА! (REF!), а не #Н/Д.

Формула: =ВПР(D2; A:B; 3; 0)
Ошибка: #ССЫЛКА!
Причина: Диапазон A:B содержит 2 столбца, а запрошен столбец номер 3.
Решение: Исправить номер столбца с 3 на 2 или расширить диапазон до A:C.

Почему мы говорим об этом здесь? Потому что это пограничный случай, который важно различать. Запомните: выход за границы диапазона — #ССЫЛКА!, отсутствие совпадения — #Н/Д.

Пример 5: Ошибка #Н/Д в ПРОСМОТРX (XLOOKUP)

Начиная с Excel 365 и Google Sheets, функция ПРОСМОТРX стала современным стандартом поиска. Она мощнее ВПР и имеет встроенный аргумент для обработки отсутствия данных.

Задача: Найти город по индексу.
Формула (без обработки):

=ПРОСМОТРX(123456; A:A; B:B)

Результат: #Н/Д (если индекса 123456 нет в столбце A).

Решение с использованием родного аргумента:
В отличие от ВПР, вам не нужна функция ЕСЛИОШИБКА. Четвертый аргумент ПРОСМОТРX создан специально для этого.

=ПРОСМОТРX(123456; A:A; B:B; ; "Индекс не найден")

Синтаксис: ПРОСМОТРX(что_ищем; где_ищем; что_возвращаем; [если_не_найдено]; [режим_совпадения]; [режим_поиска]).
Пятый аргумент (режим совпадения) по умолчанию 0 — точное совпадение, что исключает случайную потерю точности, как у ВПР с пропущенным четвертым аргументом.

Ошибка #Н/Д в Google Sheets

Google Sheets обрабатывает ошибку #Н/Д практически идентично Excel. Все примеры выше работают в Таблицах Google без изменений.

Особенности, которые важно знать пользователям Google Sheets:

1. Функция ПРОСМОТРX (XLOOKUP) полностью поддерживается. Решение из Примера 5 работает «из коробки».
2. ЕСЛИОШИБКА (IFERROR) и ЕСЛИНД (IFNA) работают одинаково. Синтаксис не отличается от Excel.
3. Диагностика пробелов. В Google Sheets также используйте =ДЛСТР() и =СЖПРОБЕЛЫ(). Проблема неразрывных пробелов из выгрузок 1С встречается и здесь.
4. Очистка данных. Инструмент «Текст по столбцам» в Google Sheets находится в меню: «Данные» → «Разделить текст на столбцы». Но проще всего использовать функцию =ЗНАЧЕН() для конвертации текста в число.

Пример для Google Sheets (текст в число):

=ЗНАЧЕН(A2)

Эта функция преобразует текстовую строку '1234 в число 1234.

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

В: ВПР выдает #Н/Д, но значение точно есть. В чем подвох?
О: В 80% случаев это пробелы или текст-как-число. Используйте =ДЛСТР() для диагностики и «Текст по столбцам» для лечения чисел.

В: Почему ВПР работает для одних строк и выдает #Н/Д для других?
О: Скорее всего, вы забыли закрепить диапазон поиска. Если формула =ВПР(A2; B:C; 2; 0) растягивается вниз, она превращается в =ВПР(A3; B:C; 2; 0), что правильно. Но если диапазон относительный (без знаков $), он «уедет». Используйте $B:$C.

В: Можно ли отличить «честную» #Н/Д от ошибки формулы?
О: Только проверкой. Если значение есть, а ошибка есть — ошибка формулы. Если значения нет — #Н/Д честная. Используйте ЕСЛИОШИБКА для управления отображением.

В: Что лучше: ЕСЛИОШИБКА или ЕСЛИНД?
О: ЕСЛИНД (IFNA) — специальная функция, которая ловит ТОЛЬКО #Н/Д. ЕСЛИОШИБКА (IFERROR) ловит все ошибки (#Н/Д, #ДЕЛ/0!, #ЗНАЧ! и т.д.). Если вы хотите скрыть только отсутствие позиции, но увидеть другие возможные проблемы — используйте ЕСЛИНД. Если хотите полностью «чистый» лист — ЕСЛИОШИБКА.

Профилактические меры

1. Явный четвертый аргумент ВПР.
   Всегда заканчивайте ВПР на ;0). Никогда не пропускайте его.
2. Переходите на ПРОСМОТРX.
   Он не боится вставки столбцов, имеет встроенный если_не_найдено и по умолчанию ищет точное совпадение.
3. Заворачивайте в обертку по умолчанию.
   Сделайте привычкой писать:
   excel =ЕСЛИОШИБКА(ВАША_ФОРМУЛА; "")
   Это делает отчеты чистыми и профессиональными.
4. Проверка данных на входе.
   Используйте выпадающие списки (Данные → Проверка данных) для ключевых полей, где пользователи вводят данные. Это исключает опечатки.
5. Чистка выгрузок. При получении файлов из внешних систем (1С, банк-клиент) сразу делайте:
   • Поиск и замена неразрывного пробела (ввести Alt+0160 с цифровой клавиатуры в поле «Найти»).
   • Выделение числовых столбцов → «Текст по столбцам» → Готово.
6. Используйте Таблицы Excel (Format as Table).
   Вместо диапазонов A:A ссылайтесь на столбцы Таблиц (Таблица1[Клиенты]). Это исключает ошибки, связанные со смещением диапазона при добавлении данных.

Похожие ошибки

Если вы столкнулись с ошибкой #Н/Д, возможно, проблема кроется в смежной области. Ознакомьтесь с нашими руководствами по другим ошибкам:

Ошибка	Описание
#ССЫЛКА!	Неверная ссылка на диапазон (удалены данные, выход за границы)
#ЗНАЧ!	Неправильный тип данных (например, пытаетесь сложить текст и число)
#ИМЯ?	Неизвестное имя функции (опечатка в названии или функция не существует)
#ДЕЛ/0!	Деление на ноль или пустую ячейку

Итоги

Ошибка #Н/Д — это индикатор того, что данные не найдены, а не обязательно фатальный сбой. В современных версиях Excel и Google Sheets есть все инструменты, чтобы либо предотвратить ее появление (чистка данных, ПРОСМОТРX), либо красиво обработать (ЕСЛИОШИБКА, ЕСЛИНД). Ключ к решению — всегда начинать с диагностики: проверка на явное наличие значения, пробелы и типы данных.