Здравствуйте, друзья. Сегодня практикуем функцию ВПР Excel (на английском — VLOOKUP). Я покажу вам несколько примеров использования функции, которые многие мои ученики не использовали, а зря.

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

Неточный поиск в Excel

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

Вот классический пример: У нас есть таблица, где указаны бонусы продавца (в процентах), в зависимости от объема продаж. Взгляните на рисунок ниже. В таблице «Бонусы» мы определяем, что для продаж:

  • 0-200 у.е. – премии не будет
  • 200-300 у.е. – 3% от продаж
  • 300-500 у.е. – 5%
  • свыше 500 у.е. – 7%

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

расчет процентов с помощью ВПР

Мои ученики решили задачу с помощью множественного применения функции ЕСЛИ. Получилась гигантская формула: =ЕСЛИ(D3 < $H$4 ; $I$3 ; ЕСЛИ(D3 < $H$5 ; $I$4 ; ЕСЛИ(D3 < $H$6 ; $I$5 ; $I$6)))

расчет процентов с ЕСЛИ

Да, результат верный, но формула слишком большая и очень запутана. Как её сократить? Вспомним, что неточный поиск ВПР ищет ближайшую меньшую величину. Воспользуемся этим: =ВПР(D3;$H$3:$I$6;2;ИСТИНА)

расчет процентов с ВПР

Результат получился тот же, а вычисления – короче и понятнее. Последний аргумент «ИСТИНА» в функции указывает на то, что используется неточный поиск. Элегантное решение вместо громоздкой формулы.

Как это работает? Например, у Смирнова продажи – 205 у.е. ВПР ищет в таблице с бонусами ближайшее меньшее число, а это – 200. Функция вернет соответствующий этой строке бонус (3%).

ВПР по двум условиям в Excel

Вопрос, который вызывает ступор: как сделать поиск с помощью ВПР по нескольким условиям, ведь функция этого не предусматривает. Но мы можем сделать это с помощью объединения колонок. Используем для этого оператор конкатенации «&», или функцию СЦЕПИТЬ.

Рассмотрим на примере предыдущей таблицы. Заметили, что в ней — два Соколова? Первый – в Центральном районе, второй —  в Южном. Как найти продажи второго? Точный поиск ВПР вернет первого, если искать по фамилии. Придется искать по двум параметрам – ФИО и району.

Для этого добавим дополнительный столбец D в таблице с исходными данными. С помощью функции СЦЕПИТЬ, объединим в ней имена и регионы. Вот так:

дополнительный столбец

 

Теперь с помощью ВПР будем искать в массиве D3:D17 связку Фамилия+Регион:

=ВПР(I2&I3;$D$3:$E$17;2;0)

ВПР по двум параметрам

Первым аргументом записано «I2 & I3», т.е. упомянутая выше связка Фамилия+Регион. Оператор конкатенации «&» объединяет строки, записанные в I2 («Соколов») и I3 («Южный»). Тогда ВПР будет искать значение «СоколовЮжный». И найдет её в ячейке D11, вернув те продажи, что мы искали.

Аналогично можно использовать три и более параметров.

Использование имен в формуле ВПР

Вернемся к формуле из предыдущего параграфа: =ВПР(I2&I3;$D$3:$E$17;2;0). Она простая и короткая, но с ней сложно разобраться, если вы видите ее впервые. Простым решением будет использование информативных имён вместо ссылок.

В этой статье я детально рассказал, как задать имя ячейки или диапазона. Самый простой способ – выделить нужный массив или ячейку и записать ей имя в поле «Имя» слева от строки формул. После этого нажать Enter, чтобы название применилось.

Теперь та же самая формула выглядит так:

=ВПР(имя&регион;таблица_продаж;2;0)

ВПР с именами

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

Динамическое изменение столбца для вывода

Очень интересный пример. Взгляните на картинку:

продажи за квартал

Здесь есть продажи за первое полугодие помесячно. Мы должны найти продажи, например, Богданова, за любой месяц. Чтобы изменять возвращаемый столбец – нужно каждый раз в формуле исправлять порядковый номер. Это неудобно, особенно когда формул несколько. Я решил эту проблему так:

  1. В ячейке L3 организовал выпадающий список с перечнем месяцев. Можно обойтись и без выпадающего списка, а записывать месяца вручную. Но мой вариант удобнее
  2. Получил номер столбца для выбранного месяца с помощью функции =ПОИСКПОЗ(месяц;B2:I2). Такая формула вернет порядковый номер выбранного месяца в диапазоне шапки B2:I2
  3. Вставил формулу из предыдущего пункта вместо номера выводимого столбца функции: =ВПР(имя;B2:I17;ПОИСКПОЗ(месяц;B2:I2);0). Теперь если я изменю период, ПОИСКПОЗ пересчитает номер выводимого столбца и результат пересчитается.

Так можно менять номер выводимой колонки без исправления формулы. Очень удобно!

изменяемый номер столбца

Подстановочные символы и ВПР

Если вы не знаете точной строки, которую ищете, можно использовать служебные подстановочные символы:

  • «*» — любое количество любых символов
  • «?» — один любой символ

В чем смысл? Смотрите, в ячейке B13 есть некто Семёнов. Его фамилия записана через «ё». Если вы будете искать его по строке «Семенов», функция вернет ошибку, что значение не найдено.

ВПР не нашел Семёнова

А по строке «Семёнов», через «ё» — мы получим правильный результат:

ВПР нашел Семёнова

Так часто бывает, когда таблицы заполняют вручную несколько человек. Кто-то пишет через «Ё», кто-то через «Е». Аналогично «Й» и «И». Как исключить подобные ошибки? Запишем вместо спорной буквы подстановочный символ: =ВПР(«Сем?нов»;$B$3:$E$17;4;0)

подстановочный символ

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

Обход ошибок при поиске ВПР

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

У нас есть две основные функции обхода таких ошибок:

  • ЕСЛИОШИБКА(выражение, значение_если_ошибка) – универсальная формула. Если в первом ее аргументе возвращается любая ошибка, возвращается второй аргумент
  • ЕНД(выражение) – проверяет выражение, и возвращает ИСТИНА, если там ошибка, или ЛОЖЬ, если все нормально. Эту функцию обычно применяют в составе других. Например, ЕСЛИ

Например, чтобы вместо #Н/Д вернуть информативную строку, запишите такую формулу:

=ЕСЛИОШИБКА(ВПР(H2;$B$3:$D$17;3;0);«Не найдено»)

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

ЕСЛИОШИБКА

 

Ускорение работы с помощью двойного ВПР

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

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

Запишем такую формулу: =ЕСЛИ(ВПР(значение ; массив ;1;1)=значение ; ВПР(значение ; массив ; номер_столбца ;1);«Не найдено»).

Функция ЕСЛИ проверяет, нашёлся ли нужный элемент с помощью неточного поиска. Нашёлся – выводит нужную колонку. Не нашёлся – выводит строку «Не найдено».

быстрый поиск с двумя ВПР

Попробуйте сами и убедитесь, что ВПР стал работать гораздо живее!

Это все хитрости, которые я хотел вам показать. Пробуйте и используйте, чтобы стать более эффективными в своей работе. До встречи!