ВПР в Excel – поисковая функция. С её помощью можно последовательно просматривать информацию по вертикали и «подтягивать» данные из одной таблицы в другую. Рассказываем, что такое ВПР и как пользоваться VLOOKUP в Excel.
Зачем нужна функция ВПР и когда её применяют
Функция ВПР в Excel (на английском VLOOKUP) служит для поиска нужных данных – номеров телефонов, цен, артикулов товаров и др. После нахождения эти данные можно объединить из нескольких таблиц.
Для примера: в таблицу занесли данные о заказе – наименование товаров, артикулы, количество. Прайс размещён в другой. Наша задача – объединить, не перенося цифры вручную, данные из таблицы заказа со стоимостью товаров так, чтобы каждая цена соответствовала определённому артикулу.
Аргументы функции ВПР
Формула ВПР в Excel имеет 4 аргумента:
- искомое значение в формате текстовой строки, ссылки на какую-либо ячейку, значения – это параметр в первом столбце слева;
- таблица – диапазон для поиска в виде именного диапазона или ссылки на определенный диапазон (это бывает и вся таблица, и отдельные столбцы);
- номер столбца – отсюда берется искомая величина, указывается номер столбца из диапазона;
- интервальный просмотр – 0 или 1.
С ВПР поиск может происходить в разных форматах и иметь разную точность результатов. Если установить значение «0», можно найти точное соответствие. При установке «1» поиск будет приблизительным, он же включен по умолчанию – функция ВПР будет искать схожие значения, если не изменить значение вручную.
Как создать функцию ВПР в Excel: пошаговая инструкция
Предлагаем простую инструкцию по созданию функции ВПР в Excel для чайников с пошаговым описанием действий.
Создаем шаблон
Для примера будем использовать функцию VPR, чтобы найти и подставить необходимые данные из одной таблицы в другую. Допустим, в магазин привезли большой объём товаров – в одной таблице указано количество товаров, а во второй – стоимость каждого товара. Наша задача – подставить значение из прайса в первую таблицу, чтобы узнать, на какую сумму привезли товаров в магазин.
Вставляем функцию ВПР
Чтобы построить функцию ВПР в Эксель:
- Выделите ячейку, куда будут перенесены найденные значения. Поскольку мы будем добавлять стоимость товаров из прайса, в таблице, где указано количество товаров, необходимо справа без пропусков добавить пустой столбец, соответствующий по высоте другим столбцам.
- Дайте столбцу название. В нашем случае он может называться «Стоимость, рублей».
- Выделите ячейку напротив наименования первого товара (это будет строка 2) в столбце с ценой.
- Откройте окно, куда вводится формула ВПР. Это можно сделать через вкладку формулы, в ней есть строка «Вставить функцию». Но проще нажать на «fx» – кнопка расположена слева над буквами, обозначающими столбцы (строка ссылок).
- При нажатии «fx» справа откроется вкладка «Построитель формул». В строке поиска нужно набрать ВПР, а затем нажать «Вставить функцию».
В построителе формул появятся 4 строки, куда можно вводить аргументы функции.
Заполняем аргументы функции ВПР
Функция VLOOKUP в Excel есть на русском языке – это облегчает заполнение аргументов:
- «Искомое значение» используется для того, чтобы обнаружить одинаковые данные для переноса в двух таблицах – в нашем случае это будет наименование товара. Чтобы указать его, нужно поставить курсор в верхнюю графу в окне построителя формул и прописать ячейку, стоящую на пересечении столбца с ценами и первого товара в списке. Это же значение автоматически появится в строке «fx».
- «Таблица» – диапазон, откуда будем получать данные. В нашем случае необходимо отметить диапазон, в котором будут наименования товаров (искомое, всегда должно находиться в первом столбце нашего диапазона) и их стоимость (переносимое). Нужно поставить курсор во вторую строку построителя формул, перейти в первую таблицу с наименованием товаров, выбрать диапазон, в который попадут названия товаров и их цены. После закрепите его – выберите значение диапазона в строке ссылок и нажмите F4 в Microsoft, а для MacOS вместо F4 жмите Cmd + T. Затем протяните функцию до последней нужной строки. При переносе диапазона в построитель формул он должен отразиться и в строке «fx» примерно в таком виде: fx=ВПР(A2;'товарная накладная'!$A$2:$E$49).
- Номер столбца – в нашем случае это столбец со стоимостью товаров. Необходимо поставить курсор в третью строчку построителя формул, ввести значение столбца – считается слева направо. Номер столбца должен отразиться в строке ссылок после точки с запятой.
- Интервальный просмотр определяет точность работы функции. Поскольку мы ищем точную стоимость товаров, вводим 0 в 4-й строке построителя формул. Одновременно ноль должен появиться в строке ссылок после номера столбца.
Если используете приблизительный поиск, желательно сортировать данные, иначе после нахождения нужного значения поиск прекратится.
Завершаем создание шаблона
Когда указан последний аргумент, функция имеет оконченный вид. После этого нужно нажать кнопку «Готово», расположенную в построителе формул внизу справа. В нашем случае в столбце с ценами товаров напротив первого наименования должна появиться цена. Чтобы стоимость подтянулась к остальным товарам из списка, зажмите первую заполненную ячейку столбца с ценой и протяните её до последнего товара в списке. Так функция найдет и перенесёт стоимость оставшихся товаров, но для этого проверьте, закреплён ли интервал при заполнении аргументов.
ВПР и приблизительный интервальный просмотр
Функция вертикального поиска позволяет получать приблизительные данные. Для этого в графе «Интервальный просмотр» достаточно поставить единицу. Такая подстановка не даст найти конкретный товар или конкретную цену, а только приблизительные данные, например, наименования на букву Б, размеры скидки в определённом диапазоне и др.
Чтобы приблизительный поиск был корректным, данные необходимо отсортировать – составить список по возрастанию или по алфавиту в зависимости от исходных данных. Без предварительной сортировки поиск прекращается, как только функция находит первое искомое, хотя ниже могут находиться и другие искомые.
Например, если будете искать товары на букву Б, поиск остановится, как только функция найдёт любое слово, в составе которого есть буква Б, необязательно стоящая первой.
Быстрое сопоставление двух таблиц с ВПР в Эксель
Функция «влукап» позволяет провести анализ двух таблиц в программе Эксель, причём их можно разместить на одном листе. Это удобно, когда, например, надо сверить отгруженные в магазин и проданные товары по количеству позиций и стоимости, записанные в формате xls.
Что нужно сделать:
- в таблице с отгруженными товарами дублируем столбцы – должно получиться два столбца с количеством и два столбца с ценой (поступления и продажи, один заполненный, а второй – пустой);
- затем с использованием ВПР перетягиваем отдельно данные с количеством товаров, отдельно – с ценами (из проданных в поставленные);
- закрепите диапазон ячеек;
- третьей и шестой строками соответственно добавьте по одному столбцу – это будет разница в количестве и в цене;
- в первом столбце с разницей вычитаем от поставленного количества товара количество проданных товаров;
- во втором столбце с разницей делаем то же с ценой.
Как интерпретировать результаты:
- если в столбце с разницей появляется ноль, значит, количество товаров или их цена соответствуют;
- если появляется другое число, например 2, значит, двух позиций не найдено или не соответствуют цены;
- значение может быть отрицательным – значит, есть лишние позиции.
Ошибка #Н/Д означает, что искомая позиция/цена в таблице с проданными товарами отсутствует.