30 функций Excel за 30 дней: ВПР (VLOOKUP)

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

Возможно, некоторые функции лучше справятся с извлечением данных из таблиц Excel (смотрите раздел Ловушки ВПР), но все же VLOOKUP (ВПР) — это первая функция поиска, которую пользователи пробуют для таких задач. Кто-то её сразу откладывает в сторону, другие бьются, чтобы заставить работать. Да, она имеет ряд недостатков, но лишь поняв, как работает эта функция, Вы будете готовы испробовать и другие варианты.

Ну что ж, давайте разберёмся с инструкцией по применению VLOOKUP (ВПР) и рассмотрим несколько примеров. Если у Вас есть какие-то свои подсказки или приёмы по работе с этой функцией, делитесь ими в комментариях. И не забывайте беречь свои секреты!

Функция 09: VLOOKUP (ВПР)

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

Как можно использовать функцию VLOOKUP (ВПР)?

Поскольку функция VLOOKUP (ВПР) может находить точное совпадение или ближайшее значение в столбце, то она сможет:

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

Синтаксис VLOOKUP (ВПР)

Функция VLOOKUP (ВПР) имеет следующий синтаксис:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
ВПР(искомое_значение;таблица;номер_столбца;интервальный_просмотр)

• lookup_value (искомое_значение): значение, которое требуется найти. Может быть значением или ссылкой на ячейку.
• table_array (таблица): таблица поиска. Может быть ссылкой на диапазон или именованным диапазоном, содержащим 2 столбца или более.
• col_index_num (номер_столбца): столбец, содержащий значение, которое должно быть возвращено функцией. Задается номером столбца внутри таблицы.
• range_lookup (интервальный_просмотр): для поиска точного совпадения используйте FALSE (ЛОЖЬ) или 0; для приблизительного поиска — TRUE (ИСТИНА) или 1. В последнем случае столбец, в котором функция выполняет поиск, должен быть отсортирован в порядке возрастания.

Ловушки VLOOKUP (ВПР)

Функция VLOOKUP (ВПР) может работать медленно, особенно когда выполняет поиск точного совпадения текстовой строки в несортированной таблице. По мере возможности, используйте приблизительный поиск в таблице, отсортированной по первой колонке по возрастанию. Вы можете сначала применить функцию MATCH (ПОИСКПОЗ) или COUNTIF (СЧЁТЕСЛИ), чтобы убедиться, что искомое значение вообще существует в первом столбце (смотри пример 3).

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

Пример 1: Найти цену для выбранного товара

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

• Название товара введено в ячейке B7.
• Таблица поиска с ценами имеет два столбца и занимает диапазон B3:C5.
• Цена записана в столбец 2 таблицы.
• Значение последнего аргумента функции установлено FALSE (ЛОЖЬ), чтобы найти точное совпадение при поиске.

Формула в ячейке C7 имеет вид:

=VLOOKUP(B7,B3:C5,2,FALSE)
=ВПР(B7;B3:C5;2;ЛОЖЬ)

Если название товара в первом столбце таблицы не найдено, то функция VLOOKUP (ВПР) выдаст результат #N/A (#Н/Д).

Пример 2: Преобразовать проценты в буквенную систему оценок

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

• Процент записан в ячейке C9.
• Таблица поиска процентов имеет два столбца и занимает диапазон C3:D7.
• Таблица поиска отсортирована по возрастанию по столбцу с процентами (1-й столбец таблицы).
• Буквенные обозначения оценок содержатся во втором столбце таблицы.
• Последний аргумент функции установлен TRUE (ИСТИНА), чтобы выполнялся приблизительный поиск значений.

В ячейке D9 запишем формулу:

=VLOOKUP(C9,C3:D7,2,TRUE)
=ВПР(C9;C3:D7;2;ИСТИНА)

Если процент не найден в первом столбце таблицы, функция VLOOKUP (ВПР) найдёт ближайшее наибольшее значение, которое меньше аргумента lookup_value (искомое_значение). Искомое значение в нашем примере — 77. Его нет в столбце с процентами, поэтому формула возьмет значение 75 и вернет оценку B.

Пример 3: Найти точную цену по приближенному совпадению

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

• Таблица должна быть отсортирована по первому столбцу по возрастанию.
• Функция COUNTIF (СЧЁТЕСЛИ) будет проверять наличие значения, чтобы предотвратить ошибку.

В ячейке C7 запишем формулу:

=IF(COUNTIF(B3:B5,B7),VLOOKUP(B7,B3:C5,2,TRUE),0)
=ЕСЛИ(СЧЁТЕСЛИ(B3:B5;B7);ВПР(B7;B3:C5;2;ИСТИНА);0)

Если наименование товара в первом столбце таблицы не найдено, функция VLOOKUP (ВПР) возвратит 0.