ВПР не работает

Как работает ВПР в Excel читайте в посте “Функция ВПР – чудо экономии времени”. Здесь же вы узнаете, что делать, если ВПР не работает. Так бывает. Скорее всего вы допустили ошибку. Есть четыре наиболее распространенных варианта ошибок:

  1. Ошибка Н/Д в каждой ячейке.
  2. Ошибка Н/Д преимущественно в нижней части таблицы.
  3. Ошибка Н/Д в отдельных ячейках.
  4. Ошибка #ССЫЛКА.

впр excel ошибки

А теперь разберем каждый вариант подробнее.

Вариант 1. ВПР вернула ошибку #Н/Д во всех ячейках

Причины:

  • Вы неправильно выделили диапазон поиска – начали не с того столбца:

впр excel ошибка #Н/Д

Все будет в порядке, как только вы исправите ошибку:

=впр(B3;'[прайс-лист.xlsx]Лист1′!$B$3:$E$7;3;ложь) на

=впр(B3;'[прайс-лист.xlsx]Лист1′!$C$3:$E$7;3;ложь)

 

  • Не совпадает текст, по которому ВПР ищет данные.

Например, в одном файле текст “Компьютер AF”, в другом файле “КомпьютерAF”. Если не заметили, разница всего лишь в наличии дефиса во втором случае. Тем не менее, ВПР не считает такое расхождение мелочью и, следовательно, не сработает.

Единственное исключение – допускаются разные регистры. Например, “Компьютер AF” и “компьютер af” не составит проблемы для ВПР.

Идентичность текста удобно проверять функцией =ЯЧЕЙКА=ЯЧЕЙКА. Смотрите пример на рисунке ниже и описание под ним:

excel проверка ячеек

  1. Введите в ячейку D2 знак “=”
  2. Выделите ячейку B2 с первым значением Планшет DC.
  3. Опять введите “=”.
  4. Выделите ячейку C2 со вторым значением.
  5. Кликните Enter и протяните формулу вниз.

В результате в ячейках отобразится либо “ИСТИНА” (значения совпадают), либо “ЛОЖЬ” (значения не совпадают).

Как видите, не совпадают значения в строке 4: “Компьютер А” и “Компьютер F” (в ячейке D4 видим слово “ЛОЖЬ”).

Использовать функцию можно как в одном, так и в разных файлах.

Что можно предпринять, если текст не совпадает?

  1. Если вы регулярно работаете с этими файлами, поменяйте текст вручную. Единожды потратив на это время, вы значительно сэкономите его в будущем. Если смена текста невозможна, создайте дополнительный столбец, куда введете текст из второго файла.
  2. Иногда текст не совпадает из-за каких-то наборов знаков, которые повторяются в каждой ячейке. К примеру, в одном из файлов везде добавлен артикул: “010-01583 Компьютер AF”, а в другом файле этого артикула нет “Компьютер AF”. В этом случае текст можно заменить автоматически. Об этом читайте в следующих постах.

 

  • В одном из файлов в тексте содержатся невидимые пробелы. Это хитрая разновидность несовпадения текста. Хитрая, потому что не всегда ее можно заметить. Вот полюбуйтесь:

excel как проверить текст в ячейке

Как быть? Воспользуйтесь формулой =СЖПРОБЕЛЫ(ЯЧЕЙКА). Эта формула убирает все лишние пробелы. Подробнее – в посте о работе с текстом.

 

  • В одном файле числа сохранены в виде текста, в другом – в виде чисел:

excel число в виде текста

Для устранения проблемы сделайте следующее:

  1. Выделите весь столбец с числами в виде текста (на рисунке выше диапазон B2:В5).
  2. Подведите курсор к значку “!” и кликните по появившейся стрелочке.
  3. Выберите из выпадающего списка “Преобразовать в число” – см. рисунок ниже.

excel преобразовать текст в число

Вариант 2. ВПР подтянула значения только в верхней части таблицы, в нижней — #Н/Д

Причина:

  • Смещение диапазона. Чаще всего это происходит, если данные подтягиваются из одного и того же файла. Например, из Листа 1 на Лист 2 или даже из одного и того же листа. Что происходит? Мы тянем формулу вниз, а вместе с ней тянется и диапазон. Вот так:

впр выдает ошибку

Как исправить? Надо закрепить диапазон:

  1. Кликните дважды по ячейке с формулой (С5) – отобразится формула.
  2. В отобразившейся формуле поместите курсор на В12 и нажмите клавишу F4 на клавиатуре.
  3. Переместите курсор на С14 в этой же формуле и опять нажмите F4.
  4. Кликните Enter и формула преобразится, из а) в б)

а) =ВПР(В5;В12:С14;2;ложь)

б) =ВПР(В5;$B$12:$С$14;2;ложь)

Значок “$” указывает на то, что диапазон закреплен. Теперь при протягивании формулы вниз, он не будет смещаться.

Вариант 3. ВПР подтягивает только часть значений, остальные  – #Н/Д

Причина:

  • В некоторых ячейках текст не совпадает на 100%.

Если таких ячеек немного, то проще всего исправить это вручную, копируя из одного файл в другой.

Вариант 4. ВПР возвращает ошибку #ССЫЛКА

Причина:

  • Вы неверно указали номер столбца. ВПР всегда считает столбцы, начиная с первого столбца выделенного диапазона. Поэтому на рисунке ниже красные номера неверные, зеленые – правильные:

впр excel номер столбца

И еще несколько советов по функции ВПР в Excel:

  1. При вводе формулы используете точку с запятой, а не запятые.
  2. Если протягиваете формулу вправо, не забывайте закреплять ссылку в начале формулы, чтобы формула имела вид: =ВПР($В5;$B$12:$С$14;2;ложь). Для этого наведите курсор на В5 (прямо в формуле) и трижды нажмите клавишу F4 на клавиатуре.
  3. Не используйте аргумент “ИСТИНА” в ВПР, если работаете с текстом. Это приблизительный поиск, в котором будет много ошибок.

9 комментариев к “ВПР не работает

  1. Очень хорошая статья! Без воды и написано именно то, что надо по сути
    Но, чтобы статья стала почти исчерпывающей я бы добавил, что иногда ВПР может возвращать ошибку, которая может быть не замечена пользователем, так как не появляется #Н/Д или #ССЫЛКА, однако, при этом функция ВПР() работает некорректно, так как результат ошибочный.
    Это происходит в тех случаях, когда в формуле последний аргумент не ЛОЖЬ, а ИСТИНА (или вообще не приведен, а это приравнивается к тому, как если бы была указана ИСТИНА) и при этом первый столбец не отсортирован. Формула вида =ВПР(В5;$B$12:$С$14;2) корректно работать то будет.

    • Спасибо за комментарий! Да, конечно, это все совершенно верно для ВПР с аргументом ИСТИНА (или без него). Но поскольку статья является фактически продолжением статьи, в которой рассматривалась исключительно ВПР с аргументом ЛОЖЬ, то данная ошибка не приведена.

  2. Спасибо большое, очень выручили. Про ВПР всё чётко и правильно, смогла решить проблему над которой ломала голову весь вечер и не могла заметить, что не так.

Добавить комментарий для Анастасия Отменить ответ