Использование функции ВПР (VLOOKUP) для подстановки значений

Кому лень или нет времени читать - смотрим видео. Подробности и нюансы - в тексте ниже.

Постановка задачи

Итак, имеем две таблицы - таблицу заказов и прайс-лист:

vlookup1.gif

Задача - подставить цены из прайс-листа в таблицу заказов автоматически, ориентируясь на название товара с тем, чтобы потом можно было посчитать стоимость.

Решение

В наборе функций Excel, в категории Ссылки и массивы (Lookup and reference) имеется функция ВПР (VLOOKUP). Эта функция ищет заданное значение (в нашем примере это слово "Яблоки") в крайнем левом столбце указанной таблицы (прайс-листа) двигаясь сверху-вниз и, найдя его, выдает содержимое соседней ячейки (23 руб.) Схематически работу этой функции можно представить так:

vlookup2.gif

Для простоты дальнейшего использования функции сразу сделайте одну вещь - дайте диапазону ячеек прайс-листа собственное имя. Для этого выделите все ячейки прайс-листа кроме "шапки" (G3:H19), выберите в меню Вставка - Имя - Присвоить (Insert - Name - Define) или нажмите CTRL+F3 и введите любое имя (без пробелов), например Прайс. Теперь в дальнейшем можно будет использовать это имя для ссылки на прайс-лист.

Теперь используем функцию ВПР. Выделите ячейку, куда она будет введена (D3) и откройте вкладку Формулы - Вставка функции (Formulas - Insert Function). В категории Ссылки и массивы (Lookup and Reference) найдите функцию ВПР (VLOOKUP) и нажмите ОК. Появится окно ввода аргументов для функции:

vlookup3.png

Заполняем их по очереди:

  • Искомое значение (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.

Если нужно извлечь не одно значение а сразу весь набор (если их встречается несколько разных), то придется шаманить с формулой массива.

Ссылки по теме 

 



Страницы: 1  2  3  
10.06.2016 14:16:30
Николай, добрый день!
Подскажите, пожалуйста, как именно можно при использовании функции ВПР подставить (рядом) для выбранного значения (названия, маркировки) соответствующую ему таблицу с данными?

Заранее спасибо!

Пример:
Имеется: "лист-1" с названиями моделей и их техническим описанием.
Необходимо: на "лист-2" указывать актуальные в данный момент модели (в определенных ячейках, позиции ячеек определены для возможности последующей печати), и чтобы рядом с ними автоматически выводились таблицы с их описаниями с "лист-1".
Далее лист с таблицами выбранных моделей отправляется на печать.
27.06.2016 10:26:56
Николай, здравствуйте!

Подскажите как можно заставить ВПР работать со списком "Наименование"(из вашего примера), диапазон которого меняется, может быть короче или длиннее. Нужно составить универсальный шаблон, где будут прописаны все формулы, но расчёты будут основываться на выборке с помощью ВПР по списку "Наименование", но длинна списка может меняться бессистемно в широком диапазоне 10-10 000.
26.08.2016 17:17:38
Александр, используйте Умные Таблицы - и про меняющиеся размеры диапазонов можно будет забыть!
25.08.2016 12:14:41
Николай, добрый день,
подписалась на ваш канал на ютубе))) периодически смотрю большое вам спасибо, за то что делитесь с нами такой ценной информацией)

Николай, у меня к вам вопрос по ВПР в "умных" таблицах, если искомое мной значение находится не в первом столбце таблице, как мне на нее сослаться?

=ВПР(I175;(ВРАБОТЕ[Столбец8]:ВРАБОТЕ[Столбец28]);13;0)

жирным выделила диапазон, т.е. в столбце8 у меня находится искомое значение, наверное можно это как-то проще написать?
26.08.2016 17:16:19
Марина, рекомендую вместо ВПР использовать связку функций ИНДЕКС и ПОИСКПОЗ - это намного удобнее и нет проблем со столбцами - см. http://www.planetaexcel.ru/techniques/2/92/
Николай, еще раз большое спасибо Вам за уроки!

Вопрос следующий. Возможно ли усовершенствовать данный прием следующим образом.
Необходимо, чтобы функция искала не точное заданное значение, а проверяла на примерное соответствие.

Задача.
Есть массив адресов. За каждым адресом закреплена обслуживающая район бригада.
Необходимо при формировании массива адресов из биллинга автоматически подставить напротив каждого адреса номер обслуживающей бригады.

