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

Возможности ВПР в Excel

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

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

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

Так, шаг за шагом, Vlookup переносит данные из одной таблицы в другую. Это очень удобно, когда нужно объединить информацию из разных источников.

Как пользоваться ВПР

1. Добавьте новый столбец в таблицу, куда нужно подставить данные.

2. Введите формулу ВПР в ячейку нового столбца.

3. Укажите ссылку на ячейку с искомым значением (например, артикул товара).

4. Выделите диапазон ячеек, где нужно искать значение (массив данных).

5. Введите номер столбца в массиве, из которого нужно вернуть значение (например, 2 — для цены).

6. Укажите «ЛОЖЬ» для точного совпадения или «ИСТИНА» для приблизительного.

7. Нажмите Enter — теперь можно протянуть формулу на весь столбец.

Интерфейс Excel: где находится функция ВПР

Функция ВПР находится во вкладке «Формулы» в группе «Ссылки и массивы». Кликните на эту группу и увидите ВПР в выпадающем списке.

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

Описание аргументов функции ВПР

У ВПР 4 основных аргумента:

1. Что ищем — искомое значение (например, артикул товара).

2. Где ищем — диапазон ячеек, в котором ищем значение (массив данных).

3. Номер столбца — номер столбца в массиве, из которого берем результат.

4. Интервальный просмотр — тип сопоставления (точный или приближенный).

Важно: ВПР всегда ищет искомое значение в первом столбце массива данных.

Поиск по нескольким условиям

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

Для этого объедините условия с помощью операторов «&». Например: 

=ВПР(A2&B2,Таблица,3,0)

Эта формула будет искать сочетание значений из ячеек A2 и B2. 

Поиск по частичному совпадению

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

=ВПР(“*”&A2&”*”,Таблица,2,0)  

найдет все значения, содержащие текст из ячейки A2. Это удобно для поиска по части артикула, имени, адреса и т. д.

Использование ВПР для нескольких таблиц

Если данные хранятся в разных таблицах, введите адрес диапазона в аргументе «Где ищем». Например:  

=ВПР(A2,Цены!A:B,2,0)

Здесь ВПР будет искать значение из ячейки A2 на листе «Цены» в столбцах A и B.

Где применяется функция ВПР

Допустим, у вас есть список клиентов с номерами договоров. В другой таблице — подробная информация по каждому договору (сумма, дата, менеджер). 

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

Пошаговый пример расчета с помощью ВПР

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

Шаг 1. Разместите прайс-лист на том же листе, что и ведомость продаж. Пусть артикулы будут в столбце A, а цены — в столбце B.

Шаг 2. В ведомости продаж добавьте колонку «Цена». В эту колонку введите формулу:

=ВПР(артикул;прайс-лист;2;0)

где:

• артикул — ссылка на ячейку с артикулом товара.

• прайс-лист — диапазон, где ищем цену (столбцы A и B).

• 2 — номер столбца, откуда берем цену.

• 0 — точное совпадение (для артикулов).

Шаг 3. Скопируйте формулу на все строки. Готово! Теперь вы можете умножить количество на подставленную цену и получить сумму продажи.

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

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

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

Символьные шаблоны ВПР

Если прямого совпадения нет, можно искать по частичному совпадению, используя символьные шаблоны. Добавьте звездочку (*) перед и/или после искомого значения. Тогда ВПР будет искать все ячейки, которые содержат указанный текст в любом месте. 

Например, формула 

=ВПР("*иванов*";сотрудники;2;0)

найдёт всех Ивановых, Иванову, Иванова и т. д. в таблице сотрудников.

Символьные шаблоны особенно полезны при поиске по неполным данным — именам, адресам, описаниям и т.д.

Больше функций Excel вы можете узнать в статье «9 фишек и возможностей Excel, которые вас удивят».

В каких профессиях нужно знать, как пользоваться ВПР в Excel

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

Бухгалтеры и экономисты

С помощью ВПР можно автоматизировать такие рутинные операции:

• Начисление зарплаты. ВПР поможет подтянуть оклады и коэффициенты из справочника должностей в ведомость по сотрудникам. 

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

• Сверка данных. ВПР за секунды сравнит данные из разных источников и покажет расхождения.

Экономисты используют ВПР, чтобы:

• Составлять прогнозы. ВПР подтягивает данные из таблиц с прошлыми периодами и помогает строить тренды.

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

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