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

К примеру, одна из причин заключается в том, что есть существенный недостаток: по умолчанию предполагается, что вам достаточно не точного, а приблизительного соответствия при поиске. Что, скорее всего, не так. Это может привести к тому, что результаты расчетов выглядят совершенно нормально, даже если они совершенно неверны. Поверьте, это НЕ то, что вы хотите объяснить своему боссу, после того, как он уже отправил вашу таблицу руководству :)

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

Содержание

Итак, что такое ВПР в Excel?  

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

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

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

Термин «вертикальный», означает, что показатели в таблице должны быть расположены вертикально, а данные - по строкам. А для горизонтально структурированных данных используется ГПР (HLOOKUP).

2.      Что делает функция ВПР?

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

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

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

3.      Для чего нужна функция ВПР?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

6.      Внимание: неточный поиск включен по умолчанию!

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

Если аргумент используется ЛОЖЬ (или ноль), то сортировка не обязательна, поскольку ищется точное совпадение.

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

Чтобы избежать этой проблемы, обязательно используйте ЛОЖЬ или ноль в качестве 4-го аргумента, когда вы хотите искать точное совпадение.

В подавляющем большинстве случаев используется точное сравнение: если в прайс-листе найдется точное совпадение названия товара, то функция выведет его цену. В противном случае мы получим ошибку #N/A.

Вы спросите – а зачем же тогда этот параметр, если его значение ИСТИНА (TRUE) или же отсутствие приводит к таким проблемам? Ответ заключается в том, что если всё же вы будете применять её на отсортированном массиве, то производительность и скорость вычислений возрастут по разным оценкам где-то в 50 (пятьдесят!) раз. При работе с большими объемами данных это будет очень заметно.

Поэтому, если есть такая возможность, старайтесь сортировать большие массивы данных. Если делать всё без ошибок, то производительность Excel вырастет многократно.

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

7.      Для поиска приблизительных совпадений данные должны быть отсортированы.

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

К примеру, если ваш критерий начинается с буквы “A” (апельсин), а в начале списка находится слово, начинающееся с буквы “C” (допустим, сливы), то, оценив это, функция решит, что если встретилась буква “C”, то в списке букву “A” дальше искать бессмысленно. Работа остановится и будет возвращена ошибка #Н/Д (#N/A в англоязычной версии), несмотря на то, что правильное наименование в вашем списке было, но чуть ниже. Но вы об этом даже не узнаете.

Может случиться и другое – будет найдено неточное совпадение, то есть найден товар с похожим названием, сотрудник с похожей фамилией. В случае, если вы ищете конкретного человека либо конкретный товар, вряд ли вас устроит такой приблизительный поиск. Но самое плохое заключается в том, что вы не узнаете о том, что найдено не точное, а приблизительное совпадение. А это может привести к ошибкам в принятии решений на основе ваших расчетов.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

13. Вставка столбца может «сломать» ваши формулы.

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

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

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

14. Индекс столбца можно рассчитать автоматически

Вы можете использовать функцию СТОЛБЕЦ() для генерации динамических индексов. Если вы получаете данные из последовательных колонок, этот трюк позволяет настроить одну первую формулу ВПР, а затем просто скопировать ее, не меняя ничего руками.

Например, с данными сотрудника ниже, мы можем использовать функцию СТОЛБЕЦ  (COLUMN в английском варианте) для создания динамического индекса. Для первой формулы в ячейке C3 нам нужен номер 2. Поэтому запишем:

=ВПР($B$2;$B$5:$G$100;СТОЛБЕЦ()-1;ЛОЖЬ)

Поскольку столбец C является третьим на листе, поэтому нам просто нужно вычесть 1.

Далее просто скопируйте из C3 в D3, E3, F3.

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

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

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

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

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

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

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

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

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

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

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

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

Каждый раз, когда вы используете ВПР в режиме точного поиска, у вас есть возможность использовать подстановочные знаки в поисковом значении. Это может показаться нелогичным, но эти знаки позволяют найти точное совпадение на основе частичного совпадения :)

Если аргумент «искомое» является текстом и интервальный_просмотр имеет значение ЛОЖЬ, то для обнаружения точного совпадения можно использовать символы подстановки * и ?.

  • * (звездочка) – любое количество любых символов (в том числе и их полное отсутствие)
  • ? (вопросительный знак) – один любой символ.

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

Они дают вам простой способ создать «ленивый поиск», но они также позволяют ошибиться.

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

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

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

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

