Как сделать ВПР в Excel¶
ВПР (в версиях Excel на английском языке — VLOOKUP, сокращение от «vertical lookup», вертикальный поиск) — встроенная функция табличного процессора Microsoft Excel, выполняющая вертикальный поиск заданного значения в первой колонке таблицы и возвращающая соответствующее значение из указанного столбца. Функция относится к категории поиска и ссылок (категория «Ссылки и массивы» в русскоязычной версии Excel). ВПР широко применяется для сопоставления данных из разных таблиц: подтягивания цен по артикулам, определения тарифа по региону, поиска данных о сотруднике по табельному номеру и других аналогичных задач.
¶Синтаксис и аргументы
`` =ВПР(искомое_значение; таблица; номер_столбца; [тип_сопоставления]) ``
| Аргумент | Описание |
|---|---|
| искомое_значение | Значение, которое нужно найти в первом столбце таблицы. Может быть числом, текстом, ссылкой на ячейку или формулой |
| таблица | Диапазон ячеек, в котором выполняется поиск. Искомое значение ищется именно в первом столбце этого диапазона |
| номер_столбца | Порядковый номер столбца таблицы (начиная с 1), из которого нужно вернуть результат |
| [тип_сопоставления] | Необязательный аргумент. ИЛИСТИНА (или 1) — приблизительное совпадение; ЛОЖЬ (или 0) — точное совпадение |
Порядковый номер столбца отсчитывается от первого столбца диапазона таблица, а не от столбца листа. Если задан диапазон B2:D50, то первый столбец — B, второй — C, третий — D.
¶Пошаговое создание формулы
- Подготовить данные. В книге должен существовать диапазон-таблица, первый столбец которого содержит ключевые значения (артикулы, номера, наименования), а остальные столбцы — искомые данные.
- Выбрать ячейку результата. Кликнуть по ячейке, куда должна записаться найденная информация.
- Ввести формулу. Начать ввод с символа
=. Пример:=ВПР(A2; Лист2!A:D; 3; ЛОЖЬ). - Указать искомое значение. Обычно это ссылка на ячейку с ключом, например A2.
- Задать таблицу. Выделить диапазон на другом листе или в другой книге. Если таблица на отдельном листе, к имени листа добавляется восклицательный знак:
Лист2!A:D. - Указать номер столбца с нужным результатом.
- Выбрать тип сопоставления. Для точного поиска — ЛОЖЬ (0), для приблизительного — ИЛИСТИНА (1) или пропуск аргумента.
- Завершить ввод клавишей Enter и протянуть формулу вниз на остальные строки.
¶Тип сопоставления
При значении ЛОЖЬ (0) функция ищет только точное совпадение ключа. Если совпадение не найдено, возвращается ошибка #Н/Д (нет данных). Этот режим рекомендуется для большинства практических задач.
При значении ИЛИСТИНА (1) или пропуске аргумента выполняется приблизительный поиск: таблица должна быть отсортирована по возрастанию первого столбца, и функция возвращает значение из последней строки, не превышающей искомое. Этот режим удобен для интервальных запросов (определение категории по числовому диапазону, например НДС по ставке, штраф по превышению скорости).
¶Типичные ошибки
| Ошибка | Причина |
|---|---|
#Н/Д | Искомого значения нет в первом столбце таблицы; при точном поиске ключ не найден |
#ССЫЛКА! | Номер столбца превышает число столбцов в диапазоне таблица |
#ЗНАЧ! | Некорректный тип аргумента, например текст вместо числа в номере столбца |
| Неверный результат | Использован приблизительный поиск, а таблица не отсортирована; либо искомое значение находится не в первом столбце диапазона |
Частая ошибка при работе с текстовыми ключами — наличие «невидимых» пробелов. Убрать лишние пробелы помогает функция =СЖПРОБЕЛЫ().
¶Альтернативы ВПР
В версиях Excel начиная с Office 365 и Excel 2021 доступна функция ПОИСКПОЗ (ПРОСМОТР/ПРОИЗВПР — XLOOKUP), которая ищет значение в любом столбце диапазона, а не только в первом, и возвращает результат из любого столбца. Другая альтернатива — ИНДЕКС в связке с ПОИСКПОЗ (INDEX+MATCH), которая позволяет обойти ограничение ВПР на поиск только по первому столбцу и работать с диапазонами в любом направлении.
¶Пример использования
На листе «Прайс» в столбце A перечислены артикулы, в столбце B — наименования, в столбце C — цены. На листе «Заказ» в столбце A указаны артикулы заказанных позиций. Для подстановки цены в столбец B листа «Заказ» используется формула:
`` =ВПР(A2; Прайс!A:C; 3; ЛОЖЬ) ``
Формула находит артикул из ячейки A2 в первом столбце диапазона Прайс!A:C и возвращает цену из третьего столбца этого диапазона (столбец C). После ввода формулы в ячейку B2 её протягивают вниз на все строки заказа.
Источники:
- Microsoft Support. Справка по функции ВПР (VLOOKUP).
- Microsoft Support. Справка по функции ПРОИЗВПР (XLOOKUP).
- Учебное пособие по информатике. Табличные процессоры Excel.