Использование функции ВПР (VLOOKUP) для подстановки значений
Кому лень или нет времени читать - смотрим видео. Подробности и нюансы - в тексте ниже.
Постановка задачи
Итак, имеем две таблицы - таблицу заказов и прайс-лист:
Задача - подставить цены из прайс-листа в таблицу заказов автоматически, ориентируясь на название товара с тем, чтобы потом можно было посчитать стоимость.
Решение
В наборе функций Excel, в категории Ссылки и массивы (Lookup and reference) имеется функция ВПР (VLOOKUP). Эта функция ищет заданное значение (в нашем примере это слово "Яблоки") в крайнем левом столбце указанной таблицы (прайс-листа) двигаясь сверху-вниз и, найдя его, выдает содержимое соседней ячейки (23 руб.) Схематически работу этой функции можно представить так:
Для простоты дальнейшего использования функции сразу сделайте одну вещь - дайте диапазону ячеек прайс-листа собственное имя. Для этого выделите все ячейки прайс-листа кроме "шапки" (G3:H19), выберите в меню Вставка - Имя - Присвоить (Insert - Name - Define) или нажмите CTRL+F3 и введите любое имя (без пробелов), например Прайс. Теперь в дальнейшем можно будет использовать это имя для ссылки на прайс-лист.
Теперь используем функцию ВПР. Выделите ячейку, куда она будет введена (D3) и откройте вкладку Формулы - Вставка функции (Formulas - Insert Function). В категории Ссылки и массивы (Lookup and Reference) найдите функцию ВПР (VLOOKUP) и нажмите ОК. Появится окно ввода аргументов для функции:
Заполняем их по очереди:
- Искомое значение (Lookup Value) - то наименование товара, которое функция должна найти в крайнем левом столбце прайс-листа. В нашем случае - слово "Яблоки" из ячейки B3.
- Таблица (Table Array) - таблица из которой берутся искомые значения, то есть наш прайс-лист. Для ссылки используем собственное имя "Прайс" данное ранее. Если вы не давали имя, то можно просто выделить таблицу, но не забудьте нажать потом клавишу F4, чтобы закрепить ссылку знаками доллара, т.к. в противном случае она будет соскальзывать при копировании нашей формулы вниз, на остальные ячейки столбца D3:D30.
- Номер_столбца (Column index number) - порядковый номер (не буква!) столбца в прайс-листе из которого будем брать значения цены. Первый столбец прайс-листа с названиями имеет номер 1, следовательно нам нужна цена из столбца с номером 2.
- Интервальный_просмотр (Range Lookup) - в это поле можно вводить только два значения: ЛОЖЬ или ИСТИНА:
- Если введено значение 0 или ЛОЖЬ (FALSE), то фактически это означает, что разрешен поиск только точного соответствия, т.е. если функция не найдет в прайс-листе укзанного в таблице заказов нестандартного товара (если будет введено, например, "Кокос"), то она выдаст ошибку #Н/Д (нет данных).
- Если введено значение 1 или ИСТИНА (TRUE), то это значит, что Вы разрешаете поиск не точного, а приблизительного соответствия, т.е. в случае с "кокосом" функция попытается найти товар с наименованием, которое максимально похоже на "кокос" и выдаст цену для этого наименования. В большинстве случаев такая приблизительная подстановка может сыграть с пользователем злую шутку, подставив значение не того товара, который был на самом деле! Так что для большинства реальных бизнес-задач приблизительный поиск лучше не разрешать. Исключением является случай, когда мы ищем числа, а не текст - например, при расчете Ступенчатых скидок.
Все! Осталось нажать ОК и скопировать введенную функцию на весь столбец.
Ошибки #Н/Д и их подавление
Функция ВПР (VLOOKUP) возвращает ошибку #Н/Д (#N/A) если:
- Включен точный поиск (аргумент Интервальный просмотр=0) и искомого наименования нет в Таблице.
- Включен приблизительный поиск (Интервальный просмотр=1), но Таблица, в которой происходит поиск не отсортирована по возрастанию наименований.
- Формат ячейки, откуда берется искомое значение наименования (например B3 в нашем случае) и формат ячеек первого столбца (F3:F19) таблицы отличаются (например, числовой и текстовый). Этот случай особенно характерен при использовании вместо текстовых наименований числовых кодов (номера счетов, идентификаторы, даты и т.п.) В этом случае можно использовать функции Ч и ТЕКСТ для преобразования форматов данных. Выглядеть это будет примерно так:
=ВПР(ТЕКСТ(B3);прайс;0)
Подробнее об этом можно почитать тут.
- Функция не может найти нужного значения, потому что в коде присутствуют пробелы или невидимые непечатаемые знаки (перенос строки и т.п.). В этом случае можно использовать текстовые функции СЖПРОБЕЛЫ (TRIM) и ПЕЧСИМВ (CLEAN) для их удаления:
=ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));прайс;0)
=VLOOKUP(TRIM(CLEAN(B3));прайс;0)
Для подавления сообщения об ошибке #Н/Д (#N/A) в тех случаях, когда функция не может найти точно соответствия, можно воспользоваться функцией ЕСЛИОШИБКА (IFERROR). Так, например, вот такая конструкция перехватывает любые ошибки создаваемые ВПР и заменяет их нулями:
=ЕСЛИОШИБКА(ВПР(B3;прайс;2;0);0)
=IFERROR(VLOOKUP(B3;прайс;2;0);0)
P.S.
Если нужно извлечь не одно значение а сразу весь набор (если их встречается несколько разных), то придется шаманить с формулой массива. или использовать новую функцию ПРОСМОТРX (XLOOKUP) из Office 365.
Ссылки по теме
- Усовершенствованный вариант функции ВПР (VLOOKUP 2).
- Быстрый расчет ступенчатых (диапазонных) скидок при помощи функции ВПР.
- Как сделать "левый ВПР" с помощью функций ИНДЕКС и ПОИСКПОЗ
- Как при помощи функции ВПР (VLOOKUP) заполнять бланки данными из списка
- Как вытащить не первое, а сразу все значения из таблицы
- Функции VLOOKUP2 и VLOOKUP3 из надстройки PLEX
У Николая лучшее объяснение как работает функция ВПР из всех, которые я встречал!…
А как сделать, чтобы искалось так же и бОльшее по значению соответствие, просто максимально приближенное число?
Заранее спасибо.
Подскажите,пожалуйста, функция будет работать только когда наименование в таблице заказов ТОЧНО совпадает с наименованием в прайс-листе? А можно как-то сделать что-бы при внесении наименования вручную в строке, например "ябло..." выпадал список с всеми существующими позициями в прайс листе, например "яблоки сладкие, яблоки красные и тд" чтобы выбрать нужную позицию.
Спасибо
Также НД может возникать по куче других причин - см. статью выше.
Скажите, пожалуйста, возможно ли с помощью ВПР подтянуть значения из двух разных таблиц в третью, сводную?
То есть, если показывать на уже изложенном Вами примере, возможно ли в таблицу заказов подставить цены из двух таких прайс-листов?
Возникает вопрос: для того, чтобы подтянуть значения из однотипных таблиц, расположенных на разных листах, приходится в ручную (либо с помощью приема "найти и заменить") в аргументе "Таблица" заменять название нужного листа (при этом сам диапазон остается прежним, меняется лишь название листа). Дело в том, что когда листов множество (30 и более) процесс замены названия листов затягивается. Как решить данную проблему?
Технически можно сделать так, чтоб название листа бралось из ячейки, значение которой бы равнялось названию листа, но настроить данную формулу не получается...
Но в таком виде функция работать не будет. Прошу помочь в данном вопросе, я думаю он может быть актуален многим, кто ВПРит с разных листов.
Вот такая формула, например:
=ВПР(D1;ДВССЫЛ(СЦЕПИТЬ(A1;B1)));2;0)
отлично будет искать содержимое ячейки D1 с текущего листа на в диапазоне на другом листе, который склеивается из ячеек А1 и B1.
Подскажите как подставить данные в третий столбик по первым двум? В первом столбике данные меняются, всего 3 значения, во втором 6 значений.
В (в "способе 2") приводится конструкция динамического диапазона через функции СМЕЩ, ПОИСКПОЗ, СЧЁТЕСЛИ. Попробовал использовать подобную конструкцию для задания второго аргумента функции ВПР - выдаёт ошибку "#Н/Д". Причём использовал два способа - прописывал в самой формуле и задавал именованный диапазон - результат одинаковый. Можно ли для ВПРа использовать данную конструкцию и какие тут есть подводные камни?
Формула получилась следующего вида:
=ВПР(J3;СМЕЩ(Лист2!$C$1;ПОИСКПОЗ(Лист1!$H$3;Лист2!$B:$B;0)-1;1;СЧЁТЕСЛИ(Лист2!$B:$B;Лист1!$H$3);2);2;0)
Вообще задача в следующем: у меня есть таблица с порядковым номером и в соседней ячейке в примечании рисунок. Необходимо подтянуть в другую таблицу по номеру, чтобы это примечание осталось. Мне главное не сколько примечание, сколько этот рисунок, другого способа как закрепить рисунок за ячейкой, кроме как через примечание я не нашёл. Ссылка на картинку в ячейке не подойдёт. Заранее спасибо!
Столкнулась с проблемой применения вложенной функции ТЕКСТ в указанном примере =ВПР(ТЕКСТ(B3);прайс;ЛОЖЬ), функция ТЕКСТ имеет 2 обязательных аргумента и в приведенном виде не работает (у меня, по крайней мере). Моя цель проставить цены из Таблицы Прайс-лист в Таблицу Заказов нужном формате (его можно взять из Таблицы Прайс-лист или прописать в аргументе функции ТЕКСТ), возможно ли это - то есть одновременно - при заборе данных ?
=ТЕКСТ(B3;"#")
Либо написать свой вариант ВПР на Visual Basic
База содержит информацию по расстоянию от ближайшего, определенного мной, населенного пункта, где есть сотрудник компании.
От заказчика приходит запрос со списком населенных пунктов и из общей базы обычно при помощи ВПР, в два клика расстояния найдены, только часто возникают ошибки из-за повторяющихся в базе названий населенных пунктов. Как вы считаете, каким наиболее удобным способом можно было бы решить эту проблему. если необходимо предоставлю файлы. Спасибо!
Подскажите пожалуйста ,возможно ли найти не первое значение соответствующее искомой ячейке , а сумму этих значений ?
Задача такая ,есть рабочие и количество дней которое отработал каждый. Иногда встречается что один рабочий дважды выходил . Сумму в таблице данных неуднобно вставлять каждый раз , можно ли ,чтобы ВПР суммировал значения ,если совпадает имя рабочего ?
Подскажите пожалуйста, как построить функцию VLOOKUP, если таблицы находятся на разных Worksheets?
=VLOOKUP(B3;Лист2!$G$3:$H$19;2;0)
УРРРЯЯЯЯЯЯ, сам разобрался. Проблема решилась когда я повторно пересмотрел урок и понял, что столбец указал не правильный для цены. Нужно указывать номер стоблца именно в ДИАПАЗОНЕ В КОТОРОМ БУДЕТ ВЫБИРАТЬСЯ ЦЕНА, а не столбец по порядку начиная с первого.
Слету составила себе подобные таблички, задействовала ВПР, всё получилось, так приятно.
Возник вопрос. На вашем примере: допустим, в прайс-листе цена товара стоит третьим столбцом, а вторым номер магазина. Товары одинаковые, магазины разные, цены в них тоже отличаются. В таблице заказов тогда тоже вторым столбцом - номер магазина, и необходимо поместить в ячейку правильную цену, в зависимости от этого номера. Есть ли возможность это сделать "легким движением руки"?
Спасибо.
Николай, у меня вопрос.
Есть три таблицы:
1. Список товаров с артикулами и другими данными
2. Таблица соответствия артикула уникальному номеру (2 столбца).
3. Таблица соответствия уникального номера пути к фотографиям. В этой таблице два столбца, в одном уникальный номер, в другом путь к фото товара. Фотографий может быть как одна так и несколько. То есть айди может быть в пяти йчейках напротив которых ячейка с указанием пути.
Вопрос: Как в первую таблицу вытащить пути к нескольким фотографиям одного товара в одну ячейку из третей через вторую? При этом пути к фото разместить через символ | например.
Заранее огромное спасибо!
У меня вопрос. Может ли ВПР найти артикул с текстом?
Например, "525 393-090" ищем в "Кроссовки 525 393-090"
Заранее огромное спасибо!
Пожалуйста, помогите понять работу предложенной вами здесь формулы с функциями ПРОСМОТР и ПОИСК.
Она создана как-то не в соответствии с описанием этих функций, но... как ни странно, работает!
Непонятки вот в чём.
По описанию функции ПРОСМОТР, аргумент №1 -- строка с искомым значением, аргумент №2 -- область-вектор (т.е. одномерная), в которой происходит поиск ячейки со значением, заданным аргументом №1, аргумент №3 -- область-вектор, из которой выбирается значение-результат. То есть, если в области “аргумент №2” находится ячейка, значение которой равно “аргумент №1” — функция вычисляет порядковый номер этой ячейки в этой области и возвращает значение ячейки уже из области “аргумент №3” с тем же порядковым номером.
Вопрос 1. В этом свете, непонятно в вашем примере, какой смысл имеет на месте аргумента №1 значение 2^15 ?
Вопрос 2. В качестве аргумента №2 в вашей формуле стоит функция ПОИСК, результатом работы которой является вроде как «позиция первого вхождения знака или текстовой строки». А по описанию функции ПРОСМОТР, аргументом №2 должен быть дипазон ячеек (область). Как так?
Вопрос 3. По описанию фукции ПОИСК её аргумент №1 -- строковый (текстовой), а в вашей формуле на этом месте -- дипазон ячеек (область). Как так?
Вопрос 4. Ещё по функции ПОИСК. Когда я скопировал часть вашей формулы (аргумент №2 из ПРОСМОТР) в отдельную ячейку, чтобы посмотреть промежуточный результат, то есть, =ПОИСК(F$2:F$4;A2) — к моему недоумению результатом оказалось #ЗНАЧ! ! То есть, фукция отдельно -- вроде неправильная, но внутри другой функции как-то работает?
Повторю, вся эта конструкция (формула) тем не менее, работает! Шаманство какое-то...
то есть,
в результирующий массив в элемент Просматриваемый_Вектор(1) помещается результат обычной функции ПОИСК(F$2;A2) (как бы, ПОИСК(Массив_Примет(1);A2)), в элемент Просматриваемый_Вектор(2) — результат ПОИСК(F$3;A2) (как бы, Массив_Примет(2);A2), и т. д.
То есть, в элементы Просматриваемый_Вектор(i) помещается: либо число (найденная в A2 позиция искомой подстроки Массив_Примет(i)), либо значение #ЗНАЧ! (если значение-подстрока Массив_Примет(i) в A2не найдена).
Что-ж, с моими вопросами 2, 3, 4 из предыдущего сообщения вроде разобрались. (За исключением того, что такое использование функции ПОИСК в экселовской Справке — отсутствует, т.е. — это недокументированная возможность?)
Остался вопрос 1, по функции ПРОСМОТР:
Почему для выявления среди элементов массива Просматриваемый_Вектор элемента, содержащего число, их надо сравнивать именно c значением 2^15 ?
И ещё: В экселовской справке по функции ПРОСМОТР отмечено: «Значения в аргументе `просматриваемый_вектор` должны быть расположены в порядке возрастания... в противном случае функция ПРОСМОТР может вернуть неверный результат.».
В то же время, в вашем примере, в возвращаемом функцией ПОИСК виртуальном массиве Просматриваемый_Вектор положение элемента, содержащего число (т.е. соответствующего искомой подстроке) — относительно других элементов произвольное. Как это может сказаться на правильности работы ПРОСМОТР?
Можете пояснить, как оно работает?
Спасибо!
Я правильно понимаю, что Вы исключили в функции VLOOKUP 2 такую вещь как "приблизительный поиск (Интервальный просмотр=1)" ?
Т.е. она ищет только точно совпадение. Верно ?
У меня потребность именно в приблизительном поиске и лучше (крайне желательно) без сортировки таблицы.
Помогите пожалуйста какая то ошибка в формуле, интересует формула в клетке `M5`, там гдето ошибка, так как если правильно, то оно должно выдать результат аналогичный как в клетке `L5`, помогите найти ошибку пожалуйста..
Проблема решена )