18. Числа, записанные как текст, могут стать причиной ошибки.

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

В этом примере артикул 99023 записан в одном случае как текст, в другом – как число. Поэтому и видим ошибку #Н/Д.

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

Один из способов сделать это - преобразовать значения в диапазоне поиска в числа. Простой способ сделать это - прибавить ноль к каждому из них с помощью инструмента специальной вставки.

И наоборот, можно преобразовать критерий поиска в текст, присоединяя пустой пробел ("") следующим образом:

= ВПР(D6&"",A3:A21,2,0)

Если вы не уверены, когда у вас будут цифры, а когда будет текст, вы можете использовать оба варианта, заключив ВПР в ЕСЛИОШИБКА и написав формулу для обработки обоих случаев:

=ЕСЛИОШИБКА(ВПР(D6,A3:A21,2,0);ВПР(D6&"",A3:A21,2,0))

А вообще об ошибках мы будем говорить в отдельной статье. Ссылки смотрите внизу.

19. ВПР нужна для замены вложенных операторов ЕСЛИ.

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

К примеру, обычное использование вложенных ЕСЛИ - это расчет процента скидки или уровня налоговой ставки на основе соответствующей шкалы.

В приведенном ниже примере вы можете сравнить использование вложенной ЕСЛИ и то, что можно сделать при помощи функции ВПР.

Полная вложенная формула ЕСЛИ выглядит следующим образом:

=ЕСЛИ(B1>50000,01;20%;ЕСЛИ(B1>30000,01;17%;ЕСЛИ(B1>20000,01;15%; ЕСЛИ(B1>10000,01;13%;ЕСЛИ(B1>2000,01;12%;10%)))))

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

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

=ВПР(B1;D2:F7;3;1)

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

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

20. Можно использовать только один критерий.

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

Это означает, что вы не можете легко сделать такие вещи, как поиск сотрудника с фамилией «Петров» в «Бухгалтерии» или поиск сотрудника на основе имени и фамилии, если они записаны в отдельных столбиках.

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

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

21. Два ВПР быстрее, чем один.

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

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

= ВПР(заказ_ID, заказ_данные, 5,ИСТИНА)

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

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

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

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

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

Окончательная формула выглядит так:

= ЕСЛИ(ВПР(заказ_ID, заказ_данные, 1,ИСТИНА)=заказ_ID; ВПР(заказ_ID,заказ_данные,5,ИСТИНА);"Не найдено")

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

Примечание: ваши данные должны быть отсортированы, чтобы использовать этот способ.

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

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

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

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

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

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

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

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

ВПР с несколькими таблицами 2 способа извлечь данные из разных таблиц при помощи ВПР. - Задача: Данные, которые нужно найти и извлечь при помощи функции ВПР, находятся в нескольких таблицах. Эти таблицы имеют одинаковую структуру (то есть, одни и те же столбцы, расположенные в одном…
Вычисление номера столбца для извлечения данных в ВПР - Задача: Наиболее простым способом научиться указывать тот столбец, из которого функция ВПР будет извлекать данные. При этом мы не будем изменять саму формулу, поскольку это может привести в случайным ошибкам.…
4 способа, как сделать левый ВПР в Excel. - Функция ВПР – одна из самых популярных, когда нужно найти и извлечь из таблицы какие-либо данные. Но при этом она имеет один существенный недостаток. Поиск она производит в крайнем левом…
Формула ВПР в Excel для сравнения двух таблиц - 4 способа - Сравнение таблиц – это задача, которую в Excel приходится довольно часто решать. Например, у нас есть старый прайс-лист и его новая версия. Нужно просмотреть, цены на какие товары изменились и…
Почему не работает ВПР в Excel? - Функция ВПР – это очень мощный инструмент поиска. Но если он по каким-то причинам завершился неудачно, то вы получите сообщение об ошибке #Н/Д (#N/A в английском варианте). Давайте постараемся вместе…
ВПР с несколькими условиями: 5 примеров. - Очень часто наши требования к поиску данных не ограничиваются одним условием. К примеру, нам нужна выручка по магазину за определенный месяц, количество конкретного товара, проданного определенному покупателю и т.д. Обычными…
Функция ВПР в Excel: пошаговая инструкция с 5 примерами - ВПР - это функция Excel для поиска и извлечения данных из определенного столбца в таблице. Она поддерживает приблизительное и точное сопоставление, а также подстановочные знаки (* и ?). Значения поиска…