Office

Функция ВПР в Excel: синтаксис, пошаговый пример, ошибки #Н/Д

Функция ВПР в Excel ищет значение в первом столбце таблицы и возвращает данные из другого столбца той же строки. Например, по артикулу она подставляет цену из прайса. Формула выглядит так: =ВПР(что_ищем; таблица; номер_столбца; ЛОЖЬ).

Функция ВПР в Excel: синтаксис, пошаговый пример, ошибки #Н/Д
Фото: Vyacheslav Argenberg, CC BY 4.0, Wikimedia Commons
Содержание
  1. Что делает ВПР и когда она нужна
  2. Синтаксис функции и её аргументы
  3. Пошаговый пример: подставляем цену из прайса
  4. Точный и приблизительный поиск
  5. Почему ВПР выдаёт ошибку #Н/Д
  6. Как скрыть ошибку или заменить её текстом
  7. Ограничения ВПР и чем её заменить
  8. Полезные приёмы при работе с ВПР

Что делает ВПР и когда она нужна

Буква "В" означает вертикальный, а "ПР" - просмотр: функция просматривает таблицу сверху вниз. Задача типичная: есть список заказов с артикулами и отдельный прайс, где у каждого артикула своя цена. Вместо ручного копирования цен ВПР находит артикул в прайсе и подставляет цену в заказ.

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

Синтаксис функции и её аргументы

Полная запись: =ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр]). Разделитель аргументов в русской версии - точка с запятой.

АргументЧто указатьПример
искомое_значениеЧто найти: ячейка или текст в кавычкахA2
таблицаДиапазон, где первый столбец содержит искомое$F$2:$H$100
номер_столбцаНомер столбца внутри таблицы, откуда взять результат3
интервальный_просмотрЛОЖЬ (0) - точное совпадение, ИСТИНА (1) - приблизительноеЛОЖЬ

Четвёртый аргумент необязателен, но пропускать его опасно: без него Excel считает, что указано ИСТИНА, и ищет приблизительно. Для списков, кодов и названий всегда ставьте ЛОЖЬ.

Пошаговый пример: подставляем цену из прайса

