Функция ВПР в Excel — пошаговая инструкция с 5 примерами
ВПР — это функция Excel для поиска и извлечения данных из определенного столбца в таблице. Он поддерживает приблизительное и точное совпадение, а также символы подстановки (* и?). Подстановочные значения должны появиться в первом столбце таблицы, а подстановочные столбцы — справа.
Давайте теперь посмотрим, как искать с помощью ВПР и как это работает. Давайте посмотрим, как использовать его в формулах Excel.
Как сделать ВПР в Excel: понятная пошаговая инструкция.
Для начала давайте рассмотрим простой пример, чтобы проанализировать, как работает функция ВПР в Excel. Допустим, у нас есть две таблицы. Первый — это прайс-лист с названиями и ценами. Второй — это заказ на покупку некоторых из этих предметов. Поиск нужного товара в прайс-листе и ручной ввод цены в заказ — очень утомительная задача. Ведь прайс-лист с ценами может состоять из сотен строк. Мы должны делать все автоматически.
Нам нужно найти интересующее нас имя в первом столбце и вернуть (т.е показать в ответ на наш запрос) содержимое нужного столбца той же строки, где находится это имя.
Наш прайс-лист находится в столбцах A и B. Список покупок находится в EH. Допустим, первый товар в списке покупок — это бананы. Нам нужно найти этот товар в столбце A, где указаны все названия, а затем ввести его цену в ячейку G2.
Для этого напишите в G2 следующую формулу:
= ВПР (E2; $ A $ 2: $ B $ 7; 2; 0)
Теперь давайте подробнее рассмотрим, как создать ВПР.
- Берём значение из E2.
- Мы ищем точное совпадение (поскольку четвертый параметр равен 0) в диапазоне $ A $ 2: $ B $ 7 в его первом (крайнем левом) столбце. Обратите внимание, что лучше сразу использовать ссылки абсолютного прайс-листа, чтобы ссылка не «проскальзывала» при копировании этой формулы».
- Если товар найден, нужно перейти во второй столбец диапазона (на это указывает третий параметр = 2).
- Берем с нее цену и помещаем в нашу ячейку G2.
Произошло? Теперь просто скопируйте формулу из G2 в G3: G8.
Отчет о продажах готов.
Кроме того, чтобы понять, что такое точное соответствие, попробуйте изменить название продукта на A5 или E2. Например, добавьте пробел в конце. Внешне ничего не изменилось, но вы сразу получите ошибку # N / A. То есть элемент не найден. При этом таких случайных ошибок легко избежать, о которых мы поговорим отдельно.
Особое внимание уделим четвертому параметру. Мы указали ноль (можно написать FALSE), что означает «точный поиск». Но что, если вы забудете указать его и получите номер столбца, из которого извлекаются запрошенные данные?
Давайте сделаем еще шаг за шагом, что будет в этом случае.
- Берём значение из E2.
- Начнем искать его в крайнем левом столбце диапазона $ A $ 2: $ B $ 7, то есть в столбце A. Поскольку совпадений в A2 не найдено, давайте посмотрим дальше: что ниже.
- Там находим товар «Слива». Предполагается, что наш список отсортирован по алфавиту. Ведь это как раз главное условие нахождения примерного совпадения.
- Поскольку в отсортированном списке «сливы» уступают «бананам», функция решает, что дальше искать слово, начинающееся с «B», нет смысла. Процесс можно остановить. И остановимся на букве «А». То есть ближайшая стоимость есть.
- Поскольку поиск завершен, перейдите от A2 ко второму столбцу, то есть к B. Введите данные из B2 в G2 как результат вычислений.
К сожалению, «бананы» были в нашем прайс-листе ниже, но они просто «не прошли». И теперь в списке покупок указана неправильная цена.
В этом руководстве мы только что рассмотрели основы. И как его реально использовать?
Как работает функция ВПР в Excel: несколько примеров для «чайников».
Предположим, вам нужно выбрать данные конкретного человека из списка сотрудников. Посмотрим, какие есть тонкости.
Во-первых, нужно сразу определиться — нужен точный или приблизительный поиск. Ведь у них разные требования к подготовке исходных данных.
Использование точного и приблизительного поиска.
Взгляните на примерные цены, которые мы получаем с помощью грубого случайного поиска.
Обратите внимание, что четвертый параметр — 1.
Некоторые результаты определены правильно, но в большинстве случаев это ошибки. Функция продолжает сканировать данные в столбце D с названиями товаров, пока не найдет значение, превышающее значение, указанное в качестве критерия поиска. Затем он останавливается и возвращает цену.
Поиск цены на египетские бананы закончился на первой позиции, так как вторая содержит сливы. И это слово по правилам алфавита уступает «Бананам Египта». Это означает, что вам не нужно искать дальше. Получил 145. И неважно, что это цена на абрикосы. Поиск цен на сливы продолжался до тех пор, пока D15 не нашел слово с наименьшей по алфавиту: яблоки. Остановились и взяли цену предыдущей линии.
Теперь посмотрим, как все должно было произойти, если все было сделано правильно. Мы выполняем только сортировку, как указано стрелкой.
Спросите: «К чему тогда такой неточный взгляд, если так много проблем?»
он отлично подходит для выбора значений из определенных диапазонов.
Допустим, у нас есть скидка для покупателей в зависимости от количества купленного товара. Вам нужно быстро подсчитать процентную ставку за совершенную покупку.
Если у нас есть количество предметов 11, мы смотрим на столбец D, пока не найдем число больше 11. Это 20, и оно находится в четвертой строке. Давай остановимся здесь. Это означает, что наша скидка находится в 3-м ряду и составляет 3%.
При работе с диапазонами «от — до» этот прием вполне подходит.
И еще небольшой совет.
Применяйте именованный диапазон.
Чтобы упростить использование формул, вы можете создать именованный диапазон, а затем ссылаться на него. В нашем случае назовем его «EmployeeData» (помните, что здесь нельзя использовать пробелы).
В ячейке B2 мы введем требуемую фамилию, а в ячейки C2: F2 запишем формулы:
= ВПР ($ B $ 2; EmployeeData; 2; ЛОЖЬ)
= ВПР ($ B $ 2; EmployeeData; 3; ЛОЖЬ)
= ВПР ($ B $ 2; EmployeeData; 4; ЛОЖЬ)
= ВПР ($ B $ 2; EmployeeData; 5; ЛОЖЬ)
Как видите, они отличаются только номером столбца, из которого будет извлекаться необходимая информация. 0 можно использовать вместо FALSE.
Какие здесь преимущества?
- Разве вас не завораживают буквы, цифры и знаки доллара в обычных адресах?
- Формула именованного диапазона кажется намного более дружелюбной, наглядной и понятной. Вместо скучных безличных координат вы видите идентификаторы, которые вызывают у вас какие-то ассоциации. Согласитесь, «цена» или «цена» — это определенно информация о цене.
- Если по какой-то причине вам нужно изменить координаты диапазона поиска, который вы использовали в большом количестве формул, вам нужно исправить каждую формулу или использовать функцию «Найти и заменить»? Согласитесь, это очень долго, кропотливо, возможны ошибки.
Используя именованный диапазон, просто нажмите
Меню — Формула — Менеджер имен.
Затем в списке диапазонов найдите нужный диапазон и настройте его. Изменения будут автоматически применены ко всем формулам.
При использовании обычных адресов мы всегда должны думать о том, какую адресацию применить: относительную или абсолютную. Эта проблема не возникает при использовании именованных диапазонов.
Использование символов подстановки и другие тонкости критерия поиска.
Как и в предыдущих примерах, при вводе фамилии выполняется точный поиск. Но есть некоторые вещи, о которых мы раньше не упоминали.
Регистр символов не влияет на результат. Можно вводить все заглавными буквами — ничего не изменится. См. Пример ниже.
Если в списке есть люди с одинаковой фамилией, будет найден только первый. Как мы уже говорили, как только что-то подходящее найдено, процесс останавливается.
вы можете использовать подстановочные знаки * и ?. Напомню, что вопросительный знак обозначает любой символ, а звездочка обозначает любое количество символов (включая ноль). Мы упомянули о них в начале.
это хорошая идея, если вам известна только часть значения аргумента.
Но будьте осторожны: будет найдено только первое совпадение, как показано на скриншоте. Это очень важное ограничение, которое необходимо учитывать.
Теперь давайте посмотрим, как можно работать с подстановочными знаками, если критерии выбора не вводятся вручную, а взяты из электронной таблицы Excel.
Формула в ячейке F2 выглядит так:
= ВПР («*» & D2 & «*»; $ A $ 2: $ B $ 7; 2; 0)
Здесь мы используем оператор конкатенации строк &.
Конструкция «*» & D2 & «*» означает, что звездочки * добавляются к содержимому ячейки D2 с обеих сторон. То есть ищем любое вхождение этого слова — до и после него могут быть другие слова и символы. Как, например, случилось с продуктом «Персики». В нашем случае первый параметр будет иметь вид «* персики *». При поиске такого дизайна приемлемым вариантом будет «Консервированные персики (Турция)».
Использование нескольких условий.
Еще один простой пример для чайников: как использовать несколько условий при выборе желаемого значения?
Допустим, у нас есть список имен и фамилий. Нам нужно найти подходящего человека и посмотреть размер его дохода.
В F2 мы используем следующую формулу:
= ВПР (D2 & «» & E2; $ A $ 2: $ B $ 21; 2; 0)
Разберем пошагово, как работает ВПР в этом случае.
Сначала формируем условие. Для этого с помощью оператора & мы «вставляем» имя и фамилию и вставляем между ними пробел.
Не забудьте заключить пробел в кавычки, иначе Excel не воспримет его как текст.
Затем в таблице доходов найдите ячейку с именем и фамилией, разделенными пробелом.
Так что все происходит по уже отработанной схеме.
Вы можете попытаться перестраховаться, если между именем и фамилией будет введено больше пробелов. Замените пробел в формуле на подстановочный знак «*».
Примечательно так — D2 & «*» & E2
Но при этом учтите, что совпадение имени и фамилии уже не будет совсем точным. Мы рассмотрели аналогичный пример чуть выше.
Отдельно рассмотрим более сложные и точные способы работы с множеством условий. См. Ссылки в конце.
«Умная» таблица.
И еще одна рекомендация — используйте умный стол.
Может быть очень удобно сначала преобразовать справочную таблицу (прайс-лист) в смарт с помощью команды Home — Format as table (в англоязычной версии Excel), затем указать имя созданной таблицы во втором аргументе. Кстати, оно будет ему присвоено автоматически.
В этом случае размер списка товаров с ценами нас больше не будет беспокоить. Когда вы добавляете новые продукты в прайс-лист или удаляете их, размер умной таблицы изменяется сам.
Специальные инструменты для ВПР в Excel.
ВПР, несомненно, одна из самых мощных и полезных функций Excel, но она также одна из самых запутанных. Чтобы упростить вашу работу, вы можете использовать надстройку Ultimate Suite для Excel с мастером ВПР, который может значительно сэкономить вам время на поиск нужных данных.
Мастер ВПР — простой способ писать сложные формулы
Интерактивный мастер ВПР проведет вас через параметры конфигурации поиска, необходимые для создания идеальной формулы для ваших критериев. В зависимости от структуры данных он будет использовать стандартную функцию ВПР или формулу ИНДЕКС + ПОИСК, если ему нужно получить значения слева от столбца подстановки.
Вот что вам нужно сделать, чтобы получить формулу решения вашей проблемы:
- Запустите мастер, нажав кнопку Vlookup Wizard на ленте Ablebits Data.
- Выберите вашу таблицу (вашу таблицу и таблицу поиска).
- Укажите следующие столбцы (во многих случаях они выбираются автоматически):
- Ключевой столбец: расположен в основной таблице, он содержит значения для поиска.
- Колонка поиска — в которой будем искать.
- Столбец возврата: мы получим из него значения.
- Нажмите кнопку Вставить).
Посмотрим все в действии.
Стандартный ВПР.
Запустите мастер Vlookup. Указываем координаты основной таблицы и справочной таблицы, а также ключевого столбца (из которого мы будем брать значения для поиска), справочного столбца (в котором мы будем их искать) и столбца результатов (из него в случае успеха берем соответствующее значение и вставляем в основную таблицу). Просто заполните все обязательные поля, как показано на изображении ниже. Прописываем (или обозначаем мышкой) только диапазоны руками. Мы просто выбираем поля из выпадающего списка.
Как и в предыдущих примерах, наша задача — найти цену на каждый товар, извлекая ее из прайс-листа. Если область поиска (Цена) является крайним левым столбцом в области поиска, для точного совпадения вводится обычная формула ВПР:
Необязательно ничего писать руками.
После нажатия на кнопку «Вставить» справа от столбца с названиями товаров будет вставлен дополнительный столбец, который будет озаглавлен так же, как столбец результатов. Все найденные значения цен будут записаны здесь и в виде формулы. При необходимости вы можете исправить это или использовать в других таблицах.
«Левый» ВПР.
Когда столбец результатов (Цена) находится слева от области поиска (Цена), мастер автоматически вставляет формулу ИНДЕКС + ПОИСК:
Дополнительный бонус! Благодаря экспертному использованию ссылок на ячейки полученные формулы ВПР можно скопировать или переместить в любой столбец без необходимости обновлять ссылки.
Надеемся, что наши подробные инструкции по использованию функции ВПР в таблицах Excel будут доступны и понятны и для чайников. Конечно, эти простые рекомендации можно использовать только в самых простых случаях.