Баннер
Технологии

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

Елизавета Иванова
/ 6 апреля 2023 08:40
6 мин.
Текст изменился / 9 сентября 2023

Excel многие не любят за сложность. Но если знать хитрости и приемы, например, формулу ВПР или Vlookup, то программа станет вашим надежным другом.

ВПР функция в Excel
Грустно, когда не знаешь, как пользоваться Excel. Источник: нейробот

Зачем нужна эта функция

Представим себе, что у нас есть две таблицы. Это могут быть прайс-листы или ведомости с оценками студентов. Суть в том, что данные таблиц иногда нужно объединить, сравнить, перемножить, – короче, выполнить нехитрое математическое действие с большим количеством данных или наименований. Для этого и нужна ВПР или по-английски VLookup. Расшифровывается ВПР как «вертикальный просмотр».

Факт дня
Считается, что первые чеканные монеты появились на территории нынешней Западной Турции в VII–VI ве­ках до нашей эры.

Как и что считать

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

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

Примеры расчета

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

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

Как работает ВПР
Так выглядит первая таблица
  • Вносим наименование каждого продукта в каждую ячейку. Создаем вторую таблицу на листе 2 со стоимостью каждой упаковки каждого продукта.
Что такое ВПР
Так выглядит вторая таблица
  • Далее в первой таблице нужно добавить столбик «Стоимость». Дальше применяем функцию вертикального просмотра. Выделяем первую ячейку в столбце «стоимость».  Нажимаем fx в строке над ячейками или вызываем мастер функций при помощи горячих клавиш Shift+F3. Путь: Ссылки и массивы – ВПР – Ок.
Как работает vlookup
Как вызвать функцию ВПР
  • Открывшееся окно – это аргументы функции. «Искомое значение» – диапазон первого столбца первой таблицы. Вводим значения крайних ячеек через знак «:» и без пробелов.
  • Следующий аргумент называется «таблица». Здесь будут значения цены из листа 2. Чтобы они появились в окне, где мы вводим аргументы, нужно просто перейти на лист 2 и выделить нужные ячейки курсором. Чтобы таблица при расчете не поползла вниз, используют значок доллара, $. Выделите аргумент «таблица» и нажмите F4 или проставьте перед каждым символом $ вручную.

Значок доллара напоминает о накопленных и припрятанных деньгах? Самое время открыть вклад в Совкомбанке.

Заставьте свои сбережения работать и приносить вам пассивный доход! В Совкомбанке есть линейка вкладов с гибкими условиями — вы сможете подобрать подходящий вариант. Высокая ставка до 15,5% убережет деньги от инфляции и поможет быстрее накопить на крупные покупки. Подайте заявку онлайн!

  • В аргумент «номер столбца» вносим тот столбец, где находятся данные, которые нужно из второй таблицы внести в первую. В нашем случае – 2.
  • В поле «искомый аргумент» пишем «ЛОЖЬ».
  • Растягиваем функцию, выделив нужные ячейки.
ВПР и vlookup
Введенные значения в мастере формул

Нюансы

В версиях MS Word 2003, 2007, 2010 и 2013 ВПР может выдавать ошибку. Я тоже столкнулась с этим при подсчете бананов для Ирины. Вот как это выглядело.

Как составить формулу для ВПР
Сначала у меня получилось так

Пути решения могут быть следующими:

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

Ошибка в вычислении поправима, но если сломан рабочий ноутбук, порой помочь сможет только дорогостоящий ремонт. Вылечите компьютер прямо сейчас с кредитом от Совкомбанка, не откладывая на потом. 

Зачем откладывать деньги долгие месяцы, если можно получить желаемое прямо сейчас? Возьмите кредит в Совкомбанке, оформите услугу «Гарантия минимальной ставки» и получите шанс вернуть проценты по истечении срока кредитования. Для этого расплачивайтесь Халвой каждый месяц и не допускайте просрочек по кредиту. Оставить заявку вы можете в два клика.

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

В моем случае максимально сокращенная готовая таблица выглядит так:

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

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

Теперь вы знаете один из секретов Excel и сможете применять его для расчетов. 

Статьи про технологии у вас в почте

Подпишитесь на подборку статей на тему технологий. Все самое интересное из рубрики Технологии будем присылать вам на почту.
Хотите узнавать о новых публикациях не заходя на сайт?
Подписывайтесь на анонсы в ВК и Телеграме
banner image
Елизавета Иванова
Елизавета Иванова
Расскажу об осознанном потреблении, бережном и разумном отношении к себе и своим финансам.
Баннер