В Майкрософт Excel очень много разных функций, которые значительно облегчают работу пользователю, и в этой записи мы поговорим про одну из таких. Называется она ВПР, а если на английском, то VLOOKUP.
Функция ВПР переносит значения из одной таблицы в определенные ячейки другой. Давайте объясню подробнее – в первой таблице вы выбираете значение, которое надо найти в левом столбце второй. Если есть совпадения, то ВПР переносит значение из указанного Вами столбца этой строки в первую таблицу. Хоть определение немного запутанное, пользоваться функцией не так уж и сложно. Рассмотрим несколько примеров.
Поскольку используют функцию чаще всего для заполнения столбца с ценой, которая указана в отдельных прайсах, то возьмем следующий пример. К примеру, есть таблица с фруктами (оранжевая), где для каждого указано, сколько килограмм мы хотим заказать. Соответственно, каждому фрукту необходимо выписать цену из прайса (зеленый), который дал поставщик, и потом рассчитать, сколько обойдется закупка. Просматривать каждое наименование и переносить данные сложно, тем более, если строчек с товарами тысячи, так что применим ВПР.
Итак, давайте приступим к разбору функции ВПР в Экселе для чайников на моём примере.
Нажмите по верхней ячейке в первой таблице в столбце Цена, а потом кнопочку «fx» в строке формул, чтобы открыть окно мастера функций.
Там, где написано категория выбираем «Ссылки и массивы». В списке выделите ее и нажимайте «OK».
Следующее, что мы делаем – прописываем аргументы в предложенные поля.
Ставьте курсив в поле «Искомое_значение» и выделяйте в первой таблице то значение, которое будем искать. У меня это яблоко.
В строке «Таблица» необходимо выделить ту, из которой будут браться данные – шапку не выделяйте. Учтите, что крайний левый столбец должен состоять из значений, которые мы ищем. То есть, мне надо яблоко и прочие фрукты, значит, их перечень должен быть в левом столбце выделяемой области.
Чтобы после того, как мы напишем формулу и растянем ее по всему столбцу, выбранный диапазон не смещался вниз, надо сделать ссылки абсолютными: выделите данные в поле и нажмите «F4». В данный момент адрес на ячейки стал абсолютным, к ним добавился знак «$», и диапазон смещаться не будет.
Там, где номер столбца, поставьте цифру, соответствующую во второй таблице тому столбцу, данные откуда надо переносить. У меня прайс состоит из фруктов и цены, мне надо второе, так что ставлю цифру «2».
В «Интервальный_просмотр» пишем «ЛОЖЬ» – если искать надо точные совпадения, или «Истина» – если значения могут быть приближенные. Для нашего примера выбираем первое. Если ничего не указать в данном поле, то по умолчанию выберется второе. Потом нажимайте «OK».
Тут обратите внимание на следующее, если работаете с числами и указываете «Истина», то вторая таблица (это наш прайс) конечно должна быть отсортирована по возрастанию. К примеру, при поиске 5,25 найдется 5,27 и возьмутся данные с этой строки, хотя ниже может еще быть и число 5,2599 – но формула дальше смотреть не будет, ввиду того, что она думает, что ниже числа лишь больше.
Как же работает ВПР? Она берет искомое значение (яблоко) и ищет его в крайнем левом столбце указанного диапазона (перечень фруктов). При совпадении берется значение из этой же строки, лишь того столбца, который указан в аргументах (2), и переносится в нужную нам ячейку (С2). Формула выглядит так:
=ВПР(А2;$G$2:$H$12;2;ЛОЖЬ)
В данный момент можете растянуть ее на необходимое количество строчек вниз, потянув за правый нижний угол.
Все цены перенесены из прайса в таблицу закупок в соответствии с названиями фруктов.
Сейчас можем рассчитать столбец Стоимость. Растянем формулу на необходимое количество строчек.
Если у вас в первой таблице есть названия продуктов, которых нет в прайсе, у меня это овощи, то напротив данных пунктов формула ВПР выдаст ошибку #Н/Д.
При добавлении столбцов на лист, данные для аргумента «Таблица» функции автоматически изменятся. В примере прайс сдвинут на 2 столбца вправо. Выделим любую ячейку с формулой и видим, что вместо $G$2:$H$12 в данный момент $I$2:$J$14.
В данный момент давайте разберемся, как работать с функцией ВПР в Excel и с выпадающими списками. Для начала надо сделать выпадающий список. Выделяем ячейку, вверху открываем «Данные» и жмем по кнопочке «Проверка данных».
В открывшемся окне «Тип данных» будет «Список», ниже указываем область источника – это названия фруктов, то есть тот столбец, который есть и в первой и во второй таблице. Нажимайте «OK».
Дальше делаем так, чтобы при выборе 1-го из фруктов в нижней ячейке отображалась его цена.
Выделяю F2 и вставляю функцию ВПР. Аргумент первый – это сделанный список (F1).
Второй – таблица поставщика с указанными ценами. Помните, что тут левый столбец должен совпадать с теми данными, из которых состоит выпадающий список.
Дальше указываем столбец (2), данные из которого надо вытянуть, пишем ЛОЖЬ, для поиска точных совпадений, и щёлкаем «OK».
Получилось что-то вроде поиска: выбираем фрукт и ВПР находит в прайсе его цену.
Если у вас были одни цены, а потом поставщики дали новый список, то нужно как-то просмотреть, что поменялось. Делать это вручную долго, а вот при помощи рассматриваемой функции все можно сделать очень быстро. Потребуется добавить один столбец и перенести в него новые значения, а потом просто сравнить данные.
Жмем по любой ячейке в столбце D и вставляем один новый.
Я назвал его Новый прайс – тут будут новые цены, а в столбце слева, указаны старые. Новая таблица у меня находится на другом листе, чтобы Вам стало понятно, как юзать ВПР, если данные расположены на разных листах. В добавленном столбце выделяем первую пустую ячейку.
Вставляем функцию и указываем аргументы. Сначала то, что будем искать, в примере яблоко (А2). Для выбора диапазона из нового прайса, поставьте курсор в поле «Таблица» и перейдите на нужный лист, у меня «Лист1».
Выделяем мышью нужные столбцы и строки, без заголовков.
Дальше делаем абсолютные ссылки на ячейки: «Лист1!$A$2:$B$12». Выделите строчку и нажмите «F4», чтобы к адресам ячеек добавился знак доллара. Указываем столбец (2) и пишем «ЛОЖЬ».
В конце нажмите кнопку «OK».
В данный момент два столбца с новой и старой ценой расположены рядом и можно сделать или визуальное сравнение, или применив определенные формулы, или условное форматирование.
Думаю, у меня получилась пошаговая инструкция по использованию и применению функции ВПР в Экселе для чайников и вам теперь стало всё понятно.
Видео про Функцию ВПР в Excel от А до Я
