Изучим работу функции ВПР в Excel, которая связывает две таблицы и позволяет подтянуть данные из одной таблицы в другую.
Приветствую всех, дорогие читатели блога Tutorexcel.ru!
Мне кажется, что если подбирать ассоциацию к слову Excel, то с большим преимуществом победит вариант ВПР. Впрочем, уверен и в обратном, услышав слово ВПР сразу становится понятно, что речь идет про Excel.
Вот и мы сегодня поговорим про работу этой замечательной функции в Excel, которая позволяет быстро сопоставлять данные между несколькими таблицами, и на самом деле используется примерно в 100% всех файлов, наверное за исключением пустых книг 🙂
В общем давайте начинать и на примерах разберем пошаговую инструкцию по функции ВПР в Excel.
Функция ВПР в Excel. Синтаксис
Изучение любой функции начинаем с синтаксиса, функция ВПР (VLOOKUP в английской версии):
ВПР (искомое_значение; таблица; номер_столбца; [интервальный_просмотр])
Ищет значение в крайнем левом столбце таблицы и возвращает значение ячейки, находящейся в указанном столбце той же строки. По умолчанию таблица должна быть отсортирована по возрастанию.
Название ВПР получается из сокращения Вертикальный Просмотр, как раз по сути именно это функция и делает.
Познакомимся с каждым из аргументов по отдельности:
- Искомое значение (обязательный аргумент) — значение, которое должно быть найдено в первом столбце массива;
Обычно это числовое или текстовое значение. - Таблица (обязательный аргумент) — таблица с текстом, числами или логическими значениями, в которой производится поиск данных;
- Номер столбца (обязательный аргумент) — номер столбца в таблице, из которого нужно вернуть значение;
- Интервальный просмотр (необязательный аргумент) — логическое значение, определяющее, точно (ЛОЖЬ) или приближенно (ИСТИНА) должен производиться поиск в первом столбце.
Таким образом схему работы функции ВПР можно представить следующим образом — в таблице в первом столбце ищем искомое значение, находим строчку с этим значением и оставаясь в этой же строчке сдвигаемся вправо на номер столбца, значение из полученной ячейки и есть результат функции.
Со схемой познакомились, самое время перейти к практике.
Формула ВПР в Excel. Пример 1
Давайте рассмотрим пример, пусть у нас имеется таблица с прайс-листом товаров в магазине (артикул, наименование, цена в рублях, объем в литрах и вес в килограммах):
К нам в магазин приходит заказ и наша задача посчитать общую сумму заказа в деньгах:
Для того, чтобы посчитать сумму заказа нам не хватает цены за каждый из товаров, но эта информация как раз есть в исходной таблице которую мы и достанем с помощью функции ВПР.
Другими словами мы подтянем данные из первой таблицы во вторую и таким образом посчитаем общую сумма заказа.
Определимся с аргументами, в качестве искомого значения — артикул (G2), таблицы — исходная таблица с данными (A1:E11), номера столбца — третий (3), интервального просмотра — ЛОЖЬ (0, так как ищем точное соответствие), и записываем полученную формулу:
Визуально все сработало как надо, напротив каждого товара подтянулась нужная цена, теперь перемножаем количество на цены и получаем итоговый результат:
На первый взгляд все получилось достаточно просто, но здесь есть несколько подводных камней, давайте разберем основные из них.
Ошибки при работе с функцией ВПР в Excel
Не закрепление диапазона в таблице
При использовании ВПР в качестве как минимум 2 аргументов (исходного значения и таблицы) используются ссылки на ячейки или диапазоны. Поэтому крайне важно грамотно прописывать ссылки и при необходимости закреплять формулы, так как при протягивании формул ссылки могут сбиться и в результате мы можем получить ошибочный результат.
В нашем примере если мы забудем зафиксировать диапазон таблицы A1:E11, то при протягивании формулы он сначала превратится в A2:E12, затем в A3:E13 и т.д.
В итоге для одного из товаров мы получим ошибку, так как нужного артикула в смещенной таблице уже нет:
Поиск не по первому столбцу
При выборе аргумента таблицы очень важно, чтобы поиск происходил именно по первому столбцу таблицы. Бывают ситуации, что таблица содержит много столбцов, мы автоматически выделяем всю таблицу, хотя производить поиск хотим не по первому столбцу.
В нашем случае, к примеру, если мы хотим искать по наименованию (которые находятся в столбце B), то в качестве таблицы нужно выбирать диапазон B1:E11 (не A1:E11), так как именно по столбцу B будет делаться поиск и именно он является первым столбцом в таблице B1:E11.
Пропуск интервального просмотра
Интервальный просмотр необязательный аргумент функции ВПР, поэтому при записи формулы его формально можно не писать. Проблема в том, что если его не прописать, то по умолчанию он считается равным 1 (ИСТИНА), таким образом поиск идет не по точному, а по приближенному значению.
Для поиска по приближенному значению необходимо, чтобы таблица была отсортирована по возрастанию, в ином случае возможна некорректная работа функции, про работу приближенного поиска расскажу в отдельной статье, так как в большинстве случаев нужен именно точный поиск.
В нашем случае пропуск параметра приводит к тому, что ВПР находит совершенно не то, что нужно, как раз из-за приближенного поиска:
Смена номера столбца
Аргумент номер столбца для функции очень часто прописывается как число в явном виде (например, 3, 4 или 7). Проблема может возникнуть тогда, когда в исходной таблице поменяется порядок столбцов. Например, мы решим добавить в таблицу новый столбец или наоборот, удалить лишний столбец, и то, что раньше было, к примеру, третьим столбцом может стать четвертым или вторым.
Если в наш пример добавить в середину таблицы новый столбец, то исходный третий столбец с ценой станет четвертым, а уже новый третий столбец станет пустым, поэтому формула ВПР вместо цены вернет пустые значения:
Идем дальше.
Функция ВПР в Excel. Пример 2
Для закрепления материала давайте рассмотрим еще один пример и сделаем сравнение двух таблиц посредством функции ВПР.
Немного модифицируем таблицу из предыдущего примера и рассмотрим 2 варианта таблицы (старый и новый прайс-листы), так как достаточно часто встречается задача сравнить одинаковые по структуре данные, которые могли быть видоизменены:
Так же как видим в правой таблице для усложнения перемешаны строчки с товарами (теперь они уже идут не по порядку), чтобы задача решалась не простым вычитанием, а все-таки с помощью более интересных инструментов 🙂
Давайте подтянем справа от новой цены старую, для этого прописываем функцию ВПР, в качестве аргумента искомого значения указываем название товара (D3), таблицы — левую часть исходной таблицы (A2:B12), номера столбца — второй (2), интервального просмотра — ЛОЖЬ (0) и не забываем закреплять диапазоны в случае необходимости, чтобы ссылки не сбились:
Старую цену подтянули, теперь осталось только сравнить полученные значения, в соседнем столбце прописываем разницу между столбцами, для наглядности ячейки отличные от нуля можно подсветить дополнительной заливкой для лучшей визуализации отклонения:
Резюмируем, с основными особенностями работы функции ВПР мы познакомились, надеюсь, что теперь пользоваться данной формулой вам будет еще проще и понятнее.
Спасибо за внимание!
Если у вас остались вопросы, то обязательно делитесь ими в комментариях.
Удачи вам и до скорых встреч на страницах блога TutorExcel.Ru!