Перейти к содержимому

Kutools для Office — один пакет. Пять инструментов. Выполняйте больше.

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

Author Amanda Li Last modified

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

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

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

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

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

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

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

  • "диапазон_условий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", найти "Выбрать", и нажать "Выбрать определенные ячейки" в выпадающем списке. Настройте параметры, как показано на скриншоте ниже:
    A screenshot showing the Kutools Select Specific Cells dialog configured to exclude cells equal to 0 in Excel
    √ Примечание: Kutools по умолчанию не подсчитывает пустые ячейки, поэтому в разделе "Указать тип" вы можете просто установить критерий как “"Не равно 0"”.

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

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

A screenshot demonstrating the use of COUNTIFS to count students based on multiple criteria in different ranges

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

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

🤖 Kutools AI Aide: Совершенно новый подход к анализу данных благодаря: Интеллектуальное выполнение |  Генерация кода  |  Создание пользовательских формул |  Анализ данных и построение диаграмм  |  Вызов Kutools Functions…
Популярные функции: Поиск, выделение или отметка дубликатов | Удалить пустые строки | Объединить столбцы или адреса без потери данных | Округлить ...
Супер ПОИСК: 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.

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