Использование функции ВПР (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
Есть такая таблица:
Как присвоить значение из строки, которая находится ниже? В данном примере "500"
Только найденное ПОИСКПОЗ значение увеличьте на 1.
У меня список большой допустим на 1000 организаций
Как связать его с другим листом?
Вместо прайс листа у меня 1) Код 2) организация 3) счёт
Заполняю рядом ячейку кода и у меня успешно всплывает рядом организация и счёт, но это всё на одном листе, а на разных не выходит!
Подскажите пожалуйста!
есть два столбца с фамилиями. Один - сотрудники, второй - руководители. Нужно найти однофамильцев между ними. Фигня вся в том, что та формула, что используется мной находит только точные совпадения (Иванов-Иванов), а вот если фамилия женская, такое совпадение игнорируется (Иванова-Иванов). Если применяется значение 1 или -1, то формула срабатывает некорректно.
Например структура таблицы такая:
СКЛАД 1
яблоки 15 кг
груши 20 кг
апельсины 15 кг
СКЛАД 2
арбузы 30 кг
дыни 30 кг
яблоки 10 кг
В этом случае получается всегда "яблоки 15 кг"
Как-то не совсем в тему комментарий получился - похоже не к формуле вопрос, а скорее вопрос по возможностям проверки исходных данных.
При использовании функции ВПР применительно к небольшим таблицам, я никогда не сталкивался с проблемой Н/Д. Но при обработке данных импортированных из БД, функция не работает, все рекомендации по устранению Н/Д я вроде опробывал, помогите пожалуйста разобраться. Нужно сверить Ф.И.О. в двух таблицах, и извлечь значения из столбца ПОЛ, соответствующего сотрудника. Вот .
P.S. Еще одно наблюдение, если задать имя таблице охватив в т.ч. шапку таблицы, и уже это "имя таблицы" использовать в ВПР, то функция возвращает значение, из нужного столбца, но только это значение из "ШАПКИ" а не соответствующей строки, и ко всем позициям в списке, возвращается значение "ПОЛ"
Вопрос можно считать закрытым. У меня вдруг появилась идея удалить столбец с нумерацией, в подправить ВПР. И заработало!
Наименование
Сочное Яблоко красное 121212
Сочное Яблоко зеленое 333222
Сочное яблоко грени 4443344
Сочная Груша зелёная 33333
Сочная Груша желтая 55555
и т.д. до бесконечности, как это бывает в прайсах. Искать надо по слову Яблоко\Груша, как это можно реализовать?
Заранее, огромное Вам спасибо.
С уважением, Джон.
Т.е. есть исходные данные которые содержат в разных строках одинаковый артикул.
Если использовать ВПР, то она ищет только первое значение, если VLOOKUP3, то массив, но только для одного артикула. А мне нужно в одну таблицу "собрать" данные соответствующие и одиночным артикулам и если их несколько.
Т.е. по сути сводная таблица, но с простой, двумерной структурой.
Составил пример в экселе, не могу его прикрепить.
Файл1 Закупка
партии, кг
партии, кг
Что необходимо
Не могли бы помочь. При использовании ВПР вопросов не возникло. Но при синхронизации моей таблицы и таблицы поставщика, в некоторых товарах , определяется как #Н/Д.
Это понятно так как не было найдено арт. в таблице поставщика. Но как сделать, если в таблице поставщика нет точного арт. номера оставлять то что уже было в ячейки, а не ставить #Н/Д.
Заранее спасибо!
Вкладка Исходник
Подскажите, пожалуйста, как именно можно при использовании функции ВПР подставить (рядом) для выбранного значения (названия, маркировки) соответствующую ему таблицу с данными?
Заранее спасибо!
Пример:
Имеется: "лист-1" с названиями моделей и их техническим описанием.
Необходимо: на "лист-2" указывать актуальные в данный момент модели (в определенных ячейках, позиции ячеек определены для возможности последующей печати), и чтобы рядом с ними автоматически выводились таблицы с их описаниями с "лист-1".
Далее лист с таблицами выбранных моделей отправляется на печать.
Подскажите как можно заставить ВПР работать со списком "Наименование"(из вашего примера), диапазон которого меняется, может быть короче или длиннее. Нужно составить универсальный шаблон, где будут прописаны все формулы, но расчёты будут основываться на выборке с помощью ВПР по списку "Наименование", но длинна списка может меняться бессистемно в широком диапазоне 10-10 000.
подписалась на ваш канал на ютубе))) периодически смотрю большое вам спасибо, за то что делитесь с нами такой ценной информацией)
Николай, у меня к вам вопрос по ВПР в "умных" таблицах, если искомое мной значение находится не в первом столбце таблице, как мне на нее сослаться?
=ВПР(I175;(ВРАБОТЕ[Столбец8]:ВРАБОТЕ[Столбец28]);13;0)
жирным выделила диапазон, т.е. в столбце8 у меня находится искомое значение, наверное можно это как-то проще написать?
Вопрос следующий. Возможно ли усовершенствовать данный прием следующим образом.
Необходимо, чтобы функция искала не точное заданное значение, а проверяла на примерное соответствие.
Задача.
Есть массив адресов. За каждым адресом закреплена обслуживающая район бригада.
Необходимо при формировании массива адресов из биллинга автоматически подставить напротив каждого адреса номер обслуживающей бригады.
К примеру:
Возможно ли, чтобы функция проверяла только улицу и дом, не обращая внимание на № квартиры (либо, в данном случае - значения после знака "-")?
Т.е. первоначально мы создаем массив, в котором указаны только улицы и дома. Но при проверке вводим полный адрес, при этом функция "не обращает" внимание на номер квартиры.
Есть две базы абонентов. Одна из них доделана полностью (#1). Адреса прописки совпадают в обеих базах. Как личные номера абонентов таблицы №1 заменить личными номерами таблицы №2. По признаку прописки
№1
Здравствуйте!
Все понятно, но уже 2 дня мучаюсь - не работает и все!
Нужно подставить цены из листа Фас ады
Пишет Допустимые для ввода значения ограничены другими пользователями.
Что это значит?
[img]C:\Users\Uliya\Desktop\ВПР\Image 2.jpg[/img]