ВПР по нескольким условиям: ключ-склейка, массив логических значений и ФИЛЬТР

Обычная подстановка видит только одно искомое значение и отдаёт первую подходящую строку. По справке Microsoft искомое значение должно находиться в первом столбце диапазона^[2], поэтому пару «менеджер + месяц» функция без подготовки не распознает.

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

Ключевые факты: ВПР по нескольким условиям

• Составной ключ превращает ВПР по нескольким условиям в обычный поиск: два и более поля склеиваются в одно значение.
• Логические значения в формуле массива считаются числами: ИСТИНА*1=1, а ЛОЖЬ*1=0^[3].
• Функции И и ИЛИ нельзя использовать в формулах массива непосредственно — их заменяют умножением и сложением^[3].
• Справка Google называет вспомогательный столбец со склейкой через & обходным путём для нескольких критериев^[1].
• ФИЛЬТР в отличие от ВПР возвращает все строки, подходящие под условия, а не первую^[5].

Что такое составной ключ и почему одного условия мало

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

Формула =ВПР(F2;A2:C7;3;0) при «Петрова» в F2 вернёт 98 — январскую сумму, первую строку Петровой. Второе условие она не видит: по справке Google функция возвращает значение первого найденного совпадения^[1]. Ошибки при этом нет, и в отчёт уходит чужая цифра. ВПР по нескольким условиям без подготовки данных не работает — нужен один из четырёх способов из таблицы.

Теперь к первому способу — ключу во вспомогательном столбце, который поймёт и старая версия Excel.

Вспомогательный столбец по шагам: ВПР по нескольким условиям в любой версии

ВПР по нескольким условиям через вспомогательный столбец обходится без новых функций: условия склеиваются в один ключ, и функция ищет его как обычное значение. Справка Google описывает именно такой обходной путь^[1].

1. Вставьте пустой столбец A перед таблицей — данные сдвинутся в B:D.
2. В A2 введите =B2&"_"&C2 и протяните до A7.
3. Запишите менеджера в G2, а месяц — в H2.
4. В I2 наберите =ВПР(G2&"_"&H2;$A$2:$D$7;4;0) и нажмите Enter — формула вернёт 110.
5. Проверьте пару, которой нет в таблице, например «Сидоров» и «Март»: исправная формула покажет #Н/Д.

Столбец-ключ обязан стоять левее ответа, потому что ВПР ищет только в первом столбце диапазона^[2]. Для трёх условий ключ собирают так же: =B2&"_"&C2&"_"&E2.

Разделитель нужен не для красоты. Без него пары «Иван» + «ова» и «Ивано» + «ва» склеятся в одинаковое «Иванова», и формула найдёт чужую строку. Подойдёт любой символ, которого нет в данных. Искомое значение и ключ должны собираться одинаково: запись =ВПР(G2&H2;$A$2:$D$7;4;0) вернёт #Н/Д, потому что ищет «ПетроваФевраль», а в столбце лежит «Петрова_Февраль».

Если сочетание встречается дважды, ВПР вернёт первую строку без предупреждения^[1]. Повторы ловит формула =СЧЁТЕСЛИ($A$2:$A$7;A2) рядом с ключом: всё, что больше единицы, нужно разобрать. Соберите ключ-склейку в тренажёре: урок меняет данные и проверяет, что формула находит нужную пару.

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

Формула массива: ИНДЕКС и ПОИСКПОЗ или ПРОСМОТРX с произведением условий

Условия в формуле массива перемножаются: каждое сравнение даёт массив ИСТИНА и ЛОЖЬ, а произведение оставляет единицу только там, где выполнены все условия. По справке Microsoft ИСТИНА*1=1, а ЛОЖЬ*1=0, поэтому логические значения считаются как числа^[3].

Формула массива заменяет ВПР по нескольким условиям без столбца-ключа и работает на исходной таблице A:C. Порядок вычисления такой:

