Top.Mail.Ru
6 мин

Как работает ВПР: пошаговая инструкция в «МойОфис Таблица»

Автор:

ВПР (вертикальный просмотр) — одна из самых популярных поисковых функций для работы в таблицах. Она помогает находить значения в одной таблице и переносить их в другую. Это удобно для сопоставления и сравнения данных. Например, чтобы быстро посчитать выручку, когда прайс-лист и список проданного товара находятся на разных листах. Рассмотрим функцию ВПР подробнее.

Логика и синтаксис функции ВПР

Работу ВПР можно сравнить с поиском места в кинотеатре. Зал кинотеатра — диапазон поиска, ряд — ключ поиска, а номер кресла — индекс столбца. Так же как мы идем по ряду к нужному сиденью, ВПР просматривает выбранный диапазон по вертикали до значения-идентификатора. Когда видит его, «забирает» искомое значение и выводит в заданную ячейку.

У ВПР три обязательных аргумента:

Ключ поиска (что ищем?) — значение для поиска в первом столбце диапазона.

Диапазон (где ищем?) — диапазон ячеек, из которого функция будет брать данные для искомого значения.

Индекс столбца — номер столбца в диапазоне, из которого будет возвращено значение.

Тип сопоставления — необязательный, но важный аргумент, который отвечает на вопрос «отсортирован ли по возрастанию первый столбец диапазона поиска?». Если отсортирован, мы указываем значение ИСТИНА или 1, в противном случае — ЛОЖЬ или 0. Когда параметр опущен, он по умолчанию равен 1.

Прежде чем начать работу с ВПР, убедитесь, что в таблице нет объединенных ячеек, пустых строк и столбцов, а в каждом столбце находятся данные одного типа.

Рассмотрим пример. У нас есть таблица с тремя столбцами: артикул, наименование товара и его стоимость.


Допустим, нам нужно найти стоимость товара с артикулом 2056. На картинке видно, что она равна 2857, но когда в таблице тысячи строк, поиск усложняется. С функцией ВПР разобраться будет легче.

Введем функцию через знак равенства в свободной ячейке.


Ключ поиска — артикул — может быть прописан в виде ссылки на ячейку (E2) или как числовое значение 2056 (если нужно найти текстовое значение, выделяем его кавычками).

Указываем диапазон таблицы — это все данные из ячеек со стоимостью, наименованием товаров и артикулами (A2:C6).

Остался индекс столбца. В нашем случае стоимость товара указана в третьем по счету столбце.


Мы записали функцию в ячейке F2:

=ВПР(E2; A2:C6; 3)

Четвертый аргумент — тип сопоставления — в этом примере пропущен, и функция по умолчанию решила, что наша таблица отсортирована по возрастанию номера артикула. Это соответствует действительности, поэтому мы получили значение, которое ожидали, — 2857. Ниже мы разберем пример, где подобное не сработало.


От теории к практике

У нас есть объемная таблица с данными клиентов интернет-магазина.


Наша задача — идентифицировать покупателя по номеру телефона и вывести его ФИО в таблицу на другом листе. То есть номер — ключ поиска, первый аргумент ВПР (ячейка B1).


Диапазоном будут все данные в листе c телефонами, адресами и ФИО клиентов (A2:G24). ФИО клиентов указаны в столбце D, по счету он 4-й. Значит, индекс столбца — 4.


Записываем функцию:

=ВПР(B1; 'клиенты'!A2:G24;4;0)

На место четвертого аргумента мы подставили ноль, чтобы задать точный поиск указанного номера телефона в неотсортированной таблице.


Завершаем ввод функции клавишей Enter и получаем результат.


Аналогичным образом найдем адрес покупателя. Вы можете скопировать уже заполненную формулу ВПР в ячейку B3, скорректировав индекс столбца. Адреса записаны во втором столбце, поэтому мы меняем цифру 4 на 2.


Теперь, когда мы получили адрес и ФИО клиента, мы можем найти данные любого покупателя по номеру телефона.

Итоги

  1. Функция ВПР означает вертикальный просмотр. Она просматривает крайний левый столбец таблицы сверху вниз.
  2. Функция ВПР полезна при работе с большими таблицами, благодаря ей можно быстро найти нужную информацию.
  3. Синтаксис ВПР: =ВПР(ключ_поиска;диапазон;индекс_столбца;тип_сопоставления).
  4. Четвертый аргумент функции важно указывать, чтобы задать поиск в неотфильтрованной таблице.
  5. Если в таблице есть объединенные ячейки, пустые строки или столбцы, ВПР может работать некорректно.

Часто задаваемые вопросы


Что такое ВПР?

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

Как расшифровывается ВПР и для чего нужен?

ВПР означает вертикальный просмотр.  Она делает просмотр сверху вниз (вертикально) до требуемого значения идентификатора и забирает значение напротив, как результат вычисления.

Как правильно написать формулу ВПР?

Введите:=ВПР(ключ_поиска;диапазон;индекс_столбца;тип_сопоставления).

Как протянуть ВПР вниз?

Для распространения формулы ВПР на всю таблицу нужно захватить крестик в правом нижнем углу ячейки и протянуть его вниз.
Назад к публикациям
Поделиться в соц. сетях: