ВПР в Excel: функция и примеры использования¶
ВПР (англ. VLOOKUP, «Vertical Lookup») — функция табличного процессора Microsoft Excel, выполняющая вертикальный поиск заданного значения в первой колонке таблицы и возвращающая соответствующее значение из указанной колонки той же строки. Функция относится к категории поиска и ссылок и является одной из наиболее широко используемых в Excel наряду с ЕСЛИ, СУММЕСЛИ и их производными.
¶Синтаксис
Формула ВПР имеет вид:
`` =ВПР(Искомое_значение; Таблица; Номер_колонки; [Интервальный_просмотр]) ``
| Аргумент | Описание |
|---|---|
| Искомое_значение | Значение, которое нужно найти в первой колонке таблицы |
| Таблица | Диапазон ячеек, в котором выполняется поиск (минимум 2 колонки) |
| Номер_колонки | Порядковый номер колонки таблицы, из которой возвращается результат (начиная с 1) |
| Интервальный_просмотр | ЛОЖЬ — точное совпадение; ИСТИНА — ближайшее совпадение (значение по умолчанию) |
Четвёртый аргумент рекомендуется всегда указывать явно: ЛОЖЬ для точного поиска, ИСТИНА — для интервального. В русской версии Excel допускается запись 0 вместо ЛОЖЬ и 1 вместо ИСТИНА.
¶Принцип работы
Функция сканирует первую колонку диапазона сверху вниз до первого совпадения с искомым значением и возвращает содержимое ячейки из указанной колонки той же строки. Если совпадение не найдено, возвращается ошибка #Н/Д (от англ. N/A — not available).
Ключевые ограничения:
- поиск выполняется только по первой колонке диапазона;
- направление поиска — только сверху вниз (для горизонтального поиска используется ГПР);
- при интервальном просмотре (ИСТИНА) первая колонка должна быть отсортирована по возрастанию;
- при точном поиске (ЛОЖЬ) сортировка не требуется.
¶Пример использования
Допустим, имеется таблица товаров с ценами:
| A | B |
|---|---|
| Товар | Цена |
| Хлеб | 45 |
| Молоко | 68 |
| Сыр | 320 |
| Кофе | 480 |
В ячейке D1 введено название искомого товара — «Молоко». Формула
`` =ВПР(D1; A2:B5; 2; ЛОЖЬ) ``
вернёт значение 68: функция найдёт «Молоко» в первой колонке диапазона A2:B5 (вторая строка диапазона) и вернёт содержимое второй колонки этой строки. Если в D1 ввести «Чай», формула вернёт ошибку #Н/Д, поскольку такого товара в таблице нет.
¶Обработка ошибок
Для предотвращения отображения #Н/Д формулу ВПР обычно оборачивают в функцию ЕСЛИОШИБКА:
`` =ЕСЛИОШИБКА(ВПР(D1; A2:B5; 2; ЛОЖЬ); "Не найдено") ``
При отсутствии совпадения ячейка покажет текст «Не найдено» вместо ошибки.
¶Типичные ошибки
- Неверный номер колонки. Номер указывается относительно переданного диапазона, а не относительно листа. Если диапазон начинается с колонки B, то первая колонка диапазона — это B, а не A.
- Забытый четвёртый аргумент. При его отсутствии Excel по умолчанию использует интервальный просмотр, что приводит к неверным результатам при несортированных данных.
- Смешанные типы данных. Числовое значение 100 и текстовое «100» не совпадут при точном поиске.
- Диапазон, зафиксированный относительно. При протягивании формулы вниз ссылка на таблицу смещается. Решение — абсолютные ссылки: $A$2:$B$5.
¶Аналоги и альтернативы
В версиях Excel начиная с Microsoft 365 доступна функция ВЫБОРЗНАЧЕНИЯ (INDEX + MATCH), которая позволяет искать по любой колонке, а не только по первой. Функция ПОИСКПОЗ (MATCH) возвращает позицию значения в диапазоне, а ИНДЕКС — значение по позиции; их комбинация считается более гибкой заменой ВПР. В Excel на английском интерфейсе эквивалентом является VLOOKUP, в Google Таблицах — VLOOKUP с идентичным синтаксисом.
¶Применение
ВПР применяется для сопоставления справочников (артикулы, коды, идентификаторы), объединения данных из нескольких таблиц, подстановки цен, наименований и реквизитов по ключу, а также при подготовке отчётности, где значения одной таблицы должны дополняться данными из другой.
¶Источники
- Документация Microsoft по функции ВПР (support.microsoft.com)
- «Excel для чайников», Джон Уокенбах
- «Большая книга Excel», Александр Фролов