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