Функция 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 и для замены функции ВПР. Например, есть таблица, в которой перечислен ряд людей и их зарплата, которая складывалась поэтапно. Мы хотим вытащить зарплату одного из работников. Для этого пишем макрос:
Sub VLookupFunctionDemo()
Dim d1 As Double
d1 = Application.WorksheetFunction.Vlookup("имя_работника_из_первой_колонки", Range("диапазон_таблицы"), номер_колонки)
MsgBox Format(d1, "Currency"), , "VBA VLookup Function"
End Sub
В заключение
В данной статье был рассмотрен вопрос: «Как пользоваться Vlookup в Excel?» Использование этой функции может сильно облегчить жизнь тем пользователям, которые ищут данные по разным таблицам, занимаются сравнением различных сведений. Применение ВПР полезно для тех пользователей, которые добавляют информацию из одной таблицы в другую с поиском по заданному критерию. В некоторых случаях функции может оказаться недостаточно, тогда пользователи, разбирающиеся в языке VBA, могут создавать макросы, которые расширяют функционал Vlookup.
