Поиск и устранение ошибок в функции ВПР / VLOOKUP в Excel
Функция ВПР / VLOOKUP долгое время была незаменимым инструментом для поиска данных в Excel. Однако даже опытные пользователи сталкиваются с ошибками, самой частой из которых является #Н/Д. В этой статье мы разберем основные причины сбоев ВПР и предоставим пошаговые инструкции по их устранению.
Если вы разбираете формулы в Excel с нуля или хотите ускорить расчеты, здесь показано, как использовать функции в экселе на понятных и рабочих примерах.
Допустим, у вас есть список артикулов товаров в столбце A, а описание к ним находится в справочной таблице в столбцах F и G. Ваша задача — подставить описания с помощью ВПР.
Правильная формула для точного поиска выглядит так, с обязательным аргументом ЛОЖЬ (или 0) в конце:
=ВПР(A2;$F$3:$G$30;2;ЛОЖЬ)=VLOOKUP(A2,$F$3:$G$30,2,FALSE)Обратите внимание на абсолютные ссылки ($) для таблицы поиска, чтобы они не менялись при копировании формулы.
Основные причины ошибок #Н/Д и их решения
Чтобы найти строки с ошибками, используйте фильтр по столбцу результата либо «Главная → Найти и выделить → Выделить группу ячеек → Формулы → Ошибки». Для проверки #Н/Д удобно добавить вспомогательный столбец с ЕНД (ISNA). Не меняйте порядок исходной таблицы только ради поиска ошибок.
1. Лишние пробелы в данных
Если значение для поиска визуально совпадает со значением в таблице, но ВПР его не находит, вероятная причина — невидимые пробелы в начале или конце ячейки (часто наследие старых систем).
Нажмите F2 для перехода в режим редактирования ячейки. Курсор, стоящий далеко от последнего символа, указывает на наличие пробелов.
- Используйте функцию СЖПРОБЕЛЫ (TRIM) внутри формулы ВПР. Она обрабатывает обычный пробел с кодом 32, оставляя одиночные пробелы между словами. Неразрывный пробел U+00A0 сначала нужно заменить; СЖПРОБЕЛЫ сама его не удаляет.
Русская версия:
=ВПР(СЖПРОБЕЛЫ(A2);$F$3:$G$30;2;ЛОЖЬ)English version:=VLOOKUP(TRIM(A2),$F$3:$G$30,2,FALSE) - Если пробелы в справочной таблице (столбец F), создайте вспомогательный столбец с формулой Русская версия:
=СЖПРОБЕЛЫ(F3)English version:=TRIM(F3), скопируйте его значения и вставьте обратно в столбец F.
2. Несоответствие типов данных (текст vs число)
ВПР не найдет соответствие, если ищет текстовое значение «4399» в столбце с реальными числами 4399, и наоборот.
- Преобразуйте «текстовые» числа в реальные с помощью инструмента «Текст по столбцам»:
- Выделите столбец с текстовыми числами.
- В настольном Excel для Windows откройте «Данные → Текст по столбцам», проверьте разбиение и выберите формат «Общий», затем нажмите «Готово». Последовательность Alt+D, E, F относится к английскому интерфейсу. Не преобразуйте артикулы с ведущими нулями и длинные идентификаторы в числа.
- Исправьте формулу, не меняя данные:
- Если ищете текст в числах: Русская версия:
=ВПР(A2*1;$F$3:$G$30;2;ЛОЖЬ)English version:=VLOOKUP(A2*1,$F$3:$G$30,2,FALSE)или Русская версия:=ВПР(ЗНАЧЕН(A2);$F$3:$G$30;2;ЛОЖЬ)English version:=VLOOKUP(VALUE(A2),$F$3:$G$30,2,FALSE). - Если ищете число в тексте: Русская версия:
=ВПР(A2 & "";$F$3:$G$30;2;ЛОЖЬ)English version:=VLOOKUP(A2 & "",$F$3:$G$30,2,FALSE)или Русская версия:=ВПР(ТЕКСТ(A2;"0");$F$3:$G$30;2;ЛОЖЬ)English version:=VLOOKUP(TEXT(A2,"0"),$F$3:$G$30,2,FALSE).
- Если ищете текст в числах: Русская версия:
Заключение и лучшие практики
Ошибка #Н/Д может означать и обычное отсутствие ключа в справочнике. ВПР ищет только в первом столбце указанной таблицы. Перед изменением данных проверяйте:
- Единообразие типов данных (текст/число) в исходном списке и таблице поиска.
- Отсутствие лишних пробелов, используя
СЖПРОБЕЛЫили визуальную проверку черезF2. - Абсолютные ссылки на таблицу поиска или использование именованных диапазонов.
Освоив эти методы поиска неисправностей, вы сможете уверенно использовать ВПР для решения самых разных задач по поиску и сопоставлению данных в Excel.