Поиск значения по нескольким условиям в excel: Как найти значение с двумя или несколькими критериями в Excel?

Функция СЧЁТЕСЛИМН — Служба поддержки Майкрософт

Excel

Формулы и функции

Логические

Логические

Функция СЧЁТЕСЛИМН

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel Web App Excel 2010 Еще…Меньше

Функция СЧЁТЕСЛИМН применяет критерии к ячейкам в нескольких диапазонах и вычисляет количество соответствий всем критериям.

Это видео — часть учебного курса Усложненные функции ЕСЛИ.

Синтаксис

СЧЁТЕСЛИМН(диапазон_условия1;условие1;[диапазон_условия2;условие2];…)

Аргументы функции СЧЁТЕСЛИМН описаны ниже.

• org/ListItem»>

  
  Диапазон_условия1.    Обязательный аргумент. Первый диапазон, в котором необходимо проверить соответствие заданному условию.

• 
  Условие1.    Обязательный аргумент. Условие в форме числа, выражения, ссылки на ячейку или текста, которые определяют, какие ячейки требуется учитывать. Например, условие может быть выражено следующим образом: 32, «>32», B4, «яблоки» или «32».

• 
  Диапазон_условия2, условие2…    Необязательный аргумент. Дополнительные диапазоны и условия для них. Разрешается использовать до 127 пар диапазонов и условий.

Важно: Каждый дополнительный диапазон должен состоять из такого же количества строк и столбцов, что и аргумент диапазон_условия1. Эти диапазоны могут не находиться рядом друг с другом.

Замечания

• Каждое условие диапазона одновременно применяется к одной ячейке. Если все первые ячейки соответствуют требуемому условию, счет увеличивается на 1. Если все вторые ячейки соответствуют требуемому условию, счет еще раз увеличивается на 1, и это продолжается до тех пор, пока не будут проверены все ячейки.

• Если аргумент условия является ссылкой на пустую ячейку, то он интерпретируется функцией СЧЁТЕСЛИМН как значение 0.

• В условии можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому одиночному символу; звездочка — любой последовательности символов. Если нужно найти сам вопросительный знак или звездочку, поставьте перед ними знак тильды (~).

Пример 1

Скопируйте образец данных из следующих таблиц и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Пример 2

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См.

также

Для подсчета непустых ячеек используйте функцию СЧЁТЗ

Для подсчета ячеек на основании одного условия используйте функцию СЧЁТЕСЛИ

Функция СУММЕСЛИ суммирует только те значения, которые соответствуют одному условию

Функция СУММЕСЛИМН суммирует только те значения, которые соответствуют нескольким условиям

Функция ЕСЛИМН (Microsoft 365, Excel 2016 и более поздних)

Полные сведения о формулах в Excel

Рекомендации, позволяющие избежать появления неработающих формул

Обнаружение ошибок в формулах

Статистические функции

Функции Excel (по алфавиту)

Функции Excel (по категориям)

Excel.

Подсчет и суммирование ячеек, отвечающих критерию условного форматирования

Ранее я описал, как с помощью пользовательской функции найти сумму значений в ячейках, выделенных цветом. К сожалению, эта функция не работает, если ячейки раскрашены с помощью условного форматирования. Я обещал «доработать» функцию. Но за два года, прошедшие с публикации той заметки, я не смог ни самостоятельно, ни с помощью информации из Интернета написать удобоваримый код… (Дополнение от 29 марта 2017 г. Спустя еще пять лет, код мне всё же удалось написать; см. заключительную часть заметки). И вот недавно я наткнулся на идею, содержащуюся в книге Д.Холи, Р. Холи «Excel 2007. Трюки», которая позволяет обойтись вовсе без кода.

Пусть есть список чисел от 1 до 100, размещенных в диапазоне А1:А100 (рис. 1; см. также лист «СУММЕСЛИ» Excel-файла) [1]. На диапазон наложено условное форматирование, помечающее ячейки, содержащие числа больше 10 и меньше или равно 20.

Рис. 1. Диапазон чисел; условным форматированием выделены ячейки, содержащие значения от 10 до 20

Скачать заметку в формате Word, примеры в формате Excel

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

Чтобы сложить диапазон ячеек, отвечающих одному критерию, можно использовать функцию СУММЕСЛИ (рис. 2).

Рис. 2. Суммирование ячеек, отвечающих одному условию

Если у вас несколько условий, можно использовать функцию СУММЕСЛИМН (рис. 3).

Рис. 3. Суммирование ячеек, отвечающих нескольким условиям

Для подсчета числа ячеек, отвечающих одному критерию, можно использовать функцию СЧЁТЕСЛИ.

Для подсчета числа ячеек, отвечающих нескольким критериям, можно использовать функцию СЧЁТЕСЛИМН.

