Функция ВПР в Excel

Функция ВПР в Excel: пошаговая инструкция с 5 примерами

ВПР — это функция Excel для поиска и извлечения данных из определенного столбца в таблице. Поддерживает приблизительные и точные совпадения, а также подстановочные знаки (* и ?). Значения поиска должны появиться в первом столбце таблицы, а столбцы поиска — справа.

Теперь давайте посмотрим, как выполнить поиск с помощью функции ВПР и как она работает. Рассмотрим способы его применения в формулах Excel.

• Как сделать ВПР в Excel: понятная пошаговая инструкция. 
• Как работает функция ВПР в Excel: несколько примеров для чайников». 
• Использование точного и приближенного поиска.
• Используйте именованный диапазон.
• Использование подстановочных знаков и прочие тонкости критериев поиска.
• Использование нескольких условий.
• Умный стол.
• Специальные инструменты для ВПР в Excel

Как сделать ВПР в Excel: понятная пошаговая инструкция. 

Для начала на простом примере разберем, как работает функция ВПР в Excel. Допустим, у нас есть две таблицы. Первый – это прайс-лист с названиями и ценами. Второй — приказ купить некоторые из этих предметов. Поиск в прайс-листе нужного товара и ввод его цены в заказ вручную — очень утомительное занятие. Ведь прайс-лист с ценами может иметь сотни строк. Мы должны делать все автоматически.

Нам нужно найти интересующее имя в первом столбце и вернуть (т.е отобразить в ответ на наш запрос) содержимое нужного столбца в той же строке, где найдено имя.

Наш прайс-лист находится в столбцах A и B. Список покупок находится в EH. Допустим, первым пунктом в списке покупок являются бананы. Нам нужно найти этот товар в столбце А, где указаны все наименования, затем поставить его цену в ячейку G2.

Для этого в G2 запишем следующую формулу:

=ВПР(E2,$A$2:$B$7,2,0)

А теперь мы подробно обсудим, как сделать ВПР.

1. Берем значение Е2.
2. Мы ищем точное совпадение (поскольку четвертый параметр равен 0) в диапазоне $A$2:$B$7 в вашем первом (самом левом) столбце. Обратите внимание, что лучше сразу использовать абсолютные ссылки на прайс-лист, чтобы при копировании этой формулы ссылка не «проскальзывала».
3. Если товар найден, он должен перейти во второй столбец диапазона (на это указывает третий параметр = 2).
4. Берем из него цену и вставляем в нашу ячейку G2.

Это случилось? Теперь просто скопируйте формулу из G2 в G3:G8.

Отчет о продажах готов.

Также, чтобы понять, что такое точное совпадение, попробуйте изменить название продукта на A5 или E2. Например, добавьте пробел в конце. Судя по всему, ничего не изменилось, но вы сразу получите ошибку #Н/Д. То есть товар не найден. В то же время таких случайных ошибок можно легко избежать, о чем мы поговорим отдельно.

Обратим внимание на четвертый параметр. Указываем ноль (можно было написать FALSE), что означает «точный поиск». Но что, если вы забудете указать его и в итоге получите номер столбца, откуда тянутся нужные данные?

Давайте пошагово рассмотрим, что будет происходить в этом случае.

1. Берем значение Е2.
2. Начинаем искать его в крайнем левом столбце диапазона $A$2:$B$7, то есть в столбце А. Поскольку совпадения в А2 не найдено, смотрим дальше: что ниже.
3. Там находим товар «Сливы». Это предполагает, что наш список отсортирован по алфавиту. Ведь это главное условие для нахождения примерного совпадения.
4. Поскольку слово «сливы» находится дальше в отсортированном списке, чем слово «бананы», функция решает, что нет смысла продолжать поиск слова, начинающегося с «Б». Процесс можно остановить. И оставайтесь на букве «А». То есть ближайшее значение существует.
5. Поскольку поиск завершен, переходим из 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.

Какие здесь преимущества?

1. Вы ослеплены буквами, цифрами и знаками доллара в распространенных диапазонах адресов?

Формула именованного диапазона выглядит намного дружелюбнее, нагляднее и понятнее. Вместо безликих скучных координат вы видите идентификаторы, которые вызывают у вас какие-то ассоциации. Хорошо, «цена» или «цена», вероятно, является информацией о ценах.

1. Если по какой-то причине вам нужно изменить координаты диапазона поиска, которые вы использовали в большом количестве формул, нужно ли исправлять каждую формулу или использовать функцию «Найти и заменить»? Согласитесь, это очень долго, кропотливо, возможны ошибки.

Используя именованный диапазон, просто нажмите

Меню — Формула — Менеджер имен.

Затем в списке диапазонов найдите нужный диапазон и настройте его. Изменения будут автоматически применены ко всем формулам.

1. Используя обычные адреса, мы всегда должны думать о том, использовать ли относительную или абсолютную адресацию. Эта проблема не возникает при использовании именованных диапазонов.

