ВПР: данные из другой таблицы
В выгрузке заказов из CRM есть артикул товара, но нет его цены. Цены есть только в прайсе справа:
Вручную пришлось бы взять артикул, найти его в прайсе и переписать цену из той же строки. Сорок раз подряд, и одна опечатка испортит весь расчёт.
Эту работу делает функция ВПР. Она берёт значение, находит его в первом столбце другой таблицы и возвращает то, что стоит в той же строке. Значение, по которому ищут, называют ключом, а таблицу, где ищут, справочником. Здесь ключом служит артикул, а справочником прайс.
Четыре части формулы
Общая запись ВПР такая:
=ВПР(ключ;справочник;номер_столбца;0)- ключ
Что ищем. В выгрузке это артикул заказа. В подсказке Excel этот аргумент называется «искомое_значение».
- справочник
Где ищем: таблица без строки заголовков, от первого артикула до последней цены. Ключ ВПР ищет только в её левом столбце, поэтому справочник начинается со столбца с артикулами. В подсказке Excel это «таблица».
- номер_столбца
Из какого столбца справочника взять ответ. Считают от левого края справочника, а не листа.
- 0
Нужно точное совпадение. Если ключа в справочнике нет, в ячейке появится #Н/Д, «нет данных», и пропажу сразу видно. В подсказке Excel это «интервальный_просмотр», и пишут здесь почти всегда 0. Единицу ставят только для шкал, о них ниже.
Для артикула из A2 формула цены получается такой:
=ВПР(A2;$D$2:$F$6;3;0)
Читается она так: «найди A2 в первом столбце справочника D2:F6 и верни значение из его третьего столбца; подходит только точное совпадение». Знаки $ нужны, чтобы справочник не съехал при протягивании. О них ниже.
Номер столбца считают от справочника
Цена лежит в столбце F, шестом на листе, но в справочнике D:F этот столбец третий: номер считают от левого края справочника.
Если прайс переставить на другое место, в формуле поменяется диапазон, а номер останется прежним.
Закрепить справочник
Справочник закрепляют знаками $ у обоих концов: $D$2:$F$6. Без них при протягивании он сдвигается вниз вместе с формулой, как любой адрес, и верхние строки прайса выпадают из поиска.
На листе ниже формулу протянули без $, и часть цен превратилась в ошибки. Почините её:
Вся выгрузка за один раз
Вернёмся к сорока заказам. Впишите в B2 ту же формулу цены и дважды щёлкните по маркеру заполнения в правом нижнем углу ячейки: Excel сам протянет её до последнего заказа.
Сорок цен за одну формулу. Выгрузку следующей недели заполняют так же: формула в B2 и двойной щелчок по маркеру.
Шкала: когда в конце пишут 1
Оптовую скидку часто дают по шкале от суммы заказа:
- до 10 000 ₽ скидки нет;
- от 10 000 ₽ скидка 5%;
- от 50 000 ₽ скидка 10%;
- от 100 000 ₽ скидка 15%.
Заказа ровно на 23 500 ₽ в такой шкале нет, и с последним аргументом 0 ВПР выдаст #Н/Д. А нужна строка «от 10 000 ₽»: 23 500 больше 10 000, но меньше 50 000.
Для этого шкалу записывают справочником: в первом столбце нижние границы по возрастанию, начиная с 0, рядом скидка от этой границы. Последним аргументом ВПР здесь пишут 1:
=ВПР(B2;$E$2:$F$5;2;1)
С единицей в конце ВПР ищет приблизительно: берёт самую большую границу, которая не больше суммы заказа. Для 23 500 это 10 000, и формула вернёт 5%. Так же по шкале считают надбавку за стаж или бонус за объём продаж.
Границы в шкале всегда сортируют по возрастанию, от меньшей к большей: по такому порядку ВПР с единицей и находит нужную строку.
Если ответ левее ключа: ПРОСМОТРX
ВПР ищет ключ только в левом столбце справочника, поэтому ответ может стоять лишь правее. Бывает наоборот: в справочнике склада артикул слева, название справа, а найти нужно артикул по названию. Для этого есть функция ПРОСМОТРX. Столбец, где искать, и столбец, откуда брать ответ, ей дают отдельно:
=ПРОСМОТРX(A2;$F$2:$F$6;$E$2:$E$6;"нет в справочнике")
Ключ из A2 функция ищет в столбце названий F2:F6, а ответ берёт из той же строки столбца артикулов E2:E6. Номер столбца ей не нужен, а точное совпадение она ищет сама, без 0. Четвёртый аргумент необязательный: его текст появляется вместо #Н/Д, если ключа в справочнике нет. Для ВПР то же самое делает функция ЕСЛИОШИБКА из урока «Почему ВПР выдаёт #Н/Д».
ПРОСМОТРX есть в Excel 2021 и Microsoft 365, в старых версиях её нет.