• (A2:A7=F2) даёт массив ИСТИНА и ЛОЖЬ: совпал ли менеджер в каждой строке;
• (B2:B7=G2) даёт такой же массив для месяца;
• произведение превращает пары в единицы и нули, и единица остаётся только в строке Петровой за февраль;
• ПОИСКПОЗ ищет первую единицу, а ИНДЕКС берёт сумму из этой строки.

Функции И и ИЛИ здесь не помогут: справка Microsoft прямо пишет, что в формулах массива их нельзя использовать непосредственно^[3]. Запись =ИНДЕКС(C2:C7;ПОИСКПОЗ(1;И(A2:A7=F2;B2:B7=G2);0)) вернёт #Н/Д, потому что И сворачивает весь массив в одно значение.

В Microsoft 365 формулу подтверждают клавишей Enter, а устаревшие формулы массива требовали сочетания Ctrl+Shift+Enter^[3]. Все диапазоны в произведении должны быть одного размера: сравнивать можно только диапазоны одинакового размера и размерности^[3]. Синтаксис связки разобран на странице о функциях ИНДЕКС и ПОИСКПОЗ по отдельности, аргументы ПРОСМОТРX — в разборе шести аргументов ПРОСМОТРX.

Именно поэтому для задач, где подходящих строк несколько, нужен отдельный инструмент — функция ФИЛЬТР.

Пример с ФИЛЬТР: все строки по двум условиям

Поиск по нескольким условиям функцией ФИЛЬТР возвращает все подходящие строки, а не первую: признаки «и» перемножаются, признаки «или» складываются^[5]. Ответ разливается по соседним ячейкам, сколько бы строк ни нашлось.

Каждое сравнение в скобках — отдельный массив той же высоты, что и таблица. Третий и четвёртый признак добавляют ещё одним множителем, например *(C2:C7>100), а смешанную логику записывают со скобками: ((усл1)+(усл2))*(усл3).

Ячейки под формулой должны быть свободны: если что-то блокирует выходной диапазон, Excel покажет #ПЕРЕНОС!^[6] Если не подошла ни одна запись, без третьего аргумента функция вернёт #CALC!, потому что Excel пока не поддерживает пустые массивы^[5]. Подпись на этот случай задают третьим аргументом: ;"нет строк".

По справке Microsoft ФИЛЬТР работает в Microsoft 365, Excel 2024 и Excel 2021, включая версии для Mac, а также на iPad, iPhone и Android^[5]. В Google Таблицах FILTER принимает каждое сравнение отдельным аргументом, и оно должно совпадать по длине с диапазоном^[7]. Подробности о третьем аргументе и сортировке результата — на странице о функции ФИЛЬТР и её аргументах.

Остаётся понять, когда от поиска по сочетанию полей лучше отказаться.

Когда поиск по составному ключу не подойдёт

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

• Если сочетание полей повторяется, ВПР и ИНДЕКС с ПОИСКПОЗ вернут только первую строку^[1] — сначала посчитайте повторы ключа или выводите все строки через ФИЛЬТР.
• При числах, сохранённых как текст, в одном из проверяемых столбцов сравнение не совпадёт: по справке Microsoft числа и даты в первом столбце не должны храниться как текст^[2].
• Если файл откроют в Excel 2016 или Excel 2019, формулы с ПРОСМОТРX не сработают^[4] — оставьте вспомогательный столбец.
• Когда сравниваемые диапазоны разной высоты, произведение массивов не сложится: сравнивать можно только диапазоны одинакового размера^[3].

В Google Таблицах ВПР тоже не принимает больше одного ключа, а справка Google предлагает тот же вспомогательный столбец со склейкой через &^[1]. Поэтому ключ-склейка остаётся самым переносимым способом: формула одинаково читается в Excel и в браузере. Соседние приёмы подстановки собраны в каталоге формул Excel для поиска данных.