Использование символов подстановки и другие тонкости критерия поиска.

Как и в примерах выше, при вводе фамилии выполняется точный поиск. Но есть некоторые вещи, о которых мы не упомянули ранее.

1. Прописные и строчные буквы не влияют на результат. Вы можете ввести все заглавные буквы; ничего не изменить. Ниже вы увидите пример.

1. Если в списке есть люди с такой же фамилией, будет найден только первый. Как мы уже говорили, как только находится что-то подходящее, процесс останавливается.
2. Вы можете использовать подстановочные знаки * и ?. Напомню, что знак вопроса заменяет любой символ, а звездочка заменяет любое количество символов (включая ноль). Мы упоминали о них в начале.

Это имеет смысл делать, если мы знаем только часть значения аргумента.

Но при этом будьте осторожны: снова будет найдено только первое совпадение, как показано на скриншоте. Это очень важное ограничение, о котором следует помнить.

Теперь посмотрим, как можно работать с подстановочными знаками, если условия выбора вводятся не вручную, а берутся из таблицы 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, который может сэкономить вам много времени на поиск нужных данных.

Мастер ВПР — простой способ писать сложные формулы

Интерактивный мастер ВПР проведет вас через параметры конфигурации поиска, необходимые для создания идеальной формулы для заданных вами критериев. В зависимости от вашей структуры данных вы будете использовать стандартную функцию ВПР или формулу ИНДЕКС+ПОИСКПОЗ, если вам нужно извлечь значения слева от столбца поиска.

Вот что вам нужно сделать, чтобы получить формулу для вашей задачи:

1. Запустите мастер с помощью кнопки Vlookup Wizard на ленте данных Ablebits.

1. Выберите основную таблицу (Your Table) и таблицу поиска (Lookup Table).
2. Укажите следующие столбцы (во многих случаях они выбираются автоматически):
   • Ключевой столбец (Key Column) — расположен в вашей основной таблице, содержит значения для поиска.
   • Столбец поиска: в котором будем искать.
   • Столбец возврата: из него мы будем получать значения.
3. Нажмите кнопку Вставить).

Посмотрим все в действии.

Стандартный ВПР.

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

Как и в предыдущих примерах, наша задача — найти цену для каждого товара, извлекая его из прайс-листа. Если область поиска (Цена) является крайним левым столбцом в области поиска, для точного совпадения вставляется обычная формула ВПР:

Не нужно ничего писать от руки.

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

«Левый» ВПР.

Когда столбец результатов (Цена) находится слева от области поиска (Цена), мастер автоматически вставляет формулу ИНДЕКС+ПОИСКПОЗ:

Левую ВПР рассмотрим в отдельной статье. Там же можно увидеть формулы для ручного ввода. Здесь мы получаем их автоматически, не вникая в тонкости синтаксиса и правильную орфографию.

Дополнительный бонус! Благодаря грамотному использованию ссылок на ячейки полученные формулы ВПР можно копировать или перемещать в любой столбец без необходимости обновления ссылок.

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

Примеры использования функции ВПР:

Как объединить две или более таблиц в Excel. В этом руководстве вы найдете несколько приемов для объединения таблиц Excel путем сопоставления данных в одном или нескольких столбцах. Как часто при анализе в Excel вся необходимая информация собирается в один…

2 способа извлечения данных из разных таблиц с помощью ВПР. — Задача: данные для поиска и извлечения с помощью функции ВПР находятся в нескольких таблицах. Эти таблицы имеют одинаковую структуру (то есть одни и те же столбцы, расположенные в одном и том же…

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

4 способа сделать левую ВПР в Excel. — Функция ВПР — одна из самых популярных, когда нужно искать и извлекать данные из таблицы. Однако у него есть большой недостаток. Она смотрит в дальний левый угол…

Формула ВПР в Excel для сравнения двух таблиц — 4 способа — Сравнение таблиц — это задача, которую Excel приходится решать довольно часто. Например, у нас есть старый прайс-лист и его новая версия. Вам нужно посмотреть, какие цены изменились и…

Почему ВПР не работает в Excel? — Функция ВПР — очень мощный инструмент поиска. Но если по какой-то причине это не удастся, вы получите сообщение об ошибке #Н/Д. Давайте попробуем вместе…

ВПР с несколькими условиями: 5 примеров. — Очень часто наши требования к восстановлению данных не ограничиваются одним условием. Например, нам нужна выручка магазина за данный месяц, количество определенного товара, проданного конкретному покупателю, и т д…

Формула ВПР в Excel: 22 факта, которые вам нужно знать. — В процессе работы в Excel часто возникает задача извлечения необходимых данных из рабочих листов. Для этой цели в Excel предусмотрена формула ВПР. И хотя ВПР относительно… Александр Трифунтов2021-11-11T13:10:43+03:0024 ноября 20191 Комментировать