К примеру:
АдресБригада
Советская, 10 - 235
Советская, 11 - 465
Советская, 22 - 25
Советская, 43 - 1236
Советская, 55 - 16
Советская, 220 - 187
Советская, 48/1 - 1177
Мы знаем, что по ул. Советской, дома 1-22 закреплены за 5 бригадой.
Возможно ли, чтобы функция проверяла только улицу и дом, не обращая внимание на № квартиры (либо, в данном случае - значения после знака "-")?
Т.е. первоначально мы создаем массив, в котором указаны только улицы и дома. Но при проверке вводим полный адрес, при этом функция "не обращает" внимание на номер квартиры.
12.10.2016 09:56:15
Здравствуйте Николай.
Есть две базы абонентов. Одна из них доделана полностью (#1). Адреса прописки совпадают в обеих базах. Как личные номера абонентов таблицы №1 заменить личными номерами таблицы №2. По признаку прописки
№1
ЛС           Фамилия      Имя        Отчество    Дата рожденияЛичный номерАдрес ЛС
99991013ИВАНОВ          ИВАНИВАНОВИЧ02.12.197500000000000000г. КАКОЙ-ТО, мкр-н МОЛОДЕЖНЫЙ, д. 1, кв.1
99991013ИВАНОВАИВАННАИВАНОВНА02.13.196600000000000000г. КАКОЙ-ТО, мкр-н МОЛОДЕЖНЫЙ, д. 1, кв.1
№2
ЛС   Фамилия  Имя        Отчество         Дата рожденияЛичный номерАдрес ЛС
2361ИВАНОВИВАНИВАНОВИЧ02.12.197500000000000000г. КАКОЙ-ТО, мкр-н МОЛОДЕЖНЫЙ, д. 1, кв.1
2361ИВАНОВАИВАННАНИКОЛАЕВНА02.13.196600000000000000г. КАКОЙ-ТО, мкр-н МОЛОДЕЖНЫЙ, д. 1, кв.1
За ранее спасибо!
12.10.2016 12:05:49
Добавлю, что массив данных очень большой ручками заменять ОЧЕНЬ долго.
13.10.2016 09:56:02
а можно брать данные из одного файла и подставлять их в другой?
13.10.2016 11:56:50
Вроде сделал всё как тут сказано,но у меня значение из второй таблицы почему то берет не из соседней ячейки справа,а ячейкой выше.Почему такое может быть?
25.10.2016 17:16:05
файл
Здравствуйте!
Все понятно, но уже 2 дня мучаюсь - не работает и все!
Нужно подставить цены из листа Фас ады
Пишет Допустимые для ввода значения ограничены другими пользователями.
Что это значит?

скрин1
скрин2
[img]C:\Users\Uliya\Desktop\ВПР\Image 2.jpg[/img]
02.12.2016 01:20:54
Здравствуйте. Только у меня отсутствует звук видео этой темы ?Использование функции ВПР (VLOOKUP) для подстановки значений
Добрый день!
у меня есть таблица с результатами и соответствующими им баллами. Например:
Приседания       Очки
         11                    1
....
         25                  10
         26                  11
         28                  12
как сделать так, чтобы при результате 27 меня возвращало к баллу 11
при результате меньше 11 - к 0
при результате больше 28 - к 12
19.01.2017 14:08:10
Здравствуйте. Есть следующие задания в Ексель. Помогите разобраться.
Заготовка
Пример1
Пример2
Инструкция
11.03.2017 15:45:01
Добрый день!
Спасибо за статью по ВПР, очень помогло!
Но задача усложнилась, у меня в таблице есть одинаковые по артикулу значения, но разные по названию (производителю).
Есть ли такая формула, которая при одинаковом значении предлагала выбор, например в виде раскрывающийся списка?
Ниже таблица в которой подтягиваются данные при помощи ВПР.
№п.пАрткулНаименованиеКоличествоПроизводитель
12000134570Карандаши500ООО "НОР"
22000134588Бумага А4300ООО "ЛЛ"
32000134570Карандаши200ООО "НОР"
42000223456Ножницы100ООО "М"
А вот так выглядят данные в базе:
№п.пАрткулНаименованиеКоличествоПроизводитель
12000134570Карандаши500ООО "НОР"
22000134588Бумага А4300ООО "ЛЛ"
32000134570Карандаши200ООО "КАРАНДАШ"
42000223456Ножницы100ООО "М"
В моем случае 3 позиция должна иметь название ООО "КАРАНДАШ"
14.03.2017 09:46:05
а если таблицы на двух страницах, возможно ли применить зту формулу?
13.09.2017 08:09:47
Да. Одна таблица может быть на одной странице, а вторая может быть на другой странице.
30.10.2017 15:33:41
Добрый день! Такая проблема с ВПР. У меня версия Excel 2016, формулу ввожу через окно f(x). Но он к сожалению он перестал мне подсказывать номер столбца в процессе ввода второго аргумента. Не знаю что делать...
10.11.2017 14:52:45
А когда это он его подсказывал? Не было такого никогда.
Если вводите любую формулу черзе строку формул напрямую, то при выделении диапазона около мыши есть подсказка по количеству выделенных строк столбцов вида 3R5C, но это все.
09.11.2017 04:56:03
НЕ получается
=ВПР(ТЕКСТ(B3);$O$2:$P$870;2;0)
Выскакивает окно= для данной функции введено слишком мало аргументов.

=ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));$O$2:$P$486;2;ЛОЖЬ)- не работает
10.11.2017 14:51:19
У вас либо английская версия Office, либо Excel настроен так, что аргументы функций надо перечислять через запятую, а не обычную точку с запятой.
ВПР тут не причем и работает отлично.
11.11.2017 20:30:18
Вы правы, данные скачала на работе, там англ версия Excel, а дома использую свой. Но как мне решить тогда эту проблему? Как в такой ситуации нужно поступить?  Писать все на англ? Я сейчас попробовала написать через VLOOKUP но выдало #ИМЯ?
т.к. я Excel только начала изучать, на работе времени нет тренироваться, беру работу на дом.
08.02.2018 07:55:15
как сравнить две таблицы с именами, если во второй таблице имеются некоторые ошибки, например вместо одного "а", поставлены две "а", вместо например "Азиата" во второй табл написано азията? а нужно привести в соответствие обе чтобы оба были одинаковы, иначе формулы не работают (не могут найти, а крыжить в списке где много сотен значений разбросанных по разным страницам, листам,  не комильфо)  ВПР не справляется или я его недостаточно знаю?
10.03.2018 11:54:44
Здравствуйте! Помогите, пожалуйста, найти в чем косяк...
Вот мой файлик - https://yadi.sk/d/EZlv6NuQ3TCqcY  -  в нем 2 таблицы, а в желтом столбике формула ВПР для объединения.
Раньше все работало, а сейчас ошибку выдает ((.
12.05.2018 19:16:51
Здравствуйте!
Подскажите, пожалуйста, есть следующая схема:
ячейка 1 меняется от 0 до 2 (вводимое значение) и имеет 2 пограничных условия: меньше 0,2 больше 0,2.
далее в следующей ячейке идет условие из выпадающего списка 5 значений (условий)
в третье ячейке должно выпасть число из двух столбцов данных, каждый столбец с данными соответственно принадлежит ограничению меньше 0,2, больше 0,2.
Т.е. выбирая число 0,3 и любое значение из выпадающего списка должен произойти выбор между двух столбцов и значений в них, т.к. выпадющему списку (строчке) соответствует два значения.
Заранее спасибо.
Вводимое значение    Условие из списка     Таблица значений зависимая от условия из списка и вводимого значения
0,1                             условие 1     значение1 значение 2
0,2     условие 2     значение3 значение 4
0,3     условие 3     значение5 значение 6
0,4                                    условие 4     значение7 значение 8
08.06.2018 10:04:08
Спасибо за Ваши труды, давно хотел сказать!
03.10.2018 12:35:42
Добрый день, вопрос следующий:
для облегчения обработки актов сверки по расчетам с клиентами использую Эксель.
в электронную таблицу контрагента подставляю данные из нашей базы и провожу поиск значения функцией ВПР. это работает только если номера документов в столбцах одинаковы (3632 и 3632), если же в ячейке кроме номера есть еще и текст, например вместо 3623 имет место "Продажа 3623 от 03.08.17", то функция уже не работает и выдает последнее значение из диапазона. что не так?
07.12.2018 09:48:38
Добрый день!
Прошу помощи!
Я использую функцию ВПР, мне необходимо вернуть значение цены на определенную номенклатуры. Сложность в том, что значение в ячейке с искомым значением является элементом выпадающего списка. То есть я заполняю номенклатуру из выпадающего списка, а потом для этой позиции пытаюсь подтянуть из таблицы цену, используя функцию ВПР. Она не работает. Подскажите, что можно сделать в данной ситуации?
07.12.2018 09:59:53
Добрый день!
Расскажите, пожалуйста, как решить проблему.
Я использую функцию ВПР, чтобы вернуть цену номенклатуры. Поиск произвожу по ячейке, в которой значение выбирается из выпадающего списка. Цену возвращаю из простой таблицы с 2-мя столбцами.  В данной ситуации ВПР не работает, или возвращает неправильную цену. Можно ли что-либо сделать, чтобы получить верное значение для искомой номенклатуры?
Написала повтор, так как предыдущее сообщение пропало из комментариев)))
11.03.2019 16:08:55
Здравствуйте.
Впервые пишу комментарий к статьям автора.
Поэтому прошу не судить меня строго, т.к. спец. в Excel я небольшой.
Однако в статье автор не упомянул, что таблица "Прайс" должна быть сформирована в алфавитном порядке, а это, к сожалению, не всегда удобно.
У меня в таблице строки пронумерованы по принципу:,
1
1.1
1.1.1...
1.2...
...
2 и т.п.

Так вот при поиске значения 1.2.1 он почему-то обращается к значению 1.1.1.17.
Вот такая вот проблемка.:(
18.03.2019 15:01:34
Вопрос:
А будет ли работать функция ВПР если нужно затянуть данные в вертикально расположенный перечень из горизонтального, причем в горизонтальном и в вертикальном перечень наименований и значения находятся в разных столбцах и строках?
Страницы: 1  2  3  
Наверх