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