Функция COUNTIFS, как использовать функцию подсчета ячеек в соответствии с несколькими условиями в Excel

Функция СЧЁТЕСЛИ в Excel имеет множество практических применений. Вот что вам нужно знать об использовании функции СЧЁТЕСЛИ в Excel .

Microsoft Excel — известная в мире программа для работы с электронными таблицами, имеющая множество полезных функций. Появление программного обеспечения делает ввод данных и расчет ряда чисел проще, чем когда-либо. А при использовании Excel вам обязательно нужно знать функцию СЧЁТЕСЛИ .

Функция СЧЕТЕСЛИ в Excel используется для подсчета ячеек, удовлетворяющих множеству заданных условий. Функция СЧЕТЕСЛИ — одна из наиболее широко используемых статистических функций Excel в Excel. Это расширенная функция функции СЧЕТЕСЛИ , которая подсчитывает только ячейки с заданным условием. При работе с функцией СЧЕТЕСЛИ пользователи могут легко найти ячейки результата, удовлетворяющие условиям, заданным в запросе. Условиями могут быть числа, даты, текст или ячейки, содержащие данные. В статье ниже рассказывается, как использовать функцию СЧЕТЕСЛИ в Excel.

• Как сравнить данные в двух столбцах Excel
• Как использовать функцию DSUM для расчета сумм со сложными условиями в Excel
• Как использовать функцию СУММЕСЛИ для расчета условных сумм в Excel
• Использование функции ВПР и конкретные примеры

Инструкции по использованию функции СЧЕТЕСЛИ в Excel

Синтаксис функции COUNTIFS: =COUNTIFS(диапазон_критерия1, критерий1, [диапазон_критерия2, критерий2],…) .

Там:

• Criteria_range1 — это первый диапазон выбора, для которого требуется статистика, обязательное значение.
• Критерий1 — это условие, применяемое к критерию выбора_диапазон1, требуемое значение может быть ячейкой, выражением или текстом.
• [criteria_range2, критерии2] — это дополнительные пары выбора и условия, позволяющие использовать до 127 пар выбора и условия.

Обратите внимание при использовании функции СЧЁТЕСЛИ:

• Дополнительные выборки должны иметь то же количество строк и столбцов, что и область критерии_диапазон1, и могут быть отделены друг от друга.
• Если условие выбора относится к пустой ячейке, функция СЧЁТЕСЛИ автоматически считает значение равным 0.
• Можно использовать символы ?. Чтобы заменить определенный символ, знак * заменяет всю строку символов. Если вам нужно найти знак? или настоящий знак *, введите ~ перед этим символом.
• Обратите внимание, что функция СЧЁТЕСЛИ не чувствительна к регистру.
• Обычно текстовые значения нужно заключать в кавычки, а числа — нет. Однако если логический оператор включен в число, число и оператор должны быть заключены в кавычки, как показано ниже:

=COUNTIFS(A1:A10,100) // count equal to 100
=COUNTIFS(A1:A10,">50") // count greater than 50
=COUNTIFS(A1:A10,"jim") // count equal to "jim"

Примечание . Дополнительные условия должны подчиняться тем же правилам.

• При использовании значения из другой ячейки в условии ссылка на ячейку должна быть объединена с оператором при использовании. В приведенном ниже примере COUNTIFS будет подсчитывать значения в A1:A10, которые меньше значения в ячейке B1. Обратите внимание, что оператор «меньше» (текстовый) заключен в кавычки, но ссылка на ячейку не требуется:

=COUNTIFS(A1:A10,"<"&b1) count="" cells="" less="" than="">

Примечание . COUNTIFS — это одна из нескольких функций, которые делят условия на две части: диапазон + критерии. Это вызывает некоторые несоответствия с другими формулами и функциями.

• COUNTIFS может подсчитывать пустые или непустые ячейки. В приведенных ниже формулах подсчитываются пустые и непустые ячейки в диапазоне A1:A10:

