Примеры функции «СЧЁТЕСЛИ» с двумя условиями в Excel
Содержание
- Синтаксис функции COUNTIFS
- Важные особенности функции COUNTIFS
- Примеры использования функций COUNTIF и COUNTIFS с несколькими условиями
- Подсчет ячеек с логикой ИЛИ
- Как считать числа между двумя заданными значениями
- Использование ссылок на ячейки в формуле COUNTIFS
- Как использовать подстановочные знаки в COUNTIFS
- COUNTIFS и COUNTIF с несколькими критериями для дат
- Заключение
Данное руководство покажет вам, как применять функции COUNTIFS и COUNTIF для подсчета ячеек в зависимости от нескольких критериев, с учетом логики И и логики ИЛИ.
Здесь приведены примеры работы с различными типами данных, такими как числа, даты, текст, символы подстановки и непустые ячейки.
Функции COUNTIF и COUNTIFS часто путают между собой, поскольку они схожи по назначению, но имеют ключевые отличия. COUNTIF позволяет считать ячейки по одному критерию в одном диапазоне, в то время как COUNTIFS может работать с несколькими критериями как для одного диапазона, так и для разных.
Синтаксис функции COUNTIFS
Формула функции COUNTIFS выглядит следующим образом:
COUNTIFS(criteria_range1; criteria1; [criteria_range2; criteria2]…)
- criteria_range1 — это диапазон, к которому применяется первое условие.
- criteria1 — это условие, которое может быть выражено как число, текст, ссылка на ячейку или логическое выражение (например, ">10", "Яблоки").
- [criteria_range2, criteria2] — дополнительные диапазоны и их условия (до 127 пар "диапазон-критерий").
Microsoft Excel отображает аргументы функции в процессе ввода, что помогает избежать ошибок.
Важные особенности функции COUNTIFS
- Функция может подсчитывать ячейки на основе одного или нескольких условий. В случае нескольких условий будут подсчитаны только те ячейки, которые удовлетворяют всем указанным критериям.
- Все диапазоны должны иметь одинаковые размеры по числу строк и столбцов.
- Поддерживаются как смежные, так и несмежные диапазоны.
- Если критерий ссылается на пустую ячейку, Excel считает это значение равным нулю.
- Символы подстановки, такие как * и ?, могут использоваться для поиска данных по шаблону.
Примеры использования функций COUNTIF и COUNTIFS с несколькими условиями
Пример 1: COUNTIFS с несколькими критериями (логика И)
Предположим, что у вас есть таблица товаров, и вы хотите узнать количество товаров, которые есть на складе (в столбце B значения больше 0), но при этом они еще не проданы (в столбце C значения равны 0).
Формула для решения этой задачи будет следующей:
=COUNTIFS(B2:B7;">0"; C2:C7;"=0")
Результат этой формулы — 2, что означает, что на складе имеются "Вишня" и "Лимоны".
Пример 2: COUNTIFS с одинаковыми критериями
Если требуется подсчитать количество товаров, для которых значения в двух столбцах равны 0, используйте следующую формулу:
=COUNTIFS($B$2:$B$7,"=0"; $C$2:$C$7;"=0")
Результат — 1, так как только "Виноград" имеет значение 0 в обоих столбцах.
Попытка использовать более простую формулу вида COUNTIFS(B2:C7;"=0")
вернет общее количество ячеек, содержащих ноль (в данном примере это будет 4).
Подсчет ячеек с логикой ИЛИ
Чтобы подсчитать ячейки, соответствующие хотя бы одному условию, нужно использовать подход, основанный на сложении нескольких формул COUNTIF или на массиве значений в формуле COUNTIFS.
Пример 1: Сложение нескольких формул COUNTIF
Предположим, что вы хотите подсчитать заказы со статусом "Отменен" и "В ожидании". Для этого можно сложить две отдельные формулы COUNTIF:
=COUNTIF($C$2:$C$11;"Отменен") + COUNTIF($C$2:$C$11;,"В ожидании")
Если требуется добавить дополнительные условия, используйте COUNTIFS. Например, для подсчета заказов "Яблок" со статусами "Отменен" и "В ожидании":
=COUNTIFS($A$2:$A$11; "Яблоки"; $C$2:$C$11;Отменен") + COUNTIFS($A$2:$A$11; "Яблоки"; $C$2:$C$11;"В ожидании")
Пример 2: SUM COUNTIFS с массивом критериев
Этот способ эффективен, когда требуется подсчитать ячейки по множеству критериев в одном столбце. Для этого можно использовать массив и функцию SUM:
=SUM(COUNTIFS($C$2:$C$11; {"Отменен"; "В ожидании";"В пути"}))
Чтобы подсчитать количество заказов "Яблок" со статусами "Отменен", "В ожидании" или "В пути", формула будет следующей:
=SUM(COUNTIFS($A$2:$A$11;"Яблоки";$C$2:$C$11,{"Отменен";"В ожидании";"В пути"}))
Как считать числа между двумя заданными значениями
Формулы COUNTIFS для числовых значений обычно делятся на две категории: на основе нескольких условий и для чисел между двумя указанными значениями.
Это можно реализовать двумя способами: используя функцию COUNTIFS либо путем вычитания одной формулы COUNTIF из другой.
Формула 1. COUNTIFS для подсчета значений между двумя числами
Чтобы узнать, сколько чисел находятся в диапазоне от 5 до 10 (исключая сами 5 и 10) в диапазоне ячеек от C2 до C10, можно воспользоваться следующей формулой:
=COUNTIFS(C2:C10;">5";C2:C10;"<10")
Для того чтобы включить в расчет числа 5 и 10, используйте операторы "больше или равно" и "меньше или равно":
=COUNTIFS(C2:C10;">=5";C2:C10;"<=10")
Формула 2. COUNTIF для подсчета значений между X и Y
Другим способом подсчета является вычитание одной формулы COUNTIF из другой. В первом случае функция подсчитает количество значений, которые превышают нижнюю границу (например, 5), а вторая — количество значений, которые превышают верхнюю границу (в данном случае 10).
Разница между результатами и даст требуемое количество значений:
=COUNTIF(C2:C10;">5")-COUNTIF(C2:C10;">=10")
Эта формула вернет результат, показывающий, сколько чисел больше 5 и меньше 10 содержится в диапазоне C2:C10, аналогично предыдущей формуле.
Для включения в расчет чисел 5 и 10 можно использовать следующую формулу:
=COUNTIF(C2:C10;">=5")-COUNTIF(C2:C10;">10")
Эта формула посчитает количество значений, которые лежат между 5 и 10 в диапазоне C2:C10, включая сами числа 5 и 10.
Использование ссылок на ячейки в формуле COUNTIFS
Когда вы используете логические операторы, такие как ">", "<", ">=" или "<=", в комбинации с ссылками на ячейки в формуле COUNTIFS, важно помнить, что логический оператор должен быть заключен в кавычки, а перед ссылкой на ячейку необходимо добавить амперсанд (&) для преобразования условия в строку.
Рассмотрим следующий пример: вы хотите подсчитать заказы на "Яблоки" с суммой заказа, превышающей 200 долларов. Используя данные из диапазонов A2:A11 и B2:B11, можно применить следующую формулу:
=COUNTIFS($A$2:$A$11;"Яблоки";$B$2:$B$11;">200")
Также можно ввести критерии в ячейки, например F1 и F2, и затем ссылаться на них в формуле:
=COUNTIFS($A$2:$A$11;$F$1;$B$2:$B$11;">"&$F$2)
Здесь важно использовать абсолютные ссылки на диапазоны критериев, чтобы при копировании формулы она корректно работала в других ячейках.
Как использовать подстановочные знаки в COUNTIFS
Excel поддерживает использование подстановочных символов в формуле COUNTIFS:
- Вопросительный знак (?) — заменяет один любой символ. Используется для подсчета ячеек, начинающихся или заканчивающихся определенными символами.
- Звездочка (*) — соответствует любому количеству символов. С ее помощью можно подсчитать ячейки, содержащие определенное слово или набор символов.
Подсказка: если нужно учитывать реальные вопросительные знаки или звездочки, перед ними следует поставить символ тильда (~).
Например, если у вас есть список проектов в столбце A, и вы хотите подсчитать, сколько проектов уже распределены между сотрудниками (т.е. в столбце B есть какое-то имя), а в столбце D установлена дата завершения, используйте следующую формулу:
=COUNTIFS(B2:B10;"*",D2:D10;"<>"&"")
Обратите внимание: подстановочные символы нельзя использовать для числовых данных, таких как даты. Поэтому для поиска непустых ячеек в столбце D используется критерий "<>"&""
.
COUNTIFS и COUNTIF с несколькими критериями для дат
Формулы COUNTIFS и COUNTIF для дат похожи на те, что используются для чисел.
Пример 1. Подсчет дат в определенном диапазоне
Для подсчета дат, попадающих в заданный диапазон, можно воспользоваться формулой COUNTIFS с двумя критериями или комбинацией двух функций COUNTIF.
Например, следующие формулы подсчитают количество дат в диапазоне C2:C10, которые лежат между 1 июня 2014 года и 7 июня 2014 года:
=COUNTIFS(C2:C10;">=6/1/2014";C2:C10;"<=6/7/2014")
=COUNTIF(C2:C10;">=6/1/2014")-COUNTIF(C2:C10;">6/7/2014")
Пример 2. Подсчет дат с несколькими условиями
Подобным образом можно использовать COUNTIFS для подсчета дат из разных столбцов, удовлетворяющих нескольким условиям. Например, следующая формула подсчитает, сколько товаров было куплено после 1 мая и доставлено после 7 июня:
=COUNTIFS(C2:C10;">5/1/2014";D2:D10;">6/7/2014")
Пример 3. Подсчет дат с использованием текущей даты
С помощью функции TODAY() в сочетании с COUNTIF можно подсчитывать даты на основе текущей даты.
Вот пример формулы COUNTIFS, которая подсчитает количество товаров, купленных до сегодняшнего дня, но еще не доставленных:
=COUNTIFS(C2:C10;"<"&TODAY();D2:D10,">"&TODAY())
Вы также можете изменить формулу для расчета товаров, купленных более недели назад, но еще не доставленных:
=COUNTIFS(C2:C10;"<="&TODAY()-7;D2:D10;">"&TODAY())
Таким образом, используя различные комбинации формул COUNTIF и COUNTIFS, вы сможете легко подсчитывать данные с несколькими условиями в Excel.
Надеюсь, что эти примеры окажутся полезными для вас. Спасибо за внимание, и до встречи на нашем блоге в следующий раз!
Заключение
Использование функций COUNTIF и COUNTIFS позволяет эффективно подсчитывать ячейки на основе заданных условий. Эти функции полезны как для простых задач с одним критерием, так и для сложных расчетов с несколькими условиями и диапазонами.
Оцените статью |
|
22.11.2024
22.11.2024
22.11.2024
22.11.2024
22.11.2024