ВПР (VLOOKUP) ЗА/ПРОТИВ +/-
Пару раз мы уже упоминали функцию ВПР (VLOOKUP). Раз. Два.Но подробного рассказа именно о ней пока еще не было.
Поехали
Начнем с определения и синтаксиса.
Если Вас на собеседовании спросят: " А ВПРом пользоваться умеете?". Смело отвечайте: "Вертикальным просмотром? Конечно!". Само название функции намекает, что она производит поиск в строках таблицы (по вертикали).
Синтаксис функции достаточно прост и логичен:
=ВПР(искомое значение; таблица; номер столбца; [интервальный просмотр]).
искомое значение - то, что мы ищем в первом столбце таблицы (чаще всего - ссылка на ячейку со значением)
таблица - где мы ищем, диапазон для поиска значения
номер столбца - столбец указанной таблицы, из которого нужно достать данные, когда найдем нужную строку в первом столбце
[интервальный просмотр] - точное или приблизительное совпадение — указывается как 0/ЛОЖЬ или 1/ИСТИНА (обратите внимание на квадратные скобки в формуле - они указывают на необязательность аргумента для функции. Если не указать ничего, то по умолчанию будет установлено значение ИСТИНА (1))
Пример работы
На скриншоте выше происходит следующее:
1) Мы ищем значение из ячейки Е2 (в нашем случае - 25);
2) Ищем мы его в таблице $А$2:$B$5;
3) Найдя строку с нужным значением в ПЕРВОМ столбце (а ВПР всегда ищет значение в крайнем левом столбце) переходим в этой же строке в столбец, указанный в аргументе номер 3 (в нашем случае - второй столбец)
Вы, наверное заметили, что числа 25 в первом столбце нужной таблицы нет. Тем не менее формула работает. А все потому, что в четвертом необязательном аргументе указано значение ИСТИНА (1). Формула будет искать приблизительное совпадение, а не точное значение. Чтобы это работало, первый столбец нужной таблицы должен быть обязательно отсортирован по возрастанию. Тогда ВПР найдет наибольшее значение, которое меньше или равно искомому (в нашем примере для 25 это число 20, а для 19 было бы уже 10 и т.д.). Если нужно искать только точные совпадения - укажите в качестве последнего аргумента функции ЛОЖЬ (0). (см. гифку ниже)
4) Выводим в ячейку с формулой то значение, которое нашли во втором столбце (на картинке выше - 10%).
Как работает точное / приблизительное совпадение
Закрепляем диапазон
Чаще всего формулу необходимо скопировать (протянуть) в соседние ячейки. Чтобы после копирования все работало правильно - не забывайте закреплять диапазон в аргументе №3.
Обход ошибки "Число как текст"
Про то, что иногда наши числа выгружаются в Excel из сторонних программ как текст, и про способы решения этой проблемы мы уже рассказывали вам здесь . Как применить эти знания к функции ВПР - смотри на рисунке ниже. Данные для первого аргумента представлены как текст, а в таблице - как числа. Преобразовываем их в нужный формат прямо внутри формулы.
Текст в качестве искомого значения
ВПР может искать в таблицах не только числа, но и текстовые значения. На них также распространяются правила использования аргумента точного и приблизительного совпадения. Если мы выбрали приблизительный поиск, то можем использовать подстановочные символы ("*" для любого числа символов и "?" для одного символа).
Работая с текстом - будьте внимательны. Любой пробел хоть и не виден для нашего глаза, Excel'ем воспринимается как полноправный символ. Чтобы быть уверенным, что в тексте нет лишних знаков - пользуемся функцией СЖПРОБЕЛЫ().
Перехват ошибок
Если ВПР не смог найти нужное нам значение в крайнем левом столбце, то результатом работы функции станет ошибка #Н/Д. Иногда нам полезно ее видеть, а иногда - нет. Чтобы перехватить и спрятать полученные ошибки - пользуемся функцией ЕСЛИОШИБКА().
Приятный бонус!
Разобравшись с функцией ВПР вы убиваете двух зайцев, так как теперь, по сути, умеете работать сразу с двумя функциями - ГПР вам в подарок. Эта функция работает точно также, но ищет значения в верхней строке диапазона (а не в левом столбце), а возвращает значения из указанной строки найденного столбца. Этакий ВПР, развернутый на 90 градусов. Скорее всего, ее вы будете использовать редко, если, конечно, умеете правильно организовывать работу с данными (про это мы вам уже рассказывали: Раз, Два). Но в любом случае знание еще одной функции лишним не будет.
Поддержать наш проект и его дальнейшее развитие можно вот здесь.
Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot
С уважением, команда tDots.ru