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

• Зачем нужна эта функция
• Как и что считать
• Примеры расчета
• Нюансы

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

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

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

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

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

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

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

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

• Вносим наименование каждого продукта в каждую ячейку. Создаем вторую таблицу на листе 2 со стоимостью каждой упаковки каждого продукта.

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

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

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

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

Нюансы

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

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

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

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

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

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

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

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