Функция ВПР в Excel – описание, пошаговые инструкции

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

1.      Как расшифровывается ВПР в Excel?

Запомнить назначение формулы несложно: ВПР (VLOOKUP) переводится как сокращение «Вертикальный ПРосмотр» или на английском — “Vertical Look Up”.

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

Термин «вертикальный», означает, что показатели в таблице должны быть расположены вертикально, а данные – по строкам. А для горизонтально структурированных данных используется ГПР (HLOOKUP на английском). Как вы, думаю, догадываетесь, ГПР расшифровывается как “горизонтальный просмотр”. В общем, то же самое действие, но по горизонтали.

2.      Что делает ВПР?

Она возвращает значение из таблицы как результат поиска другого, связанного с ним значения этой же таблицы.

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

Необходимо, чтобы таблица была создана таким образом, чтобы значения поиска располагались в крайнем левом столбце. Скажем, названия товаров. Данные, которые вы хотите получить (результирующие значения), могут быть записаны в любом месте, находящемся правее. А правее в этой таблице могут значиться артикул товара, его вес, цена, количество и т.д. Мы движемся по первому столбцу сверху вниз, и как только находим что-то подходящее, то останавливаемся на этой строке и берем данные из какого-то из столбцов, находящихся правее. Нужно только указать, из какого по счету.

3.      Для чего используется ВПР?

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

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

Еще раз сделаем важное замечание: поиск всегда происходит в первом (крайнем левом) столбце.

Именно это ограничение и не позволяет считать ее универсальным решением. Но все же возможности применения очень широки.

Синтаксис ВПР позволят нам применять ее для очень большого круга задач, при котором необходимо найти и вернуть определённое значение.

Мы начнем с самого простого и постепенно будем рассматривать все более сложные способы  ее использования.

Синтаксис

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) – английская версия

=ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр]) – русская версия

4.      Как расшифровываются параметры ВПР?

Чтобы лучше понять, что такое ВПР в Excel, давайте для начала просто попробуем создать ее на нашем рабочем листе.

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

Либо просто ставим «=» и начинаем писать название. И  тут же появляется выпадающий список подходящих функций. Выбираем нужную.

Затем в открывшемся окне заполняем все параметры. Их вы видите на рисунке выше.

ВПР(искомое; таблица; номер_столбца; параметр_просмотра)

  • Искомое — то, что ищем.
  • Таблица — диапазон ячеек, в котором ищем и из которого затем извлекаем данные.
  • Номер_столбца — номер столбца диапазона, из которого возвращаются данные, если значение в первом столбце данной строки совпадает с аргументом искомое_ значение.
  • Интервальный_просмотр — необязательный, но очень важный логический аргумент. О нем мы поговорим ниже.

Видео — «Быстрый перенос данных с помощью функции ВПР в Экселе»

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

Это очень актуально для тех кто работает в закупках и отправляет заказы поставщику.

Обычно происходит следующая ситуация. Вы отправляете заказ поставщику, через некоторое время получаете ответ в виде счёта и сверяете заказ с счётом.

Всё ли есть в счёте, в нужном ли количестве, по правильным ли ценам и т.д.

5.      ВПР имеет два режима поиска.

Четвертый параметр «интервальный просмотр» позволяет задать режим сравнения, в котором нужно работать – точный или приблизительный. Это логический параметр, то есть для него возможно 2 варианта:

  • 0 или ЛОЖЬ – интервальный просмотр выключен (ищем точное соответствие)
  • 1 или ИСТИНА – интервальный просмотр включен (достаточно приблизительного)

Если этот параметр – ЛОЖЬ, то ищется точное равенство в первом столбце с аргументом «искомое». Если же такого нет, воз­вращается ошибка #Н/Д. Точный режим нужен, когда вы хотите найти информацию, основанную на каком-то уникальном ключе. Скажем, информацию о товаре – по коду товара, или данные фильма по его названию, человека – по фамилии.

Если он равен ИСТИНА или вовсе пропущен, то допускается неточное совпадение в данных отсортированной таблицы с аргументом «искомое». То есть нам нужно «лучшее соответствие», «как можно самое близкое».  Возможно, вы просматриваете почтовые тарифы по весу посылки, ищете налоговую ставку на основе дохода, или определяете скидку на основе ежемесячного объема продаж. В этих случаях вы, скорее всего, не найдете именно ваш вес либо точную цифру вашего дохода.  А если точного нет, то принимается ближайшее подходящее значение.

