Функция Excel СЧЁТЕСЛИ позволяет подсчитать количество ячеек, которые соответствуют определенным критериям или условиям в заданном диапазоне.
Функция СЧЁТЕСЛИ - одна из статистических функций в Excel, которая представляет собой комбинацию функций СЧЁТ и ЕСЛИ или функцию СЧЁТ. При использовании в формуле функция подсчитывает количество ячеек, которые соответствуют определенным критериям или условиям в одном или нескольких диапазонах. Функция СЧЁТЕСЛИ помогает подсчитать ячейки, содержащие текст, числа или даты, которые соответствуют определенным критериям.
Вы можете подсчитывать ячейки с помощью функций СЧЁТЕСЛИ или СЧЁТЕСЛИ в Excel. Разница между функциями СЧЁТЕСЛИ и СЧЁТЕСЛИМН состоит в том, что СЧЁТЕСЛИ используется для подсчета ячеек, которые соответствуют одному критерию в одном диапазоне, а СЧЁТЕСЛИ подсчитывает ячейки, которые удовлетворяют нескольким условиям в одном или нескольких диапазонах.
В этой статье будет показано, как использовать две функции СЧЁТЕСЛИ и СЧЁТЕСЛИ в Excel.
Функция СЧЁТЕСЛИ в Excel
Функция СЧЁТЕСЛИ позволяет выполнять подсчет данных на основе определенного критерия или условия. Условие, используемое в функции, работает с логическими операторами (,, =,> =, <=) и символами подстановки (*,?) Для частичного сопоставления.
Синтаксис функции СЧЁТЕСЛИ
Структура функции СЧЁТЕСЛИ:
= СЧЁТЕСЛИ (диапазон; критерии)Параметры:
диапазон- Диапазон подсчитываемых ячеек.критерии- Условие определяет, какие ячейки должны быть включены в подсчет в указанном диапазоне. Критерии могут быть числовым значением, текстом, ссылкой на адрес ячейки или уравнением.
Использование функции СЧЁТЕСЛИ для подсчета числовых значений
Как мы обсуждали выше, критерий (второй аргумент) в функции СЧЁТЕСЛИ определяет условие, которое сообщает функции, какие ячейки следует подсчитывать.
Эта функция помогает подсчитать количество ячеек со значениями, удовлетворяющими логическим условиям, например, равно, больше, меньше или не равно указанному значению и т. Д.
В приведенном ниже примере формула подсчитывает ячейки, содержащие значение, равное 5 (критерии). Вы можете напрямую вставить «5» в формулу или использовать ссылку на адрес ячейки, имеющей значение (ячейка D2 в приведенном ниже примере).
= СЧЁТЕСЛИ (B2: B11; D2)
Приведенная выше формула подсчитывает количество ячеек в диапазоне ячеек (B2: B11), которые содержат значение, равное значению в ячейке D2.
Следующая формула подсчитывает ячейки со значением меньше 5.
= СЧЁТЕСЛИ (B2: B11; «<5»)Оператор «меньше» (<) указывает формуле подсчитывать ячейки со значением меньше «5» в диапазоне B2: B11. Каждый раз, когда вы используете оператор в условии, обязательно заключайте его в двойные кавычки («»).
Иногда, когда вы хотите подсчитать ячейки, исследуя их по критерию (значению) в ячейке. В таких случаях создайте критерий, объединив оператор и ссылку на ячейку. При этом вам нужно заключить оператор сравнения в двойные кавычки («»), а затем поместить амперсанд (&) между оператором сравнения и ссылкой на ячейку.
= СЧЁТЕСЛИ (B2: B11; "> =" & D2)
На рисунке ниже показаны несколько примеров формул и их результат.
Использование функции COUNTIF для подсчета текстовых значений
Чтобы подсчитать ячейки, содержащие определенные текстовые строки, используйте эту текстовую строку в качестве аргумента критерия или ячейку, содержащую текстовую строку. Например, в приведенной ниже таблице, если мы хотим подсчитать все ячейки в диапазоне (B21: D27) с текстовым значением в ячейке B21 (sam) в нем, мы можем использовать следующую формулу:
= СЧЁТЕСЛИ (B21: D27; B21)
Как мы обсуждали ранее, мы могли бы использовать текст «sam» непосредственно в формуле или использовать ссылку на ячейку, которая имеет критерий (B21). Текстовую строку всегда следует заключать в двойные кавычки («»), когда она используется в формуле в Excel.
= СЧЁТЕСЛИ (B21: D27; "сам")
Чтобы подсчитать ячейки, не содержащие заданного текста, используйте следующую формулу:
= СЧЁТЕСЛИ (B21: D27; "" & B21)Не забудьте заключить "не равно" "" оператор в двойных кавычках.
Если вы используете текст «sam» непосредственно в формуле, вам необходимо заключить оператор «» и текстовую строку вместе ("Сэм") в двойных кавычках.
= СЧЁТЕСЛИ (B21: D27; "сам") 
Использование подстановочных знаков в функции Excel СЧЁТЕСЛИ (частичное соответствие)
Вы можете использовать формулу СЧЁТЕСЛИ с подстановочными знаками для подсчета ячеек, содержащих определенное слово, фразу или буквы. В функции СЧЕТЕСЛИ в Excel можно использовать три подстановочных символа:
*(звездочка) - используется для подсчета ячеек с любым количеством начальных и конечных символов / букв. (например, St * может означать Stark, Stork, Stacks и т. д.?(вопросительный знак) - используется для поиска ячеек с одним символом. (например, St? rk может означать Старка или Аиста.~(тильда) - используется для поиска и подсчета количества ячеек, содержащих в тексте вопросительный знак или символ звездочки (~, *,?).
Подсчет ячеек, начинающихся или заканчивающихся определенными символами
Чтобы подсчитать ячейки, которые начинаются или заканчиваются определенным текстом с любым количеством других символов в ячейке, используйте подстановочный знак звездочки (*) во втором аргументе функции СЧЁТЕСЛИ.
Используйте эту формулу-образец:
= СЧЁТЕСЛИ (A1: A10; «A *») - для подсчета ячеек, начинающихся с «А».
= СЧЁТЕСЛИ (A19: A28; «* э») - для подсчета количества ячеек, оканчивающихся на символы «эр».
= СЧЁТЕСЛИ (A2: A12; «* QLD *») - для подсчета ячеек, содержащих текст «QLD» в любом месте текстовой строки.
А? представляет ровно один символ, используйте этот подстановочный знак в функции СЧЁТЕСЛИ ниже, чтобы подсчитать количество ячеек, которые содержат ровно +1 символ, где ‘?' используется.
= СЧЁТЕСЛИ (A1: A10; «Par? S»)
Подсчет пустых и непустых ячеек с функцией СЧЁТЕСЛИ
Формула СЧЁТЕСЛИ также полезна, когда дело доходит до подсчета количества пустых или непустых ячеек в заданном диапазоне.
Подсчет непустых ячеек
Если вы хотите подсчитывать только ячейки, содержащие какие-либо «текстовые» значения, используйте формулу ниже. Эта формула рассматривает ячейки с датами и числами как пустые и не учитывает их при подсчете.
= СЧЁТЕСЛИ (A1: B12; «*»)Подстановочный знак * совпадает только с текстовыми значениями и возвращает количество всех текстовых значений в заданном диапазоне.
Если вы хотите подсчитать все непустые ячейки в заданном диапазоне, попробуйте эту формулу:
= СЧЁТЕСЛИ (A1: B12; "")
Подсчет пустых ячеек
Если вы хотите подсчитать пустые ячейки в определенном диапазоне, используйте функцию СЧЁТЕСЛИ с * подстановочный знак и в аргументе критерия для подсчета пустых ячеек.
Эта формула подсчитывает ячейки, не содержащие текстовых значений:
= СЧЁТЕСЛИ (A1: B12; "" & "*")С * подстановочный знак соответствует любому текстовому значению, приведенная выше формула будет считать все ячейки, не равные *. Он также считает пустыми ячейки с датами и числами.
Чтобы подсчитать все пробелы (все типы значений):
= СЧЁТЕСЛИ (A1: B12; "")Эта функция считает только пустые ячейки в диапазоне.
Использование функции СЧЁТЕСЛИ для подсчета дат
Вы можете подсчитывать ячейки с датами (так же, как и с числовыми критериями), которые соответствуют логическому условию или указанной дате или дате в ссылочной ячейке.
Чтобы подсчитать ячейки, содержащие указанную дату (05.05.2020), мы должны использовать эту формулу:
= СЧЁТЕСЛИ (B2: B10; «05-05-2020»)
Вы также можете указать дату в разных форматах в качестве критерия в функции СЧЁТЕСЛИ, как показано ниже:
Если вы хотите подсчитать ячейки, содержащие даты до или после определенной даты, используйте операторы «меньше» (до) или «больше» (после) вместе с конкретной датой или ссылкой на ячейку.
= СЧЁТЕСЛИ (B2: B10, "> = 05.05.2020")
Вы также можете использовать ссылку на ячейку, содержащую дату, объединив ее с оператором (в двойных кавычках).
Чтобы подсчитать количество ячеек в диапазоне A2: A14 с датой перед датой в E3, используйте приведенную ниже формулу, где оператор больше (<) означает перед датой в E3.
= СЧЁТЕСЛИ (A2: A14; "<" & E3)
Несколько примеров формул и их результат:
Дата подсчета на основе текущей даты
Вы можете комбинировать функцию СЧЁТЕСЛИ с конкретными функциями даты в Excel, например, СЕГОДНЯ () для подсчета ячеек с текущей датой.
= СЧЁТЕСЛИ (A2: A14; ">" & СЕГОДНЯ ())Эта функция считает все даты с сегодняшнего дня в диапазоне (A2: A14).
Подсчет дат между определенным диапазоном дат
Если вы хотите подсчитать все даты между двумя датами, вам нужно использовать в формуле два критерия.
Мы можем сделать это, используя два метода: функции СЧЁТЕСЛИ и СЧЁТЕСЛИ.
Использование функции СЧЁТЕСЛИ в Excel
Вам нужно использовать две функции СЧЁТЕСЛИ для подсчета всех дат между двумя указанными датами.
Чтобы подсчитать даты между '09-02-2020 'и '20-08-2021', используйте эту формулу:
= СЧЁТЕСЛИ (A2: A14, "> 09-02-2020") - СЧЁТЕСЛИ (A2: A14, "> 20-08-2021")Эта формула сначала находит количество ячеек с датой после 2 февраля и вычитает количество ячеек с датой после 20 августа. Теперь мы получаем число. ячеек с датами, наступающими после 2 февраля и не позднее 20 августа (количество 9).
Если вы не хотите, чтобы формула учитывала одновременно 2 февраля и 20 августа, используйте вместо этого следующую формулу:
= СЧЁТЕСЛИ (A2: A14, "> 09-02-2020") - СЧЁТЕСЛИ (A2: A14, "> = 20-08-2021")Просто замените оператор ‘>’ на ‘> =’ во втором критерии.
Использование функции СЧЁТЕСЛИМН в Excel
Функция СЧЁТЕСЛИ поддерживает несколько критериев и, в отличие от функции СЧЁТЕСЛИ, подсчитывает ячейки только после выполнения всех условий. Если вы хотите подсчитать ячейки со всеми датами между двумя указанными датами, введите эту формулу:
= СЧЁТЕСЛИМН (A2: A14; ">" & A11; A2: A14; "<" & A10)
Если вы хотите включить в подсчет и указанные даты, используйте операторы ‘> =’ и ‘<=’. Вот эта формула:
= СЧЁТЕСЛИМН (A2: A14, «> = 09-02-2020», A2: A14, «<= 20-08-2021»)В этом примере мы использовали дату непосредственно в критериях вместо ссылки на ячейку.
Как обрабатывать СЧЁТЕСЛИ и СЧЁТЕСЛИ с несколькими критериями в Excel
Функция СЧЁТЕСЛИ чаще всего используется для подсчета ячеек с одним критерием (условием) в одном диапазоне. Но вы все равно можете использовать COUNTIF для подсчета ячеек, которые соответствуют нескольким условиям в одном диапазоне. Однако функцию СЧЁТЕСЛИМН можно использовать для подсчета ячеек, которые соответствуют нескольким условиям в одном или разных диапазонах.
Как считать числа в пределах диапазона
Вы можете подсчитать ячейки, содержащие числа между двумя указанными числами, используя две функции: СЧЁТЕСЛИ и СЧЁТЕСЛИ.
СЧЁТЕСЛИ для подсчета чисел между двумя числами
Одним из распространенных способов использования функции СЧЁТЕСЛИ с несколькими критериями является подсчет чисел между двумя указанными числами, например для подсчета чисел больше 10, но меньше 50. Чтобы подсчитать числа в пределах диапазона, объедините две или более функций СЧЁТЕСЛИ вместе в одну формулу. Давайте покажем вам, как это сделать.
Допустим, вы хотите подсчитать ячейки в диапазоне B2: B9, где значение больше 10 и меньше 21 (не включая 10 и 21), воспользуйтесь следующей формулой:
= СЧЁТЕСЛИ (B2: B14; "> 10") - СЧЁТЕСЛИ (B2: B14; "> = 21")Разница между двумя числами находится путем вычитания одной формулы из другой. Первая формула считает числа больше 10 (то есть 7), вторая формула возвращает количество чисел больше или равных 21 (то есть 4), а результат второй формулы вычитается из первой формулы (7 -4), чтобы получить количество чисел между двумя числами (3).
Если вы хотите подсчитать ячейки с числом больше 10 и меньше 21 в диапазоне B2: B14, включая числа 10 и 21, используйте эту формулу:
= СЧЁТЕСЛИ (B2: B14; "> = 10") - СЧЁТЕСЛИ (B2: B14; "> 21")
СЧЕТЕСЛИ для подсчета чисел между 2 числами
Чтобы подсчитать числа от 10 до 21 (исключая 10 и 21), содержащиеся в ячейках с B2 по B9, используйте эту формулу:
= СЧЁТЕСЛИМН (B2: B14; «> 10»; B2: B14; «<21»)
Чтобы включить 10 и 21 в счет, просто используйте в формулах операторы «больше или равно» (> =) вместо «больше» и «меньше или равно» (<=) вместо операторов «меньше». .
СЧЕТЕСЛИ для подсчета ячеек с несколькими критериями (И критериями)
Функция СЧЁТЕСЛИМН является множественным аналогом функции СЧЁТЕСЛИ, которая подсчитывает ячейки на основе двух или более критериев в одном или нескольких диапазонах. Это известно как «логика И», потому что функция предназначена для подсчета ячеек только тогда, когда все данные условия ИСТИННЫ.
Например, мы хотим узнать, сколько раз (количество ячеек) этот хлеб (значение в столбце A) был продан менее 5 (значение в столбце C).
Мы можем использовать эту формулу:
= СЧЁТЕСЛИМН (A2: A14; «Хлеб»; C2: C14; «<5»)
СЧЁТЕСЛИ для подсчета ячеек с несколькими критериями (критериями ИЛИ)
Если вы хотите подсчитать количество ячеек, удовлетворяющих нескольким критериям в одном диапазоне, объедините две или более функций СЧЁТЕСЛИ вместе. Например, если вы хотите узнать, сколько раз «Хлеб» или «Сыр» повторяются в указанном диапазоне (A2: A14), используйте следующую формулу:
= СЧЁТЕСЛИ (A2: A14, «Хлеб») + СЧЁТЕСЛИ (A2: A14, «Сыр»)Эта формула подсчитывает ячейки, для которых хотя бы одно из условий истинно. Вот почему это называется логикой ИЛИ.
Если вы хотите оценить более одного критерия в каждой из функций, лучше использовать СЧЁТЕСЛИ вместо СЧЁТЕСЛИ. В приведенном ниже примере мы хотим получить количество статусов «Заказано» и «Доставлено» для «Хлеба», поэтому мы будем использовать эту формулу:
= СЧЁТЕСЛИМН (A2: A14, «Хлеб», C2: C14, «Заказано») + СЧЁТЕСЛИМН (A2: A14, «Хлеб», C2: C14, «Доставлено»)
Мы надеемся, что это простое, но довольно длинное руководство даст вам некоторое представление о том, как использовать функции СЧЁТЕСЛИ и СЧЁТЕСЛИ в Excel.