В Excel предусмотрена еще одна функция, которая позволяет указать несколько условий. Эта функция входит в набор функций баз данных Excel и называется БДСУММ. Чтобы проверить ее, используйте тот же набор чисел в диапазоне А2:А100 (рис. 4; см. также лист «БДСУММ» Excel-файла).

Рис. 4. Использование функций баз данных

Выделите ячейки C1:D2 и присвойте этому диапазону имя Критерий, введя его в поле имени слева от строки формул. Теперь выделите ячейку С1 и введите =$А$1, то есть ссылку на первую ячейку на листе, содержащую имя базы данных. Введите =$А$1 в ячейку D1 и вы получите две копии заголовка столбца А. Эти копии будут использоваться как заголовки для условий БДСУММ (C1:D2), который вы назвали Критерий. В ячейке С2 введите >10. В ячейке D2 введите <=20. В ячейке, где должен быть результат, введите следующую формулу:

=БДСУММ($А$1:$А$101,1,Критерий)

Для подсчета числа ячеек, отвечающих нескольким критериям, можно использовать функцию БСЧЁТ.

Дополнение от 29 марта 2017 г.

Читая книгу Джона Уокенбаха Excel 2010. Профессиональное программирование на VBA я узнал, что, начиная с версии Excel 2010 в VBA появилось новое свойство DisplayFormat (см., например, Range.DisplayFormat Property). Т.е., VBA может считывать формат, отображаемый на экране. При этом не важно, как он был получен, прямыми настройками пользователя, или с помощью условного форматирования. К сожалению, разработчики MS сделали так, что свойство DisplayFormat работает только в процедурах, вызываемых из VBA, а пользовательские функции на основе этого свойства выдают ошибку #ЗНАЧ! Тем не менее, получить сумму значений в диапазоне по ячейкам определенного цвета, можно с помощью процедуры (макроса, но не функции). Откройте Excel-файл с примером (содержит код VBA). Пройдите по меню Вид -> Макросы -> Макросы; в окне Макрос, выделите строку СумЦветУсл, и нажмите Выполнить. Запустится макрос, выберите диапазон суммирования и критерий. Ответ появится в окне.

Код процедуры

Sub СумЦветУсл()
Application.Volatile True
Dim SumColor As Double
Dim i As Range
Dim UserRange As Range
Dim CriterionRange As Range
SumColor = 0
‘ Запрос диапазона
Set UserRange = Application.InputBox( _
Prompt:=»Выберите диапазон суммирования», _
Title:=»Выбор диапазона», _
Default:=ActiveCell.Address, _
Type:=8)
‘ Запрос критерия
Set CriterionRange = Application.InputBox( _
Prompt:=»Выберите критерий суммирования», _
Title:=»Выбор критерия», _
Default:=ActiveCell.Address, _
Type:=8)
‘ Суммирование «правильных» ячеек
For Each i In UserRange
If i. DisplayFormat.Interior.Color = _
CriterionRange.DisplayFormat.Interior.Color Then
SumColor = SumColor + i
End If
Next
MsgBox SumColor
End Sub

Хотя пользовательская функция и дает ошибку, но в процессе ее вызова можно «подсмотреть» ответ. В ячейке начните набирать формулу =su…

Воспользуйтесь подсказкой, кликнув на нее, а затем нажмите знак функции в строке формул:

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

[1] Массив я создал с помощью функции =СЛУЧМЕЖДУ(1;100)

7 способов поиска значения на основе нескольких критериев или условий в Excel

Сценарий

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

Образец файла

Загрузить пример файла — Поиск на основе нескольких критериев

Решение

1. СУММПРОИЗВ

ФОРМУЛА :

=СУММПРОИЗВ((B3:B10=D12)*(C3:C10=D13)*(D3:D10))

ФОРМУЛА :

=СУММ((B3:B10=D12)*(C3:C10=D13)*(D3:D10))

Нажмите 0ENTER+SHIFT4 для подтверждения этой формулы . Если все сделано правильно, Excel автоматически поместит формулу в фигурные скобки {…}.

После размещения фигурных скобок формула в строке формул будет выглядеть так:

{=СУММ((B3:B10=D12)*(C3:C10=D13)*(D3:D10))}

3. СУММЕСЛИМН

.

ФОРМУЛА:

=СУММЕСЛИМН(D3:D10,B3:B10,D12,C3:C10,D13)

Эта формула не работает в Excel 2003 и более ранних версиях.

4. ИНДЕКС-СООТВЕТСТВИЕ

ФОРМУЛА:

=ИНДЕКС(D3:D10,ПОИСКПОЗ(1,(B3:B10=D12)*(C3:C10=D13),0))