Обратите внимание, что при этом предполагается, что наш перечень отсортирован по столбцу поиска.

8.      ВПР ищет только справа.

Возможно, самым большим ограничением ВПР является то, что она может искать только вправо от ключевого столбца.

Это означает, что вы сможете получить данные только из колонок, находящихся справа от первого столбца. Когда значения поиска находятся в первом (крайнем левом) столбце, это ограничение не имеет большого значения, поскольку все остальные уже находятся справа. Однако, если область поиска находится внутри таблицы, вы сможете искать значения только справа от неё. Вам также нужно будет в качестве источника данных брать не всю таблицу, а только ее часть, которая начинается с области поиска.

Впрочем, это ограничение можно преодолеть, о чем мы также вам расскажем.

9.      ВПР пригодится, чтобы объединять данные из разных таблиц.

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

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

Обратите также внимание, что даже точный поиск можно сделать немного приблизительным, используя знаки подстановки * и ?. Напомню, что знак вопроса заменяет собой любой один символ, а звездочка – любое количество знаков (в том числе и ноль). Таким вот образом мы обнаружили в перечне товаров персики, записав в условии поиска “*”&”персики”&”*” или же “*”&D2&”*”.

10. Может помочь классифицировать данные.

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

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

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

11. Абсолютные ссылки облегчают и ускоряют работу.

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

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

12. Именованные диапазоны облегчают понимание расчетов и еще больше упрощают работу.

Абсолютные ссылки выглядят довольно некрасиво и не очень понятно и наглядно. Поэтому можно сделать ваши расчёты намного чище и проще для понимания, заменив абсолютные ссылки именованными диапазонами. И никакие возможные изменения на вашем листе Excel не смогут их «испортить».

Копировать и переносить их также можно без проблем.

В приведенном выше примере с данными о сотрудниках вы можете назвать входную ячейку B2 «фамилия», а затем выделить все ячейки с информацией и назвать диапазон B5:F100 как «ДанныеСлужащего». Затем перепишите свою формулу в C2 следующим образом:

=ВПР(фамилия;ДанныеСлужащего;2;ЛОЖЬ)

Сравните сами – насколько понятнее стал расчет из совета №12 по сравнению с №11.

15. Используйте ВПР + ПОИСКПОЗ для полностью динамического индекса столбца

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

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

Пригодиться это может при вычислении продаж для продавца в конкретном месяце или выборе цены на конкретный продукт от конкретного поставщика.

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

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

При этом давайте еще попробуем использовать именованные диапазоны. Так нам будет проще разобраться.

Итак, A7:D17 присвоим имя «данные». A6:D6 назовем «месяц».

В результете в G8 мы можем записать:

=ВПР(G6;данные;ПОИСКПОЗ(G7;месяц;0);0)

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

17. Вместо ошибки #Н/Д можно показать любое сообщение

В режиме точного совпадения ВПР отобразит ошибку #Н/Д, если ничего не найдено. С одной стороны, это полезно, потому что оно однозначно говорит вам, что нет подходящих данных. 

Иногда это не очень удобно, поскольку такие ошибки #Н/Д потом порождают новые ошибки при попытке рассчитать сумму, попадают в распечатку и т.д. Можно легко перехватить их и заменить на любое другое подходящее значение (скажем, на ноль) с помощью функции ЕСЛИОШИБКА (IFERROR).

Далее мы рассмотрим обработку ошибок более подробно.

22. ИНДЕКС и ПОИСКПОЗ могут больше и лучше, чем ВПР.

Если вы будете читать о работе в Excel, то вы, вероятно,  столкнетесь с дебатами «ВПР или ИНДЕКС+ПОИСКПОЗ ?».  Спор может быть на удивление горячим 🙂

Суть в следующем: ИНДЕКС+ПОИСКПОЗ может делать все, что может делать ВПР (и ГПР), с гораздо большей гибкостью, но за счет немного большей сложности. 

Таким образом, те, кто поддерживает ИНДЕКС и ПОИСКПОЗ, будут утверждать (и очень разумно), что вы могли бы также начать их изучать, так как в итоге это дает вам лучший набор инструментов.

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

Мой совет состоит в том, что если вы часто используете Excel, то обязательно нужно научиться использовать ИНДЕКС и ПОИСКПОЗ. Это очень мощная комбинация.

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

[the_ad_group id=”48″]

Еще об использовании функции ВПР:

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...