ВПР в Excel: что такое и как пользоваться

Что такое функция ВПР в Excel

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

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

Принцип работы формулы такой: функция анализирует выбранную область и столбец, а когда находит нужное значение, копирует его в другую таблицу, из которой поступил запрос. Работать можно на нескольких листах. Например, когда исходные данные «лежат» на Листе-1, а перенести информацию нужно на Лист-3.

Общая формула ВПР выглядит так: =ВПР (искомое значение; область для поиска; номер столбца, в котором нужное значение; 0 (если ЛОЖЬ) или 1 (если ИСТИНА).

«ЛОЖЬ» ставят, когда допустимо точное значение, а «ИСТИНА» ― если нужны приблизительные данные. Это актуально только для функции ВПР. В остальных случаях «ИСТИНА» означает, что условие выполняется (например, 15>5), а «ЛОЖЬ», что не выполняется (например, 15<5).

Для чего можно использовать ВПР в Excel

Чаще всего функцию ВПР используют, когда нужно перенести данные из одного списка в другой, а вручную это сделать невозможно. К примеру, вам нужно посчитать зарплату 800 сотрудникам. У вас есть шаблон ведомости в Excel, где проставлены ФИО и табельные номера, а оклады находятся в другой таблице. Если открыть документы в двух окнах и перебивать вручную, то на это можно потратить несколько дней, а если правильно вбить формулу — от 5 до 10 минут.

Из исходной таблицы слева нужно найти зарплату сотрудников и перенести в таблицу с ведомостью

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

• Найти цену на товар.
• Узнать номер договора с конкретным предприятием.
• Вычленить из общего реестра единичные данные, например только ФИО и возраст каких-то сотрудников, или перенести в одну таблицу данные о работниках мужчинах, а в другую — о женщинах.
• Найти в реестре товары, которые не продавались в течение года, и перенести их в отдельную таблицу.

В каждой компании ВПР используют в разных целях. Функция пригодится тем, кто работает с маркетплейсами, бухгалтерам, экономистам и аналитикам. А если проанализировать бизнес с помощью Excel не получается, то сервис сквозной аналитики от Calltouch даст все необходимые данные для улучшения маркетинговой стратегии.

Как создать функцию ВПР в Excel

Данные из 10-20 ячеек можно посчитать и вручную. Обычно формулу применяют для 100 и более наименований. Чтобы было проще понять, как работает ВПР, разберем функцию на примере. У нас есть таблица в Excel, в которой указаны ФИО директоров, названия их фирм и номера договоров. Найдем номер договора, который заключен с Лысенко Анной Викторовной.

Таблица с выходными данными

В строке ниже нам нужно автоматически найти № договора

В поле В13 начинаем писать функцию следующим образом:

1. Первое — «=ВПР», чтобы чтобы начать работу с формулой.
2. А13 ― так как наше искомое значение — это «Лысенко Анна Викторовна», поэтому выбираем ячейку, где уже вбиты ФИО.
3. Область для поиска в данной таблице — А2:С7. Название столбцов сюда не входят. Выделяем ту зону, по которой функция ВПР будет искать информацию.
4. Вводим номер столбца, из которого нужно взять значение. В примере это столбец С. Он третий слева. Выбирать нужно из массива, который участвует в формуле. В случае, когда нам понадобится информация не по номеру договора, а по названию компании, в формуле нужно будет поменять всего одно значение: вместо 3 — 2.
   

   Нужно поменять номер столбца, чтобы узнать другую информацию

5. Конечный этап ― выбрать «ЛОЖЬ». Программа сама подскажет этот шаг, когда все значения будут выбраны. Не забудьте закрыть скобку, иначе формула ВПР не сработает. После того как формула внесена, мы получаем ответ на запрос. 

Результат

Чтобы отработать навыки, можно потренироваться на маленьких таблицах. 

Функция ВПР для нескольких условий

Бывают ситуации, когда нужно подтянуть в другую excel-таблицу конкретное значение, но по нескольким параметрам сразу. Например, к компании и номеру договора нужно подобрать ФИО директора. Но функция ВПР не ищет по нескольким столбцам. Выйти из этой ситуации можно за счет создания дополнительного столбца. Поместить информацию в ячейку можно с помощью формулы «СЦЕП». 

Исходная таблица со вспомогательным столбцом

Теперь в соответствии и синтаксисом функции вбиваем формулу:

• Критерий поиска ― это 3 параметра (компания, номер договора и предмет договора). Исходя из этих значений, нужно найти ФИО директора.
• Область таблицы важно выбрать вместе со вспомогательным столбцом.
• Так как мы хотим узнать ФИО, стоит указать столбец «2» как искомое значение.
• Цель — найти точное значение. Поэтому выбираем «ЛОЖЬ».

Вбиваем формулу

Если проставить знак «$», то формулу можно протягивать. Этот способ подойдет, если искать цену продукта в конкретные дни или искать выручку магазина по его подразделению и городу. Чтобы в дальнейшем интерпретировать эту информацию с выгодой, побеспокойтесь о маркетинговых знаниях заранее. Тем более, что базовые навыки можно получить всего за 1,5 часа.

Знания про маркетинг, аналитику, диджитал, быстро и бесплатно

• Новый скилл всего за 1,5 часа
• Практические знания
• Интерактивные юниты
• Без оплат и встроенных покупок

Пройти юнит

Функция ВПР по частичному совпадению

Представим ситуацию. Задача — найти информацию по фирме Василия, но вы не знаете его фамилию. А в excel-таблице с информацией нет отдельного критерия «Имя». Тогда вам поможет функция ВПР по частичному совпадению. Выражение будет выглядеть так: =ВПР(«*имя*»;таблица;столбец со значением;ЛОЖЬ).

Ищем информацию по частичному совпадению

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

Функция ВПР для нескольких таблиц

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

Как сравнить данные в таблицах

В I3 вводим формулу: =ВПР(ФИО работника;диапазон из левой таблицы;столбец;ЛОЖЬ). Затем копируем формулу в строки ниже, как на примере.

Формула удобна для сравнения текущих и старых цен или начальной цены и скидки. Так можно мониторить, как меняются цены у поставщиков или продажи в зависимости от стоимости продуктов.

Аналоги ВПР

Функция ВПР довольно сложная, но ее можно заменить другими формулами в Excel:

• ПРОСМОТР (LOOKUP) доступна в новых Google-таблицах и помогает найти столбец или строку с нужным значением;
• ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) ― при одновременном использовании ищут положение запроса в графах и перемещают в другую ячейку;
• СУММЕСЛИ (SUMIF) ― суммирует выбранный диапазон. К примеру, если нужно просуммировать только те оклады, которые выше 36 000 руб.

Ошибки при использовании функции ВПР

Функция ВПР ― одна из самых сложных формул в Excel, поэтому в ней можно допустить ошибки. Одни неточности можно заметить сразу ― программа не сделает расчет. А бывает и так, что погрешность не видна ― результат есть, но неверный. Разберем типовые ошибки пользователя:

• #Н/Д ― распространенное значение, когда ссылка сделана неверно. Либо вы сослались на окно с неправильно заполненным значением, либо ячейка пустая, а в ссылке обязательно должны быть данные.
• Формула сработала, но когда протягиваешь ее, выдает неверные значения. Это самая опасная ошибка, так как в крупных аналитических excel-таблицах ее трудно заметить. Такое бывает, когда вы не закрепили ссылку или таблицу. Чтобы закрепить ссылки, проставьте знак «$», нажав клавишу F4.
• Функция ВПР дает результаты, но при проверке они неточные. Тогда проверьте, что стоит в последнем значении: 0 или 1 (ЛОЖЬ или ИСТИНА).
• #ЗНАЧ ― выбивает тогда, когда значение в строке слишком длинное.