ВПР: связь листов

В задаче темы нужны продажи магазинов Северного района и товары фабрики А, а в строке операции нет ни района, ни поставщика — только коды.

Поэтому к каждой операции дописывают сведения её магазина и товара — поиском по коду. Вручную это два поиска. У операции 2 ID магазина M1, артикул 101: в листе «Магазин» у M1 район Северный, в листе «Товар» у 101 поставщик «Фабрика А» и 0,5 кг в упаковке.

Для двенадцати операций это ещё можно сделать руками, для сотен — нет. Тот же поиск выполняет формула.

ВПР находит код в первом столбце справочника и берёт значение из нужного столбца найденной строки. На листе «Движение» напишем в H1 заголовок «Район», а в H2:

Прочитаем её по частям:

• C2 — ID магазина этой операции, что ищем;
• 'Магазин'!$A$2:$C$4 — где ищем: справочник на листе «Магазин»; знак ! отделяет лист от адреса;
• 2 — какой столбец диапазона вернуть: второй, район;
• 0 — искать точное совпадение кода.

Номер столбца считают внутри диапазона: в A:C столбец A первый, C — третий. Последний аргумент не пропускай: без него ВПР ищет приблизительно и может вернуть чужую строку.

Справочник закрепляют знаками $, а код операции оставляют без них. Формулу из H2 копируют вниз до последней операции. При этом C2 становится C3, C4 — каждая строка берёт свой ID. А справочник должен остаться тем же, поэтому он записан как $A$2:$C$4: без знаков $ он сполз бы вниз вместе с формулой.

После копирования проверь формулу в одной из последних строк: она должна брать ID своей операции, а справочник — прежний.

Остальные сведения дописывают той же формулой с другим ключом и номером столбца. Сведения о товаре ищут по артикулу D2 в листе «Товар», где поставщик — шестой столбец, количество в упаковке — пятый:

Три формулы, скопированные вниз, дают каждой операции район, поставщика и массу упаковки. Выбери ячейку в столбцах H–J — рамки покажут код операции и справочник на другом листе:

В файле экзамена формулы те же, меняются только диапазоны: например, справочник из 16 магазинов — $A$2:$C$17.

• ВПРчто ищем; где ищем; какой столбец вернуть; 0
• Первый столбецдиапазон начинается со столбца кода
• Номер столбцасчитают внутри диапазона
• $справочник закреплён, код операции — нет
• #Н/Дкод не найден — ищи причину