KutoolsforOffice — Одно решение — пять мощных инструментов.Меньше усилий — больше результата.

Функция СЧЁТЕСЛИМН в Excel — подсчёт ячеек по нескольким критериям — логика И и ИЛИ

АвторАманда ЛиДата изменения

Синтаксис функции СЧЁТЕСЛИМН в Excel

Подсчёт ячеек, соответствующих нескольким заданным критериям с помощью СЧЁТЕСЛИМН по логике И

Подсчёт ячеек, соответствующих нескольким заданным критериям по логике ИЛИ

Синтаксис функции СЧЁТЕСЛИМН в Excel

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

Синтаксис функции СЧЁТЕСЛИМН
=COUNTIFS(criteria_range1, criteria1, [criteria_range  iteria2]…)

Синтаксис функции СЧЁТЕСЛИМН включает следующие аргументы:

  • «диапазон_условия1» (обязательный) — первый диапазон ячеек, к которому применяется «критерий1».
  • «критерий1» (обязательный) — условие, по которому определяются ячейки для подсчёта в «диапазон_условия1».
  • «диапазон_условия2», «критерий2» (необязательные) — дополнительные пары диапазонов и соответствующих им критериев. Поддерживается до 127 таких пар.

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

Важные моменты
  • Количество строк и столбцов в каждом дополнительном диапазоне должно точно соответствовать количеству строк и столбцов в «диапазон_условия1». Диапазоны могут совпадать, быть соседними или несмежными.
  •  
  • В формуле СЧЁТЕСЛИМН в Excel звёздочка (*) соответствует любому количеству символов, а вопросительный знак (?)" — любому одиночному символу. Если нужно подсчитать ячейки, содержащие сам символ подстановки — звёздочку (*) или вопросительный знак (?), поставьте перед ним тильду (~). Например: ~*, ~?.
  • Кавычки («»)обязательны для текстовых критериев, тогда как числовые можно указывать и безкавычек («»). Например: 80, «>=80», «a», «india».
  • Функция СЧЁТЕСЛИМН не учитывает регистр, поэтому строки «India» и «INDIA» дают одинаковый результат.
  • Чтобы использовать ссылку на ячейку в функции СЧЁТЕСЛИМН с операторами сравнения, нужно заключить эти операторы в кавычки («») и добавить между ними амперсанд (&). Например: «>»&B2.
  • Порядокоператоров сравнения важен:=может использоваться только самостоятельно или после>или<.

Подсчёт ячеек, соответствующих нескольким заданным критериям с помощью СЧЁТЕСЛИМН по логике И

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

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


√ Примечание: пустые ячейки означают, что баллы по какой-то причине не были корректно записаны. Балл «0» означает, что студент не прошёл тест.

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

  • Можно использовать формулу СЧЁТЕСЛИМН:
    =COUNTIFS(C2:C8,">=60",C2:C8,"<=90")
    Результат — «5»
  •  
  • Или, если у вас установлен Kutools для Excel, перейдите на вкладку «Kutools», выберите «Выделить» и нажмите «Выбрать определённые ячейки» в раскрывающемся списке. Настройте параметры, как показано на скриншоте ниже:

    • Kutools для Excel— раскройте весь потенциал Excel с помощью более чем 300 незаменимых инструментов, которые ускоряют и упрощают вашу работу, а также воспользуйтесь возможностями ИИ для более разумной обработки данных и повышения продуктивности.Получить сейчас

☞ Другие способы решения задачи:Как подсчитать количество ячеек между двумя значениями или датами в Excel?

