Функция ВПР в Excel: гид для бизнеса
Привет! Я Марко Очоа, Product Marketing Manager в Т‑Реклама. Мы публикуем серию статей, которая поможет сориентироваться в терминах, подходах и темах на стыке технологий и маркетинга. Сегодня поговорим о функции ВПР.
ВПР — это функция вертикального просмотра. Она помогает работать с большими объемами данных и автоматизирует задачи, когда нужно найти что‑то в одной таблице и подставить в нужное место в другую таблицу. К примеру, ВПР поможет автоматически собрать несколько отчетов в один, перенести информацию о ценах или артикулах, или собрать вместе все данные о рекламных кампаниях.
На первый взгляд может показаться — это же можно сделать вручную, зачем нужна функция? На самом деле, найти и перенести данные вручную можно, когда в таблице 10–20 строк. А если их 100, 500 или 1000? Тут и пригодится автоматизация.
Что такое ВПР и какие задачи она помогает решать
ВПР, в англоязычной версии Vlookup, — это функция вертикального просмотра в таблицах Excel и Google Sheets.
Как она работает — сразу на примере.
У вас есть две таблицы. В первой — список всех товаров на маркетплейсе с артикулами. Во второй — тот же список, но с ценами.
Вам нужно перенести цены из одной таблицы в другую, но делать это вручную долго и сложно: нужно найти название товара, скопировать цену, открыть другую таблицу и перенести данные туда.
Вы вводите формулу вертикального просмотра, указываете нужные данные (об этом мы поговорим ниже), и Excel самостоятельно это делает.
Excel берет название, которое вы указываете, например, «Покрывало стеганое коричневое 200х200». Дальше идет в другую таблицу и ищет там ячейку, в которой написано то же самое.
Когда находит, берет данные из столбца с ценами и переносит эту информацию в таблицу, где вы вводили формулу. При этом заполнять формулу отдельно для каждой ячейки не нужно, ее можно будет растянуть на весь нужный диапазон, об этом тоже поговорим ниже.
Вот еще несколько примеров, когда ВПР нужна в работе:
- Сравнить эффективность рекламных кампаний. К примеру, нужно оценить, какая кампания сработала хорошо, а какую можно остановить.
- Автоматизировать отчетность. Можно автоматически переносить данные из разных таблиц в одну, а не собирать конверсию, клики или покупки для сотен товаров из десяти таблиц.
- Сравнить разные сегменты. К примеру, вы ведете таблицу с сегментами аудитории по полу, возрасту, разным городам. Эту таблицу можно дополнить данными по покупкам или конверсии из других отчетов, чтобы все данные о сегментах хранились в одном месте.
Словом, ВПР поможет в любой задаче, где нужно работать с большими объемами данных и переносить много информации из одной таблицы в другую.
Рассылка: как вести бизнес в России
Пять полезных писем пришлем сразу после подписки. В них — бизнес‑идеи, готовые промпты для нейросетей, советы, как выбрать налоговый режим и получать пассивный доход
Из чего состоит формула ВПР
Формула ВПР содержит информацию о том, какое значение нужно найти, в каком диапазоне ячеек искать, из какого столбца нужно взять данные и каким должно быть совпадение: точным или примерным. Вот как она выглядит:
=ВПР(искомое значение;таблица;номер столбца;интервальный просмотр)
Все значения в формуле вводятся через «;» и без пробелов.
Рассмотрим подробнее каждый элемент формулы.
Искомое значение — это слово или словосочетание, которое формула будет искать в другой таблице. Его можно указать как «адрес» ячейки. Например, если в формуле укажем «А3», а в ячейке написано «Стеганое одеяло», формула будет искать во второй таблице ячейку, где написано «Стеганое одеяло». Второй вариант — прописать искомое значение словами прямо в формуле, но этот вариант сложнее: можно ошибиться в слове или поставить лишний пробел, и формула ничего не найдет.
Таблица — это «поле», где формула будет искать нужное нам значение. Таблицу можно не записывать вручную, а выбрать мышкой нужное поле при вводе формулы. Она отображается в формуле как диапазон в формате «A2:C6».
Номер столбца — это столбец, откуда формула вытащит значение, которое нам нужно добавить. Столбцы считаются по порядку слева направо в том диапазоне, который мы обозначили для поиска: если выбрали столбцы от B до E, первым столбцом будет B. Например, если нам нужно перенести в таблицу артикулы, а они в третьем столбце, мы пишем в формуле «3».
Интервальный просмотр — это точность поиска. Ее значение может быть «Истина» или «Ложь». «Истину» еще записывают как 1 или True. Для «Истины» формула ищет значения, которые примерно похожи на то, что нам нужно. К примеру, для запроса «Сидоров А.И.» может взять «Сидоров А.Н». А если указать «Ложь» (она же 0 или False), формула будет искать точное совпадение. По умолчанию формула автоматически использует «Истину», так что результаты поиска могут быть неточными — в начале работы нужно поменять параметр на «Ложь».
На первый взгляд формула выглядит сложно — но в Excel ее не нужно вводить полностью вручную, есть специальный инструмент подстановки формул. Ниже рассмотрим на примере двух таблиц, как воспользоваться функцией ВПР и перенести данные автоматически.
Пошаговая инструкция: как сделать ВПР в Excel
ВПР можно использовать в том виде, который мы показали выше: функция найдет указанное значение и вернет в таблицу цены, артикулы или показатели рекламной кампании.
Бывают задачи сложнее, когда нужно учитывать при поиске не только название, но и, к примеру, цвет или размер товара. К такому поиску нужно будет подготовиться отдельно. Для начала рассмотрим базовый вариант, а затем — усложненный.
В нашем примере есть две таблицы. В первой — информация о товаре: артикул, название, стоимость, размер. Во второй таблице — количество продаж по каждому товару. Нам нужно перенести данные о продажах в первую таблицу.
Мы работаем в официальной онлайн‑версии Excel. Он немного отличается от стационарной программы интерфейсом, но принцип работы и расположение основных кнопок — такое же.
Шаг 1. Готовимся к работе с функцией. ВПР помогает ускорить выполнение рутинных задач, но не автоматизирует его полностью: функция не может брать данные в разных файлах. Поэтому для начала нужно все данные для работы перенести в одну таблицу, но на разные листы. Таблицу нужно скопировать и вставить в нужный документ на другой лист, чтобы получилось вот так:
Дальше на листе, куда будем переносить информацию, создаем новый столбец с нужным названием. Мы будем переносить информацию о количестве продаж, поэтому столбец назовем Продажи.
Шаг 2. Выбираем нужную функцию. Это можно сделать двумя способами. Можно просто ввести в ячейке формулу: «=ВПР» и дальше подставить нужные значения. Но в первый раз вручную прописывать элементы в правильном порядке может быть сложно, поэтому мы воспользуемся подстановщиком.
Выбираем нужную ячейку и слева от строки формул нажимаем на значок «fx», вводим в поиске «ВПР» и выбираем нужную.
После этого появляется окно, в котором нужно заполнить данные для функции. В стационарной версии Excel оно может быть справа от таблицы. Рассмотрим подробнее, как заполнять каждый элемент функции.
Шаг 3. Указываем искомое значение — что именно функция будет искать на другом листе. Нажимаем на нужную ячейку, и ее адрес автоматически подставляется в формулу. В нашем случае это первый товар — «Покрывало стеганое».
Шаг 4. Указываем диапазон поиска — где функция будет искать нужное нам название. Чтобы указать диапазон, нужно открыть Лист 2 и выделить всю таблицу, диапазон автоматически проставится в формуле.
Шаг 5. Указываем столбец, откуда функция вытащит нужные нам данные. Столбцы считаются слева направо в таблице, которую мы выбрали на прошлом шаге.
Шаг 6. Указываем точный или приблизительный поиск. Точный поиск обозначается как «ЛОЖЬ», 0 или False в англоязычной версии. Это означает, что функция будет искать точное соответствие тому значению, которое мы указали. «ЛОЖЬ» стоит использовать всегда, когда вам нужно, чтобы функция нашла конкретно то, что вы указали в первой строке.
Приблизительный поиск обозначается как «ИСТИНА», 1 или True в англоязычной версии. Почему использовать его нужно с осторожностью: например, вы ищете сотрудника, который в таблице указан как «Иванов И.И». Если в строке интервальный просмотр указать «ИСТИНА», функция может взять данные из строки «Иванов И.Н.» или «Иванов И.М.» — это ведь приблизительно.
Другими словами, в этом критерии функция спрашивает: «Мы ведь приблизительно ищем, правда?». Если ответить «ИСТИНА», то ответом будет «Да, ищем приблизительно». Если «ЛОЖЬ» — то «нет, неправда, нам нужно точное соответствие».
Готово, функция подставила нужное нам число — количество продаж. А дальше нужно растянуть формулу вниз на все нужные нам строки. Тут есть нюанс, о нем на следующем шаге.
Шаг 7. Растягиваем формулу на нужные ячейки. Если просто растянуть эту формулу на все ячейки, будет сдвигаться и диапазон поиска. Но мы ищем значение среди конкретных строк, так что диапазон нам нужно закрепить. Вот как это сделать: перед каждой буквой и цифрой диапазона ставим знак $.
Было: A2:C11 → Стало: $A$2:$C$11
Отлично, теперь формулу можно растянуть на все нужные нам ячейки.
Готово, теперь мы видим количество продаж по каждому товару с другого листа таблицы.
ВПР по нескольким критериям
Иногда у товаров может быть несколько параметров, по которым их должна искать функция.
Например, у нас есть несколько пледов с одинаковыми названиями, но разными размерами. Из‑за этого подтянуть информацию о количестве продаж стандартной формулой не получится. Функция возьмет первое точное совпадение и перенесет информацию в таблицу без учета размеров. Поэтому данные будут неверными.
Шаг 1. Объединяем нужную для поиска информацию. Сначала нам нужно объединить данные из разных столбцов в один с помощью функции &.
Вот как это сделать:
- В обеих таблицах создаем вспомогательный столбец. В первой его ячейке пишем «=», выбираем ячейку с названием.
- Дальше прописываем значок «&» и выбираем ячейку с размером товара. То есть у вас получится формула вида «=С2&D2».
- В ячейке вспомогательного столбца отображается название товара и его размер.
- Дальше растягиваем формулу на все строки вспомогательного столбца. Здесь значения у нас должны сдвигаться вниз по строчкам, так что никаких дополнительных «закрепляющих» значков ставить не нужно.
Делаем то же самое на другом листе: объединяем размер товара и его название в один столбец.
Шаг 2. Подставляем формулу ВПР и получаем верный результат.
В гугл‑таблицах ВПР работает точно так же, только формулу нужно будет вводить вручную в нужной ячейке. Вот формула, для примера сразу добавим конкретные данные:
=ВПР(B2;Лист2!$A$2:$F$11;6;ЛОЖЬ)
Вам нужно было только подставить свои цифры. Коротко напомним, что означает каждый элемент формулы:
- B2 — значение, которое функция будет искать;
- Лист2! — название листа, на котором находится таблица, откуда забрать данные;
- !$A$2:$F$11 — закрепленный диапазон, в котором функция будет искать совпадения;
- 6 — номер столбца, откуда функция возьмет данные; обратите внимание, что первый столбец — это не обязательно столбец «А» — это самый левый столбец в диапазоне, который вы указали для поиска;
- ЛОЖЬ — или 0 — значит, что функция будет искать точное совпадение.
Какие бывают ошибки при работе с функцией
Главное про работу с ВПР
ВПР, вертикальный просмотр или Vlookup помогает работать с большими объемами данных и автоматизирует задачи, когда нужно найти что‑то в одной таблице и перенести в другую, например, автоматически собрать несколько отчетов в один или совместить информацию о ценах и артикулах.
Что важно на практике:
- Функция ищет нужное значение в другой таблице, берет данные из нужного столбца и возвращает их в рабочую таблицу.
- Функция ВПР строится так: какое значение нужно найти, в каком диапазоне ячеек искать, из какого столбца нужно взять данные и каким должно быть совпадение: точным или примерным.
- Чтобы результаты были корректными, надо перепроверить, что диапазон поиска не сдвигается для всех строк таблицы.
