Функция СЧЕТЕСЛИ в Excel

КАЛЬКУЛЯЦИИ в Excel - примеры одномерных и многомерных функций.

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

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

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

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

CALCULATE (диапазон; критерий)

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

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

Что мне нужно иметь в виду?

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

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

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

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

Давайте рассмотрим, как это работает. На рисунке ниже показан список заказов, сделанных менеджерами. Выражение =CALCULATION(B2:B22, "Никитенко") подсчитывает, сколько раз этот сотрудник появляется в списке:

 

 

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

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

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

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

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

Кроме того, указывать условие непосредственно в формуле не очень полезно, так как если вам понадобится вычислить какие-то другие значения, вам придется его корректировать. Это не очень удобно.

Хорошая идея - записать условие в ячейке, а затем сделать ссылку на него. Это то, что мы сделали в H9. Вы также можете использовать подстановочные знаки со ссылками с помощью оператора конкатенации (&). Например, вместо того, чтобы указывать "* Koro *" непосредственно в формуле, вы можете записать ее где-нибудь и использовать следующую конструкцию для подсчета ячеек, содержащих "Koro":

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

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

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

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

=CALCULATE(A2:A22; "K*") - подсчет значений, начинающихся с " K" .

=CALCULATE(A2:A22; "*p") - подсчет значений, которые заканчиваются на "p" .

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

=CHOTHESLI(C2:C22;"????d") - находит количество букв "e" в конце и текст, состоящий из 5 букв, включая пробелы.

=CHANGE(C2:C22,"??") - подсчитывает количество текста, состоящего из 2 символов, включая пробелы.

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

Например, =Counting(C2:C22, "*~?*") подсчитает все ячейки, содержащие вопросительный знак в диапазоне C2:C22.

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

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

= COUNTSLICE(D2:D22,10000)

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

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

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

