В этой статье мы поговорим, наверное, о самой популярной и удобной функции Excel – ВПР().
Если Вы хоть раз пользовались Excel, то 100% создавали формулу с этой функцией. Сегодня мы еще раз напомним, как эффективно использовать функцию ВПР, и покажем необычные ситуации, где она тоже может пригодиться. О других полезных функциях Excel мы рассказываем в нашем бесплатном гайде, который можно скачать по ссылке.
1. Автономный ВПР
Основная задача функции ВПР – поиск элементов в таблице или диапазоне по строкам. Также есть «симметричная» функция ГПР, которая позволяет искать элементы по столбцам. Все довольно просто: например, можно найти имя клиента по его id или стоимость продукта по его артикулу и так далее.
Тем, кто знаком с SQL, функция ВПР напоминает запрос SELECT c оператором WHERE, где указывается условие поиска.
Самое простое использование функции ВПР:
= ВПР (искомое значение; таблица для поиска; номер столбца, содержащего возвращаемое значение; интервальный просмотр (1/истина или 0/ложь)).
Три первых аргумента являются обязательными. Главное требование к организации данных – искомое значение должно находится в первом столбце таблицы для поиска. (Если речь идет о ГПР, то в первой строке диапазона).
Интервальный просмотр – это необязательный аргумент, который может принимать значения 0 или 1. Единичка означает, что функция будет искать приближенное совпадение, а ноль – точное. Если нет других указаний, то по умолчанию применяется приближенный поиск.
На рассмотренном простом примере мы видим, как работает функция: в выбранном диапазоне в первом столбце она ищет значение, совпадающее со значением из ячейки F2 (ID = 3). Затем, выбирает значение из второго столбца найденной строки. В результате мы получаем цвет, соответствующий заданному ID.
Это все, что касается простого использования ВПР. Давайте посмотрим, в каких еще ситуациях можно использовать данную функцию.
2. Как найти несколько совпадений, используя ВПР
ВПР – крайне удобная функция. Но есть одно «но»: в случае, если в выбранном диапазоне присутствуют несколько совпадений, функция возвращает данные только из первого сопоставленного экземпляра.
И что же делать, если нужно найти значения первого, второго или n-ого соответствия?
Для этой задачи нужно использовать сочетание функций СЧЕТЕСЛИ (COUNTIF) и ВПР (VLOOKUP).
Предположим, у нас есть таблица с именами покупателей, внесенной суммой в кассу и сдачей. Нам необходимо найти все покупки, совершенные одним человеком. Если мы воспользуемся функцией ВПР, то она нам выдаст только первое совпадение. Нужно сделать так, чтобы первый столбец таблицы имел уникальные значения. Создадим его.
Для этого воспользуемся формулой:
А3 = B3&СЧЁТЕСЛИ($B$3:B3;B3)
Благодаря этой формуле мы создали вспомогательный столбец #Имя, который содержит имя клиента и число, которое показывает, сколько раз до этого в таблице появлялось имя клиента – это новое уникальное значение поиска.
Теперь воспользуемся созданным столбцом, чтобы найти все данные о сумме покупок для каждого клиента.
1. Воспользуемся расширенным фильтром, чтобы не переписывать вручную уникальные имена клиентов.
2. Создаем формулу для поиска значений в таблице:
В качестве условия поиска указываем склеенное значение двух ячеек (Имя и номер покупки), чтобы получить значение, как в первом столбце таблицы (#Имя).
Функция ЕСНД необходима для того, чтобы у покупателей, совершивших немного покупок в ячейках стоял 0, а не сообщение об ошибке (#Н/Д).
Замечание! Не забывайте закреплять ячейки и диапазон. Иначе при растягивании формулы все значения съедут и дадут неправильные результаты. К сожалению, не всегда это можно быстро заметить.
Теперь, протянув формулу на все ячейки, мы получили таблицу с данными о суммах покупки каждого клиента и количестве покупок. Теперь можно их с легкостью просуммировать, найти среднее и так далее.
Для решения данной задачи также можно было воспользоваться функциями ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH):
= ИНДЕКС($A$3:$E$12; ПОИСКПОЗ($G$3&H2;$A$3:$A$12;0);5)
3. Как смотреть налево с VLOOKUP
VLOOKUP – одна из самых мощных функций в Excel. Тем не менее, она имеет несколько недостатков. Одним из них является то, что эта функция возвращает значения, находящиеся справа от столбца поиска и не ищет слева. Но для решения этой проблемы есть обходной путь: комбинация функций VLOOKUP и ВЫБОР (CHOOSE).
Обратимся к таблице из первого примера. Допустим, мы знаем название цвета (Синий), но нам нужно найти его код.
Хитрость здесь состоит в том, чтобы виртуально изменить порядок столбцов в таблице, используя функцию CHOOSE. Многие думают, что эта функция работает только с массивами и одиночными индексами. Но это не так. В нашем случае в качестве диапазона для поиска мы укажем следующее выражение: ВЫБОР({1;2};C3:C7;B3:B7) или ВЫБОР({2;1};B3:B7;C3:C7).
Это выражение выдаст ссылку на два объединенных столбца в порядке Colour; ID. А это именно то, что нам нужно.
В результате мы получим следующую формулу:
Обратите внимание, что в функции ВПР в качестве номера столбца, из которого берется результат мы указываем 2, несмотря на то, в таблице, которая у нас перед глазами, столбец ID является первым. Это все потому, что функция ВЫБОР меняет местами столбцы.
В итоге получаем нужный нам результат:
Также для решения этой задачи можно использовать комбинация функций ИНДЕКС и ПОИСКПОЗ:
=ИНДЕКС(B3:B7; ПОИСКПОЗ(F3;C3:C7;0))
4. Как сделать двусторонний поиск с ВПР
Когда нужно найти значение из таблицы по строке и столбцу, стандартный ВПР сталкивается с проблемой. Однако можно использовать комбинацию функций ВПР и ПОИСКПОЗ, чтобы обойти это узкое место.
Как вы знаете, ВПР возвращает значение, просматривая один столбец, потому что третий аргумент – номер столбца – является статическим значением.
Но с помощью функции ПОИСКПОЗ можно сделать номер столбца также изменяемым параметром.
Например, у нас есть таблица с данными о количествах продаж товара каждого производителя за первые полгода.
Предположим, Вам нужно найти какое количество товара от Производителя5 было проданного в апреле.
В этом примере мы используем формулу для динамического поиска столбцов и необходимой строки с помощью ВПР.
Синтаксис формулы:
=ВПР (искомое значение1; таблица; ПОИСКПОЗ (искомое значение2, столбец, 0), 0)
Функция ПОИСКПОЗ сопоставляет требуемое значение из указанного диапазона и возвращает ссылку на нужный столбец.
В нашем случае формула примет вид:
Эта комбинация делает формулу ВПР более устойчивой. Эта функция может изменяться, когда столбцы вставляются или удаляются из таблицы, но все же сочетание ВПР + ПОИСКПОЗ обеспечит плавную работу даже при внесении изменений в столбцы.
В результате получим:
В качестве альтернативы можно использовать функции ИНДЕКС и ПОИСКПОЗ:
=ИНДЕКС(B3:H7; ПОИСКПОЗ(K2;B3:B7;0); ПОИСКПОЗ(K3;B2:H2;0))
5. Как найти частичное совпадение в Excel с VLOOKUP
Иногда мы сталкиваемся с ситуациями, когда нужно найти информацию из таблицы на основе частичного совпадения. Не всегда в данном случае изменение параметра (интервальный просмотр) выдает правильный результат.
В таком случае можно сделать это с помощью подстановочных знаков в VLOOKUP.
Важное примечание: при частичном сопоставлении (4-тый аргумент – интервальный просмотр) всегда должен быть равен 0, чтобы совпадение с подстановочными знаками работало должным образом.
Рассмотрим пример:
Мы хотим узнать подробности о сотруднике, чье имя начинается с «Ил».
Для решения этой задачи мы воспользуемся классической функцией ВПР, но преобразуем первый аргумент – искомое значение. В этом случае мы предоставляем значение поиска как part_name&”*”. Part_name – это тот кусочек, по которому нужно найти соответствие, а «*» – подстановочный знак. Машина переведет выражение part_name&”*” как «начинается с part_name». В нашем случае part_name = Ил.
В SQL такую задачу можно решить с помощью оператора LIKE “Ил%”
Вот, какая формула у нас получилась:
Для того, чтобы достать всю информацию о сотруднике, нам пришлось написать три формулы, в которых мы изменили третий параметр – номер столбца.
Как видите, мы получили все нужные нам значения, соответствующие нашему частичному поисковому значению, без указания полного имени.
Вы также можете найти значение, которое заканчивается определенным символом или имеет их в середине.
Если присоединить подстановочный знак (“*”), то функция будет искать ячейку со значением, заканчивающимся на part_name.
Если Вам нужно совпадение «содержит part_name», то нужно использовать два подстановочных знака, например, ”*” & part_name & ”*”.
6. Как использовать ВПР с несколькими критериями
VLOOKUP – мощная функция, без сомнения, но в случае поиска на основе двух или более критериев ВПР не справится.
Однако, если у Вас есть доступ и права на изменение данных, то можно воспользоваться вспомогательным столбцом, как в примере 2. Мы объединим несколько полей с помощью амперсанда и используем их в функции. Проще говоря, мы добавим новый столбец, в котором объединятся все критерии в один родительский критерий. Теперь мы сможем применить к данным функцию ВПР.
Давайте рассмотрим пример:
Несмотря на то, что все проектировщики баз данных как Отче наш должны знать три основные нормальные формы баз данных, частенько мы сталкиваемся с таблицами, как представлена в примере.
Она не подчиняется никаким правилам, поэтому с ней трудно проводить различные манипуляции.
Попробуем привести таблицу в более понятный вид.
Предположим, нам нужно найти количество баллов ученицы Ольги по физике. Сначала добавим новый столбец, склеив первый и второй:
Чтобы сделать Вспомогательный столбец более читабельным, мы добавили пробел после имени, используя “ ”.
Теперь запишем функцию ВПР. Так как мы должны искать два условия имя и предмет, то первый аргумент нужно также записать так, как во вспомогательном столбце. Похожую процедуру мы делали во втором примере, когда склеивали имя покупателя с порядковым номером покупки.
Посмотрим, что у нас получилось:
Важно, чтобы функция была настроена на точное совпадение.
Новости
Для подписчиков Microsoft 365, начиная с 2020 июля, доступна новая функция ПРОСМОТРХ (XLOOKUP), которая призвана заменить сразу функции ВПР и ГПР. XLOOKUP отличается от функции ВПР тем, что использует отдельные массивы уточняющих запросов и возвращаемых значений там, где функция ВПР использует один массив таблиц, за которым следует порядковый номер столбца.
Например, старая запись ВПР (В3; B3:D7; 3; ложь) будет соответствовать новой: ПРОСМОТРХ (В3; B2:B7; D2:D7) для следующей таблицы:
Также функция ПРОСМОТРХ даст возможность выводить по значению для искомого значения всю строку. Так, например, для таблицы ниже по ID можно будет вывести и имя сотрудника, и его должность одной формулой.
Заключение
Вот мы и вспомнили 6 случаев, в которых может пригодиться функция ВПР. Эти примеры достаточно простые, но очень важны для понимания работы функции. Если Вы часто используете Excel в работе и хотите создавать сложные, автоматизированные отчеты, предлагаем Вам пройти обучение в нашей Excel Академии, где Вы найдете еще больше интересных задач и получите дополнительные навыки по работе с функциями Excel.
Автор: Андрон Алексанян, эксперт SF Education
