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

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

Зачем функция ВПР есть в Excel 

ВПР или Vlookup расшифровывается как вертикальный просмотр. Функция имеет следующий синтаксис:

=ВПР(искомое значение;таблица;номер столбца;интервальный просмотр)

С помощью неё удобно сравнивать многосоставные данные, находящиеся в одном файле. Например, у вас есть информация о производителях телефона и их стоимости, а также контактные данные покупателей. Всё это находится в разных таблицах, а вам нужно позвонить потенциальным клиентам, которые оставили заявки, и сообщить подробности. Для этого как раз и удобно использовать описанную выше функцию — не придётся искать вручную цену смартфона в столбце, а затем переносить её. Функция поможет автоматически скопировать цены из списка с товарами в список с номерами. Теперь напротив каждого покупателя будет стоять не только производитель, но и цена, которую он должен заплатить. Такой подход помогает избежать большого количества ошибок и используется в проектах для решения многих задач.

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

Активно используете Excel в работе, но никак не можете разобраться с полным функционалом программы? Обратите внимание на онлайн-курсы, которые можно проходить в удобном для вас темпе прямо из дома:

• «Excel и Google-таблицы: от новичка до эксперта» от Eduson Academy
• «Power BI и Excel для продвинутых» от Нетологии

Запуск функции ВПР

Принцип работы Vlookup довольно простой: ВПР проводит анализ выбранного диапазона, передвигается сверху вниз, ищет заданное значение. Когда функция его находит, то выбирает информацию напротив него из нужного столбца и переносит во вторую таблицу. Excel — универсальная программа не только для сотрудников крупных компаний и стартапов. Её активно используют в бизнесе в различных случаях. Там никуда и без аналитики — на курсе от Moscow Business Academy подробно расскажут про инструменты капитализации, веб-аналитику и правильную постройку модели бюджета компании.

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

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

Маркетолог — востребованная профессия с широким спектром задач. На курсе «Интернет-маркетолог» покажут, как работать с аудиторией и настраивать рекламу. Один из блоков состоит из изучения специализированных программ, погружения в специфику их функционирования. 

Аргументы функции ВПР

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

Искомое значение — это наименование ячейки с одинаковыми данными, на основе которых функция и будет осуществлять поиск нужной информации в этом случае. В примере это тип смартфона. ВПР сможет найти подходящую в таблице с каталогом из всех моделей, затем вытащит из столбца точную стоимость и скопирует её в таблицу с покупателями. Чтобы установить указанный параметр и количество, выставите курсор в окне «Искомое значение». Затем выберите ячейку А2 — это первое значение столбца «Тип». Указанное нами значение аргумента в этом случае кроме построителя формул отразится в формуле строки ссылок. Выглядит это так — fx = ВПР (А2).

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

Таблица — аргумент-диапазон ячеек, в котором будет выполняться поиск и использование искомого и возвращаемого значения. Он должен состоять из столбцов с искомым значением и с теми данными, которые надо скопировать в первую таблицу. Мы переносим цену техники, это может быть и любой другой параметр. Диапазон для поиска обязательно должен включать столбцы «Тип» (искомое) и «Цена, руб.» (данные, которые переносим). Обратите внимание, что для правильного функционирования искомое значение должно быть в первом столбце по возрастанию. В нашем примере оно в ячейке A2, диапазон базы должен начинаться именно с A.

Действуйте так: установите курсор в поле «Таблица» в построителе формул, а затем вернитесь к таблице с марками смартфонов. Выберите диапазон с колонками «Модель» и «Цена». В примере это будет выбор — А2:Е19. Следующий шаг — необходимо сделать закрепление. Если работаете на Windows, выберите значение диапазона в строке ссылок и нажмите F4. MacOS требует другого подхода — сначала надо выбрать данные диапазона в строке ссылок, а затем нажать сочетание клавиш Cmd + T. Закрепление необходимо для того, чтобы при протягивании функции вниз, она нормально сработала во всех строках по количеству чисел. В формуле строки ссылок появится такое возвращаемое значение — fx=ВПР(A2; товары1′!$A$2:$E$7).

Следующий пункт — номер столбца. Это столбец, из которого нужно взять данные о количестве или цене, например. Из него мы и «подтягиваем» результат. Чтобы найти нужные цифры, считаем слева направо — цена телефона находится в пятом столбце слева. Важно установить курсор в окно «Номер столбца» и ввести число. У нас эти данные соответствуют 5 — указываем это число. Такой текст программа возвращает в формуле в строке ссылок: fx=ВПР(A2;’ товары1!$A$2:$E$7;5).

Последний аргумент — интервальный просмотр. Он поможет настроить точность работы функции. Аргумент принимает два значения для символов в каждом случае: «истина» или «ложь». При точном (истина) совпадении при поиске ВПР надо ввести 0, второй вариант: для приближенного значения (ложь) ставим 1. В примере важно точное значение — поэтому выбираем первый вариант. Значение 0 нужно ввести в окно «Интервальный просмотр». Оно тут же появится в формуле строки ссылок: fx=ВПР(A2;’ товары1′!$A$2:$E$7;5;0).

Другие инструменты пакета MS Office также важны для работы. На курсе «MS Office:Excel, Word и PowerPoint» вы научитесь эффективно работать с текстовыми документами и писать электронные материалы, анализировать большое количество данных с помощью таблиц и делать запоминающиеся презентации.

Результат использования ВПР в Excel

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

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

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

Поиск по нескольким критериям

Мы рассмотрели сравнение по одному критерию — марке телефона. Но часто при работе может потребоваться сравнение двух или более критерий. Например, у одной модели может быть 128 и 256 Гб памяти — так таблица содержит разные товары. Или же смартфоны разных оттенков. В Екселе можно использовать и представить эту функцию с несколькими условиями.

Если мы применим описанный выше путь ВПР, выйдет такой вариант функции: fx=ВПР(A2;’ товар’!$A$2:$E$7;5;0). Если оставить её в таком формате, то ВПР при подтягивании стоимости будет выбирать первый попавшийся вид без учёта цвета. Теперь надо осуществлять поиск стоимости и сортировку сразу по двум критериям — тип и цвет. 

Для этого добавьте в общую формулу — ЕСЛИ(‘товар’!$B$2:$D$7=B2):

‘товар’!$B$2:$D$7 — так мы выделяем диапазон цвета,

B2 — это искомое значение цвета, первая ячейка в столбце с цветом.

В итоге получилась такая функция: fx=ВПР(A2;ЕСЛИ(‘Товары’!$B$2:$D$7=B2;’Товары’!$A$2:$E$7);5;0).

«Инструменты бухгалтера: 1C: Бухгалтерия 8.3 и Excel» — обучение для бухгалтеров, которые хотят научиться работать с большими массивами данных. Вы также без труда сможете автоматизировать процессы заполнения документов на сайтах и регистрации хозяйственных операций.

Сравнение нескольких таблиц с помощью функции ВПР

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

1. Откройте таблицу со старыми ценами и добавьте колонку «Новая цена». 
2. Наведите курсор на первую ячейку и через «Мастер функций» выберите ВПР. Задайте параметры и получите: =ВПР($A$2:$A$9;’новые цены’!$A$2:$B$9;2;ЛОЖЬ). 
3. Что получилось? Мы выделили диапазон А2:А9 в старой таблице и сравнили его с новыми ценами. Теперь мы убрали новые значения в ячейку С2 нового столбца в старой таблице — это помогло вернуть знак соответствия и поставить значения в нужно порядке. 

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