Нажмите CTRL+SHIFT+ENTER , чтобы подтвердить эту формулу. Если все сделано правильно, Excel автоматически поместит формулу в фигурные скобки {…}.

После размещения фигурных скобок формула в строке формул будет выглядеть так:

{=ИНДЕКС(D3:D10,ПОИСКПОЗ(1,(B3:B10=D12)*(C3:C10=D13),0)) }

5. ИНДЕКС-ПОИСКПОЗ (Версия 2)

ФОРМУЛА :

=ИНДЕКС(D3:D10,ПОИСКПОЗ(1,ЕСЛИ(B3:B10=D12,ЕСЛИ(C3:C10=D13,1) ))0))

Нажмите CTRL+SHIFT+ENTER , чтобы подтвердить эту формулу. Если все сделано правильно, Excel автоматически поместит формулу в фигурные скобки {…}.

После размещения фигурных скобок формула в строке формул будет выглядеть следующим образом:

{=ИНДЕКС(D3:D10,ПОИСКПОЗ(1,ЕСЛИ(B3:B10=D12,ЕСЛИ(C3:C10=D13,1)) ,0))}

6. ПРОСМОТР

ФОРМУЛА :

=ПРОСМОТР(2,1/(B3:B10=D12)/(C3:C10=D13),(D3:D10))

7. ЕСЛИ СООТВЕТСТВИЕ НЕ НАЙДЕНО

ФОРМУЛА :

=ЕСЛИОШИБКА(ИНДЕКС(D3:D10,СООТВЕТСТВИЕ(1,(B3:B10=D12)*(C3:C10=D13),0)),»Нет совпадения»)

Эта формула возвращает «Нет соответствия», если значение не существует на основе условий.
Нажмите CTRL+SHIFT+ENTER , чтобы подтвердить эту формулу. Если все сделано правильно, Excel автоматически поместит формулу в фигурные скобки {…}.

После размещения фигурных скобок формула в строке формул будет выглядеть следующим образом:

{=ЕСЛИОШИБКА(ИНДЕКС(D3:D10,СООТВЕТСТВИЕ(1,(B3:B10=D12)*(C3:C10=D13),0)),»Нет совпадения»)}

Загрузить книгу

Об авторе:

Deepanshu основал ListenData с простой целью — сделать аналитику простой для понимания и использования. Он имеет более чем 10-летний опыт работы в области науки о данных. За время своего пребывания в должности он работал с глобальными клиентами в различных областях, таких как банковское дело, страхование, частный капитал, телекоммуникации и управление персоналом.

Обработка поиска по нескольким критериям в Excel

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

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

Несколько вариантов

Есть несколько возможных подходов, которые вы можете использовать, все они приведены в таблице ниже.

Как видите, выполнение ВПР с несколькими критериями может вообще не включать ВПР! Мы рассмотрим каждый из этих методов в приведенных ниже примерах.

Загрузите бесплатный файл для практики Excel

Выполните действия, описанные в статье, загрузив этот учебный файл.

Введите адрес электронной почты

Функция ФИЛЬТР

Когда дело доходит до наилучшего варианта реализации этой функции, бесспорным победителем в этом соревновании является функция ФИЛЬТР. FILTER был специально создан для сопоставления нескольких критериев в массиве и возврата всех результатов, соответствующих этим критериям. Лучше всего то, что он динамический, поэтому результаты обновляются при изменении критериев. Его способность выбрасывать несколько значений присуща функции и не требует каких-либо специальных маневров.

Вернуть несколько результатов

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

  =ФИЛЬТР(A2:C16, B2:B16="carolyn")  

Поиск нескольких значений

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

Стандартный синтаксис ФИЛЬТРА:

  =ФИЛЬТР(массив, включить, [if_empty])  

что переводится как

  =ФИЛЬТР(B2:C16,(B2:B16="бен")+(B2:B16="тони")+(B2:B16="Кертис"))  

Приведенная выше формула ищет любые экземпляры где столбец B соответствует «Бен», «Тони» или «Кэролин» и возвращает все результаты.

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

Стандартный синтаксис ФИЛЬТР:

=ФИЛЬТР(массив, включает , [if_empty])

применяется следующим образом:

=ФИЛЬТР(B2:C16, (A2:A16=2020)*(B2: B16=»Кэролин») )

Приведенная выше формула ищет случаи, когда значение в столбце A равно 2020 , И та же строка содержит значение «Carolyn» в столбце B.

Примечания о функции ФИЛЬТР:

• Функция ФИЛЬТР недоступен для версий, предшествующих Microsoft 365. Однако его можно использовать в Excel для Интернета.
• Если пустых ячеек недостаточно для возврата всех результатов, Excel вернет ошибку #SPILL! ошибка.
• Чтобы управлять способом отображения результатов, попробуйте объединить ФИЛЬТР с функцией СОРТИРОВКА.

