10 825 
Оставить комментарий
Сохранить статью:
Что это? Функция ВПР в Еxcel (или иначе VLOOKUP, то есть вертикальный просмотр) позволяет работать с двумя таблицами одновременно, оперируя их данными путем переноса значений из одной в другую.
Зачем нужна? К примеру, вам нужно оценить бюджет предстоящего маркетингового мероприятия, когда в одной таблице прайс-лист, а в другой – предполагаемое количество единиц разного мерча. Как же в этом случае использовать ВПР в Еxcel?
В статье рассказывается:
- Зачем нужна функция ВПР в Excel
- Пошаговая инструкция по работе с ВПР в Excel
- Поиск по нескольким критериям
- Быстрое сравнение двух таблиц с помощью ВПР в Excel
-
Пройди тест и узнай, какая сфера тебе подходит:
айти, дизайн или маркетинг.Бесплатно от Geekbrains
Зачем нужна функция ВПР в Excel
Допустим, вы занимаетесь продажей автомобилей. У вас есть две таблицы: в одной указаны характеристики и цены каждой модели, в другой находится список клиентов с контактами, которые желают приобрести ту или иную машину.
Перед вами стоит задача обзвонить всех потенциальных покупателей, чтобы сообщить окончательную стоимость выбранного ими автомобиля. Но прежде требуется сопоставить имеющиеся у вас данные путем переноса колонки с ценой в таблицу с данными клиентов.
Однако сделать это не так просто. Нужного результата вы не достигнете, если просто скопируете и вставите столбец. А поиск и перенос цен вручную – долгая и кропотливая работа.
Как раз с помощью функции ВПР в Excel можно произвести сравнение двух таблиц и перенести стоимость забронированных автомобилей из каталога в клиентский список.
Пошаговая инструкция по работе с ВПР в Excel
Итак, как работает ВПР в Excel? На самом деле принцип довольно прост: функция анализирует выбранный диапазон таблицы, двигаясь сверху вниз в поисках заданного значения. Как только идентификатор найден, ВПР копирует в другую таблицу данные из нужной колонки напротив него.
Ниже мы разберем, как происходит определение требуемых значений, а прямо сейчас предлагаем понятную инструкцию – как сделать ВПР в Excel на примере продажи автомобилей.
входят в ТОП-30 с доходом
от 210 000 ₽/мес
Скачивайте и используйте уже сегодня:
Топ-30 самых востребованных и высокооплачиваемых профессий 2023
Поможет разобраться в актуальной ситуации на рынке труда
Подборка 50+ бесплатных нейросетей для упрощения работы и увеличения заработка
Только проверенные нейросети с доступом из России и свободным использованием
ТОП-100 площадок для поиска работы от GeekBrains
Список проверенных ресурсов реальных вакансий с доходом от 210 000 ₽
Шаг 1. Построение функции
Первым делом следует выделить ячейку, в которую формула скопирует искомое значение.
В нашем примере это цены на машины, которые забронировали клиенты. Поэтому во второй таблице требуется создать столбец, прописать его название: «Цена» и выделить в нем ячейку напротив первого покупателя.
Приступаем к построению функции. Сделать это можно двумя способами: во вкладке «Формулы» выбрать пункт «Вставить функцию» либо в строке ссылок нажать на значок «fx».
После этого откроется окно «Построитель формул», в котором, используя поиск, нужно найти ВПР, а затем нажать кнопку «Вставить функцию».
Далее появится поле для ввода аргументов, которое необходимо заполнить. А как это сделать смотрим ниже.
Шаг 2. Заполнение значений функции
Перед тем, как объяснить выбор аргументов, рассмотрим понятие каждого.
Искомое значение, то есть наименование ячейки, содержащей данные, одинаковые для обеих таблиц. Именно по ним функция ВПР будет искать нужные. В нашем случае искомое значение – это модель автомобиля. ВПР находит ее в каталоге, берет соответствующую ей цену и копирует в таблицу с клиентами.
Как установить данный параметр?
- Курсор устанавливаем в поле «Искомое значение» в окне «Построитель формул».
- Выбираем ячейку А2, в которой содержится первое значение из столбца моделей автомобилей.
- Значение, которое мы указали, помимо построителя формул, дублируется в формуле строки ссылок, которая выглядит так: fx = ВПР (А2).
Таблица представлена диапазоном ячеек, из которого функция ВПР Excel выбирает данные для искомого значения. В него должны быть включены колонки с ним и теми данными, которые нужно подставлять в таблицу.
У нас это стоимость автомобилей. Соответственно, в область работы функции мы включаем столбцы «Модель», которые является искомым значением и «Цена», то есть то, что будет перенесено.
Последовательность действий при выборе диапазона:
- Устанавливаем курсор в поле «Таблица» в окне построителя формул.
- Возвращаемся к таблице каталога автомобилей.
- Выделяем диапазон с колонками «Модель» и «Цена». У нас получается: А2:Е19
- Теперь выбранную область нужно закрепить. На Windows для этого требуется выбрать значение диапазона в строке ссылок, а затем нажать клавишу F4. На macOS подтверждаем выбранную в строке ссылок область сочетанием клавиш Cmd + T. Это действие необходимо, чтобы функцию можно было протянуть вниз для ее правильного срабатывания во всех строках.
Диапазон, который мы установили, появляется в построителе формул и в строке ссылок. Визуально это выглядит так: fx=ВПР(A2;’каталог авто’!$A$2:$E$19).
Номер столбца — это порядковый номер колонки в первой таблице (каталог автомобиль в примере), содержащей значение, которое необходимо перенести во вторую. Счет ведется слева-направо.
Если нумерация колонок не проставлена, необходимо сделать это вручную. У нас столбец «Цена» под номером пять.
Устанавливаем курсор в поле «Номер столбца» в окне «Построитель формул», вводим нужное значение. Функция ВПР теперь выглядит следующим образом: fx=ВПР(A2;’каталог авто’!$A$2:$E$19;5).
Интервальный просмотр – это параметр настройки работы формулы.
- Если требуется точное совпадение данных, нужно ввести 0.
- Если допускается приближенное значение, ставим 1.
В нашем примере необходим поиск точных значений цены, поэтому выбираем 0.
Значение вводим в поле «Интервальный просмотр» и функция ВПР в Excel приобретает завершенный вид: fx=ВПР(A2;’каталог авто’!$A$2:$E$19;5;0)
Шаг 3. Получение результатов
После всех проделанных действий и установки требуемых аргументов, жмем кнопку «Готово». В ячейке, выделенной нами в первом шаге, появится соответствующее значение – цена модели машины.
Таким образом, мы видим, что функция ВПР в Excel для одной строки сработала. Теперь протягиваем полученное значение вертикально вниз, заставляю формулу работать на оставшихся строчках. С этой целью ранее мы выбрали и закрепили диапазон.
Поиск по нескольким критериям
Мы рассмотрели простой ВПР в ExceL, где действовало лишь одно условие. Но может потребоваться сравнение большего количества областей по разным критериям. В таком случае потребуется построение функции ВПР с несколькими условиями.
Давайте попробуем выяснить, например, цену, по которой от ООО «Запад» нам поступил картон. Нужно произвести поиск значений по двум критериям: наименованию материала и поставщику.
на курсы от GeekBrains до 28 апреля
Загвоздка в том, что грузоотправитель доставляет товары разных наименований.
Порядок действий таков:
- В первую очередь нужно добавить в таблицу столбец слева, при этом произведя объединение «Материалов» и «Поставщиков».
- Аналогично совмещаем искомые значения.
- Задаем аргументы для функции ВПР: =ВПР(I6;$A$2:$D$15;4;ЛОЖЬ)
- Нажимаем «Готово». Поиск завершен, цена найдена.
ТОП-100 площадок для поиска работы от GeekBrains
20 профессий 2023 года, с доходом от 150 000 рублей
Чек-лист «Как успешно пройти собеседование»
Предположим, что данные по материалам у нас внесены в виде раскрывающегося списка. Тогда настройку функции ВПР в Excel нужно произвести таким образом, чтобы цена отображалась при выборе наименования товара.
- Создаем раскрывающийся список: устанавливаем курсор в ячейку Е8.
- Переходим во вкладку «Данные» в меню «Проверка данных».
- Тип данных, нужный нам – «Список», для отбора указываем область с названием материалов.
- Подтверждаем свои действия нажатием клавиши «ОК». Раскрывающийся список создан.
Следующий этап – настроить ВПР данных в Excel так, чтобы при выборе конкретного материала переносилась соответствующая ему цена.
Используем «Мастер функций» и устанавливаем аргументы. Искомое значение – ячейка с раскрывающимся списком; таблица – диапазон, включающий в себя «Материалы» и «Цены» (столбец 2). Наша формула должна выглядеть так: =ВПР(E8;A2:B16;2;ЛОЖЬ)
Подтверждаем настройку нажатием соответствующей клавиши.
Быстрое сравнение двух таблиц с помощью ВПР в Excel
Для обработки информации, содержащейся в объемных таблицах, нет ничего проще и полезней данной функции.
Пример. В связи с изменением прайса, перед нами поставлена задача: произвести сравнение между старыми и новыми ценами.
Для упрощения воспользуемся формулой ВПР в Excel для сравнения:
- Открываем старый прайс и добавляем колонку «Новая цена».
- Встаем на первую ячейку и через «Мастер функций» выбираем ВПР. Задаем параметры и получаем: =ВПР($A$2:$A$15;’новый прайс’!$A$2:$B$15;2;ЛОЖЬ).
- Таким образом мы сделали следующее: указали диапазон наименований А2:А15 в старом прайсе и сравнили его с новым. После чего значения новых цен подставили в ячейку С2 созданного столба прежнего прайса.
С помощью этой функции можно не только проводить сравнительный анализ, но высчитывать разницу в процентах и количестве.
Мы рассказали, как работает функция ВПР в Excel, используя пошаговую инструкцию на конкретном примере. Это полезный инструмент, позволяющий в кратчайшие сроки получить нужный результат. Сначала это может показаться сложным, но уверяем вас: потратив некоторое время на то, чтобы разобраться с формулой, вы сэкономите куда больше.
Разберем 11 самых важных жизненных вопросов
Только до 
Эксклюзивные лекции по основам программирования
