Как найти значение по двум или более критериям в Excel?

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

Формула массива 1: поиск значения по двум или нескольким критериям в Excel

Общая структура этой формулы массива следующая:

{=INDEX(array,MATCH(1,(criteria1=lookup_array1)*(criteria2= lookup_array2)…*(criteria n= lookup_array n),0))}

Например, если вы хотите найти сумму продаж манго, проданного 9/3/2019, введите следующую формулу в пустую ячейку и нажмите Ctrl+Shift+Enter, чтобы подтвердить её как формулу массива:

=INDEX(F3:F22,MATCH(1,(J3=B3:B22)*(J4=C3:C22),0))

Примечание: в этом примере

• F3:F22 — это столбец «Сумма», из которого вы хотите получить значение.
• B3:B22 — это столбец «Дата»; C3:C22 — это столбец «Фрукт».
• J3 — дата, выбранная в качестве первого критерия; J4 — название фрукта, используемое в качестве второго критерия.

Добавить дополнительные критерии совсем несложно. Например, чтобы найти сумму продаж манго за 3 сентября 2019 года с весом 211, просто добавьте третье условие как в функцию ПОИСКПОЗ, так и в соответствующие массивы поиска — как показано ниже:

=INDEX(F3:F22,MATCH(1,(J3=B3:B22)*(J4=C3:C22)*(J5=E3:E22),0))

После ввода формулы нажмите Ctrl+Shift+Enter, чтобы подтвердить. Результатом станет сумма продаж, соответствующая всем указанным критериям.

Формула массива 2: поиск значения по двум или нескольким критериям в Excel с помощью конкатенации

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

=INDEX(array,MATCH(criteria1&, criteria2…&, criteriaN, lookup_array1&, lookup_array2…&, lookup_arrayN,0),0)

Например, чтобы получить сумму продаж фрукта с весом 2429/1/2019:

=INDEX(F3:F22,MATCH(J3&,J4,B3:B22&,C3:C22,0),0)

Примечание: здесь

• F3:F22 — столбец «Сумма»; B3:B22 — дата; E3:E22 — столбец «Вес».
• J3 — дата; J5 — значение веса для ваших критериев.

Для более чем двух критериев расширьте как критерии, так и массивы поиска в том же порядке:

=INDEX(F3:F22,MATCH(J3&,J4&,J5,B3:B22&,C3:C22&,E3:E22,0),0)

Как и раньше, нажмите Ctrl+Shift+Enter, чтобы получить правильный результат.

Оба метода формул массива позволяют найти первое значение, соответствующее всем критериям. Однако они требуют, чтобы диапазоны ячеек были одинакового размера, и не возвращают несколько совпадающих значений — вы получите только первое из них. Если совпадений нет, формула возвращает ошибку #Н/Д. Хотите видеть все совпадения? Обратите внимание на функцию ФИЛЬТР (подробнее см. ниже).

Некоторые практические советы и примечания:

• Если вы работаете с новыми версиями Excel (Microsoft 365, Excel 2021), вы можете упростить этот процесс с помощью формул динамических массивов и функции ФИЛЬТР.
• Чтобы избежать ошибок #Н/Д при отсутствии совпадений, оберните формулу в ЕСЛИОШИБКА, например: =IFERROR(INDEX(F3:F22,MATCH(1,(J3=B3:B22)*(J4=C3:C22),0)),«Not found»).
• Внимательно проверьте, что в ячейках с критериями поиска нет лишних пробелов или несовпадающих типов данных.
• Если ошибка появляется после нажатия только клавиши Enter, убедитесь, что вы используете комбинацию клавиш Ctrl+Shift+Enter, чтобы ввести формулу как формулу массива (в Excel 2019 и более ранних версиях).

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

1. Перейдите на вкладку Данные и выберите команду Расширенный в группе «Сортировка и фильтр», чтобы открыть диалоговое окно «Расширенный фильтр».

2. В диалоговом окне «Расширенный фильтр» выполните следующие настройки:
(1) В разделе Действие выберите пункт Скопировать в другое место.
(2) Для параметра Диапазон спискавыделите диапазон с данными для фильтрации ()A1:E21 в данном примере).
(3) Для параметра Диапазон условийукажите диапазон, содержащий условия фильтрации ()H1:J2 в данном случае). Убедитесь, что заголовки в этом диапазоне точно совпадают с заголовками в вашей таблице данных.
(4) В поле Копировать ввыберите первую ячейку, куда следует вставить отфильтрованные результаты ()H9 в данном случае).

3. Нажмите кнопку ОК, чтобы применить фильтрацию.

Строки, удовлетворяющие всем условиям в заданном диапазоне критериев, будут скопированы в указанную вами область назначения. Это особенно удобно для просмотра или формирования отчётов по записям, соответствующим сразу нескольким условиям фильтрации.

Некоторые советы и меры предосторожности:

• Убедитесь, что заголовки диапазона критериев полностью совпадают с заголовками основной таблицы данных — в противном случае фильтр может работать некорректно.
• Расширенный фильтр поддерживает условия «И» и «ИЛИ»: если критерии размещены в одной строке, применяется логика «И» (все условия должны выполняться), а если в разных строках — логика «ИЛИ» (достаточно выполнения любого из условий).
• Расширенный фильтр не обновляется автоматически при изменении данных — его нужно применять заново после обновления данных или критериев.
• Имейте в виду: пустые ячейки в диапазоне критериев могут интерпретироваться как «подходит любое значение» для соответствующего поля.

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

Если вы используете недавнюю версию Excel (Microsoft 365 или Excel 2021 и новее), функция ФИЛЬТР предлагает динамичный и интуитивно понятный способ извлечения всех значений, соответствующих нескольким критериям. Это решение настоятельно рекомендуется тем, кому нужно автоматическое обновление результатов при изменении данных или критериев — сложный ввод массивов не требуется.

1. В пустой ячейке введите формулу, аналогичную приведённой ниже:

=FILTER(F3:F22, (B3:B22=J3)*(C3:C22=J4))

В этой формуле:

• F3:F22 — это ваш столбец с суммами.
• B3:B22 — столбец с датами, который сопоставляется с датой в ячейке J3.
• C3:C22 — столбец «Фрукты», сопоставляемый с фруктом в ячейке J4.

Если Вы хотите добавить третье условие, например, совпадение столбца «Вес»(E3:E22)со значением в ячейке J5, расширьте формулу:

=FILTER(F3:F22, (B3:B22=J3)*(C3:C22=J4)*(E3:E22=J5))

После нажатия клавиши Enter Excel отобразит все суммы, соответствующие заданным критериям. Если совпадений не найдено, формула вернёт ошибку #ВЫЧИСЛ!, которую можно обработать с помощью функции ЕСЛИОШИБКА:

=IFERROR(FILTER(F3:F22, (B3:B22=J3)*(C3:C22=J4)*(E3:E22=J5)), "No match")

Преимущества:

• Результаты автоматически обновляются при изменении данных или критериев.
• Формулы проще поддерживать и расширять, чем старые формулы массива.
• Возвращает все совпадения, а не только первое найденное значение.

Ограничение: Доступно только в Microsoft 365, Excel 2021 или более поздних версиях. Не поддерживается в старых версиях.