СЧЕТЕСЛИ в Excel

СЧЁТЕСЛИ в Excel — примеры функций с одним и несколькими условиями

В этой статье мы сосредоточимся на функции Excel COUNTIF, которая предназначена для подсчета ячеек с определенным условием. Сначала мы кратко рассмотрим синтаксис и общее использование, а затем я приведу несколько примеров и предупрежу о возможных странностях одновременного использования нескольких критериев или определенных типов данных.

Они в основном одинаковы во всех версиях, поэтому вы можете использовать примеры в MS Excel 2016, 2013, 2010 и 2007.

Функция Excel COUNTIF используется для подсчета количества ячеек в указанном диапазоне, которые соответствуют заданному условию.

Например, вы можете использовать его, чтобы узнать, сколько ячеек в вашей электронной таблице содержат число больше или меньше указанного вами значения. Другое стандартное использование — подсчет ячеек с определенным словом или с одной или несколькими буквами).

СЧЁТЕСЛИ(диапазон, критерии)

Как видите, здесь всего 2 аргумента, оба обязательные:

• диапазон: указывает одну или несколько ячеек для подсчета. Вы вставляете диапазон в формулу как обычно, например, A1:A20.
• критерий: определяет условие, определяющее, что именно следует учитывать. Это может быть число, текстовая строка, ссылка или выражение. Например, вы можете использовать следующие критерии: «10», A2, «>= 10», «какой-то текст».

Что там помнить?

• В аргументе условия условие всегда должно быть заключено в кавычки, за исключением случаев использования хука или функции.
• Любой аргумент относится к диапазону в другой книге Excel, эта книга должна быть открыта.
• Буквы не чувствительны к регистру.
• Вы также можете использовать подстановочные знаки * и ? (о них позже).
• Во избежание ошибок текст не должен содержать непечатаемых символов.

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

Примеры работы функции СЧЕТЕСЛИ.

Для подсчета текста.

Давайте разберемся, как это работает. На рисунке ниже вы можете увидеть список выполненных менеджерами заказов. Выражение =СЧЁТЕСЛИ(B2:B22;»Никитенко») подсчитывает, сколько раз этот работник присутствует в списке:

 

 

Комментарий. Критерий не чувствителен к регистру, поэтому вы можете вводить как прописные, так и строчные буквы.

Если ваши данные содержат несколько вариантов слов, которые вы хотите подсчитать, вы можете использовать подстановочные знаки для подсчета всех ячеек, которые содержат определенное слово, фразу или букву как часть своего содержимого.

Например, в нашей таблице есть несколько клиентов Короны из разных городов. Нам нужно рассчитать общее количество заказов Corona вне зависимости от города.

=СЧЁТЕСЛИ(A2:A22;»*Коро*»)

Мы подсчитываем количество заказов, в которых имя клиента содержит «коро» в любой записи. Звездочка (*) используется для поиска ячеек с любой последовательностью начальных и конечных символов, как показано в примере выше. Если вам нужно заменить какой-либо символ, введите знак вопроса (?) вместо (?).

Также не совсем рационально указывать условие непосредственно в формуле, так как если нужно вычислить какое-то другое значение, то придется его корректировать. А это не очень удобно.

Рекомендуется написать условие в какой-нибудь ячейке и потом ссылаться на него. Так мы сделали это в H9. Вы также можете использовать подстановочные знаки со ссылками, используя оператор конкатенации (&). Например, вместо того, чтобы вводить «*Коро*» прямо в формулу, вы можете ввести его где-нибудь и использовать следующую конструкцию для подсчета ячеек, содержащих «Коро»:

=СЧЁТЕСЛИ(A2:A22,»*»&H8&»*»)

Подсчет ячеек, начинающихся или заканчивающихся определенными символами

Вы можете использовать звездочку (*) или вопросительный знак (?) в качестве подстановочного знака, в зависимости от точного результата, которого вы хотите достичь.

Если вы хотите узнать количество ячеек, которые начинаются или заканчиваются определенным текстом, независимо от того, сколько других символов, используйте:

=СЧЁТЕСЛИ(A2:A22;»K*») — считать значения, начинающиеся с «K» .

