Функция ВПР в Excel для чайников и не только

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

Начало работы

Для построения синтаксиса функции ВПР вам потребуется следующая информация:

  1. Значение, которое вам нужно найти, то есть искомое значение.

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

  3. Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона указать B2:D11, следует посчитать B первым столбцом, C — вторым и так далее.

  4. При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.

Теперь объедините все перечисленное выше аргументы следующим образом:

=ВLOOKUP(искомые значения, диапазон, содержащий искомые значения, номер столбца в диапазоне, содержащий возвращаемую величину, приблизительное совпадение (ИСТИНА) или Точное совпадение (ЛОЖЬ)).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Синтаксис

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

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

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

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

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

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

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

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

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

Примеры

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

Пример 1

=ВЛОП (B3;B2:E7;2;ЛОЖЬ)  ВРОТ.В.В.ищет Шрифтана в первом столбце (столбце B) в table_array B2:E7 и возвращает Olivier из второго столбца (столбца C) table_array.  False возвращает точное совпадение.

Пример 2

=ВКП (102;A2:C7;2;ЛОЖЬ)  ВРОТ.В.В.ищет точное совпадение (ЛОЖЬ) фамилии 102 (lookup_value) во втором столбце (столбец B) в диапазоне A2:C7 и возвращает fontana.

Пример 3

=ЕСЛИ(ВЛП(103;A1:E7;2;ЛОЖЬ)="Souse";"Located";"Не найдено")  ЕСЛИ проверяет, возвращает ли Суза фамилию сотрудника в качестве фамилии сотрудника с именем 103 (lookup_value) в A1:E7 (table_array). Поскольку фамилия, соответствующая 103, является Лилем, условие ЕСЛИ ложно и не найдено.

Пример 4

=INT(YEARFRAC(ДАТА(2014;6;30);ВLOOKUP(105;A2:E7;5;ФЛАЖКИ);1))  ВЗ ПРОСМОТР ищет дату рождения сотрудника, соответствующую 109 (lookup_value) в диапазоне A2:E7 (table_array), и возвращает 04.03.1955. После этого преобразуется даты рождения с 06.02.2014 и возвращается значение, которое затем преобразуется с помощью iny в значение 59.

Пример 5

ЕСЛИ(ЕНА(ВЛП(105;A2:E7;2; FLASE))=ИСТИНА;"Сотрудник не найден";ВЗ ПРОСМОТР(105;A2:E7;2;ЛОЖЬ))  ЕСЛИ проверяет, возвращает ли значение фамилии в столбце B для 105 (lookup_value). Если ВЛОК находит фамилию, то если отображается фамилия, в противном случае если возвращает не найден сотрудник. IsNA позволяет убедиться, что если #N/A, ошибка заменяется не найденной ошибкой, а не #N/A.    В этом примере возвращается значение "Бурк", которое является фамилией, соответствующей 105.

Функцию ВЛОП можно использовать для объединения нескольких таблиц в одну, если одна из таблиц имеет поля, общие для всех остальных. Это особенно полезно, если вам нужно поделиться книгой с людьми, у которых есть более старые версии Excel, которые не поддерживают функции данных с несколькими таблицами в качестве источников данных, путем объединения источников в одну таблицу и изменения источника данных функции данных в новую таблицу, функцию данных можно использовать в более старых версиях Excel (при условии, что функция данных поддерживается более старой версией).

A worksheet with columns that use VLOOKUP to get data from other tables

Здесь столбцы A–F и H имеют значения или формулы, которые используют только значения на этом сайте, а остальные столбцы используют В ПРОСМОТР и значения столбцов A (код клиента) и B (Доверенность) для получения данных из других таблиц.

  1. Скопируйте таблицу с общими полями на новый и придать ей имя.

  2. Чтобы открыть диалоговое окно Управление отношениями, > в > управления отношениями нажмите кнопку Data > Data Tools (Управление отношениями).

    Диалоговое окно "Управление связями"

  3. Для каждой из указанных связей обратите внимание на следующее:

    • Поле, которое связывает таблицы (в скобки в диалоговом окне). Это первый lookup_value для формулы ВЛКП.

    • Имя связанной таблицы подсмотра. Это первый table_array в формуле ВЛИО.

    • Поле (столбец) в связанной таблице подытовки с данными, которые должны быть в новом столбце. Эта информация не отображается в диалоговом оке Управление связями. Чтобы узнать, какое поле нужно извлечь, необходимо посмотреть в связанной таблице подыска. Обратите внимание на номер столбца (A=1) — это col_index_num формуле.

  4. Чтобы добавить поле в новую таблицу, введите формулу в первом пустом столбце СРОТ, используя сведения, собранные на шаге 3.

    В нашем примере в столбце G для получения данных “Ставка выставления счета” из четвертого столбца(col_index_num = 4) из таблицы “Доверенности” используется столбец “Доверенность” lookup_value(table_array) с формулой =ВЛП([@Attorney];tbl_Attorneys;4;ЛОЖЬ).

    В формуле также можно использовать ссылку на ячейку и ссылку на диапазон. В нашем примере это будет =ВЛВП(A2;’Поверенные’! A:D,4;ЛОЖЬ).

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

