Функция ВПР в Excel: пошаговая инструкция с 5 примерами
ВПР — это функция Excel для поиска и извлечения данных из определенного столбца в таблице. Поддерживает приблизительные и точные совпадения, а также подстановочные знаки (* и ?). Значения поиска должны появиться в первом столбце таблицы, а столбцы поиска — справа.
Теперь давайте посмотрим, как выполнить поиск с помощью функции ВПР и как она работает. Рассмотрим способы его применения в формулах Excel.
- Как сделать ВПР в Excel: понятная пошаговая инструкция.
- Как работает функция ВПР в Excel: несколько примеров для чайников».
- Использование точного и приближенного поиска.
- Используйте именованный диапазон.
- Использование подстановочных знаков и прочие тонкости критериев поиска.
- Использование нескольких условий.
- Умный стол.
- Специальные инструменты для ВПР в Excel
Как сделать ВПР в Excel: понятная пошаговая инструкция.
Для начала на простом примере разберем, как работает функция ВПР в Excel. Допустим, у нас есть две таблицы. Первый – это прайс-лист с названиями и ценами. Второй — приказ купить некоторые из этих предметов. Поиск в прайс-листе нужного товара и ввод его цены в заказ вручную — очень утомительное занятие. Ведь прайс-лист с ценами может иметь сотни строк. Мы должны делать все автоматически.
Нам нужно найти интересующее имя в первом столбце и вернуть (т.е отобразить в ответ на наш запрос) содержимое нужного столбца в той же строке, где найдено имя.
Наш прайс-лист находится в столбцах A и B. Список покупок находится в EH. Допустим, первым пунктом в списке покупок являются бананы. Нам нужно найти этот товар в столбце А, где указаны все наименования, затем поставить его цену в ячейку G2.
Для этого в G2 запишем следующую формулу:
=ВПР(E2,$A$2:$B$7,2,0)
А теперь мы подробно обсудим, как сделать ВПР.
- Берем значение Е2.
- Мы ищем точное совпадение (поскольку четвертый параметр равен 0) в диапазоне $A$2:$B$7 в вашем первом (самом левом) столбце. Обратите внимание, что лучше сразу использовать абсолютные ссылки на прайс-лист, чтобы при копировании этой формулы ссылка не «проскальзывала».
- Если товар найден, он должен перейти во второй столбец диапазона (на это указывает третий параметр = 2).
- Берем из него цену и вставляем в нашу ячейку G2.
Это случилось? Теперь просто скопируйте формулу из G2 в G3:G8.
Отчет о продажах готов.
Также, чтобы понять, что такое точное совпадение, попробуйте изменить название продукта на A5 или E2. Например, добавьте пробел в конце. Судя по всему, ничего не изменилось, но вы сразу получите ошибку #Н/Д. То есть товар не найден. В то же время таких случайных ошибок можно легко избежать, о чем мы поговорим отдельно.
Обратим внимание на четвертый параметр. Указываем ноль (можно было написать FALSE), что означает «точный поиск». Но что, если вы забудете указать его и в итоге получите номер столбца, откуда тянутся нужные данные?
Давайте пошагово рассмотрим, что будет происходить в этом случае.
- Берем значение Е2.
- Начинаем искать его в крайнем левом столбце диапазона $A$2:$B$7, то есть в столбце А. Поскольку совпадения в А2 не найдено, смотрим дальше: что ниже.
- Там находим товар «Сливы». Это предполагает, что наш список отсортирован по алфавиту. Ведь это главное условие для нахождения примерного совпадения.
- Поскольку слово «сливы» находится дальше в отсортированном списке, чем слово «бананы», функция решает, что нет смысла продолжать поиск слова, начинающегося с «Б». Процесс можно остановить. И оставайтесь на букве «А». То есть ближайшее значение существует.
- Поскольку поиск завершен, переходим из A2 во второй столбец, то есть в B. Вставляем данные из B2 в G2 как результат вычислений.
К сожалению, «бананы» были в нашем прайс-листе ниже, но просто «не дошли до заселения». И теперь в списке покупок написана неправильная цена.
В этом руководстве мы рассмотрели только основы. Как вы можете использовать его на самом деле?
Как работает функция ВПР в Excel: несколько примеров для «чайников».
Предположим, нам нужно выбрать данные конкретного человека из списка сотрудников. Давайте разберемся, какие здесь есть тонкости.
Во-первых, нужно сразу определиться: нужен точный или приблизительный поиск. Ведь у них разные требования к подготовке исходных данных.
Использование точного и приблизительного поиска.
Посмотрите, какие результаты выборки цен мы получаем при использовании нечеткого поиска на неупорядоченных данных.
Обратите внимание, что четвертый параметр равен 1.
Некоторые результаты верны, но в большинстве случаев это ошибки. Функция продолжает поиск данных в столбце D с названиями продуктов, пока не найдет значение, превышающее указанные критерии поиска. Затем он останавливается и возвращает цену.
Поиск цен на египетские бананы оказался на первой позиции, а на второй зафиксированы сливы. И это слово по правилам алфавита ниже, чем «бананы Египта». Поэтому нет необходимости искать дальше. У нас 145. И неважно, что это цена абрикосов. Поиск цены на сливы продолжался до тех пор, пока D15 не наткнулся на слово, расположенное ниже по алфавиту: яблоки. Останавливаемся и берем цену предыдущей линии.
А теперь посмотрите, как все должно было бы произойти, если бы все было сделано правильно. Мы просто делаем классификацию, как указано стрелкой.
Вы спросите: «Зачем тогда это неточное отображение, если с ним столько проблем?»
Он отлично подходит для выбора значений из определенных диапазонов.
Допустим, у нас есть скидка для покупателей в зависимости от количества купленного товара. Вам нужно быстро рассчитать, сколько процентов причитается за совершенную покупку.
Если у нас есть количество товаров 11 единиц, то мы ищем в столбце D, пока не найдем число больше 11. Это 20 и оно находится в 4-й строке. Мы останавливаемся здесь. Это означает, что наша скидка находится на 3-й строчке и равна 3%.
При работе с интервалами «от-до» этот прием вполне подходит.
И еще один небольшой совет.
Применяйте именованный диапазон.
Чтобы упростить работу с формулами, вы можете создать именованный диапазон и ссылаться на него позже. В нашем случае назовем его «Данные сотрудника» (помните, что здесь не допускаются пробелы).
В ячейку B2 введем желаемую фамилию, а в ячейках C2:F2 запишем формулы:
=ВПР($B$2;ДанныеСотрудника;2;ЛОЖЬ)
=ВПР($B$2;ДанныеСотрудника;3;ЛОЖЬ)
=ВПР($B$2;Данные Сотрудника;4;ЛОЖЬ)
=ВПР($B$2;ДанныеСотрудника;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 for Excel с инструментом VLOOKUP Wizard, который может сэкономить вам много времени на поиск нужных данных.
Мастер ВПР — простой способ писать сложные формулы
Интерактивный мастер ВПР проведет вас через параметры конфигурации поиска, необходимые для создания идеальной формулы для заданных вами критериев. В зависимости от вашей структуры данных вы будете использовать стандартную функцию ВПР или формулу ИНДЕКС+ПОИСКПОЗ, если вам нужно извлечь значения слева от столбца поиска.
Вот что вам нужно сделать, чтобы получить формулу для вашей задачи:
- Запустите мастер с помощью кнопки Vlookup Wizard на ленте данных Ablebits.
- Выберите основную таблицу (Your Table) и таблицу поиска (Lookup Table).
- Укажите следующие столбцы (во многих случаях они выбираются автоматически):
- Ключевой столбец (Key Column) — расположен в вашей основной таблице, содержит значения для поиска.
- Столбец поиска: в котором будем искать.
- Столбец возврата: из него мы будем получать значения.
- Нажмите кнопку Вставить).
Посмотрим все в действии.
Стандартный ВПР.
Запустите мастер ВПР. Указываем координаты основной таблицы и таблицы запросов, а также ключевой столбец (из которого будем брать значения для поиска), поисковый столбец (в котором будем их искать) и столбец результатов (из него, в случае успеха, берем соответствующее значение и вставляем в основную таблицу). Просто заполните все необходимые поля, как показано на рисунке ниже. Прописываем руками (или обозначаем мышкой) только диапазоны. Поля просто выбираются из выпадающего списка.
Как и в предыдущих примерах, наша задача — найти цену для каждого товара, извлекая его из прайс-листа. Если область поиска (Цена) является крайним левым столбцом в области поиска, для точного совпадения вставляется обычная формула ВПР:
Не нужно ничего писать от руки.
После нажатия кнопки Вставить справа от столбца с наименованиями товаров будет вставлен дополнительный, который будет озаглавлен так же, как и столбец результатов. Все найденные ценовые значения будут записаны сюда и в виде формулы. При необходимости его можно исправить или использовать в других таблицах.
«Левый» ВПР.
Когда столбец результатов (Цена) находится слева от области поиска (Цена), мастер автоматически вставляет формулу ИНДЕКС+ПОИСКПОЗ:
Левую ВПР рассмотрим в отдельной статье. Там же можно увидеть формулы для ручного ввода. Здесь мы получаем их автоматически, не вникая в тонкости синтаксиса и правильную орфографию.
Дополнительный бонус! Благодаря грамотному использованию ссылок на ячейки полученные формулы ВПР можно копировать или перемещать в любой столбец без необходимости обновления ссылок.
Надеемся, что наша пошаговая инструкция по использованию функции ВПР в таблицах Excel будет доступна и понятна даже «чайникам». Конечно, эти очень простые рекомендации можно использовать только в самых простых случаях. Все более сложное рассмотрим отдельно.
Примеры использования функции ВПР:
Как объединить две или более таблиц в Excel. В этом руководстве вы найдете несколько приемов для объединения таблиц Excel путем сопоставления данных в одном или нескольких столбцах. Как часто при анализе в Excel вся необходимая информация собирается в один…
2 способа извлечения данных из разных таблиц с помощью ВПР. — Задача: данные для поиска и извлечения с помощью функции ВПР находятся в нескольких таблицах. Эти таблицы имеют одинаковую структуру (то есть одни и те же столбцы, расположенные в одном и том же…
Вычисление номера столбца для извлечения данных в ВПР — Домашнее задание: Самый простой способ узнать, как указать столбец, из которого функция ВПР будет извлекать данные. При этом мы не будем менять саму формулу, так как это может привести к случайным ошибкам.…
4 способа сделать левую ВПР в Excel. — Функция ВПР — одна из самых популярных, когда нужно искать и извлекать данные из таблицы. Однако у него есть большой недостаток. Она смотрит в дальний левый угол…
Формула ВПР в Excel для сравнения двух таблиц — 4 способа — Сравнение таблиц — это задача, которую Excel приходится решать довольно часто. Например, у нас есть старый прайс-лист и его новая версия. Вам нужно посмотреть, какие цены изменились и…
Почему ВПР не работает в Excel? — Функция ВПР — очень мощный инструмент поиска. Но если по какой-то причине это не удастся, вы получите сообщение об ошибке #Н/Д. Давайте попробуем вместе…
ВПР с несколькими условиями: 5 примеров. — Очень часто наши требования к восстановлению данных не ограничиваются одним условием. Например, нам нужна выручка магазина за данный месяц, количество определенного товара, проданного конкретному покупателю, и т д…
Формула ВПР в Excel: 22 факта, которые вам нужно знать. — В процессе работы в Excel часто возникает задача извлечения необходимых данных из рабочих листов. Для этой цели в Excel предусмотрена формула ВПР. И хотя ВПР относительно… Александр Трифунтов2021-11-11T13:10:43+03:0024 ноября 20191 Комментировать