=СЧЁТЕСЛИ(A2:A22,»*p») — подсчитываются те, которые заканчиваются на букву «p».

Если вы ищете количество ячеек, которые начинаются или заканчиваются определенными буквами и содержат точное количество символов, поставьте знак вопроса (?):

=СЧЁТЕСЛИ(С2:С22;»????d») — находит число с буквой «d» на конце и текст, состоящий из 5 букв, включая пробелы.

=СЧЁТЕСЛИ(С2:С22, «??») — подсчитывает количество 2-х символов, включая пробелы.

Примечание. Чтобы узнать количество ячеек, содержащих знак вопроса или звездочку в тексте, введите тильду (~) перед ? или *.

Например, =СЧЁТЕСЛИ(C2:C22;»*~?*») подсчитает все позиции, содержащие вопросительный знак в диапазоне C2:C22.

Подсчет чисел по условию.

Что касается чисел, то редко бывает так, что нужно посчитать их количество равным какому-то определенному числу. Тем не менее, мы рекомендуем вам написать что-то вроде этого:

= СЧЁТЕСЛИ(D2:D22,10000)

Чаще нужно вычислить количество значений, которые больше или меньше определенного значения.

Чтобы подсчитать значения, которые больше, меньше или равны указанному вами числу, просто добавьте соответствующие критерии, как показано в таблице ниже.

Обратите внимание, что математический оператор вместе с числом всегда заключен в кавычки .

