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

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

• Синтаксис функции СЧЁТЕСЛИМН
• Важные моменты для запоминания

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

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

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

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

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

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

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

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

• "диапазон_условий1" (обязательный) относится к первому диапазону ячеек, где применяется "условие1".
• "условие1" (обязательный) определяет условие, по которому будут подсчитываться ячейки в "диапазоне_условий1".
• "диапазон_условий2", "условие2" (необязательные) относятся к дополнительным диапазонам и их связанным условиям. Допускается до 127 пар диапазонов и условий.

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

Важные моменты для запоминания

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

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

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

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

√ Примечание: Пустые ячейки означают, что баллы каким-то образом не были правильно записаны. Оценка 0 означает, что студент не сдавал тест.

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

• Вы можете использовать формулу СЧЁТЕСЛИМН:

  =COUNTIFS(C2:C8,">=60",C2:C8,"<=90")

  Результат равен "5"
•  
• Или, если у вас установленоKutools for Excelвы можете перейти на вкладку "Kutools", найти "Выбрать", и нажать "Выбрать определенные ячейки" в выпадающем списке. Настройте параметры, как показано на скриншоте ниже:
  

Kutools для Excel - Усильте Excel более чем 300 необходимыми инструментами. Наслаждайтесь постоянно бесплатными функциями ИИ! Получите прямо сейчас

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

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

• Вы можете использовать следующую формулу:

  =COUNTIFS(C2:D8,"<>0",C2:D8,"<>")

  Результат равен "12"
  √ Примечание: "<>" в Excel означает не равно. ""<>0"" означает не равно 0; А ""<>"" означает не равно пустому, или можно сказать, не являются пустыми.
•  
• СKutools for Excelустановленным, вы также можете перейти на вкладку "Kutools", найти "Выбрать", и нажать "Выбрать определенные ячейки" в выпадающем списке. Настройте параметры, как показано на скриншоте ниже:
  
  √ Примечание: Kutools по умолчанию не подсчитывает пустые ячейки, поэтому в разделе "Указать тип" вы можете просто установить критерий как “"Не равно 0"”.

  Kutools для Excel - Усильте Excel более чем 300 необходимыми инструментами. Наслаждайтесь постоянно бесплатными функциями ИИ! Получите прямо сейчас

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

• Чтобы подсчитать количество студентов, которые "из стран, заканчивающихся буквой 'а'" и "их имена начинаются с '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, и ячеек, соответствующих…, другими словами, подсчет ячеек, соответствующих любому из (по крайней мере одному) критериев. Для этого мы перечислили 2 способа ниже:

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

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

• Чтобы подсчитать количество студентов, которые "из Индии (значение в B2)" или "Англии (значение в B3)", используйте одну из следующих формул:

  =COUNTIF(B2:B8,"india")+COUNTIF(B2:B8,"england")

  =COUNTIF(B2:B8,B2)+COUNTIF(B2:B8,B3)

  Результат равен "4"
  √ Примечание: Вы можете использовать "Kutools for Excel", если у вас установлен профессиональный надстройка: "Kutools" > "Выбрать" > "Выбрать определенные ячейки", выберите "ИЛИ" в разделе "Указать тип".
•  
• Чтобы подсчитать количество студентов с "баллом по английскому языку или математике выше 80 (включая 80)", используйте одну из следующих формул:

  =COUNTIF(C2:C8,">=80")+COUNTIF(D2:D8,">=80")-COUNTIFS(C2:C8,">=80", D2:D8,">=80")

  Результат равен "5"
  √ Примечание: Здесь вычитание СЧЁТЕСЛИМН(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"
  √ Примечание: Вам нужно использовать "точку с запятой (;)" во второй константе массива. Для случаев с большим количеством критериев рассмотрите использование формулы "СУММПРОИЗВ".