Поиск по двум условиям в Excel без склейки столбцов

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

Подготовьте таблицу тарифа

Пусть Тарифы содержит столбцы Группа, Регион и Цена. В A2 выбрана группа, в B2 — регион. В каждой допустимой паре должна быть одна цена. Если тариф зависит ещё и от веса, двух условий недостаточно: сначала определите полный набор признаков.

Приведите значения к согласованным справочникам. «СПб» и «Санкт-Петербург» не станут равными только от появления второго условия. Для управляемого списка лучше использовать код региона и отображать название отдельно.

Постройте массив совпадений

Сравнение Тарифы[Группа]=A2 создаёт массив истины и лжи. Второе сравнение делает то же для региона. При умножении останется 1 только в строках, где совпали оба условия. ПОИСКПОЗ находит 1, а ИНДЕКС возвращает цену из этой позиции.

В Microsoft 365 такая формула работает с массивами обычным вводом. В старых версиях поведение ввода отличается; пример рассчитан на среду курса — настольный Excel Microsoft 365 на Windows. Сначала проверьте количество совпадений СЧЁТЕСЛИМН.

Контроль:
=СЧЁТЕСЛИМН(Тарифы[Группа];A2;Тарифы[Регион];B2)

Поиск при единственном совпадении:
=ИНДЕКС(Тарифы[Цена];ПОИСКПОЗ(1;(Тарифы[Группа]=A2)*(Тарифы[Регион]=B2);0))

Отделите отсутствие и неоднозначность

Простая формула ниже предполагает, что контрольная СЧЁТЕСЛИМН дала 1. Если получилось 0, пользователь должен увидеть, что тарифа нет. При значении больше 1 нужно исправить справочник или добавить ещё один критерий. Обёртка ЕСЛИОШИБКА с нулём скроет эти разные проблемы.

Не используйте приблизительное совпадение для произвольных категорий. Для интервалов веса или расстояния потребуется отдельное правило границ и порядок тарифной сетки. Точное совпадение пары значений решает другую задачу.

Проверьте формулу на маленькой сетке

Возьмите два региона и две группы с разными ценами. Каждая из четырёх пар должна вернуть своё значение. Затем добавьте повтор одной пары и убедитесь, что контроль совпадений сигнализирует конфликт. Удалите одну строку и проверьте состояние «тариф отсутствует».

Если вместо нескольких условий нужна сразу вся выборка, используйте ФИЛЬТР. ИНДЕКС с ПОИСКПОЗ возвращает одну позицию и не предназначен для списка всех подходящих тарифов. Выбор функции зависит от результата, который нужен следующему действию.

Для большого регулярного отчёта те же условия можно перенести в объединение Power Query. Тогда пара ключей задаётся двумя столбцами, а количество совпадений проверяется до раскрытия. Формула удобна для интерактивного расчёта, запрос — для подготовки множества строк.

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

Соберите обновляемый отчёт на практике

В программе курса есть поиск по двум условиям через ИНДЕКС и ПОИСКПОЗ. Затем вы примените проверку ключей в Power Query и в рабочих отчётах. Это даёт выбор между интерактивной формулой и подготовкой большого набора строк без ручной склейки признаков.

Откройте программу курса «Продвинутый Excel: Power Query, Power Pivot и автоматизация отчётов».

Начните с пробного урока о рабочей книге и первом запросе Power Query.

Для следующего этапа: объединение файлов из папки и обновление отчёта.