Гайды

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

Что это за функция и как с ней работать
Гид по ВПР в Excel и Google Таблицах

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

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

Чтобы самостоятельно поработать с шаблоном и примерами из статьи, можно открыть эту таблицу, выбрать «Файл → Создать копию».

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

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

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

Так, ВПР используют магазины, когда нужно объединить или сравнить две таблицы. К примеру, таблицу заказов (какой товар заказали) и прайс-лист (по какой цене заказали, сколько денег ушло на закупку партии и так далее). Или, допустим, ВПР можно использовать, чтобы вычислить скидку для клиента или размер прибыли работника в зависимости от количества продаж.

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

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

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

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

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

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

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

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

А что если будет несколько ячеек, которые соответствуют искомому запросу? Тогда функция все равно выдаст только один результат — завершит работу, как только наткнется на первое совпадение.

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

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

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

pic
Полученная таблица со списком товаров и ценой

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

pic
А это — таблица, в которую нужно передать данные из предыдущей

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

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

pic
Первый этап работы — выбираем запрос

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

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

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

pic
Второй этап — настраиваем диапазон ячеек

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

pic
Переносим диапазон из одной таблицы в другую

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

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

pic
Третий этап — выбираем столбец

Важно: столбцы считаются внутри выбранного диапазона.

Так, в нашем примере нужно взять и перенести информацию о стоимости электроники, которая находится в столбце D. Если смотреть на весь лист, то D — это четвертый по счету столбец. А вот в диапазоне C:D столбец D — это именно второй по счету.

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

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

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

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

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

pic
Сортируем запрос на примере скидок

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

pic
Вот так выглядит формула для расчета скидок
pic
Все еще сортируем запрос

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

pic
Ура! Все работает

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

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

Но перед этим важно зафиксировать значения диапазона. Для этого нужно поставить знаки доллара как минимум после названий столбцов («G» и «H» в нашем случае), а лучше и перед тоже. Это можно сделать вручную или выделить диапазон и нажать «F4» на клавиатуре. 

В противном случае при протягивании формула будет меняться, например, так: G4:H16 → G5:H17 → G6:H18 и так далее. А нам важно искать все значения в определенном диапазоне.

pic
Пятый этап — настроили функцию для всей таблицы

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

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

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

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

pic
Подтягиваем в таблицу данные со старыми ценами

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

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

pic
Таблица для сравнения старых и новых цен

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

pic
Сравниваем цены в процентном отношении

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

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

pic
Вот такую табличку мы получили от поставщика: с названием товара, ценой, кодом и количеством

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

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

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

Массив в Excel и Google Таблицах — это определенный набор данных, можно сказать, та же таблица, только «виртуальная». 

Массивы могут быть одномерными, то есть состоять только из строк вроде {1\2} или столбцов вида {1;2}. Или же многомерными — включать и столбцы, и строки.

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

pic
Поля можно создавать в листе с товарами или в новом

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

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

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

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

pic
Так выглядит формула с массивами

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

pic
Проверяем, как все работает

Есть и другие способы работать с ВПР, если искомое значение слева. Например, с помощью функции СУММЕСЛИ, ВЫБОР, ИНДЕКС и ПОСКПОЗ — о таком варианте рассказывали на сайте «Планета Excel». Другую полезную инструкцию по работе с массивами выкладывали в телеграм-канале «Google Таблицы».

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

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

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

pic
Ищем Iphone из списка

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

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

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

pic
Проверяем, как работает фильтрация со звездочкой

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

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

pic

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

Также ВПР можно настроить для нескольких условий одновременно, о таком способе рассказывал в своем блоге Евгений Намоконов. Пригодится, например, чтобы быстро найти стоимость битого Iphone 12 из таблицы.