09.11.2017      38      8
 

Функция ВПР в Excel с примерами


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

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

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

Нажмите по верхней ячейке в первой таблице в столбце Цена, а потом кнопочку «fx» в строке формул, чтобы открыть окно мастера функций.

Открываем окно

Там, где написано категория выбираем «Ссылки и массивы». В списке выделите ее и нажимайте «ОК».

Первый шаг

Следующее, что мы делаем – прописываем аргументы в предложенные поля.

Ставьте курсив в поле «Искомое_значение» и выделяйте в первой таблице то значение, которое будем искать. У меня это яблоко.

А2

В строке «Таблица» необходимо выделить ту, из которой будут браться данные – шапку не выделяйте. Учтите, что крайний левый столбец должен состоять из значений, которые мы ищем. То есть, мне нужно яблоко и другие фрукты, значит, их перечень должен быть в левом столбце выделяемой области.

Выделение

Чтобы после того, как мы напишем формулу и растянем ее по всему столбцу, выбранный диапазон не смещался вниз, нужно сделать ссылки абсолютными: выделите данные в поле и нажмите «F4». Теперь адрес на ячейки стал абсолютным, к ним добавился знак «$», и диапазон смещаться не будет.

Знак $

Там, где номер столбца, поставьте цифру, соответствующую во второй таблице тому столбцу, данные откуда нужно переносить. У меня прайс состоит из фруктов и цены, мне нужно второе, поэтому ставлю цифру «2».

Второй столбец

В «Интервальный_просмотр» пишем «ЛОЖЬ» – если искать нужно точные совпадения, или «Истина» – если значения могут быть приближенные. Для нашего примера выбираем первое. Если ничего не указать в данном поле, то по умолчанию выберется второе. Потом нажимайте «ОК».

Здесь обратите внимание на следующее, если работаете с числами и указываете «Истина», то вторая таблица (это наш прайс) обязательно должна быть отсортирована по возрастанию. Например, при поиске 5,25 найдется 5,27 и возьмутся данные с этой строки, хотя ниже может еще быть и число 5,2599 – но формула дальше смотреть не будет, поскольку она думает, что ниже числа только больше.

Аргументы заполнены

Как же работает ВПР? Она берет искомое значение (яблоко) и ищет его в крайнем левом столбце указанного диапазона (перечень фруктов). При совпадении берется значение из этой же строки, только того столбца, который указан в аргументах (2), и переносится в нужную нам ячейку (С2). Формула выглядит так:

=ВПР(А2;$G$2:$H$12;2;ЛОЖЬ)

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

Перенесено число

Все цены перенесены из прайса в таблицу закупок в соответствии с названиями фруктов.

Цены на фрукты

Теперь можем рассчитать столбец Стоимость. Растянем формулу на необходимое количество строчек.

Стоимость

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

Нет данных

При добавлении столбцов на лист, данные для аргумента «Таблица» функции автоматически изменятся. В примере прайс сдвинут на 2 столбца вправо. Выделим любую ячейку с формулой и видим, что вместо $G$2:$H$12 теперь $I$2:$J$14.

Переместили

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

Создание

В открывшемся окне «Тип данных» будет «Список», ниже указываем область источника – это названия фруктов, то есть тот столбец, который есть и в первой и во второй таблице. Нажимайте «ОК».

Выбор Источника

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

Выбор фрукта

Выделяю F2 и вставляю функцию ВПР. Аргумент первый – это сделанный список (F1).

F1

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

Диапазон

Дальше указываем столбец (2), данные из которого нужно вытянуть, пишем ЛОЖЬ, для поиска точных совпадений, и нажимаем «ОК».

Аргументы заполнены 2

Получилось что-то вроде поиска: выбираем фрукт и ВПР находит в прайсе его цену.

Поиск

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

Жмем по любой ячейке в столбце D и вставляем один новый.

Добавление

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

Разные листы

Вставляем функцию и указываем аргументы. Сначала то, что будем искать, в примере яблоко (А2). Для выбора диапазона из нового прайса, поставьте курсор в поле «Таблица» и перейдите на нужный лист, у меня «Лист1».

Выбор листа

Выделяем мышкой необходимые столбцы и строки, без заголовков.

Без шапки

Дальше делаем абсолютные ссылки на ячейки: «Лист1!$A$2:$B$12». Выделите строчку и нажмите «F4», чтобы к адресам ячеек добавился знак доллара. Указываем столбец (2) и пишем «ЛОЖЬ».

Заполненные поля

В конце нажмите кнопку «ОК».

Кнопка ок

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

Данные рядом

Надеюсь, у меня получилась пошаговая инструкция по использованию и применению функции ВПР в Excel, и Вам теперь все понятно.


1 Звезда2 Звезды3 Звезды4 Звезды5 Звезд (6 оценок, среднее: 5,00 из 5)
Загрузка...
Обсуждение: 8 комментариев
  1. Дмитрий:

    Большое спасибо автору за понятное объяснение.

    Ответить
  2. Михаил:

    Очень подробно и грамотно.
    Спасибо

    Ответить
  3. Анна:

    то что надо, только у меня не рисуется знак S

    Ответить
  4. Мария:

    Спасибо, всё очень информативно и понятно 🙂

    Ответить
  5. Елена:

    Спасибо хорошая статья

    Ответить
  6. Лиза:

    Спасибо Вам огромное!

    Ответить
  7. Стеня Разин:

    хм…не плохо…

    Ответить
  8. Диана:

    аааааааааааааааааа о господи ВПР

    Ответить

Ваш комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

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