критерии		Описание
Если более	=СЧЕТЕСЛИ(А2:А10;">5")	Подсчитать, где значение больше 5.
Если меньше	=СЧЕТЕСЛИ(А2:А10;">5")	Считайте, используя числа меньше 5.
Если равно	=СЧЕТЕСЛИ(А2:А10;"=5")	Определите, сколько раз значение равно 5.
Если оно не равно	=СЧЕТЕСЛИ(А2:А10;"5")	Посчитайте, сколько раз значение не равно 5.
Если оно больше или равно	=СЧЕТЕСЛИ(А2:А10;">=5")	Считайте, если больше или равно 5.
Если оно меньше или равно	=СЧЕТЕСЛИ(А2:А10;"	Считайте, если меньше или равно 5.

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

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

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

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

Предупреждение. Если вы используете ссылку, вы должны заключить математический оператор в двойные кавычки и добавить перед ним амперсанд (&). Например, чтобы подсчитать числа в диапазоне D2: D9, которые больше D3, используйте =CALCULATION(D2:D9,">"&D3).

Если вы хотите подсчитать записи, которые содержат математический оператор как часть своего содержимого, т.е. ">", "

Например, =CALCULATION(D2:D9, "*>5*") подсчитает все товары в диапазоне D2: D9 с таким содержанием, как "доставка >5 дней" или ">5 товаров на складе".

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

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

 

 

Позвольте мне привести несколько примеров:

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

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

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

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

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

Непустой.

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

CETESLI(диапазон; "*").

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

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

UPDATE(range; "" & "")

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

 

 

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

=COUNTZ(E2:E22).

Пустой.

Если вы хотите подсчитать пустые элементы в диапазоне, вам нужно использовать тот же подход - используйте подстановочный знак для текстовых значений и "" для всех пустых ячеек.

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

CALCULATE( диапазон; "" & "*")

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

Для подсчета пустых ячеек (все типы значений):

=COUNTSLICE(E2:E22;"")

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

=COUNT EMPTY(E2:E22)

Однако не все знают о его существовании. Но теперь вы знаете об этом.

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

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

Что это за пустые ряды? Они также часто появляются при импорте данных из других программ (например, 1C). На первый взгляд в них ничего нет, но на самом деле это не так. Если вы попытаетесь найти такие "пустые строки" (F5 -Select - Empty Cells), они не будут обнаружены. Но фильтр данных видит их как пустые и фильтрует как пустые.

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

Проблема начинается, когда вы пытаетесь произвести с ним какой-либо математический расчет (вычитание, деление, умножение и т.д.). Появится сообщение об ошибке #NAME! В то же время функции SUM и NUM будут игнорировать его, как если бы текст был на месте. Но не похоже, что там есть текст.

И самое интересное - если навести на него курсор мыши и нажать Delete (или вкладка Home - Edit - Clear Content) - он становится действительно пустым, а формулы и другие функции Excel начинают работать с ним безупречно.

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

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

 

 

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

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

=IF(E1=1;10;"")

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

Если вы проверяете некоторые условия с помощью функции IF и планируете выполнять дальнейшие математические операции с результатами, лучше вставить 0 вместо "". В этом случае проблем не возникнет. Вы всегда можете заменить или скрыть нули: Файл - Параметры - Дополнительно - Показать нули в элементах, содержащих нулевые значения.

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

Функция Excel CUTESLI на самом деле не предназначена для подсчета количества ячеек в соответствии с несколькими условиями. В большинстве случаев я рекомендую использовать его эквивалент во множественном числе - функцию SCETESLYMN. Он предназначен для подсчета количества ячеек, соответствующих двум или более условиям (логика AND). Однако некоторые проблемы можно решить, объединив две или более функций SETTING в одно выражение.

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

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

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

 

 

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

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

Когда нужно найти количество нескольких различных элементов в диапазоне, добавьте к выражению 2 или более функций SCALESLY. Предположим, у вас есть список покупок, и вы хотите узнать, сколько безалкогольных напитков в нем.

 

 

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

=CHOTHESLY(A4:A13; "Lemonade")+CHOTHESLY(A2:A11; "*juice")

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

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

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

Еще одно возможное использование функции CALCULATE в Excel - поиск дубликатов в одном столбце, между двумя столбцами или в строке.

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

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

А вторая формула, READ(B2:B24;TRUE), покажет вам, сколько дубликатов имеется:

 

 

Я использовал условное форматирование значения TRUE, чтобы более четко показать, что совпадения были найдены.

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

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

 

 

Скопируйте выражение =CALCULATION($A$2:$A$24;C2) в столбец E.

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

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

=SUMMPROV((COUNT(A2:A24;C2:C24)>0)*(C2:C24""))

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

=SUMMULTIV((COUNTSLICE(A2:A24;C2:C24)=0)*(C2:C24""))

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

Полезно. Если вы хотите выбрать дубликаты записей или целые строки, содержащие дубликаты записей, вы можете создать правила условного форматирования на основе формул SCREEN, как показано в этом уроке - Правила условного форматирования в Excel.

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

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

 

 

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

=SUMMULTIV((COUNT(A2:K2;A2:K2)>1)*(A2:K2""))

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

Вычисление уникальных значений:

=SUMMULTIV((COUNTSLICE(A2:K2;A2:K2)=1)*(A2:K2""))

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

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

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

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

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

Неправильно: =CALCULATION(A2;B3;C4;">0")

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

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

 

 

=СУММ(СЧЁТЕСЛИ(ДВССЫЛ({"B2:B11";"D2:D11"});"=0"))

В качестве альтернативы

=SUM($B2:$B11;0) +SUM($D2:$D11;0)

1. Амперсанд и перевернутые запятые в формулах SCHEDULE

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

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

Поэтому вы можете придерживаться этих правил:

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

= СЧЕТЧИК(A1:A10;10) или = СЧЕТЧИК(A1:A10;C1)

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

= СЧЕТ(A2:A10; "яблоко") или = СЧЕТ(A2:A10; "*") или = СЧЕТ(A2:A10;">5")

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

=CATCHESLI(A2:A10;">"&D2) или =CATCHESLI(A2:A10;"

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

Например, =CALCULATION(C2: C8;"

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

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

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

1. #ErrorIM?

Проблема: Я постоянно получаю ошибку #IMN? Как я могу это исправить?

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

1. Моя формула не работает.

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

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

Будьте очень внимательны к использованию пробелов. При создании одной из формул для этой статьи я уже был готов рвать на себе волосы, потому что правильная конструкция (я точно знал, что она есть!) не работала. Как оказалось, проблема была на виду..... Например, посмотрите на это: =CHOTESLI(A4:A13;" Лимонад"). На первый взгляд в нем нет ничего плохого, кроме лишнего пробела после открывающей кавычки. Программа проглотит все идеально без сообщения об ошибке, предупреждения или любого другого признака. Но если вы действительно хотите посчитать предметы, содержащие слово "Лимонад" и пробел, то вас ждет большое разочарование.

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

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

Больше примеров того, как вычислить сумму:

 

 

Функция СУММПРОИЗВ с примерами формул - В этой статье объясняются основные и расширенные способы использования функции СУММПРОИЗВ в Excel. Вы найдете ряд примеров формул для сравнения массивов, условного суммирования и подсчета ячеек по нескольким условиям, вычисления средневзвешенного значения.

 

 

Сумма по цвету и подсчет по цвету в Excel - В этой статье вы узнаете, как подсчитывать ячейки по цвету и получать сумму по цвету ячеек в Excel. Эти решения работают как для ручного, так и для условного форматирования. Если.

 

 

Формула PROMOTION TOGETHER - основные функции с примерами. - В этой статье объясняются свойства функции СУММЕСЛИ в Excel и показывается, как использовать формулы промежуточных итогов для обобщения данных в видимых ячейках. В предыдущей статье мы рассмотрели, как автоматически вставлять промежуточные итоги.....

 

 

Промежуточные итоги в Excel - В этом уроке объясняется, как использовать инструмент "Промежуточные итоги" в Excel для автоматического суммирования, подсчета или усреднения различных групп ячеек. Вы также узнаете, как отображать или скрывать детали промежуточных итогов, копировать только строки.

 

 

Формула суммы в Excel - несколько полезных советов и примеров - Как быстро и легко вычислять суммы в электронной таблице Excel? Попробуйте разные способы: посмотрите на сумму выделенных ячеек в строке состояния, используйте автосумму для суммирования всех или только нескольких отдельных ячеек.

 

 

Функция SUMMESLIMN - как суммировать ячейки в Excel при наличии нескольких условий? - Руководство объясняет разницу между функциями SUMIF и SUMIFS с точки зрения их синтаксиса и использования, а также приводит примеры формул для суммирования значений с несколькими критериями.

 

 

7 примеров использования формулы SUM в Excel с несколькими условиями - В электронных таблицах Excel можно не просто находить сумму чисел, а делать это в соответствии с заранее заданными критериями отбора. Мы рассмотрим, как правильно использовать функцию SUM (Sumif) в электронных таблицах Excel.

 

 

8 простых способов суммировать столбец в Excel - Как быстро и легко суммировать столбец в Excel? В большинстве случаев нас интересует сумма столбцов или строк. Попробуйте разные способы нахождения суммы столбца, используйте функцию суммы или преобразуйте.