=COUNTIFS(A1:A10,"<>") // not blank
=COUNTIFS(A1:A10,"") // blank

• Самый простой способ использовать COUNTIFS с датами — это обратиться к действительной дате в другой ячейке, которая имеет ссылку на ячейку. Например, чтобы подсчитать ячейки в A1:A10, содержащие дату, большую, чем дата в B1, вы можете использовать такую ​​формулу:

=COUNTIFS(A1:A10, ">"&B1) // count dates greater than A1

Пример использования функции СЧЁТЕСЛИ в Excel

1. Паспорт №1.

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

Пример 1: Рассчитайте количество сотрудников мужского пола со стажем 25 рабочих дней.

В ячейку, куда нам нужно ввести результат, вводим формулу =СЧЁТЕСЛИ(C2:C7,"Мужской",D2:D7,25) и нажимаем Enter.

Там:

• C2:C7 — это обязательная зона с 1-м номером, указывающая пол сотрудника.
• Юг — это состояние учетной зоны 1.
• C2:C7 — это вторая зона учета рабочего дня сотрудника.
• 25 – это состояние графа 2 региона.

В результате у нас есть 2 сотрудника, Нам, с 25 рабочими днями.

Пример 2. Подсчитайте количество сотрудников мужского пола, у которых выходной равен 0.

В поле ввода формулы введите =COUNTIFS(C2:C7,"Nam",E2:E7,0) и нажмите Enter.

Результаты показывают, что у 1 сотрудника мужского пола 0 выходных.

Пример 3. Подсчитайте количество сотрудников мужского пола, имеющих 25 рабочих дней и имеющих выходные дни менее или равные 2 дням.

