Функция ВПР в Microsoft Excel¶
Функция ВПР (англ. VLOOKUP, «Vertical Lookup») — встроенная функция табличного процессора Microsoft Excel, предназначенная для поиска заданного значения в левом столбце таблицы и возврата значения из соседнего столбца той же строки. Относится к категории функций поиска и ссылок. Используется для сопоставления данных между двумя и более таблицами, объединения разрозненных наборов данных и заполнения недостающих полей значениями из справочника.
¶Синтаксис и аргументы
`` =ВПР(Искомое_значение; Таблица; Номер_столбца; [Интервальный_просмотр]) ``
| Аргумент | Описание |
|---|---|
| Искомое_значение | Значение, которое необходимо найти в первом столбце таблицы |
| Таблица | Диапазон ячеек, в котором выполняется поиск; первый столбец диапазона используется для поиска |
| Номер_столбца | Порядковый номер столбца таблицы, из которого возвращается результат (начиная с 1) |
| Интервальный_просмотр | Логическое значение: ИСТИНА (или опущено) — приблизительный поиск; ЛОЖЬ — точный поиск |
При установке аргумента «Интервальный_просмотр» в значение ИСТИНА (по умолчанию) данные в первом столбце таблицы должны быть отсортированы по возрастанию — функция выполняет бинарный поиск и возвращает наибольшее значение, не превышающее искомое. При значении ЛОЖЬ выполняется точный поиск: функция ищет точное совпадение и возвращает #Н/Д (N/A), если совпадение не найдено.
¶Принцип работы
Функция ВПР ищет значение в первом столбце указанного диапазона и возвращает значение из указанного столбца той же строки. Ключевое ограничение — поиск возможен только справа от первого столбца: ВПР не способен возвращать данные из столбцов, расположенных левее ключевого. Это ограничение является основным недостатком функции и причиной перехода многих пользователей на более гибкие аналоги.
¶Пример: перенос данных из одной таблицы в другую
Рассмотрим типовую задачу: имеется таблица «Заказы» с идентификатором товара и количеством, а также отдельный справочник «Товары» с идентификатором, наименованием и ценой. Задача — дополнить таблицу «Заказы» наименованиями и ценами из справочника.
Допустим, в таблице «Заказы» идентификатор находится в столбце A, а в справочнике «Товары» идентификатор — в столбце A, наименование — в столбце B, цена — в столбце C. Для заполнения столбца с наименованием в ячейке B2 таблицы «Заказы» используется формула:
`` =ВПР(A2; Товары!$A$2:$C$100; 2; ЛОЖЬ) ``
Для заполнения столбца с ценой:
`` =ВПР(A2; Товары!$A$2:$C$100; 3; ЛОЖЬ) ``
После ввода формулы в первой строке её копируют вниз по столбцу. Ссылка $A$2:$C$100 записана с абсолютными обозначениями (знаки доллара), чтобы при копировании диапазон справочника не смещался.
¶Типичные ошибки
- #Н/Д — искомое значение не найдено в первом столбце таблицы. Причина может заключаться в различии типов данных (текст «123» и число 123 не совпадают), наличии лишних пробелов или в отсутствии записи в справочнике.
- #ССЫЛКА! — номер столбца превышает количество столбцов в указанном диапазоне.
- #ЗНАЧ! — аргумент «Номер_столбца» передан как текст, а не число.
- Некорректный результат при ИСТИНА — таблица не отсортирована по первому столбцу по возрастанию.
Для подавления ошибки #Н/Д при отсутствии совпадения используется обёртка ЕСЛИОШИБКА:
`` =ЕСЛИОШИБКА(ВПР(A2; Товары!$A$2:$C$100; 2; ЛОЖЬ); "не найдено") ``
¶Альтернативы ВПР
В версиях Excel начиная с 2016 года доступна функция ГПР (ГИПУЗВП, XLOOKUP), лишённая основных ограничений ВПР: она ищет значение в любом направлении (вправо, влево, вверх, вниз) и по умолчанию выполняет точный поиск. Для объединения данных по ключу также используется функция ИНДЕКС в связке с ПОИСКПОЗ:
`` =ИНДЕКС(Товары!$B$2:$B$100; ПОИСКПОЗ(A2; Товары!$A$2:$A$100; 0)) ``
Эта комбинация позволяет возвращать значения из столбцов, расположенных левее ключевого, что недоступно ВПР. В среде пользователей и разработчиков табличных процессоров (включая российские аналоги Excel — «Р7-Офис», «МойОфис») ВПР продолжает широко применяться благодаря простоте и совместимости с существующими документами.
¶Особенности локализации
В русскоязычных версиях Excel функция носит название ВПР, в англоязычных — VLOOKUP. При открытии файла, созданного в одной локали, в другой, Excel автоматически преобразует имя функции. В таблицах, используемых совместно с Google Таблицами и LibreOffice Calc, синтаксис сохраняется, однако разделитель аргументов может отличаться (точка с запятой или запятая) в зависимости от региональных настроек системы.
¶Применение
ВПР применяется в бухгалтерском учёте, аналитике продаж, кадровом учёте и других областях, где требуется сопоставлять данные из нескольких источников: подтягивать цены по артикулам, ФИО по табельным номерам, реквизиты по ИНН. Функция поддерживается всеми основными табличными процессорами, включая Microsoft Excel, LibreOffice Calc, Google Таблицы, «Р7-Офис» и «МойОфис Таблицы».
¶Источники
- Microsoft Support. Документация по функции ВПР (VLOOKUP).
- Excel. Функции поиска и ссылок. Справочные материалы.
- Гордонов И. Н. «Excel — это просто. Практическое руководство для начинающих».