Выбрать курс

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

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

Финансы
Инструкции
14
310
04.10.2024
Вы узнаете:

Возможности ВПР в 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.

Пройдите обучение в Академии Eduson

Записывайтесь на курс «Excel и Google-таблицы для бухгалтера»: научитесь создавать собственные формулы и сводные таблицы, работать с надстройками, макросами и функциями, быстро импортировать информацию из разных источников и строить наглядные графики и диаграммы.

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

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

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

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

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

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

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

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

где:

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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


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

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

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

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

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

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

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


Записывайтесь на курс «Excel и Google-таблицы для бухгалтера»: научитесь создавать собственные формулы и сводные таблицы, работать с надстройками, макросами и функциями, быстро импортировать информацию из разных источников и строить наглядные графики и диаграммы.

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

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

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

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


Аналитики и маркетологи

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

  • Объединяют данные. ВПР соберет в одну таблицу данные по продажам из CRM-системы, рекламным кампаниям, опросам клиентов.

  • Сегментируют клиентов. Эта функция поможет разделить клиентов на группы по заданным критериям (например, по сумме покупок, частоте заказов, демографическим признакам) и найти наиболее ценных и перспективных покупателей. На основе этих данных можно персонализировать коммуникации и предложения.

Вот несколько примеров, где эта функция будет полезна для маркетологов:

  • Работа с подрядчиками. Можно вести базу подрядчиков (дизайнеров, копирайтеров) и подтягивать их контакты и цены прямо в бриф. 

  • Медиапланирование. С ВПР легко сводить прайсы разных площадок и рассчитывать общий бюджет кампании.

  • Анализ конкурентов. ВПР поможет собрать данные по конкурентам из разных файлов в одну сводную таблицу.

Менеджеры по продажам

Менеджеры по продажам постоянно работают с номенклатурой и ценами и используют ВПР для следующих задач:

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

  • Ведение клиентской базы. В карточку клиента можно подтянуть контактные данные, сумму покупок, сегмент.

  • Планирование продаж. ВПР соберёт данные по продажам из CRM и рассчитает квоты для менеджеров.


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


Почему ВПР может не работать

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

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

Самая частая ошибка — #Н/Д. Она означает, что ВПР не смогла найти нужное значение в таблице. Вот основные причины:

  • Искомое значение не совпадает по типу или формату с данными в таблице. Например, вы ищете число, а в таблице — текст.

  • В искомом значении или таблице есть лишние пробелы или невидимые символы.

  • Неправильно указан диапазон поиска или номер столбца.

  • В диапазоне есть пустые ячейки или повторяющиеся значения.

Другая ошибка — #ССЫЛКА!. Она возникает, когда неверно указан диапазон или ячейки в нем удалены.

Также ВПР может выдать неправильный результат, если в строке формул задан неточный критерий поиска или неверный аргумент.

Чтобы ВПР корректно работала, проверьте исходные данные:

  1. Убедитесь, что искомое значение и данные в таблице имеют одинаковый тип (текст, число, дата).
  2. Проверьте формат данных — он должен совпадать. Если в таблице даты в формате ДД.ММ.ГГГГ, то и искомую дату нужно вводить так же.
  3. Удалите лишние пробелы и невидимые символы в искомом значении и таблице. Используйте формулу СЖПРОБЕЛЫ или инструмент «Найти и заменить».
  4. Проверьте, нет ли в диапазоне поиска пустых ячеек или дублей. Они могут нарушить работу функции.

Проверка правильности введенных данных

Чтобы ВПР корректно работала, проверьте исходные данные:

1. Убедитесь, что искомое значение и данные в таблице имеют одинаковый тип (текст, число, дата).

2. Проверьте формат данных — он должен совпадать. Если в таблице даты в формате ДД.ММ.ГГГГ, то и искомую дату нужно вводить так же.

3. Удалите лишние пробелы и невидимые символы в искомом значении и таблице. Используйте формулу СЖПРОБЕЛЫ или инструмент «Найти и заменить».

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

Удаление дубликатов

Если в диапазоне поиска есть повторы, ВПР вернёт только первое совпадение. Чтобы этого избежать, нужно удалить дубли.

Самый простой способ — использовать функцию «Удалить дубликаты». Выделите диапазон, перейдите на вкладку «Данные» и нажмите кнопку «Удалить дубликаты».

Если нужно оставить только уникальные значения, примените формулу СЧЁТЕСЛИ. Она посчитает количество повторов для каждого значения, и вы сможете отфильтровать строки, где число повторов больше 1.

Исправление значений для корректной работы ВПР

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

  • Исправить значение регистра текста (для ВПР «Иванов» и «иванов» — разные значения).

  • Удалить лишние символы (пробелы, кавычки, скобки).

  • Округлить числа до нужного разряда.

  • Привести даты к одному формату. 

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


Совет: если всё же ВПР выдает ошибки, проверьте ещё раз строку формул и каждый аргумент по отдельности.
Скорее всего, вы забыли исправить значение, удалить дубли или указали «точное совпадение».

Какие ограничения есть у ВПР

У функции ВПР в Excel есть несколько ограничений, о которых важно помнить:

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

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

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

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

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

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

7. ВПР плохо работает с большими объёмами данных. Если диапазон поиска содержит десятки тысяч строк, формула будет выполняться медленно. В таких случаях лучше использовать другие инструменты, например, Power Query или сводные таблицы.

8. ВПР не поддерживает поиск по нескольким критериям. Если нужно искать по комбинации значений из разных столбцов, придётся использовать другие функции (например, ИНДЕКС и ПОИСКПОЗ) или объединять критерии в одну ячейку.


Присоединяйтесь к курсу по Excel, если хотите автоматизировать работу и высвободить время под аналитические задачи или отдых.

Екатерина Озерова
Копирайтер

Бегает, крутит педали и много смеётся.

Убеждена, что главное в жизни — наслаждаться каждым днём, даже рабочим.

ЧЁРНАЯ ПЯТНИЦА в Академии Edusonскидки -75% на все курсы
-75
%
на все
курсы
Подобрать обучение