Вводим формулу =COUNTIFS(C2:C7,"Male",D2:D7,25,E2:E7,"<> и нажимаем Enter.

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

2. Паспорт №2.

Пример 1: Рассчитайте общее количество тайваньских товаров стоимостью менее 200 000 донгов за единицу.

В поле ввода результата мы вводим формулу расчета =COUNTIFS(B2:B7,"*Тайвань",C2:C7,"<> и затем нажимаем Enter.

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

Пример 2: Рассчитайте общий объем тайваньской продукции с ценой за единицу от 100 000 до 150 000 донгов.

В поле ввода результата введите формулу =COUNTIFS(B2:B7,"*Тайвань",C2:C7,">100000",C2:C7,"<> и нажмите Enter.

В результате будет 2 тайваньских продукта по цене от 100 000 до 150 000 донгов.

Как использовать функцию СЧЕТЕСЛИ в сочетании с СУММ в Excel

Функция СЧЕТЕСЛИ по сути является более сложной версией функции СЧЕТЕСЛИ. Основное преимущество COUNTIFS перед COUNTIF заключается в том, что он поддерживает несколько условий и диапазонов.

Однако вы также можете определить диапазон и одно условие для функции СЧЕТЕСЛИ, как вы это сделали с функцией СЧЕТЕСЛИ.

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

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

Вы можете лучше понять функциональность функции СЧЁТЕСЛИ, изучив пример ниже.

Пример функции COUNITFS в Excel

Помимо цвета яблок, есть еще столбец с описанием их размера. Конечная цель в этом примере — подсчитать количество больших красных яблок.

1. Выберите ячейку, в которой вы хотите отобразить результаты. (В этом примере в статье будет отображаться большое количество красных яблок в ячейке E3).

2. Перейдите в строку формул и введите формулу ниже:

=COUNTIFS(A3:A11, "Red", B3:B11, "Big")

3. С помощью этой команды формула проверяет ячейки от A3 до A11 на наличие условия «Красный». Ячейки, прошедшие этот тест, затем снова проверяются в диапазоне от B3 до B11 на предмет «большого» состояния .

4. Нажмите кнопку Ввод.

5. Теперь Excel посчитает количество больших красных яблок.

Пример результатов функции Excel СЧЕТЕСЛИ

Обратите внимание, как формула подсчитывает ячейки, имеющие как красный , так и большой атрибуты. Формула берет ячейки от A3 до A11 и проверяет их, чтобы найти красный результат. Ячейки, соответствующие этому условию, затем снова проверяются на соответствие следующему условию во втором диапазоне, в данном случае большому условию.

В заключение отметим, что диапазоны и условия после первого диапазона и условия все больше сужают фильтр счета и не являются независимыми друг от друга. Итак, конечный результат рецепта — красные и большие яблоки. Вы можете посчитать количество красных или больших яблок, объединив функцию СЧЕТЕСЛИ с функцией СУММ .

1. Выберите ячейку, в которой вы хотите отобразить результаты формулы. (В этом примере в статье будет использоваться ячейка E6).

2. Введите формулу ниже:

=SUM(COUNTIF(A3:A11, "Red"), COUNTIF(B3:B11, "Big"))

3. Эта формула подсчитает ячейки, содержащие красные яблоки, затем количество ячеек, содержащих большие яблоки, и, наконец, просуммирует эти два числа.

4. Нажмите кнопку Ввод.

5. Excel теперь будет подсчитывать и отображать количество больших или красных яблок.

Объедините функцию СЧЁТЕСЛИ с функцией СУММ.

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

Как исправить распространенные ошибки при использовании функции СЧЁТЕСЛИ в Excel

COUNTIFS не работает при подсчете текстовых значений

При подсчете текстовых строк их необходимо заключать в кавычки. В противном случае функция COUNTIFS не сможет посчитать текстовую строку и вернет значение 0. В следующем примере изображения текстовая строка не заключена в кавычки. Следовательно, эта формула возвращает 0.

Чтобы исправить эту ошибку, вам просто нужно точно переписать формулу: =СЧЁТЕСЛИ(E5:E12, «Автомобиль»)

COUNTIFS не работает, поскольку ссылка на диапазон неверна.

При использовании более одного критерия в функции СЧЁТЕСЛИ диапазон ячеек для других критериев должен иметь одинаковое количество ячеек. В противном случае функция СЧЁТЕСЛИ работать не будет.

Допустим, в этом примере мы хотим посчитать продажи автомобилей в Остине. Введенная формула: =COUNTIFS(E5:E12,"Автомобиль",D5:D11,"Остин"). Внимательно взглянув на формулу, вы увидите, что первый диапазон критериев здесь — E5:E12, а второй диапазон критериев — D5:D11. Количество ячеек в диапазоне по этому критерию неодинаково.

Теперь нажмите Enter , формула вернет #ЗНАЧЕНИЕ! .

Перепишите формулу правильно следующим образом: =COUNTIFS(E5:E12,"Car",D5:D12,"Остин")

COUNTIFS не работает из-за ошибки в формуле.

Если формула вставлена ​​неправильно, функция СЧЕТЕСЛИ работать не будет. При использовании любого математического оператора, такого как больше (>), меньше (<), равно="" (=")" и "not="" равно=""> <> ), как оператор, так и числовой критерий должен быть введен внутри того же уравнения. Например, если вы хотите найти объем продаж, превышающий 100 000 долларов США, вставьте следующую формулу:

=COUNTIFS(F5:F12,">" 100000)

Здесь внутри уравнения подставлен только оператор, числового критерия нет.

Если вы нажмете Enter, в диалоговом окне сообщения Microsoft Excel отобразится: «Возникла проблема с этой формулой».

Решить проблему. Введите точную формулу:

=COUNTIFS(F5:F12,">100000")

Теперь мы ввели в скобках оператор и критерии. Теперь эта формула вернет необходимое количество.

Нажмите Ввод.

В результате вы получите сумму продаж, превышающую 100 000 долларов США.

Желаю вам успехов!