Использование функции ВПР (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
у меня есть таблица с результатами и соответствующими им баллами. Например:
Приседания Очки
11 1
....
25 10
26 11
28 12
как сделать так, чтобы при результате 27 меня возвращало к баллу 11
при результате меньше 11 - к 0
при результате больше 28 - к 12
Спасибо за статью по ВПР, очень помогло!
Но задача усложнилась, у меня в таблице есть одинаковые по артикулу значения, но разные по названию (производителю).
Есть ли такая формула, которая при одинаковом значении предлагала выбор, например в виде раскрывающийся списка?
Ниже таблица в которой подтягиваются данные при помощи ВПР.
Если вводите любую формулу черзе строку формул напрямую, то при выделении диапазона около мыши есть подсказка по количеству выделенных строк столбцов вида 3R5C, но это все.
=ВПР(ТЕКСТ(B3);$O$2:$P$870;2;0)
Выскакивает окно= для данной функции введено слишком мало аргументов.
=ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));$O$2:$P$486;2;ЛОЖЬ)- не работает
ВПР тут не причем и работает отлично.
т.к. я Excel только начала изучать, на работе времени нет тренироваться, беру работу на дом.
Вот мой файлик - - в нем 2 таблицы, а в желтом столбике формула ВПР для объединения.
Раньше все работало, а сейчас ошибку выдает ((.
Подскажите, пожалуйста, есть следующая схема:
ячейка 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
для облегчения обработки актов сверки по расчетам с клиентами использую Эксель.
в электронную таблицу контрагента подставляю данные из нашей базы и провожу поиск значения функцией ВПР. это работает только если номера документов в столбцах одинаковы (3632 и 3632), если же в ячейке кроме номера есть еще и текст, например вместо 3623 имет место "Продажа 3623 от 03.08.17", то функция уже не работает и выдает последнее значение из диапазона. что не так?
Прошу помощи!
Я использую функцию ВПР, мне необходимо вернуть значение цены на определенную номенклатуры. Сложность в том, что значение в ячейке с искомым значением является элементом выпадающего списка. То есть я заполняю номенклатуру из выпадающего списка, а потом для этой позиции пытаюсь подтянуть из таблицы цену, используя функцию ВПР. Она не работает. Подскажите, что можно сделать в данной ситуации?
Расскажите, пожалуйста, как решить проблему.
Я использую функцию ВПР, чтобы вернуть цену номенклатуры. Поиск произвожу по ячейке, в которой значение выбирается из выпадающего списка. Цену возвращаю из простой таблицы с 2-мя столбцами. В данной ситуации ВПР не работает, или возвращает неправильную цену. Можно ли что-либо сделать, чтобы получить верное значение для искомой номенклатуры?
Написала повтор, так как предыдущее сообщение пропало из комментариев)))
Впервые пишу комментарий к статьям автора.
Поэтому прошу не судить меня строго, т.к. спец. в Excel я небольшой.
Однако в статье автор не упомянул, что таблица "Прайс" должна быть сформирована в алфавитном порядке, а это, к сожалению, не всегда удобно.
У меня в таблице строки пронумерованы по принципу:,
1
1.1
1.1.1...
1.2...
...
2 и т.п.
Так вот при поиске значения 1.2.1 он почему-то обращается к значению 1.1.1.17.
Вот такая вот проблемка.
А будет ли работать функция ВПР если нужно затянуть данные в вертикально расположенный перечень из горизонтального, причем в горизонтальном и в вертикальном перечень наименований и значения находятся в разных столбцах и строках?
Для вашей задачи прекрасно подходит функция ГПР (аналог ВПР, только по строкам). Обязательно закрепите диапазон.
Пример. =ГПР(D66;$G$63:$Q$64;2;0)
Подскажите пожалуйста есть рабочая функция =ВПР(Лист1!B2;Лист2!$A$1:$J$17775;10;ЛОЖЬ) Из нее видно что у меня там до 17 000 разных наименований...
Как сделать так чтобы при сортировке столбца В - менялись данные функции? То есть чтобы ссыль была на ячейку и при смене данных в этой ячейке происходила замена подтягиваемых данных.
Спасибо.
Коротко и ясно, каждая картинка на своем месте, шрифты выделяют действительно важное.. я просто любуюсь : )
Раньше где-то читала про эту функцию, думала "боже, как сложно... в другой раз". А вы сделали это простым и сразу понятным : )
Спасибо!
Мне надо на другой лист в зависимости от даты (у меня функция СЕГОДНЯ) вынести текущий этап проекта.
В одном листе будут собранны все проекты и с каждого листа там должны показываться текущие этапы для проверки и контроля - не могу придумать (((