Поиск по ключу

В продаже записаны только код товара и число упаковок, а категория и масса лежат на другом листе — в справочнике. Чтобы посчитать продажи напитков, к каждой продаже нужно «подтянуть» категорию. ВПР находит по ключу строку справочника и возвращает из неё нужное поле.

Разберём ключ и справочник, функции ВПР, ИНДЕКС и ПОИСКПОЗ и ошибку #Н/Д.

Свойства товара хранят один раз в справочнике, а в операциях — только ключ. Если в каждой продаже повторять название, массу и категорию, файл разрастётся, а исправлять придётся в сотне мест. Поэтому в операции пишут только код товара и сведения о самой продаже.

Код, позволяющий однозначно найти запись справочника, называется ключом. Например, код товара 101 связывает строку продажи со строкой «Чай» в таблице товаров. Номер строки на листе ключом не является: после сортировки положение записи может измениться.

Лист «Товары» содержит четыре записи, лист «Магазины» — коды 1, 2, 3: магазины 1 и 3 находятся в Северном районе, магазин 2 — в Южном. Переключай листы под сеткой:

Связь имеет вид «много операций могут относиться к одному товару». Поэтому повторение кода в операциях нормально, а в справочнике товаров код должен обозначать одну определённую запись. Если в справочнике один код связан с разными свойствами, обычный поиск первой строки не разрешит противоречие.

ВПР(ключ; справочник; номер столбца; 0) ищет ключ в первом столбце справочника и берёт значение из указанного столбца той же строки. Название значит «вертикальный поиск».

B2 — код товара из операции. Товары! указывает другой лист. $A$2:$D$5 — закреплённый диапазон справочника. 3 означает третий столбец внутри этого диапазона, то есть категорию. 0 задаёт точное совпадение; вместо него можно использовать ЛОЖЬ.

Вот что ВПР делает, когда в B2 код 103:

Выбери E3: формула ищет код 103, и вкладка «Товары» отмечена цветом — справочник лежит там. Перейди на неё: рамка стоит на всём диапазоне A2:D5. Поменяй код в B3 на 102 — категория станет «Напитки».

Диапазон справочника удобнее выделить мышью на другом листе — Excel сам впишет ссылку с именем листа. Четвёртый аргумент 0 не пропускай: без него поиск приближённый.

ПОИСКПОЗ находит номер позиции ключа, ИНДЕКС берёт элемент по номеру; вместе они ищут в любую сторону. ПОИСКПОЗ(B2;Товары!$A$2:$A$5;0) возвращает позицию кода внутри диапазона. Если найден третий элемент, результат равен 3, даже если на листе он находится в строке 4. Это относительная позиция в указанном списке.

ИНДЕКС(Товары!$C$2:$C$5;3) возвращает третий элемент столбца категорий. Объединив операции, получаем:

Внутренняя ПОИСКПОЗ вычисляется первой и отдаёт ИНДЕКС номер. При коде 103 в B2:

Во второй строке поиск идёт по названию, а возвращается код — столбец левее столбца поиска. ВПР так не умеет.

ВПР требует, чтобы ключ был первым столбцом диапазона, а пара ИНДЕКС и ПОИСКПОЗ ищет в любую сторону.

#Н/Д — ключа нет в справочнике; это повод проверить данные, а не заменить ошибку нулём. Проверь сам код, его тип, пробелы, границы диапазона и опечатки. Не заменяй ошибку нулём: неизвестная масса — не масса ноль, а отсутствие цены — не бесплатный товар.

Кода 105 в справочнике нет. А в A3 код записан текстом '102: на вид тот же, но число 102 и текст «102» для поиска разные значения.

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

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

Упаковок 17, а операций 3: обе величины считаются по одной выборке, но отвечают на разные вопросы.