Чтобы подсчитать количество корректных баллов (ячейки не содержат 0 и не являются пустыми), приводим также два способа.

  • Можно использовать следующую формулу:
    =COUNTIFS(C2:D8,"<>0",C2:D8,"<>")
    Результат — «12»
    √ Примечание: в Excel «» означает «не равно». «0» — «не равно нулю», а «""» — «не пусто», то есть ячейка содержит данные.
  •  
  • Если у вас установлен «Kutools для Excel», перейдите на вкладку «Kutools», выберите «Выделить» и нажмите «Выбрать определенные ячейки» в раскрывающемся списке. Настройте параметры, как показано на скриншоте ниже:
    Снимок экрана, показывающий диалоговое окно Kutools «Выделить определенные ячейки», настроенное на исключение ячеек, равных 0, в Excel
    √ Примечание: Kutools по умолчанию не учитывает пустые ячейки, поэтому в разделе «Указать тип» достаточно выбрать критерий «Не равно 0».

    Kutools для Excel— раскройте весь потенциал Excel с помощью более чем 300 незаменимых инструментов, которые ускоряют и упрощают вашу работу, а также воспользуйтесь возможностями ИИ для более разумной обработки данных и повышения продуктивности.Получить сейчас

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

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

  • Чтобы подсчитать количество студентов, «страны которых оканчиваются на букву “a”», и «имена которых начинаются на “J”», используйте формулу:
    =COUNTIFS(A2:A8,"j*", B2:B8,"*a")
    Результат — «2»
    √ Примечание: символ подстановки «звёздочка (*)» соответствует любому количеству символов.
  •  
  • Чтобы подсчитать количество студентов, «баллы по английскому и математике которых одновременно выше (не равны) баллов Эдди (значения в ячейках C4 и D4)», используйте одну из следующих формул:
    =COUNTIFS(C2:C8,">60",D2:D8,">80")
    =COUNTIFS(C2:C8,">"&C4,D2:D8,">"&D4
    Результат — «3»
  •  
  • Чтобы подсчитать количество студентов, «являющихся из Индии (значение в B2)» и «имеющих оценку A (значение в E2)», используйте одну из следующих формул:
    =COUNTIFS(B2:B8,"india",E2:E8,"a")
    =COUNTIFS(B2:B8,B2,E2:E8,E2)
    Результат — «2»

Подсчёт ячеек, соответствующих нескольким заданным критериям по логике ИЛИ

Подсчёт ячеек, соответствующих нескольким заданным критериям по логике «ИЛИ», означает определение общего количества ячеек, удовлетворяющих хотя бы одному из условий — будь то критерий 1, критерий 2 и так далее. Ниже приведены два способа для выполнения такой задачи:

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

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

  • Чтобы подсчитать количество студентов, «являющихся из Индии (значение в B2)» или «Англии (значение в B3)», используйте одну из следующих формул:
    =COUNTIF(B2:B8,"india")+COUNTIF(B2:B8,"england")
    =COUNTIF(B2:B8,B2)+COUNTIF(B2:B8,B3)
    Результат — «4»
    √ Примечание: при наличии профессионального надстройки «Kutools» можно воспользоваться командой: «Kutools» > «Выделить» > «Выбрать определенные ячейки», выбрав «ИЛИ» в разделе «Указать тип».
  •  
  • Чтобы подсчитать количество студентов, «баллы по английскому или математике которых выше 80 (включая 80)», используйте одну из следующих формул:
    =COUNTIF(C2:C8,">=80")+COUNTIF(D2:D8,">=80")-COUNTIFS(C2:C8,">=80", D2:D8,">=80")
    Результат — «5»
    √ Примечание: здесь To minus COUNTIFS(C2:C8,«>=80», D2:D8,«>=80») используется для Удалить дубликаты, то есть для студентов, у которых баллы по английскому и математике одновременно выше 80.
СЧЁТЕСЛИМН с константой массива для подсчёта ячеек, соответствующих любому из заданных критериев

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

  • Чтобы подсчитать количество студентов, «являющихся из Индии или Англии», используйте формулу (один диапазон):
    =SUM(COUNTIF(B2:B8,{"india","england"}))
    Результат — «4»
  •  
  • Чтобы подсчитать количество студентов, «являющихся из Индии или Америки и имеющих оценки A, B или C», используйте формулу:
    =SUM(COUNTIFS(B2:B8,{"india","america"},E2:E8,{"a","b","c"}))
    Результат — «4»
    √ Примечание: во втором константном массиве обязательно используйте «точку с запятой (;)». Если критериев становится больше, рекомендуем перейти на формулу «СУММПРОИЗВ».

Лучшие инструменты повышения продуктивности в Office

🤖KUTOOLS AI Помощник: Преобразуйте Анализ данных с помощью:Интеллектуального выполнения   |  Генерации кода|  Создания пользовательские формулы  |  Анализа данных и построения диаграмм|  Вызова Расширенные функции…
Популярные функции:Поиск, выделение или Отметить дубликаты   |  Удалить пустые строки   |  Объединить столбцы или ячеек без потери данных   |  Округление без использования формул…
Супер ПОИСК:VLookup по нескольким критериям  |  VLookup по нескольким значениям  |   VLookup по нескольким листам   |   Распознавание нечетких соответствий…
Расширенный раскрывающийся список:Быстрое создание выпадающего списка   |  Зависимый выпадающий список   |  Выпадающий список с множественным выбором…
Управление столбцами:Добавление заданного количества столбцов|Перемещение столбцов|Переключение видимости скрытых столбцов|Сравнение диапазонов и столбцов…
Избранные функции:Сетка фокусировки   |  Просмотр дизайна   |Улучшенная строка формулы   | Управление рабочими книгами и листами   |  Библиотека ресурсов(автотекст)|  Выбор даты   |  Объединить листы  |  Шифрование/Расшифровать ячейки   | Отправка писем по списку   |  Супер фильтр   |   Специальный фильтр(Фильтр ячеек с жирным шрифтом/курсив/зачёркивание…) …
Лучшие наборы инструментов 15:12 Текстовыеинструменты(Добавить текст,Удалить определенные символы, …)|   50+Типыдиаграмм(Диаграмма Ганта, …)|   40+ Практические формулы(Рассчитать возраст на основе даты рождения, …)|   19 Инструментывставки(Вставить QR-код,Вставка изображения по пути, …)|   12 Инструментыпреобразования(Преобразовать в слова,Конвертация валют, …)|   7 Объединить и разделитьИнструменты(Расширенное объединение строк,Разделить ячейки, …)|… и многое другое
Используйте Kutools на предпочитаемом языке — поддержка английского, испанского, немецкого, французского, китайского и ещё 40+ языков!

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

Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу

  • Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
  • Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
  • Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!

Все надстройки Kutools — один установщик

Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.

ExcelWordOutlookTabsPowerPoint
  • Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
  • Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
  • Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
  • 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
  • Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек