Гид по ВПР в Excel и Google Таблицах

Если нужно объединить данные в таблицах, можно вручную перепроверять и переносить значения с одного места на другое. Но это сложно и долго, плюс легко ошибиться.

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

Как работает ВПР

ВПР (VLOOKUP) — функция поиска и извлечения данных, которая:

• принимает определенный набор символов в качестве запроса;
• ищет совпадение с этим запросом в крайнем левом столбце заданного диапазона;
• копирует значения из ячейки, которая находится в соседнем столбце, но на этой же строке.

Функция принимает четыре параметра: запрос, диапазон, номер столбца и сортировки. Подробнее о каждом:

Запрос показывает, что мы ищем в таблице. Например, наименование товара.

Диапазон отражает, где мы ищем запрос. Например, в диапазоне B2:C20. И если будет совпадение с ячейкой B1, функция ничего не вернет, так как эта ячейка не входит в указанный диапазон.

Индекс — номер столбца, который определяет, из какого столбца возвращать значение. Например, если в качестве диапазона указать B2:D11, то столбец C будет вторым, а D — третьим.

Сортировка говорит, отсортированы значения в таблице или нет. 1 или ИСТИНА (TRUE) — да, 0 или ЛОЖЬ (FALSE) — нет.

Как правило, указывают 0 — в таком случае ВПР будет искать только точное совпадение с запросом. В противном случае функция выберет значения, которые примерно похожи на запрос — то есть меньшие или равные ему.

Как пользоваться функцией ВПР

Разберемся на примере. Допустим, магазин электроники постоянно торгует разными видами товаров и в очередной раз закупил новую партию. 

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

Перенести данные можно вручную, если товаров не очень много. Но если таблица состоит из сотни наименований — это проще сделать с ВПР.

Шаг 1: Выбираем функцию и запрос

Для этого в ячейке «стоимость покупки» набираем равно «=» и пишем ВПР. После этого нажимаем на ячейку с запросом в столбце «название товара». Либо прописываем в скобках координаты ячейки. В нашем случае это «B4».

Шаг 2: Настраиваем диапазон запроса

После того, как выбрали запрос, настраиваем диапазон. Для этого выделяем всю вторую таблицу, из которой в будущем функция будет искать информацию. 

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

Шаг 3: Выбираем номер столбца

Индекс (номер столбца) передаем следующим после диапазона.

Шаг 4: Выбираем параметр «отсортировано» или «не отсортировано»

На этом этапе функция определяет, что искать: точное или неточное (меньшее или равное) совпадение с запросом. Напомню, что здесь есть два варианта:

0 (ЛОЖЬ, FALSE). ВПР выбирает точь-в-точь подходящий вариант. Как правило, используют именно такой режим поиска.

1 (ИСТИНА, TRUE). Функция выбирает примерно подходящий вариант, меньший или равный, но не больший. Это нужно гораздо реже, и имеет смысл, только если значения в диапазоне отсортированы.

Допустим, нам нужно сопоставить размер скидки для клиента с количеством покупок. Для этого создаем отдельную таблицу с диапазоном скидок. Важно, чтобы диапазон был возрастающий. Например, 5, 7, 9, 12, 15. Иначе функция работать не будет.

Теперь прописываем функцию. Ячейка запроса — количество покупок, диапазон — вторая таблица с количеством покупок и размером скидки, номер столбца — второй, сортировка — «ИСТИНА» или «1».

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

Шаг 5: Настраиваем функцию под всю таблицу

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

Как сравнить таблицы с помощью ВПР

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

Для начала подтягиваем в таблицу старые цены. Формула будет выглядеть так: 

=ВПР(A4;’Таблица со старыми ценами’!$B$4:$C$16;2;0)

После этого добавляем данные из таблицы с новыми ценами. Формула будет такой:

=ВПР(A4;$G$4:$H$16;2;0)

Теперь можно сравнить цены. Чтобы не делать это вручную, прописывайте формулу: новая цена – старая цена / новая цена. А в формате чисел выберите процент.

Как работать с ВПР, если искомое значение — слева, а не справа

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

Прописать ВПР без изменений в таблице нельзя — по правилам формулы поиск производится по крайнему левому столбцу диапазона. 

Поэтому самый простой способ — скопировать столбец «Цена» и перенести его в правый, после «Код товара». После этого внести новый столбец в диапазон и работать с ВПР как и прежде.

Если такой вариант не подходит, есть более сложный — через массивы.

Для поиска цены создадим отдельные поля «Код товара» и «Цена».

И прописываем ВПР для ячейки «Цена». Функция будет вида

=ВПР(H6;{‘Лист6’!D:D \ ‘Лист6’!C:C};2;0), где:

• H6 — номер ячейки с кодом товара.
• Лист6 — название листа, в котором находится наша таблица с ценами, кодами и количеством товаров.
• D:D — диапазон столбца с кодами товаров.
• C:C — диапазон столбца с ценами товаров.
• 2 — номер столбца из диапазона.
• 0 — точное соответствие.

Фигурными скобками как раз создаем массив, а обратной косой чертой показываем, что данные разделяются по столбцам. Тем самым меняем столбцы исходной таблицы местами — теперь сначала идет D:D, а только потом C:C. Такой лайфхак по обходу ограничения функции ВПР.

В итоге, когда введем в ячейку «Код товара» соответствующие данные, получим информацию о стоимости устройств.

Как использовать символьные шаблоны ВПР

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

Разберемся на примере наших товаров. Попробуем найти ячейку, в которой встречается слово Iphone с любыми словами и цифрами до и после него.

Для этого прописываем формулу:

=ВПР(«*Iphone*»;диапазон;1;0) 

Звездочки означают любое количество любых символов (в том числе их отсутствие). То есть условию будут соответствовать и «Apple Iphone», и «Iphone 12», и «Iphone».

А чтобы найти ячейку со словом Iphone с определенным количеством знаков после него, нужно составить формулу вида:

=ВПР(«*Iphone ?? ???»;диапазон;1;0)

А теперь смотрим, как работает более строгая фильтрация. Каждый знак вопроса — один символ.
Напоминаем: если в таблице есть несколько ячеек, которые соответствуют запросу из формулы, то функция выдаст только первое вхождение по порядку.