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

119986 10.11.2012 Скачать пример

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

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

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

vlookup1.gif

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

Решение

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

vlookup2.gif

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

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

vlookup3.png

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

  • Искомое значение (Lookup Value) - то наименование товара, которое функция должна найти в крайнем левом столбце прайс-листа. В нашем случае - слово "Яблоки" из ячейки B3.
  • Таблица (Table Array) - таблица из которой берутся искомые значения, то есть наш прайс-лист. Для ссылки используем собственное имя "Прайс" данное ранее.
  • Номер_столбца (Column index number) - порядковый номер (не буква!) столбца в прайс-листе из которого будем брать значения цены. Первый столбец прайс-листа с названиями имеет номер 1, следовательно нам нужна цена из столбца с номером 2.
  • Интервальный_просмотр (Range Lookup) - в это поле можно вводить только два значения: ЛОЖЬ или ИСТИНА:
      • Если введено значение 0 или ЛОЖЬ (FALSE), то фактически это означает, что разрешен поиск только точного соответствия, т.е. если функция не найдет в прайс-листе укзанного в таблице заказов нестандартного товара (если будет введено, например, "Кокос"), то она выдаст ошибку #Н/Д (нет данных).
      • Если введено значение 1 или ИСТИНА (TRUE), то это значит, что Вы разрешаете поиск не точного, а приблизительного соответствия, т.е. в случае с "кокосом" функция попытается найти товар с наименованием, которое максимально похоже на "кокос" и выдаст цену для этого наименования. В большинстве случаев такая приблизительная подстановка может сыграть с пользователем злую шутку, подставив значение не того товара, который был на самом деле, поэтому для большинства реальных бизнес-задач приблизительный поиск лучше не разрешать. Исключением являются не текстовые, а числовые искомые значения, например, при расчете Ступенчатых скидок.

Все! Осталось нажать ОК и скопировать введенную функцию на весь столбец.

Ошибки #Н/Д и их подавление

Функция ВПР (VLOOKUP) возвращает ошибку #Н/Д (#N/A) если:

  1. Включен точный поиск (аргумент Интервальный просмотр=0) и искомого наименования нет в Таблице.
  2. Включен приблизительный поиск (Интервальный просмотр=1), но Таблица, в которой происходит поиск не отсортирована по возрастанию наименований.
  3. Формат ячейки, откуда берется искомое значение наименования (например B3 в нашем случае) и формат ячеек первого столбца (F3:F19) таблицы отличаются (например, числовой и текстовый). Этот случай особенно характерен при использовании вместо текстовых наименований числовых кодов (номера счетов, идентификаторы, даты и т.п.) В этом случае можно использовать функции Ч и ТЕКСТ для преобразования форматов данных. Выглядеть это будет примерно так:
    =ВПР(ТЕКСТ(B3);прайс;ЛОЖЬ)
  4. Функция не может найти нужного значения, потому что в коде присутствуют пробелы или невидимые непечатаемые знаки (перенос строки и т.п.). В этом случае можно использовать текстовые функции СЖПРОБЕЛЫ (TRIM) и ПЕЧСИМВ (CLEAN) для их удаления:
    =ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));прайс;ЛОЖЬ)
    =VLOOKUP(TRIM(CLEAN(B3));прайс;FALSE)

Для подавления сообщения об ошибке #Н/Д (#N/A) в тех случаях, когда функция не может найти точно соответствия, можно воспользоваться вот такой конструкцией:

vlookup4.gif

Функция ЕНД (ISNA) проверяет - не возникла ли ошибка #Н/Д как результат работы ВПР и если да, то выводит пустую строку ("") или ноль, а если нет - то выводит результат работы ВПР.

В Excel 2007 и новее для подавления сообщения об ошибке можно воспользоваться новой функцией ЕСЛИОШИБКА (IFERROR). Так, например, вот такая конструкция перехватывает любые ошибки создаваемые ВПР и заменяет их нулями:

=ЕСЛИОШИБКА(ВПР(B3;прайс;2;ЛОЖЬ);0)

=IFERROR(VLOOKUP(B3;прайс;2;FALSE);0)

P.S.

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

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

 


