Открыть сервисСервис

Функция ВПР в Excel: пошаговая инструкция

ВПР (Вертикальный ПРосмотр, в англоязычной версии Excel — VLOOKUP) — встроенная функция табличного процессора Microsoft Excel, выполняющая поиск заданного значения в первой колонке таблицы и возвращающая значение из указанного столбца той же строки. Функция относится к категории поисковых и справочных функций и применяется для сопоставления данных между разными диапазонами, например для подстановки цены по артикулу, фамилии по табельному номеру или адреса по индексу.

Синтаксис и аргументы

Функция имеет вид:

`` =ВПР(искомое_значение; таблица; номер_столбца; [сортировка]) ``

АргументОписание
Искомое значениеЗначение, которое нужно найти в первом столбце таблицы
ТаблицаДиапазон ячеек, в котором выполняется поиск (минимум два столбца)
Номер столбцаПорядковый номер столбца таблицы, из которого возвращается результат (начиная с 1)
СортировкаЛогическое значение: ИСТИНА (или опущено) — приблизительное совпадение; ЛОЖЬ — точное совпадение

Последний аргумент определяет режим работы. При значении ИСТИНА таблица должна быть отсортирована по возрастанию первого столбца, поиск останавливается на последнем значении, не превышающем искомое. При значении ЛОЖЬ функция требует точного совпадения и возвращает ошибку #Н/Д (в англоязычной версии — #N/A), если значение не найдено.

Пошаговая инструкция

  1. Подготовить таблицу-источник. Первый столбец диапазона должен содержать ключи поиска (артикулы, номера, коды) — именно по нему выполняется сопоставление.
  2. Выбрать ячейку результата и ввести знак =.
  3. Ввести имя функции — ВПР (или VLOOKUP в английской раскладке Excel). После открытия скобки появится подсказка с аргументами.
  4. Указать искомое значение — обычно ссылку на ячейку, например A2, либо текст в кавычках.
  5. Указать таблицу — выделить диапазон данных, например D2:F100. Желательно зафиксировать адреса, нажав F4 (появятся знаки $), чтобы при копировании формулы диапазон не смещался.
  6. Указать номер столбца — число, соответствующее нужному полю в таблице (если искомый столбец — первый, а нужен третий, вводится 3).
  7. Указать признак сортировки — для точного поиска вводится ЛОЖЬ (или 0).
  8. Завершить формулу закрывающей скобкой и нажать Enter.

Пример: в таблице D2:F100 первый столбец содержит артикулы, третий — цены. Чтобы найти цену по артикулу из ячейки A2, вводится:

`` =ВПР(A2;$D$2:$F$100;3;ЛОЖЬ) ``

Типичные ошибки

  • #Н/Д — искомое значение отсутствует в первом столбце таблицы. Часто возникает из-за лишних пробелов в ячейках; устраняется функцией СЖПРОБЕЛЫ или очисткой данных.
  • #ССЫЛКА! — номер столбца превышает фактическое количество столбцов в диапазоне.
  • #ЗНАЧ! — в аргументах использованы несовместимые типы данных.
  • Неверный результат при приблизительном поиске — таблица не отсортирована по возрастанию первого столбца.

Ограничения и альтернативы

ВПР ищет только в первом столбце диапазона и не умеет возвращать данные, расположенные слева от ключа. Для обхода этих ограничений в Excel 2016 и новее доступна функция ГПР (Горизонтальный ПРосмотр, HLOOKUP), а в Excel 2019 и Microsoft 365 — функции ВЫБОРЗНАЧ (INDEX) и ПОИСКПОЗ (MATCH), а также ПОИСКЗНАЧ (XLOOKUP), которая поддерживает поиск в любом направлении и возвращает данные из любого столбца диапазона.

Источники:

Заметили ошибку или не согласны с информацией в статье? Напишите нам support@bfometr.ru