критерии		Описание
Если больше чем	=СЧЁТЕСЛИ(A2:A10;»>5″)	Подсчитайте, где значение больше 5.
Если меньше	=СЧЁТЕСЛИ(A2:A10;»>5″)	Считай числами меньше 5.
Если это то же самое	=СЧЁТЕСЛИ(A2:A10;»=5″)	Определите, сколько раз число равно 5.
Если это не то же самое	=СЧЁТЕСЛИ(A2:A10;»5″)	Подсчитайте, сколько раз не равно 5.
Если больше или равно	=СЧЁТЕСЛИ(A2:A10;»>=5″)	Считайте, когда больше или равно 5.
Если он меньше или равен	=СЧЁТЕСЛИ(A2:A10;»	Подсчитайте, где меньше или равно 5.

В нашем примере

=СЧЁТЕСЛИ(D2:D22;»>10000″)

Мы подсчитываем количество крупных заказов, которые превышают 10 000. Обратите внимание, что здесь мы записываем условие подсчета в виде текстовой строки и поэтому заключаем его в двойные кавычки.

Вы также можете использовать все вышеперечисленные параметры для подсчета ячеек на основе значения другой ячейки. Вам просто нужно заменить номер ссылкой.

Комментарий. При использовании ссылки вы должны заключить математический оператор в кавычки и добавить перед ним амперсанд (&). Например, чтобы подсчитать числа в диапазоне D2:D9 больше, чем D3, используйте =СЧЁТЕСЛИ(D2:D9,»>»&D3)

Если вы хотите подсчитывать записи, содержащие в своем содержании математический оператор, то есть символ «>», «

Например, =СЧЁТЕСЛИ(D2:D9,»*>5*») будут подсчитаны все товары в диапазоне D2:D9 с таким содержанием, как «Отправка >5 дней» или «>5 единиц на складе».

Примеры с датами.

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

 

 

Приведу несколько примеров:

критерии		Описание
Даты равны указанной дате.	=СЧЁТЕСЛИ(E2:E22;»01.02.2019″)	Подсчитывает количество ячеек в диапазоне E2:E22 с датой 1 июня 2014 г.
Даты больше или равны другой дате.	=СЧЁТЕСЛИ(E2:E22;»>=02.01.2019″)	Подсчитайте количество ячеек в диапазоне E2:E22 с датой больше или равной 01.06.2014.
Даты больше или равны дате в другой ячейке минус X дней.	=СЧЁТЕСЛИ(E2:E22,»>=»&H2-7)	Определите количество ячеек в диапазоне E2:E22 с датой, большей или равной дате в H2 минус 7 дней.

В дополнение к этим стандартным методам вы можете использовать функцию СЧЁТЕСЛИ в сочетании с функциями даты и времени, такими как СЕГОДНЯ(), для подсчета ячеек на основе текущей даты.

критерии
То же, что и текущая дата.	=СЧЁТЕСЛИ(E2:E22,СЕГОДНЯ())
До текущей даты, то есть меньше, чем сегодня.	=СЧЁТ.ЕСЛИ(E2:E22;»
После текущей даты, то есть больше, чем сегодня.	=СЧЁТЕСЛИ(E2:E22;»>»& СЕГОДНЯ ())
Сроки через неделю.	= СЧЁТЕСЛИ(E2:E22,»=»&СЕГОДНЯ()+7)
В пределах определенного диапазона времени.	=СЧЁТЕСЛИ(E2:E22;»>=»&СЕГОДНЯ()+30)-СЧЁТЕСЛИ(E2:E22;»>»&СЕГОДНЯ())

Как посчитать количество пустых и непустых ячеек?

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

Не пустой.

В некоторых учебниках COUNTIF вы можете найти предложения для подсчета непустых ячеек, например:

СЧЁТЕСЛИ(диапазон;»*»)

Но дело в том, что приведенное выше выражение подсчитывает только ячейки, содержащие текстовые значения. И это означает, что те, которые содержат даты и числа, будут рассматриваться как пустые (игнорируемые) и не будут включены в общую сумму!

Если вам нужно общее решение для подсчета всех непустых ячеек в заданном диапазоне, введите:

СЧЁТЕСЛИ(диапазон;»» & «»)

Это правильно работает со всеми типами значений — текстом, датами и числами — как вы можете видеть на изображении ниже.

 

 

Также можно подсчитать непустые ячейки в диапазоне:

=СЧЁТЧАСТЬ(E2:E22).

Пустой.

Если вы хотите подсчитать пустые позиции в определенном диапазоне, вы должны следовать тому же подходу: использовать подстановочный знак для текстовых значений в условиях и параметр «» для подсчета всех пустых ячеек.

Считаем ячейки, не содержащие текста:

СЧЁТЕСЛИ(диапазон; «» и; «» & «*»)

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

Чтобы подсчитать пустые ячейки (все типы значений):

=СЧЁТЕСЛИ(E2:E22;»»)

Конечно, для таких случаев есть специальная функция

=СЧИТАТЬПУСТО(E2:E22)

Но не все знают о его существовании. Но теперь ты знаешь …

Нулевые строки.

Также обратите внимание, что COUNTIF и COUNTBLANK подсчитывают ячейки с пустыми строками, которые кажутся пустыми только на первый взгляд.

Что это за пустые строки? Также они часто возникают при импорте данных из других программ (например, 1С). Внешне в них ничего нет, но на самом деле это не так. При попытке найти такие «заготовки» (F5 -Выбрать — Пустые ячейки) они не обнаруживаются. Но фильтр данных видит его как пустой и отфильтровывает как пустой.

Дело в том, что существует «строка нулевой длины» (или «нулевая строка»). Нулевая строка возникает, когда программе нужно вставить какое-то значение, но вставлять нечего.

Проблемы начинаются, когда вы пытаетесь произвести с ним какие-то математические вычисления (вычитание, деление, умножение и т д.). Вы получаете сообщение об ошибке #ЗНАЧ!. При этом функции СУММ и СЧЁТ их игнорируют, как если бы там был текст. Внешне его нет.

И самое интересное, что если навести на него мышкой и нажать Удалить (или вкладку Главная — Правка — Очистить содержимое), то он действительно становится пустым, и формулы и другие функции Excel начинают с ним работать без ошибок.

Если вы не хотите рассматривать их как пустые, используйте следующее выражение для подсчета фактических пустых ячеек:

=СТРОКИ(E2:E22)*СТОЛБЦ(E2:E22)-СЧЁТЕСЛИ(E2:E22;»»&»»)

 

 

Где в ячейках могут появляться нулевые строки? Тут может быть несколько вариантов:

1. Он есть изначально, потому что так вы настраиваете загрузку и создание файлов в сторонней программе (типа 1С). В некоторых случаях такие нагрузки настраиваются таким образом, что пустых ячеек как таковых нет; они просто заполняются строкой нулевой длины.
2. Была создана формула, результатом которой стал текст нулевой длины. Самый простой случай:

=ЕСЛИ(Е1=1,10;»»)

В результате, если в E1 будет записано что-то отличное от 1, программа вернет строку нулевой длины. А если мы потом заменим формулу значением (Специальная вставка — Значения), то получим нашу псевдопустую позицию.

Если вы проверяете какие-то условия с помощью функции ЕСЛИ и планируете в дальнейшем производить над результатами математические операции, то вместо « » лучше поставить 0 . Тогда проблем не будет. Нули всегда можно заменить или скрыть: Файл -Параметры -Дополнительно — Показать нули в позициях, содержащих нулевые значения.

СЧЕТЕСЛИ с несколькими условиями.

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

Количество чисел в диапазоне

Одним из наиболее распространенных применений функции СЧЁТЕСЛИ с двумя критериями является определение количества чисел в определённом диапазоне, то есть меньше X, но больше Y.

Например, вы можете использовать для вычисления ячеек в диапазоне B2:B9, где значение больше 5 и меньше или равно 15:

 

 

=СЧЁТЕСЛИ(B2:B11;»>5″)-СЧЁТЕСЛИ(B2:B11;»>15″)

Количество ячеек с несколькими условиями ИЛИ.

Если вы хотите найти количество нескольких различных элементов в диапазоне, добавьте в выражение 2 или более функций СЧЁТЕСЛИ. Допустим, у вас есть список покупок, и вы хотите узнать, сколько в нем безалкогольных напитков.

 

 

Давай сделаем это:

=СЧЁТЕСЛИ(A4:A13;»лимонад»)+СЧЁТЕСЛИ(A2:A11;»*сок»)

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

Как вы понимаете, здесь можно добавить больше условий.

Использование СЧЕТЕСЛИ для подсчета дубликатов.

Другое возможное использование функции СЧЁТЕСЛИ в Excel — поиск дубликатов в столбце, между двумя столбцами или в строке.

1. Ищем дубликаты в одном столбце

Это простое выражение COUNTIF($A$2:$A$24,A2)>1 найдет все одинаковые записи в A2:A24.

И еще одна формула СЧЁТЕСЛИ(B2:B24,ИСТИНА) сообщит вам, сколько существует дубликатов:

 

 

Для более наглядного представления найденных совпадений я использовал условное форматирование значения ИСТИНА.

2. Сколько совпадений между двумя столбцами?

Сравните список2 со списком1. В столбце E мы последовательно берем каждое значение из списка2 и подсчитываем, сколько раз оно встречается в списке1. Если совпадений нет, то это уникальное значение. На рисунке они выделены цветом с использованием условного форматирования.

 

 

Выражение =СЧЁТЕСЛИ($A$2:$A$24,C2) копируется в столбец E.

Аналогичный расчет можно делать и наоборот: брать значения из первого списка и искать дубликаты во втором.

Чтобы просто определить количество дубликатов, вы можете использовать комбинацию функций СУММПРОИЗВ и СЧЁТЕСЛИ.

=СУММПРОИЗВ((СЧЁТЕСЛИ(A2:A24;C2:C24)>0)*(C2:C24″»))

Подсчитаем количество уникальных значений в list2:

=СУММПРОИЗВ((СЧЁТЕСЛИ(A2:A24;C2:C24)=0)*(C2:C24″»))

Получаем 7 уникальных и 16 дублирующих записей, что видно на рисунке.

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

3. Сколько дубликатов и уникальных значений в строке?

Если вы хотите подсчитать повторяющиеся или уникальные значения в определенной строке, а не в столбце, используйте одну из следующих формул. Они могут быть полезны, например, для анализа истории розыгрыша лотереи.

 

 

Подсчет количества дубликатов:

=СУММПРОИЗВ((СЧЁТЕСЛИ(A2:K2,A2:K2)>1)*(A2:K2″»))

Мы видим, что 13 выпало 2 раза.

Подсчет уникальных значений:

=СУММПРОИЗВ((СЧЁТЕСЛИ(A2:K2,A2:K2)=1)*(A2:K2″»))

Часто задаваемые вопросы и проблемы.

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

1. Можно ли считать в диапазоне несмежных ячеек?

Вопрос: Как я могу использовать СЧЁТЕСЛИ для диапазона или несмежных ячеек?

Ответ: Не работает с несмежными диапазонами, синтаксис не позволяет указывать несколько отдельных ячеек в качестве первого параметра. Вместо этого вы можете использовать комбинацию нескольких функций COUNTIF:

Неправильно: =СЧЁТЕСЛИ(A2,B3,C4,»>0″)

Правильно: = СЧЁТЕСЛИ(A2;»>0″) + СЧЁТЕСЛИ(B3;»>0″) + СЧЁТЕСЛИ(C4;»>0″)

Альтернативный способ — использовать функцию ДВССЫЛ для создания массива несмежных ячеек. Например, два варианта ниже дают тот же результат, что и на изображении:

 

 

=СУММ(СЧЁТЕСЛИ(ДВССЫЛ({«B2:B11″,»D2:D11″}),»=0»))

ИЛИ

=СЧЁТЕСЛИ($B2:$B11,0) + СЧЁТЕСЛИ($D2:$D11,0)

1. Амперсанд и кавычки в формулах СЧЁТЕСЛИ

Вопрос: Когда мне нужно использовать амперсанд?

Ответ: Это, пожалуй, самая сложная часть функции СЧЁТЕСЛИ, которая лично меня тоже смущает. Хотя, если подумать, вы увидите, что амперсанд и кавычки необходимы для построения строки аргумента.

Итак, вы можете следовать этим правилам:

• Если вы используете номер или ссылку на ячейку в точном соответствии, вам не нужны амперсанд или кавычки. Например:

= СЧЁТЕСЛИ(A1:A10,10) или = СЧЁТЕСЛИ(A1:A10,C1)

• Если ваши условия содержат текст, подстановочный знак или логический оператор с числом, заключите его в кавычки. Например:

= СЧЁТЕСЛИ(A2:A10,»яблоко») или = СЧЁТЕСЛИ(A2:A10,»*») или = СЧЁТЕСЛИ(A2:A10,»>5″)

• Если вашим критерием является выражение со ссылкой или какая-либо другая функция Excel, вы должны использовать кавычки («»), чтобы начать текстовую строку, и амперсанд (&), чтобы конкатенировать (объединить) и закончить строку. Например:

= СЧЁТЕСЛИ(A2:A10;»>»&D2) или = СЧЁТЕСЛИ(A2:A10;»

Если вы не уверены, нужен вам амперсанд или нет, попробуйте оба способа. В большинстве случаев амперсанд работает нормально.

Например, =СЧЁТЕСЛИ(C2: C8;»

1. Как считать клетки по цвету?

Вопрос: Как считать ячейки по цвету заливки или шрифту, а не по значениям?

Ответ: К сожалению, синтаксис функции не позволяет использовать форматы в качестве условия. Единственный возможный способ суммировать ячейки на основе их цвета — использовать макрос или, точнее, определяемую пользователем функцию Excel VBA.

1. Ошибка #ИМЯ?

Проблема: я продолжаю получать #NAME? Как я могу это исправить?

Ответ: Скорее всего вы указали неверный диапазон. См пункт 1 выше.

1. Формула не работает

Проблема: Моя формула не работает! Что я сделал не так?

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

Будьте очень осторожны с пробелами. Создавая одну из формул для этой статьи, я был готов рвать на себе волосы, потому что правильный дизайн (я знал, что он правильный!) не сработал. Оказывается, проблема была там… Например, посмотрите на это: =СЧЁТ.ЕСЛИ(A4:A13;»Лимонад»). На первый взгляд ничего страшного, кроме лишнего пробела после открывающей цитаты. Программа проглотит все без каких-либо сообщений об ошибках, предупреждений или каких-либо других указаний. Но если вы действительно хотите посчитать предметы, содержащие слово «лимонад» и начальный пробел, вы будете очень разочарованы….

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

И это все на сегодня. В следующей статье мы рассмотрим несколько способов подсчета ячеек в Excel с несколькими условиями.