02.04.2013 16:33:29
Отличное руководство. Спасибо.
28.04.2013 16:07:50
Спасибо огромное за Ваш труд!!! Отличные уроки!!!! Граммотная речь, темп в норме, понятное объяснение!!! Многое открыла для себя из Ваших уроков. Подскажите где найти уроки подряд. Спасибо!!!!!
01.05.2013 00:59:51
Не за что - стараемся по мере сил и способностей :) Все мои видеоуроки можно найти (и подписаться на них) на канале planetaexcel на YouTube.
05.05.2013 11:41:21
хорошая статья! спасибо!!!
03.06.2013 15:31:07
Подскажите пожалуйста, если введено значение ИСТИНА (TRUE), то это значит, что идет поиск не точного, а приблизительного, МЕНЬШЕГО ПО ЗНАЧЕНИЮ ИЛИ РАВНОГО, соответствия.
А как сделать, чтобы искалось так же и бОльшее по значению соответствие, просто максимально приближенное число?
04.06.2013 23:40:14
Анастасия, посмотрите как работают функции ИНДЕКС и ПОИСКПОЗ, заменяя ВПР. В этом случае можно выбирать - в какую сторону работает округление при приблизительном поиске.
04.06.2013 17:24:46
А если мне нужно найти значение, просматривая таблицу СНИЗУ?
04.06.2013 23:38:47
А почему бы не отсортировать ее наоборот предварительно тогда?
11.06.2013 15:19:32
Подскажите пожалуйста, всё сделал как написано у Вас, во вкладке Аргументы функции все срабатывает, а в строке таблицы формула а не искомое значение. Как быть?
13.06.2013 09:25:02
Проверьте формат ячейки - он, наверняка, текстовый. Исправьте на Общий (General) и введите формулу повторно.
02.07.2013 15:16:37
Добрый день! Подскажите, пожта, каким способом можно использовать функцию ВПР если происходит поиск значения, которое является частью текста в ячейке? Т.е. если ячейка состоит из одного значения - работает, а если значение "зашито" в ячейку - ВПР ее не распознает.