Расширенный фильтр

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

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

Следующий фильтр извлекает записи, в которых год больше или равен 2020 для Бена или Тони.

Примечания:

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

Автофильтр

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

В приведенном ниже сценарии мы можем легко выбрать отображение всех продаж от определенного торгового представителя.

• Щелкните в любом месте набора данных. Перейдите на вкладку «Данные» и щелкните значок «Фильтр»

.

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

Это вернет все записи для этого сотрудника, как показано ниже.

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

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

Таким образом, если бы мы применили автоматические фильтры для поиска записей за год «2021», а затем еще один фильтр для продаж менее 200, мы не получили бы результатов, поскольку все продажи в 2021 г. превышали 200. Таким образом, фильтрация по нескольким категориям использует И логика (должна удовлетворять всем критериям).

Примечание:

XLOOKUP

XLOOKUP имеет определенные встроенные преимущества по сравнению с VLOOKUP. Одним из таких преимуществ является то, что он позволяет Excel искать несколько критериев без необходимости во вспомогательном столбце. Стандартный синтаксис XLOOKUP (без необязательных аргументов):

  =XLOOKUP(искомое_значение, искомое_массив, возвращаемое_массив)  

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

  =XLOOKUP(F2&F3, A2:A16&B2:B16, C2:C16)  

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

Возвращает расширенный диапазон

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

=XLOOKUP(F2&F3,A2:A16&B2:B16, A2:C16 )

Примечания:

• Функция XLOOKUP недоступна для версий, предшествующих Microsoft 365. Однако ее можно использовать в Excel для Интернета. .
• Этот метод можно использовать для объединения любого количества критериев.
• XLOOKUP лучше всего подходит для уникальных значений в наборе данных. Он может сбрасывать диапазон ячеек, но не может возвращать несколько совпадений.

ВПР со вспомогательным столбцом

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

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

=ВПР(G2&G3,C2:D16,2,ЛОЖЬ)

Примечания:

• ВПР ищет комбинацию и использует первое совпадающее значение для возврата результата.
• Этот метод можно использовать для объединения любого количества критериев.
• Будьте осторожны при вставке столбцов в исходные данные, так как это может повлиять на зависимые формулы в другом месте.

ПОИСКПОЗ С ВПР

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

Если ваши данные имеют двумерную матричную структуру и вы хотите выполнить ВПР по нескольким критериям, вы можете немного запутаться.

Поскольку функция ВПР предназначена для вертикального поиска, нам просто нужно вставить формулу ПОИСКПОЗ вместо аргумента col_index_num . Это позволит найти искомое значение на горизонтальной оси и вернуть его номер позиции (или столбца).

• lookup_value для функции ВПР должно быть для значений, перечисленных вертикально. В данном случае это будет ячейка G1.
• table_array будет полным набором данных, A1:D6.
• Для аргумента column_index_num вложите функцию ПОИСКПОЗ, чтобы номер столбца обновлялся динамически в зависимости от значения, введенного в G2.

  =ВПР(G1,A1:D6,ПОИСКПОЗ(G2,A1:D1,0),ЛОЖЬ)  

Примечания:

• размерный массив.
• Этот метод используется максимум для двух критериев.
• ПОИСКПОЗ ВПР использует первое совпадающее значение для возврата результата.

INDEX MATCH MATCH

Этот метод почти идентичен вышеописанному методу ПОИСКПОЗ ВПР. Обычная комбинация ПОИСКПОЗ ИНДЕКС использует синтаксис

=ИНДЕКС(массив,(ПОИСКПОЗ(искомое_значение, диапазон_заголовков * ,0),номер_столбца)

или

=ИНДЕКС(массив,значение_строки_ , column_array * ,0))

* имя аргумента только для пояснительных целей

В каждом случае формула предполагает, что имеется только одно искомое значение и что номер строки или столбца неизвестен , но не оба.  

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

Решение состоит в том, чтобы просто добавить в последовательность вторую формулу ПОИСКПОЗ.

• Для аргумента row_num вставьте формулу ПОИСКПОЗ, используя значение, которое будет лежать на вертикальной оси.
• Для столбца номер_столбца , вставьте формулу ПОИСКПОЗ, используя значение, которое попадет на горизонтальную ось.

  =ИНДЕКС(A1:D6,ПОИСКПОЗ(G1,A1:A6,0),ПОИСКПОЗ(G2,A1:D1,0))  

Примечания:

• ПОИСКПОЗ ИНДЕКС использует первое совпадающее значение для возврата результат.
• Этот метод можно использовать максимум для двух критериев.

Загрузите бесплатный учебный файл Excel

Выполните действия, описанные в статье, загрузив этот учебный файл.