Проблема

Возможная причина

Неправильное возвращаемое значение

Если аргумент интервальный_просмотр имеет значение ИСТИНА или не указан, первый столбец должны быть отсортирован по алфавиту или по номерам. Если первый столбец не отсортирован, возвращаемое значение может быть непредвиденным. Отсортируйте первый столбец или используйте значение ЛОЖЬ для точного соответствия.

#Н/Д в ячейке

  • Если аргумент интервальный_просмотр имеет значение ИСТИНА, а значение аргумента искомое_значение меньше, чем наименьшее значение в первом столбце таблицы, будет возвращено значение ошибки #Н/Д.

  • Если аргумент интервальный_просмотр имеет значение ЛОЖЬ, значение ошибки #Н/Д означает, что найти точное число не удалось.

Дополнительные сведения об устранении ошибок #Н/Д в функции ВПР см. в статье Исправление ошибки #Н/Д в функции ВПР.

#ССЫЛКА! в ячейке

Если значение аргумента номер_столбца больше, чем число столбцов в таблице, появится значение ошибки #ССЫЛКА!.

Дополнительные сведения об устранении ошибок #ССЫЛКА! в функции ВПР см. в статье Исправление ошибки #ССЫЛКА!.

#ЗНАЧ! в ячейке

Если значение аргумента таблица меньше 1, появится значение ошибки #ЗНАЧ!.

Дополнительные сведения об устранении ошибок #ЗНАЧ! в функции ВПР см. в статье Исправление ошибки #ЗНАЧ! в функции ВПР.

#ИМЯ? в ячейке

Значение ошибки #ИМЯ? чаще всего появляется, если в формуле пропущены кавычки. Во время поиска имени сотрудника убедитесь, что имя в формуле взято в кавычки. Например, в функции =ВПР(“Иванов”;B2:E7;2;ЛОЖЬ) имя необходимо указать в формате “Иванов” и никак иначе.

Дополнительные сведения см. в статье Исправление ошибки #ИМЯ?.

Ошибки #ПЕРЕНОС! в ячейке

Эта конкретная #SPILL! обычно означает, что формула использует неявное пересечение для искомого значения и использует целый столбец в качестве ссылки. Например, =ВКП(A:A;A:C;2;ЛОЖЬ). Эту проблему можно устранить, привязав ссылку на подытовку к оператору @: =В ПРОСМОТР(@A:A,A:C;2;ЛОЖЬ). Кроме того, можно использовать традиционный метод ВЛКП и ссылаться на одну ячейку,а не весь столбец: =ВЛИ ПРОСМОТР( A2;A:C;2;ЛОЖЬ).

Действие

Примечания

Используйте абсолютные ссылки в аргументе интервальный_просмотр

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

Узнайте, как использовать абсолютные ссылки на ячейки.

Не сохраняйте числовые значения или значения дат как текст.

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

Сортируйте первый столбец

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

Используйте подстановочные знаки

Если значение аргумента интервальный_просмотр — ЛОЖЬ, а аргумент искомое_значение представляет собой текст, то в аргументе искомое_значение допускается использование подстановочных знаков: вопросительного знака (?) и звездочки (*). Вопросительный знак соответствует любому отдельно взятому символу. Звездочка — любой последовательности символов. Если требуется найти именно вопросительный знак или звездочку, следует ввести значок тильды (~) перед искомым символом.

Например, =ВЛП(“Шрифт?”;B2:E7;2;ЛОЖЬ) будет искать все экземпляры Шрифтаны с последней буквой, которая может отличаться.

Убедитесь, что данные не содержат ошибочных символов.

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

Для получения точных результатов попробуйте воспользоваться функциями ПЕЧСИМВ или СЖПРОБЕЛЫ.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

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

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

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

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

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

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

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

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

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

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

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

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

См. также

Краткий справочник. Обновление ВРОТ
Краткий справочник: советы по устранению неполадок с ВЛК

Исправление ошибки #VALUE! в функции ВЛОПИсправление ошибки #Н/Д в функции ВПРОбщие сведения о формулах в ExcelРекомендации, позволяющие избежать появления неработающих формулПоиск ошибок в формулахФункции Excel (по алфавиту)Функции Excel (по категориям)Функция ВПР (бесплатное ознакомительное видео)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[the_ad_group id=”48″]

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

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