Как сделать лукап в экселе

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!

Источник

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *