Поиск по двум критериям без макросов

Все мы любим функцию VLOOKUP (ВПР) и дорожим ей. Но в то же время бесимся, когда она не работает или когда ее не достаточно, чтобы решить нашу проблему. Сталкивались с тем, что нужно было организовать поиск сразу по нескольким параметрам и не смогли справиться с этой проблемой? Читайте данную статью.

Если вам не очень хорошо знакома функция VLOOKUP (ВПР) или она часто выдает ошибки, почитайте эту статью.

Если же данная функция вам не в новинку, давайте разберемся с тем, как искать по двум параметрам. Однако, перед тем как перейти непосредственно к вопросу, я хочу порекомендовать вам одну потрясающую книгу по Microsoft Excel — Формулы в Microsoft Excel 2010 Джона Уокенбаха. Книга описывает, как эффективно использовать различные функции и как эффективно сочетать их в формулах. В ней также описаны специальные формулы, такие как финансовые и формулы массива. Книга учит эффективно использовать сводные таблицы и диаграммы. Она определенно стоит того, чтобы иметь ее в своей библиотеке.

Итак, зачем использовать функция VLOOKUP (ВПР)

Позвольте привести пару примеров возможного применения. Скажем, вы учитываете материалы с помощью номенклатурных номеров. Их стоимость формируется отдельно для каждого места хранения. Вам необходимо подтянуть текущую стоимость материала из другой таблицы по двум критериям, соответственно, номенклатурному номеру и коду места хранения. Например, чтобы заполнить цену в заказе, просто по номеру и складу, с которого была сделана продажа.

Другой пример. У вас есть множество заказов на покупку (Purchase order), каждый из которых может содержать несколько услуг. Каждая из услуг нумеруется как отдельная линия основного заказа на покупку (Purchase order item). Необходимо «подтянуть» название линейки заказа на покупку из другой таблицы по этим двум номерам.

Как сделать

Основная идея использования функции VLOOKUP (ВПР) для поиска по двум критериям заключается в том, чтобы создать уникальный код, который будет

(1) включать в себя оба критерия, (2) который будет уникальным для любых двух уникальных критериев (не будет повторяться для двух разных наборов критериев), и (3) который будет находиться слева от базы, в которой мы будем искать.

Самый простой способ добиться этого — просто объединить два критерия (это можно сделать с помощью оператора & или функции CONCATENATE (СЦЕПИТЬ). Если оба критерия числовые и могут содержать разное количество символов длины, то лучше поставить между ними какой-нибудь символ, который точно не будет встречаться в коде, например, -, / или #.

Далее делайте поиск по этому совмещенному коду для поиска. Все просто — давайте разберем на примере.

Пример

Вернемся к примеру с материалами. Предположим, у нас есть следующая база данных и таблица, в которую необходимо перенести цену материала.

Добавим в самом начале базы (обязательно перед искомым параметром — текущей ценой) параметр, по которому мы будем производить поиск. Для удобства просто объединим две колонки: номенклатурный номер и складом. В результате этот параметр будет иметь вид 24141982СК1. Выглядеть это будет следующим образом.

Вы наверняка обратили внимание, что вместо конкретных ячеек в формулах я использую целые колонки, это может быть очень удобным: формулы выглядят коротко и лаконично, не надо возиться со знаками $, чтобы организовать нужный вид ссылок (зачем это делать подробнее читайте в этой статье), а также не надо будет переписывать все поисковые формулы, если вы добавили несколько строк. Подробнее об использовании диапазонов вместо ссылок на конкретные ячейки читайте здесь.

Теперь организуем поиск по этому критерию. Для этого в функции VLOOKUP (ВПР) повторим совмещение двух параметров, чтобы они имели точно такой же вид, что и в базе в поле Index.

Обратите внимание, что если в базе данных, по которой вы производите поиск есть два одинаковых кода с разными значениями цены, то функция всегда будет выбирать первое попавшееся значение. Если совпадения нет вообще, функция вернет ошибку #N/A (#Н/Д).

Если вам необходимо просуммировать все совпадения, воспользуйтесь функцией SUMIFS (СУММЕСЛИМН). О том, как ей пользоваться, читайте здесь.

Полезные материалы и курсы обучения по Microsoft Excel
Копирование материалов сайта разрешено только с согласия владельца с размещением ссылки на источник.