Допустим, прайс лежит в диапазоне F2:H7: в столбце F артикул, в G название, в H цена. На другом листе или в столбце B стоит список заказанных артикулов, а рядом нужна цена.

  1. Щёлкните ячейку C2, куда должна попасть цена, и начните формулу: =ВПР(.
  2. Первым аргументом укажите ячейку с артикулом: B2.
  3. Вторым - прайс целиком, и сразу закрепите адрес клавишей F4: $F$2:$H$7.
  4. Третьим - номер столбца с ценой. Считать нужно внутри выбранной таблицы: F - первый, G - второй, H - третий, значит 3.
  5. Четвёртым впишите ЛОЖЬ и закройте скобку. Итог: =ВПР(B2;$F$2:$H$7;3;ЛОЖЬ).
  6. Нажмите Enter и протяните формулу вниз за маркер в правом нижнем углу ячейки.

Знаки доллара фиксируют адрес таблицы. Без них при протягивании диапазон поедет вниз, и последние артикулы окажутся за его пределами. Это одна из самых частых причин ошибок.

Макет окна Excel с формулой ВПР и найденной ценой
Формула ВПР в ячейке C2 и результат, подставленный из прайса

Если формулы вводить неудобно, соберите её в конструкторе формул Excel: он подставит аргументы и покажет готовую запись.

Открыть инструмент отдельной страницей

Точный и приблизительный поиск

Режим определяется четвёртым аргументом. С ЛОЖЬ функция ищет точное совпадение и, если его нет, возвращает #Н/Д. С ИСТИНА она ищет наибольшее значение, не превышающее искомое, и первый столбец при этом обязательно должен быть отсортирован по возрастанию.

Приблизительный поиск нужен для шкал: оценки по баллам, скидки по сумме заказа, ставки по стажу. Пример шкалы скидок:

Сумма отСкидка
00%
100003%
500005%
1000008%

Для заказа на 65000 формула =ВПР(65000;$A$2:$B$5;2;ИСТИНА) вернёт 5%, потому что 50000 - наибольшее значение, не превышающее 65000. Если такую шкалу не отсортировать, результат будет случайным.

Почему ВПР выдаёт ошибку #Н/Д

#Н/Д означает "нет данных": функция не нашла значение. Причин обычно немного, и их можно проверить по порядку.

  • Значения нет в первом столбце таблицы. Проверьте, что артикул написан так же, как в прайсе, и нет опечатки.
  • Лишние пробелы в начале или конце. Очистите их функцией СЖПРОБЕЛЫ или через "Данные", "Текст по столбцам".
  • Число записано как текст в одной из таблиц. Признак - зелёный уголок в ячейке и выравнивание по левому краю. Преобразуйте в число.
  • Таблица сдвинулась при протягивании, потому что адреса не закреплены знаками доллара.
  • Искомый столбец не первый в диапазоне: ВПР всегда ищет в самом левом столбце таблицы.

Если номер столбца больше числа столбцов в таблице, Excel вернёт ошибку #ССЫЛКА!, а если меньше 1 - #ЗНАЧ!.

Как скрыть ошибку или заменить её текстом

Когда часть значений в прайсе действительно отсутствует, #Н/Д портит отчёт. Оберните ВПР функцией ЕСЛИОШИБКА: =ЕСЛИОШИБКА(ВПР(B2;$F$2:$H$7;3;ЛОЖЬ);"нет в прайсе"). Теперь вместо ошибки в ячейке появится понятная надпись.

Не прячьте ошибки бездумно: сначала убедитесь, что значения и правда отсутствуют, а не потерялись из-за пробелов. Для более точной реакции именно на #Н/Д используйте функцию ЕСНД вместо ЕСЛИОШИБКА, тогда другие ошибки не замаскируются.

Чек-лист причин ошибки Н/Д в функции ВПР
Проверка причин ошибки #Н/Д по порядку

Ограничения ВПР и чем её заменить

У функции три слабых места: она ищет только слева направо, плохо переживает вставку столбцов в таблицу (номер столбца придётся править) и по умолчанию ищет приблизительно. Всё это обходится двумя способами.

СпособФормулаОсобенность
ВПР=ВПР(B2;$F$2:$H$7;3;ЛОЖЬ)Просто, ищет только вправо
ИНДЕКС + ПОИСКПОЗ=ИНДЕКС($H$2:$H$7;ПОИСКПОЗ(B2;$F$2:$F$7;0))Ищет в любом столбце, не зависит от вставок
ПРОСМОТРX=ПРОСМОТРX(B2;$F$2:$F$7;$H$2:$H$7)Есть в Microsoft 365 и Excel 2021 и новее

ПРОСМОТРX по умолчанию ищет точное совпадение и не требует номера столбца, но в Excel 2016 и 2019 её нет. Если файл будут открывать на старых версиях, надёжнее оставить ВПР или ИНДЕКС с ПОИСКПОЗ.

Полезные приёмы при работе с ВПР

  • Подстановочные знаки: при ЛОЖЬ в искомом значении работают звёздочка и вопросительный знак, например =ВПР("Иван*";A2:B20;2;ЛОЖЬ) найдёт первую запись, начинающуюся с "Иван".
  • Ссылка на другой лист: при выборе таблицы мышью Excel сам запишет имя листа, например 'Прайс'!$A$2:$C$100.
  • Именованный диапазон: присвойте прайсу имя Прайс, и формула станет читаемой: =ВПР(B2;Прайс;3;ЛОЖЬ).
  • Результаты ВПР - формулы. Чтобы зафиксировать значения, скопируйте столбец и вставьте через "Специальная вставка", "Значения".

Если прайс разросся, удобно сначала свести данные сводной таблицей, а затем подтягивать итоги ВПР. А для суммирования по условию вместо поиска существует функция СУММЕСЛИ. Выбор артикула без опечаток обеспечит выпадающий список в ячейке поиска.

Частые вопросы

Что такое ВПР в Excel простыми словами?

Это функция, которая находит нужное значение в первом столбце таблицы и берёт данные из того же ряда, но из другого столбца. Например, по коду товара она подставляет его название или цену.

Как пользоваться функцией ВПР?

Введите =ВПР(искомое; таблица; номер_столбца; ЛОЖЬ). Закрепите адрес таблицы клавишей F4, номер столбца считайте от первого столбца таблицы, а в конце всегда ставьте ЛОЖЬ для точного поиска.

Почему ВПР пишет #Н/Д, хотя значение есть в таблице?

Чаще всего мешают лишние пробелы, число в виде текста, опечатка или сдвинувшийся диапазон. Проверьте, что искомое находится в первом столбце таблицы, адреса закреплены знаками доллара, а четвёртый аргумент - ЛОЖЬ.

Может ли ВПР искать влево?

Нет, ВПР возвращает данные только из столбцов справа от искомого. Для поиска влево используйте связку ИНДЕКС и ПОИСКПОЗ или функцию ПРОСМОТРX в новых версиях Excel.

В чём разница между ИСТИНА и ЛОЖЬ в ВПР?

ЛОЖЬ означает точное совпадение, а ИСТИНА - ближайшее меньшее значение в отсортированном по возрастанию первом столбце. Если четвёртый аргумент не указан, работает ИСТИНА.

Нашли ошибку или устаревший пункт меню? Напишите на admin@programistik.top с адресом страницы. Перед правкой реестра, системных файлов и разделов диска делайте резервную копию.

Читайте по теме