Как сделать лукап в экселе
Vlookup Excel: как пользоваться (примеры)
Функция Vlookup Excel на русском языке пишется как ВПР. С ее помощью осуществляется поиск указанной величины среди какого-то определенного множества данных величин, приведенных в одной колонке.
Необходимость ВПР
Необходимость в использовании функции возникает при осуществлении сравнения данных и поиске последних, расположенных в другой таблице, если нужно добавить сведения из одной таблицы в другую, осуществив их отбор по необходимому критерию.
Как пользоваться Vlookup в Excel будет показано в этой статье.
Синтаксис функции
Vlookup Excel function= ВПР по-русски. Синтаксис этой функции следующий:
С помощью использования функции ВПР осуществляется поиск значений в крайнем левом столбце таблицы, при этом происходит возврат значений ячейки, находящейся в этом столбце в той же строке.
С помощью использования данной функции происходит подбор значений, которые отображены в исходной таблице, а по заданному номеру столбца определяет то значение, которое необходимо вставить во вторую таблицу. Поиск осуществляется по значению, заданному для поиска, а затем переносится из того столбца, который задан для переноса (номер_столбца).
Пример использования ВПР
Как пользоваться Vlookup в Excel, исходя из приведенного выше синтаксиса?
Рассмотрим пример, когда у нас в таблице заданы ФИО, даты принятия на работу и отделы, в которых данные работники функционируют. Предположим, что нам из одной таблицы в другую нужно подставить значения даты принятия. В том случае, если значений немного, это можно выполнить вручную.
Но если фамилий много, они расположены в разных таблицах в хаотичном порядке, число фамилий неодинаковое, да к тому же они расположены на разных листах электронной таблицы, то задача значительно усложняется.
Или другой пример Vlookup в Excel — есть прайс-лист, в котором указаны цены конкретных товаров, и есть таблица, в которую нужно перетянуть эти цены, вбить число проданных товаров для того, чтобы автоматически высчитался товарооборот за день.
Функция Vlookup (ВПР) ищет значение, которое задано для поиска, в левом столбце заданной таблицы, при этом передвижение осуществляется сверху вниз.
Предположим, что в рассматриваемом примере использования Vlookup в Excel осуществляется поиск товаров, объединенных под общим названием «Корма для кошек».
По умолчанию колонка электронной таблицы с прайс-листами «Наименование товара» отсортировывается по возрастанию. Формат данной колонки в двух таблицах должен быть одинаковым.
Кликаем на ячейке таблицы расчета товарооборота, в которой будет вставляться цена из прайс-листа.
На панели инструментов кликаем на иконку fx и выбираем функцию ВПР, которую можно найти поиском, а также просматривая категорию «Ссылки и массивы».
После подтверждения выбора функции откроется диалоговое окно, в котором нужно задать значения:
Подтверждаем введенные значения, в результате получим расчет. Таким образом, на данном примере мы рассмотрели некоторые моменты пользования Vlookup в Excel.
Улучшаем ввод
Предположим, что у нас есть не две таблицы, а много таблиц. В этой ситуации, для того чтобы найти ту таблицу, которую необходимо будет выделить, нужно затратить определенное количество времени.
Чтобы облегчить работу, можно при вводе определенных таблиц их сразу выделять, а с помощью контекстного меню давать имя диапазона, которое затем использовать в формуле вместо указания диапазона таблицы.
Таким образом, мы рассмотрели основные условия, как пользоваться Vlookup в Excel.
Ошибка #Н/Д (#N/A) В ВПР (Vlookup)
Если данная функция не находит искомого значения, то в ячейке, в которую должно было оно записаться, вводится #Н/Д (или #N/A, если версия английская).
Чтобы предотвратить появление этой надписи, то есть оставить ячейку пустой в случае ненахождения требуемого значения, в Excel до версии 2007 года вводим =ЕСЛИ(ЕНД(ВПР(;Лист1!;номер столбца;значение «Истина» = 1 или «Ложь» = 0));»»;ВПР(копируем то же, что в предыдущей скобке)))
С 2007 версии эту формулу можно упростить =ЕСЛИОШИБКА(ВПР(указываем то же самое, что и в предыдущей формуле);»»)
ВПР с несколькими условиями
Если необходимо осуществлять поиск по нескольким требованиям, необходимо создать два условия для осуществления поисковой операции, добавить колонку, в которой в дальнейшем будут объединятся колонки с данными, по ним и будет проводиться поиск.
Это позволит нам получить одну необходимую для нас колонку, в которой мы сможем использовать формулу так же, как это было описано выше.
Например, у нас есть поставщики, которые осуществляют поставку разных материалов. Поставщики отображаются в первой колонке, а товары — во второй. Если осуществлять поиск только по поставщикам, мы можем попасть не на тот товар; даже если переставить колонки местами, пользователь попадет на тот товар, но другого поставщика. Поэтому осуществляют объединение этих двух колонок с помощью знака &: =B2&C2. В результате в колонку А будет помещена объединенная колонка (если данную формулу вводили в A2, а затем протягивали), по которой будет осуществляться поиск аналогично предыдущему.
Как работает Vlookup в Excel при вставке значений без привязывания к таблице?
Пишем свой Vlookup
Используя VBA, Vlookup в Excel можно создать самому, точнее, сделать макрос, который будет в чем-то похож на ВПР, но будет предназначен для выполнения конкретных задач определенного пользователя. Так, например, при сравнении необходимо выполнять двойную проверку, поскольку некоторые сравнения могут оказаться более верными по сравнению с остальными. Если ВПР ищет только по левой колонке, то можно создать макрос, с помощью которого будет осуществляться поиск по уточняющей колонке при удачном завершении поиска по базовой.
Допускается использовать VBA и для замены функции ВПР. Например, есть таблица, в которой перечислен ряд людей и их зарплата, которая складывалась поэтапно. Мы хотим вытащить зарплату одного из работников. Для этого пишем макрос:
d1 = Application.WorksheetFunction.Vlookup(«имя_работника_из_первой_колонки», Range(«диапазон_таблицы»), номер_колонки)
В заключение
В данной статье был рассмотрен вопрос: «Как пользоваться Vlookup в Excel?» Использование этой функции может сильно облегчить жизнь тем пользователям, которые ищут данные по разным таблицам, занимаются сравнением различных сведений. Применение ВПР полезно для тех пользователей, которые добавляют информацию из одной таблицы в другую с поиском по заданному критерию. В некоторых случаях функции может оказаться недостаточно, тогда пользователи, разбирающиеся в языке VBA, могут создавать макросы, которые расширяют функционал Vlookup.
Функция ВПР (VLOOKUP) в 6 различных сценариях
В этой статье мы поговорим, наверное, о самой популярной и удобной функции Excel – ВПР().
Если Вы хоть раз пользовались Экселем, то 100% создавали формулу с этой функцией. Сегодня мы еще раз напомним, как эффективно использовать функцию ВПР, и покажем необычные ситуации, где она тоже может пригодиться.
1. Автономный ВПР
Основная задача функции ВПР – поиск элементов в таблице или диапазоне по строкам. Также есть «симметричная» функция ГПР, которая позволяет искать элементы по столбцам. Все довольно просто: например, можно найти имя клиента по его id или стоимость продукта по его артикулу и так далее.
Тем, кто знаком с SQL, функция ВПР напоминает запрос SELECT c оператором WHERE, где указывается условие поиска.
Самое простое использование функции ВПР:
= ВПР (искомое значение; таблица для поиска; номер столбца, содержащего возвращаемое значение; интервальный просмотр (1/истина или 0/ложь)).
Три первых аргумента являются обязательными. Главное требование к организации данных – искомое значение должно находится в первом столбце таблицы для поиска. (Если речь идет о ГПР, то в первой строке диапазона).
Интервальный просмотр – это необязательный аргумент, который может принимать значения 0 или 1. Единичка означает, что функция будет искать приближенное совпадение, а ноль – точное. Если нет других указаний, то по умолчанию применяется приближенный поиск.
На рассмотренном простом примере мы видим, как работает функция: в выбранном диапазоне в первом столбце она ищет значение, совпадающее со значением из ячейки F2 (ID = 3). Затем, выбирает значение из второго столбца найденной строки. В результате мы получаем цвет, соответствующий заданному ID.
Это все, что касается простого использования ВПР. Давайте посмотрим, в каких еще ситуациях можно использовать данную функцию.
2. Как найти несколько совпадений, используя ВПР
ВПР – крайне удобная функция. Но есть одно «но»: в случае, если в выбранном диапазоне присутствуют несколько совпадений, функция возвращает данные только из первого сопоставленного экземпляра.
И что же делать, если нужно найти значения первого, второго или n-ого соответствия?
Для этой задачи нужно использовать сочетание функций СЧЕТЕСЛИ (COUNTIF) и ВПР (VLOOKUP).
Предположим, у нас есть таблица с именами покупателей, внесенной суммой в кассу и сдачей. Нам необходимо найти все покупки, совершенные одним человеком. Если мы воспользуемся функцией ВПР, то она нам выдаст только первое совпадение. Нужно сделать так, чтобы первый столбец таблицы имел уникальные значения. Создадим его.
Для этого воспользуемся формулой:
Благодаря этой формуле мы создали вспомогательный столбец #Имя, который содержит имя клиента и число, которое показывает, сколько раз до этого в таблице появлялось имя клиента – это новое уникальное значение поиска.
Теперь воспользуемся созданным столбцом, чтобы найти все данные о сумме покупок для каждого клиента.
1. Воспользуемся расширенным фильтром, чтобы не переписывать вручную уникальные имена клиентов.
2. Создаем формулу для поиска значений в таблице:
В качестве условия поиска указываем склеенное значение двух ячеек (Имя и номер покупки), чтобы получить значение, как в первом столбце таблицы (#Имя).
Функция ЕСНД необходима для того, чтобы у покупателей, совершивших немного покупок в ячейках стоял 0, а не сообщение об ошибке (#Н/Д).
Замечание! Не забывайте закреплять ячейки и диапазон. Иначе при растягивании формулы все значения съедут и дадут неправильные результаты. К сожалению, не всегда это можно быстро заметить.
Теперь, протянув формулу на все ячейки, мы получили таблицу с данными о суммах покупки каждого клиента и количестве покупок. Теперь можно их с легкостью просуммировать, найти среднее и так далее.
Для решения данной задачи также можно было воспользоваться функциями ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH):
3. Как смотреть налево с VLOOKUP
VLOOKUP — одна из самых мощных функций в Excel. Тем не менее, он имеет несколько недостатков. Одним из них является то, что эта функция возвращает значения, находящиеся справа от столбца поиска и не ищет слева. Но для решения этой проблемы есть обходной путь: комбинация функций VLOOKUP и ВЫБОР (CHOOSE).
Обратимся к таблице из первого примера. Допустим, мы знаем название цвета (Синий), но нам нужно найти его код.
Хитрость здесь состоит в том, чтобы виртуально изменить порядок столбцов в таблице, используя функцию CHOOSE. Многие думают, что эта функция работает только с массивами и одиночными индексами. Но это не так. В нашем случае в качестве диапазона для поиска мы укажем следующее выражение: ВЫБОР(<1;2>;C3:C7;B3:B7) или ВЫБОР(<2;1>;B3:B7;C3:C7).
Это выражение выдаст ссылку на два объединенных столбца в порядке Colour; ID. А это именно то, что нам нужно.
В результате мы получим следующую формулу:
Обратите внимание, что в функции ВПР в качестве номера столбца, из которого берется результат мы указываем 2, несмотря на то, в таблице, которая у нас перед глазами, столбец ID является первым. Это все потому, что функция ВЫБОР меняет местами столбцы.
В итоге получаем нужный нам результат:
Также для решения этой задачи можно использовать комбинация функций ИНДЕКС и ПОИСКПОЗ:
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 на курсе «Excel Academy»!
Автор: Андрон Алексанян, эксперт SF Education
Подписывайтесь на полезную рассылку от SF Education в Вконтакте и в Telegram!