Заранее спасибо.
10.08.2013 01:24:56
Евгений, боюсь, что ВПР тут не поможет. Попробуйте применить технику поиска ближайшего похожего слова.
03.07.2013 11:43:20
Добрый день. Подскажите, как установить настройки функции так, чтобы при поиске из списка (столбец с названиями линий метро) у меня отображался цвет заливки ячейки соответствующий цвету ветки метрополитена?Спасибо.
10.08.2013 01:23:36
Марина, при помощи самой функции ВПР (и любой другой функции Excel, кстати) цвет ячеек менять нельзя в принципе. Но можно определять с ее помощью название линии метро и на ячейку с названием назначить правило условного форматирования (вкладка Главная - Условное форматирование), которое и будет перекрашивать ячейку в нужный цвет.
08.08.2013 19:25:44
Здравствуйте.
Подскажите,пожалуйста, функция будет работать только когда наименование в таблице заказов ТОЧНО совпадает с наименованием в прайс-листе? А можно как-то сделать что-бы при внесении наименования вручную в строке, например "ябло..." выпадал список с всеми существующими позициями в прайс листе, например "яблоки сладкие, яблоки красные и тд" чтобы выбрать нужную позицию.
Спасибо
11.08.2013 01:35:33
Спасибо Вам, Николай, как раз то что надо! :)
15.08.2013 13:15:13
Николай, вопрос. Если в "таблице заказов" несколько строк "яблоки", "банан" - то почему то не все строки заполняются. В некоторых выпадает НД. Как этого избежать?
08.10.2013 11:30:24
Проверьте закрепление адресов ($) в ссылке на таблицу в функции ВПР.
Также НД может возникать по куче других причин - см. статью выше.
Здравствуйте!
Скажите, пожалуйста, возможно ли с помощью ВПР подтянуть значения из двух разных таблиц в третью, сводную?
То есть, если показывать на уже изложенном Вами примере, возможно ли в таблицу заказов подставить цены из двух таких прайс-листов?
08.10.2013 11:32:31
Я бы сделал два столбца с двумя функциями ВПР, подтягивающими данные из двух прайс-листов. Причем НД заменял бы на нули. Тогда сумма этих столбцов дала бы результирующий итог.
15.10.2013 15:05:37
спасибо Вам огромное, господин Павлов за помощь!
02.11.2013 23:43:38
Николай,здравствуйте! Спасибо, с Вашей помощью научилась пользоваться функцией ВПР. Не могли бы помочь ещё в одном вопросе : возможно ли соединить функции ВПР и ЗАМЕНИТЬ (либо ПОДСТАВИТЬ)? Например, если взять Ваш пример с функцией ВПР : в столбце Н цены с точкой , при вставке данных в столбец D с помощью функции заменять точку на запятую.
25.11.2013 18:31:12
Николай огромное спасибо вам за ваш сайт и статью!
27.11.2013 14:17:15
Спасибо, Николай, за доходчивое и наглядное обучение функции ВПР.
Возникает вопрос: для того, чтобы подтянуть значения из однотипных таблиц, расположенных на разных листах, приходится в ручную (либо с помощью приема "найти и заменить") в аргументе "Таблица" заменять название нужного листа (при этом сам диапазон остается прежним, меняется лишь название листа). Дело в том, что когда листов множество (30 и более) процесс замены названия листов затягивается. Как решить данную проблему?
Технически можно сделать так, чтоб название листа бралось из ячейки, значение которой бы равнялось названию листа, но настроить данную формулу не получается...
11.12.2013 19:40:24
Копайте в строну функции ДВССЫЛ - она умеет преобразовывать текст в ссылку. Если с помощью функции СЦЕПИТЬ склеить из кусочков нужный адрес таблицы с нужного листа, то ДВССЫЛ потом превратит его в настоящую ссылку, которую можно подставить во второй аргумент ВПР.
14.12.2013 04:11:07
Не совсем пойму как это реализовать. Например, вот второй аргумент функции ВПР: (Омск!$A$3:$B$4). Предположим, в ячейке A1 находится название нужно листа (Москва!), а в ячейке В2 диапазон таблицы ($A$3:$B$4). Тогда второй аргумент должен выглядеть так: ДВССЫЛ(СЦЕПИТЬ(A1;B1)) ?
Но в таком виде функция работать не будет. Прошу помочь в данном вопросе, я думаю он может быть актуален многим, кто ВПРит с разных листов.
15.12.2013 09:51:39
Почему вы решили, что "в таком виде функция работать не будет"?
Вот такая формула, например:
=ВПР(D1;ДВССЫЛ(СЦЕПИТЬ(A1;B1)));2;0)
отлично будет искать содержимое ячейки D1 с текущего листа на в диапазоне на другом листе, который склеивается из ячеек А1 и B1.
15.12.2013 11:28:09
Да, действительно, все отлично работает! Огромное СПАСИБО.
09.12.2013 13:19:24
Добрый день!
Подскажите как подставить данные в третий столбик по первым двум? В первом столбике данные меняются, всего 3 значения, во втором 6 значений.
11.12.2013 19:46:08
Олег, если склеить данные из первых двух столбцов с помощью функции СЦЕПИТЬ, то мы получим уникальный код, по которому ВПР и может подставлять данные в третью ячейку из заранее подготовленной таблицы, где перечислены все возможные сочетания первых двух ячеек.
26.12.2013 08:00:44
Здравствуйте, Николай!
В статье про связанные выпадающие списки (в "способе 2") приводится конструкция динамического диапазона через функции СМЕЩ, ПОИСКПОЗ, СЧЁТЕСЛИ. Попробовал использовать подобную конструкцию для задания второго аргумента функции ВПР - выдаёт ошибку "#Н/Д". Причём использовал два способа - прописывал в самой формуле и задавал именованный диапазон - результат одинаковый. Можно ли для ВПРа использовать данную конструкцию и какие тут есть подводные камни?
Формула получилась следующего вида:
=ВПР(J3;СМЕЩ(Лист2!$C$1;ПОИСКПОЗ(Лист1!$H$3;Лист2!$B:$B;0)-1;1;СЧЁТЕСЛИ(Лист2!$B:$B;Лист1!$H$3);2);2;0)
26.12.2013 16:16:11
Степан, однозначно можно - я так сто раз делал. Ищите ошибку в формуле. Советую сначала сделать именованный диапазон с формулой СМЕЩ с помощью Диспетчера Имен на вкладке Формулы, а потом просто подставить имя созданного динамического диапазона в работающий ВПР.
30.12.2013 07:33:06
Спасибо, Николай. Проблема действительно в формуле была - промахнулся немножко с диапазоном.
11.02.2014 09:39:24
Добрый день, а не подскажите, с помощью функции ВПР или чего-нибудь другого можно подтянуть примечание к ячейке?
Вообще задача в следующем: у меня есть таблица с порядковым номером и в соседней ячейке в примечании рисунок. Необходимо подтянуть в другую таблицу по номеру, чтобы это примечание осталось. Мне главное не сколько примечание, сколько этот рисунок, другого способа как закрепить рисунок за ячейкой, кроме как через примечание я не нашёл. Ссылка на картинку в ячейке не подойдёт. Заранее спасибо!
17.02.2014 08:29:18
Добрый день, Николай! Большое Вам спасибо за информацию по ВПР.
Столкнулась с проблемой применения вложенной функции ТЕКСТ в указанном примере =ВПР(ТЕКСТ(B3);прайс;ЛОЖЬ), функция ТЕКСТ имеет 2 обязательных аргумента и в приведенном виде не работает (у меня, по крайней мере). Моя цель проставить цены из Таблицы Прайс-лист в Таблицу Заказов нужном формате (его можно взять из Таблицы Прайс-лист или прописать в аргументе функции ТЕКСТ), возможно ли это - то есть одновременно - при заборе данных ?
02.04.2014 10:52:08
Ирина, попробуйте использовать функцию преобразования в текст в виде:
=ТЕКСТ(B3;"#")
02.04.2014 10:42:34
Добрый день! А как сделать так чтоб при появлении в ячейке определенного слова в другой таблице заполнись данные из строки той самой ячейки? коротко говоря у меня есть строка заказа и когда в столбце "состояни заказа" будет значение "готов" данные этого заказа "размер , цена, категория и т.д." будут автоматически добавляться строкой в таблице склада. заранее благодарен
02.04.2014 10:48:54
Такое добавление можно только макросом организовать.
ann
10.04.2014 14:01:15
добрый день, почему при вычислении формулы в искомом значении отображаются нужные данные а в ячейке прописывается сама формула?
19.04.2014 09:42:44
У вас, скорее всего, текстовый формат у ячейки с формулой. Поменяйте его на Общий и введите формулу еще раз.
23.04.2014 15:20:47
Да функция ограниченная, вот например я не могу выполнить поиск по двум критериям и уже из массива присвоить значение в ячейку, базируясь на 2-х критериях, как это сделать?Спасибо
07.05.2014 12:26:14
Сцепить два ваших критерия в отдельном столбце в один и делать ВПР по нему.
Либо написать свой вариант ВПР на Visual Basic :)
12.05.2014 17:37:46
Здравствуйте Николай! У меня есть рабочая задача. Создана база населенных пунктов страны порядка 14 тыс. значений, есть много повторяющихся названий.
База содержит информацию по расстоянию от ближайшего, определенного мной, населенного пункта, где есть сотрудник компании.
От заказчика приходит запрос со списком населенных пунктов и из общей базы обычно при помощи ВПР, в два клика расстояния найдены, только часто возникают ошибки из-за повторяющихся в базе названий населенных пунктов. Как вы считаете, каким наиболее удобным способом можно было бы решить эту проблему. если необходимо предоставлю файлы. Спасибо!
14.05.2014 14:53:57
Диана, я бы склеил в один столбец (с помощью функции СЦЕПИТЬ) название области и населенного пункта, чтобы получить столбец с уникальными неповторяющимися названиями и уже по нему искал ВПРом.
14.05.2014 18:06:16
Спасибо Николай, да, это вариант, только иногда, населенные пункты в списке бывают без области, т.е для начала нужно найти какой области этот населенный пункт соответствует, и эта область присваивается неправильно так как ВПР находит первый попавшийся в списке, соответствующий населенный пункт. А есть ли способ, если такие повторы существуют, чтобы выдавался не один результат, а сразу несколько вариантов или например их количество (повторов) тогда уже и легко будет проверить вручную какой населенный пункт нужен?
01.08.2014 16:37:28
Подскажите пожалуйста, если в прайсе есть яблоки и еще одна позиция яблоки. Одинаковое название, но разные цены, и изменить наименование нет возможности. А функция "впр" берет только первое значение. Есть ли возможность, чтобы значения складывались?
02.08.2014 16:26:32
Да, вам нужна функция СУММЕСЛИ.
26.09.2014 12:27:49
Подскажите, почему формула не работает? Составил Таблицу из 2-х столбцов, в первом Наименование, во втором параметр. Рядом 2 ячейки - выбор Наименования (через выпадающий список), во второй должно ставиться значение через формулу ВПР. Но появляются ошибки: При выборе Строчки "Ручка Опера" значение подставляется неверно, При выборе строчки "Ручка Бридж" значения не находит.
Ручка Н54
Ручка С 36
Ручка Сэко16
Ручка Опера37
Ручка Тауэр62
Ручка Бридж2
P.S.: и как сюда пример готовый добавить?
27.09.2014 09:21:54
Никак не добавить - это комментарии. Лучше на форум или мне в почту.
06.10.2014 06:55:20
Добрый день !
Подскажите пожалуйста ,возможно ли найти не первое значение соответствующее искомой ячейке , а сумму этих значений ?
Задача такая ,есть рабочие и количество дней которое отработал каждый. Иногда встречается что один рабочий дважды выходил . Сумму в таблице данных неуднобно вставлять каждый раз , можно ли ,чтобы ВПР суммировал значения ,если совпадает имя рабочего ?
14.10.2014 10:45:53
Доброе время суток) Подскажите, пожалуйста, как разделить любое значение в ячейке на искомое по формуле "ВПР"? Заранее спасибо!
24.10.2014 18:00:43
Спасибо, экономил очень много времении )))));)
05.11.2014 16:27:41
Добрый день!
Подскажите пожалуйста, как построить функцию VLOOKUP, если таблицы находятся на разных Worksheets?
05.11.2014 18:17:21
Галина, да так же, как и в случае обычного использования. Только у второго аргумента добавится перед адресом таблицы имя листа и восклицательный знак как разделитель, т.е. будет примерно так (исходный пример, но прайс на другом листе):
=VLOOKUP(B3;Лист2!$G$3:$H$19;2;0)
06.11.2014 10:23:45
Огромное спасибо за быстрый ответ!!!. Надеюсь у меня получится!