Функция СЧЁТЕСЛИМН
Функция СЧЁТЕСЛИМН применяет критерии к ячейкам в нескольких диапазонах и вычисляет количество соответствий всем критериям.
Это видео — часть учебного курса Усложненные функции ЕСЛИ.
Синтаксис
Аргументы функции СЧЁТЕСЛИМН описаны ниже.
Диапазон_условия1. Обязательный аргумент. Первый диапазон, в котором необходимо проверить соответствие заданному условию.
Условие1. Обязательный аргумент. Условие в форме числа, выражения, ссылки на ячейку или текста, которые определяют, какие ячейки требуется учитывать. Например, условие может быть выражено следующим образом: 32, «>32», B4, «яблоки» или «32».
Диапазон_условия2, условие2. Необязательный аргумент. Дополнительные диапазоны и условия для них. Разрешается использовать до 127 пар диапазонов и условий.
Важно: Каждый дополнительный диапазон должен состоять из такого же количества строк и столбцов, что и аргумент диапазон_условия1. Эти диапазоны могут не находиться рядом друг с другом.
Замечания
Каждое условие диапазона одновременно применяется к одной ячейке. Если все первые ячейки соответствуют требуемому условию, счет увеличивается на 1. Если все вторые ячейки соответствуют требуемому условию, счет еще раз увеличивается на 1, и это продолжается до тех пор, пока не будут проверены все ячейки.
Если аргумент условия является ссылкой на пустую ячейку, то он интерпретируется функцией СЧЁТЕСЛИМН как значение 0.
В условии можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому одиночному символу; звездочка — любой последовательности символов. Если нужно найти сам вопросительный знак или звездочку, поставьте перед ними знак тильды (
Пример 1
Скопируйте образец данных из следующих таблиц и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
В этой статье мы сосредоточимся на функции Excel СЧЕТЕСЛИ (COUNTIF в английском варианте), которая предназначена для подсчета ячеек с определённым условием. Сначала мы кратко рассмотрим синтаксис и общее использование, а затем я приведу ряд примеров и предупрежу о возможных причудах при подсчете по нескольким критериям одновременно или же с определёнными типами данных.
По сути,они одинаковы во всех версиях, поэтому вы можете использовать примеры в MS Excel 2016, 2013, 2010 и 2007.
Функция Excel СЧЕТЕСЛИ применяется для подсчета количества ячеек в указанном диапазоне, которые соответствуют определенному условию.
СЧЕТЕСЛИ(диапазон; критерий)
Как видите, здесь только 2 аргумента, оба из которых являются обязательными:
Что нужно обязательно запомнить?
Как видите, синтаксис очень прост. Однако, он допускает множество возможных вариаций условий, в том числе символы подстановки, значения других ячеек и даже другие функции Excel. Это разнообразие делает функцию СЧЕТЕСЛИ действительно мощной и пригодной для многих задач, как вы увидите в следующих примерах.
Примеры работы функции СЧЕТЕСЛИ.
Для подсчета текста.
Давайте разбираться, как это работает. На рисунке ниже вы видите список заказов, выполненных менеджерами. Выражение =СЧЕТЕСЛИ(В2:В22,»Никитенко») подсчитывает, сколько раз этот работник присутствует в списке:
Замечание. Критерий не чувствителен к регистру букв, поэтому можно вводить как прописные, так и строчные буквы.
Если ваши данные содержат несколько вариантов слов, которые вы хотите сосчитать, то вы можете использовать подстановочные знаки для подсчета всех ячеек, содержащих определенное слово, фразу или буквы, как часть их содержимого.
К примеру, в нашей таблице есть несколько заказчиков «Корона» из разных городов. Нам необходимо подсчитать общее количество заказов «Корона» независимо от города.
Мы подсчитали количество заказов, где в наименовании заказчика встречается «коро» в любом регистре. Звездочка (*) используется для поиска ячеек с любой последовательностью начальных и конечных символов, как показано в приведенном выше примере. Если вам нужно заменить какой-либо один символ, введите вместо него знак вопроса (?).
Кроме того, указывать условие прямо в формуле не совсем рационально, так как при необходимости подсчитать какие-то другие значения вам придется корректировать её. А это не слишком удобно.
Рекомендуется условие записывать в какую-либо ячейку и затем ссылаться на нее. Так мы сделали в H9. Также можно употребить подстановочные знаки со ссылками с помощью оператора конкатенации (&). Например, вместо того, чтобы указывать «* Коро *» непосредственно в формуле, вы можете записать его куда-нибудь, и использовать следующую конструкцию для подсчета ячеек, содержащих «Коро»:
Подсчет ячеек, начинающихся или заканчивающихся определенными символами
Вы можете употребить подстановочный знак звездочку (*) или знак вопроса (?) в зависимости от того, какого именно результата вы хотите достичь.
Если вы хотите узнать количество ячеек, которые начинаются или заканчиваются определенным текстом, независимо от того, сколько имеется других символов, используйте:
Если вы ищете количество ячеек, которые начинаются или заканчиваются определенными буквами и содержат точное количество символов, то поставьте вопросительный знак (?):
Примечание. Чтобы узнать количество клеток, содержащих в тексте знак вопроса или звездочку, введите тильду (
?*») будут подсчитаны все позиции, содержащие знак вопроса в диапазоне С2:С22.
Подсчет чисел по условию.
В отношении чисел редко случается, что нужно подсчитать количество их, равных какому-то определённому числу. Тем не менее, укажем, что записать нужно примерно следующее:
Гораздо чаще нужно высчитать количество значений, больших либо меньших определенной величины.
Чтобы подсчитать значения, которые больше, меньше или равны указанному вами числу, вы просто добавляете соответствующий критерий, как показано в таблице ниже.
критерии
Описание
Подсчитайте, где значение больше 5.
Подсчет со числами менее 5.
Определите, сколько раз значение равно 5.
Подсчитайте, сколько раз не равно 5.
Если больше или равно
Подсчет, когда больше или равно 5.
Если меньше или равно
Считаем количество крупных заказов на сумму более 10 000. Обратите внимание, что условие подсчета мы записываем здесь в виде текстовой строки и поэтому заключаем его в двойные кавычки.
Вы также можете использовать все вышеприведенные варианты для подсчета ячеек на основе значения другой ячейки. Вам просто нужно заменить число ссылкой.
Замечание. В случае использования ссылки, вы должны заключить математический оператор в кавычки и добавить амперсанд (&) перед ним. Например, чтобы подсчитать числа в диапазоне D2: D9, превышающие D3, используйте =СЧЕТЕСЛИ(D2:D9,»>»&D3)
Если вы хотите сосчитать записи, которые содержат математический оператор, как часть их содержимого, то есть символ «>», « 5*») будет подсчитывать все позиции в диапазоне D2: D9 с таким содержимым, как «Доставка >5 дней» или «>5 единиц в наличии».
Примеры с датами.
Если вы хотите сосчитать клетки с датами, которые больше, меньше или равны указанной вами дате, вы можете воспользоваться уже знакомым способом, используя формулы, аналогичные тем, которые мы обсуждали чуть выше. Все вышеприведенное работает как для дат, так и для чисел.
Позвольте привести несколько примеров:
критерии
Описание
Даты, равные указанной дате.
Подсчитывает количество ячеек в диапазоне E2:E22 с датой 1 июня 2014 года.
Даты больше или равные другой дате.
Сосчитайте количество ячеек в диапазоне E2:E22 с датой, большей или равной 01.06.2014.
Даты, которые больше или равны дате в другой ячейке, минус X дней.
Определите количество ячеек в диапазоне E2:E22 с датой, большей или равной дате в H2, минус 7 дней.
Помимо этих стандартных способов, вы можете употребить функцию СЧЕТЕСЛИ в сочетании с функциями даты и времени, например, СЕГОДНЯ(), для подсчета ячеек на основе текущей даты.
критерии
Равные текущей дате.
До текущей даты, то есть меньше, чем сегодня.
После текущей даты, т.е. больше, чем сегодня.
Даты, которые должны наступить через неделю.
В определенном диапазоне времени.
Как посчитать количество пустых и непустых ячеек?
Посмотрим, как можно применить функцию СЧЕТЕСЛИ в Excel для подсчета количества пустых или непустых ячеек в указанном диапазоне.
Непустые.
В некоторых руководствах по работе с СЧЕТЕСЛИ вы можете встретить предложения для подсчета непустых ячеек, подобные этому:
Но дело в том, что приведенное выше выражение подсчитывает только клетки, содержащие любые текстовые значения. А это означает, что те из них, что включают даты и числа, будут обрабатываться как пустые (игнорироваться) и не войдут в общий итог!
Если вам нужно универсальное решение для подсчета всех непустых ячеек в указанном диапазоне, то введите:
Также непустые ячейки в диапазоне можно подсчитать:
Пустые.
Считаем клетки, не содержащие текст:
Поскольку звездочка (*) соответствует любой последовательности текстовых символов, в расчет принимаются клетки, не равные *, т.е. не содержащие текста в указанном диапазоне.
Для подсчета пустых клеток (все типы значений):
Конечно, для таких случаев есть и специальная функция
Но не все знают о ее существовании. Но вы теперь в курсе …
Нулевые строки.
Также имейте в виду, что СЧЕТЕСЛИ и СЧИТАТЬПУСТОТЫ считают ячейки с пустыми строками, которые только на первый взгляд выглядят пустыми.
Дело в том, что существует такое понятие, как «строка нулевой длины» (или «нулевая строка»). Нулевая строка возникает, когда программе нужно вставить какое-то значение, а вставить нечего.
Проблемы начинаются тогда, когда вы пытаетесь с ней произвести какие-то математические вычисления (вычитание, деление, умножение и т.д.). Получите сообщение об ошибке #ЗНАЧ!. При этом функции СУММ и СЧЕТ их игнорируют, как будто там находится текст. А внешне там его нет.
Если вы не хотите рассматривать их как пустые, используйте для подсчета реально пустых клеток следующее выражение:
Откуда могут появиться нулевые строки в ячейках? Здесь может быть несколько вариантов:
В итоге, если в Е1 записано что угодно, отличное от 1, программа вернет строку нулевой длины. И если впоследствии формулу заменять значением (Специальная вставка – Значения), то получим нашу псевдо-пустую позицию.
СЧЕТЕСЛИ с несколькими условиями.
Количество чисел в диапазоне
Одним из наиболее распространенных применений функции СЧЕТЕСЛИ с двумя критериями является определение количества чисел в определенном интервале, т.е. меньше X, но больше Y.
Например, вы можете использовать для вычисления ячеек в диапазоне B2: B9, где значение больше 5 и меньше или равно 15:
Количество ячеек с несколькими условиями ИЛИ.
Когда вы хотите найти количество нескольких различных элементов в диапазоне, добавьте 2 или более функций СЧЕТЕСЛИ в выражение. Предположим, у вас есть список покупок, и вы хотите узнать, сколько в нем безалкогольных напитков.
Обратите внимание, что мы включили подстановочный знак (*) во второй критерий. Он используется для вычисления количества всех видов сока в списке.
Как вы понимаете, сюда можно добавить и больше условий.
Использование СЧЕТЕСЛИ для подсчета дубликатов.
1. Ищем дубликаты в одном столбце
Эта простое выражение СЧЁТЕСЛИ($A$2:$A$24;A2)>1 найдет все одинаковые записи в A2: A24.
А другая формула СЧЁТЕСЛИ(B2:B24;ИСТИНА) сообщит вам, сколько существует дубликатов:
Для более наглядного представления найденных совпадений я использовал условное форматирование значения ИСТИНА.
2. Сколько совпадений между двумя столбцами?
Сравним список2 со списком1. В столбце Е берем последовательно каждое значение из списка2 и считаем, сколько раз оно встречается в списке1. Если совпадений ноль, значит это уникальное значение. На рисунке такие выделены цветом при помощи условного форматирования.
Выражение =СЧЁТЕСЛИ($A$2:$A$24;C2) копируем вниз по столбцу Е.
Аналогичный расчет можно сделать и наоборот – брать значения из первого списка и искать дубликаты во втором.
Для того, чтобы просто определить количество дубликатов, можно использовать комбинацию функций СУММПРОИЗВ и СЧЕТЕСЛИ.
Подсчитаем количество уникальных значений в списке2:
Получаем 7 уникальных записей и 16 дубликатов, что и видно на рисунке.
3. Сколько дубликатов и уникальных значений в строке?
Если нужно сосчитать дубликаты или уникальные значения в определенной строке, а не в столбце, используйте одну из следующих формул. Они могут быть полезны, например, для анализа истории розыгрыша лотереи.
Считаем количество дубликатов:
Видим, что 13 выпадало 2 раза.
Подсчитать уникальные значения:
Часто задаваемые вопросы и проблемы.
Я надеюсь, что эти примеры помогли вам почувствовать функцию Excel СЧЕТЕСЛИ. Если вы попробовали какую-либо из приведенных выше формул в своих данных и не смогли заставить их работать или у вас возникла проблема, взгляните на следующие 5 наиболее распространенных проблем. Есть большая вероятность, что вы найдете там ответ или же полезный совет.
Вопрос: Как я могу использовать СЧЕТЕСЛИ для несмежного диапазона или ячеек?
Ответ: Она не работает с несмежными диапазонами, синтаксис не позволяет указывать несколько отдельных ячеек в качестве первого параметра. Вместо этого вы можете использовать комбинацию нескольких функций СЧЕТЕСЛИ:
Правильно: = СЧЕТЕСЛИ (A2;»>0″) + СЧЕТЕСЛИ (B3;»>0″) + СЧЕТЕСЛИ (C4;»>0″)
Вопрос: когда мне нужно использовать амперсанд?
Итак, вы можете придерживаться этих правил:
= СЧЕТЕСЛИ(A1:A10;10) или = СЧЕТЕСЛИ(A1:A10;C1)
= СЧЕТЕСЛИ(A2:A10;»яблоко») или = СЧЕТЕСЛИ(A2:A10;»*») или = СЧЕТЕСЛИ(A2:A10;»>5″)
Функция СЧЕТЕСЛИ в Excel и примеры ее использования
Функция СЧЕТЕСЛИ входит в группу статистических функций. Позволяет найти число ячеек по определенному критерию. Работает с числовыми и текстовыми значениями, датами.
Синтаксис и особенности функции
Сначала рассмотрим аргументы функции:
В диапазоне ячеек могут находиться текстовые, числовые значения, даты, массивы, ссылки на числа. Пустые ячейки функция игнорирует.
В качестве критерия может быть ссылка, число, текстовая строка, выражение. Функция СЧЕТЕСЛИ работает только с одним условием (по умолчанию). Но можно ее «заставить» проанализировать 2 критерия одновременно.
Рекомендации для правильной работы функции:
Функция СЧЕТЕСЛИ в Excel: примеры
Посчитаем числовые значения в одном диапазоне. Условие подсчета – один критерий.
У нас есть такая таблица:
Посчитаем количество ячеек с числами больше 100. Формула: =СЧЁТЕСЛИ(B1:B11;»>100″). Диапазон – В1:В11. Критерий подсчета – «>100». Результат:
Если условие подсчета внести в отдельную ячейку, можно в качестве критерия использовать ссылку:
Посчитаем текстовые значения в одном диапазоне. Условие поиска – один критерий.
Формула: =СЧЁТЕСЛИ(A1:A11;»табуреты»). Или:
Во втором случае в качестве критерия использовали ссылку на ячейку.
Формула с применением знака подстановки: =СЧЁТЕСЛИ(A1:A11;»таб*»).
Для расчета количества значений, оканчивающихся на «и», в которых содержится любое число знаков: =СЧЁТЕСЛИ(A1:A11;»*и»). Получаем:
Формула посчитала «кровати» и «банкетки».
Используем в функции СЧЕТЕСЛИ условие поиска «не равно».
Формула: =СЧЁТЕСЛИ(A1:A11;»<>«&»стулья»). Оператор «<>» означает «не равно». Знак амперсанда (&) объединяет данный оператор и значение «стулья».
При применении ссылки формула будет выглядеть так:
Часто требуется выполнять функцию СЧЕТЕСЛИ в Excel по двум критериям. Таким способом можно существенно расширить ее возможности. Рассмотрим специальные случаи применения СЧЕТЕСЛИ в Excel и примеры с двумя условиями.
СЧЕТЕСЛИ с двумя условиями в Excel очень часто используется для автоматизированной и эффективной работы с данными. Поэтому продвинутому пользователю настоятельно рекомендуется внимательно изучить все приведенные выше примеры.
ПРОМЕЖУТОЧНЫЕ.ИТОГИ и СЧЕТЕСЛИ
Посчитаем количество реализованных товаров по группам.
Формула нашла количество значений для группы «Стулья». При большом числе строк (больше тысячи) подобное сочетание функций может оказаться полезным.
Функция СЧЁТЕСЛИМН в Excel с несколькими условиями — объясняем на примерах.
Начиная с версии Excel 2007, Microsoft добавила в Excel «старших сестер» функциям выборочного подсчета СУММЕСЛИ, СЧЁТЕСЛИ и СРЗНАЧЕСЛИ – функции СУММЕСЛИМН, СЧЁТЕСЛИМН и СРЗНАЧЕСЛИМН. В английском варианте эти функции выглядят как SUMIFS, COUNTIFS и AVERAGEIFS, т.е. имеют на конце букву -S, обозначающую в английском языке множественное число. В русской версии эту роль играет -МН.
Их часто путают, поскольку они очень похожи друг на друга и предназначены для подсчета на основе указанных критериев.
Разница в том, что СЧЕТЕСЛИ предназначен для подсчета ячеек с одним условием в одном диапазоне, тогда как СЧЕТЕСЛИМН может оценивать разные критерии в одном и том же или в разных диапазонах.
Как работает функция СЧЕТЕСЛИМН?
Она вычисляет количество соответствий в нескольких диапазонах на основе одного или множества критериев.
Синтаксис функции выглядит следующим образом:
СЧЕТЕСЛИМН(диапазон1;условие1; [диапазон2;условие2]…)
На самом деле, вам не нужно помнить этот синтаксис наизусть. Microsoft Excel отобразит аргументы функции, как только вы начнете печатать; аргумент, который вы вводите в данный момент, будет выделен жирным шрифтом.
Что нужно запомнить?
Считаем с учетом всех критериев (логика И).
Этот вариант является самым простым, поскольку функция СЧЕТЕСЛИМН предназначена для подсчета только тех ячеек, для которых все указанные параметры имеют значение ИСТИНА. Мы называем это логикой И, потому что логическая функция И работает таким же образом.
Предположим, у вас есть список товаров, как показано на скриншоте ниже. Вы хотите узнать количество товаров, которые есть в наличии (у них значение в столбце B больше 0), но еще не были проданы (значение в столбце D равно 0).
Задача может быть выполнена таким образом:
Видим, что 2 товара (крыжовник и ежевика) находятся на складе, но не продаются.
б. Одинаковый критерий для всех диапазонов.
Если вы хотите посчитать элементы с одинаковыми критериями, вам все равно нужно указывать каждую пару диапазон/условие отдельно.
Например, вот правильный подход для подсчета элементов, которые имеют 0 как в столбце B, так и в столбце D:
Получаем 1, потому что только Слива имеет значение «0» в обоих столбцах.
Если достаточно выполнения хотя бы одного условия (логика ИЛИ).
Как вы видели в приведенных выше примерах, подсчет ячеек, отвечающих всем указанным критериям, прост, поскольку функция СЧЕТЕСЛИМН как раз и предназначена для такой работы.
Способ 1. Две или более формулы СЧЕТЕСЛИ или СЧЕТЕСЛИМН.
Подсчитаем заказы со статусами «Отменено» и «Ожидание». Чтобы сделать это, вы можете просто написать 2 обычные формулы СЧЕТЕСЛИ и затем сложить результаты:
В случае, если нужно оценить более одного параметра отбора, используйте СЧЕТЕСЛИМН.
Чтобы получить количество «отмененных» и «отложенных» заказов для клубники, используйте такой вариант:
Способ 2. СУММ+СЧЁТЕСЛИМН с константой массива.
Вставьте СЧЕТЕСЛИМН в функцию СУММ, вот так:
В нашей таблице с примерами для подсчета заказов со статусом «Отменено» или «Ожидание» расчет будет выглядеть следующим образом:
Массив означает, что в начале ищем все отмененные заказы, потом ожидающие. Получается массив из двух цифр итогов. А затем функция СУММ просто их складывает.
Аналогичным образом вы можете использовать две или более пары диапазон/условие. Чтобы вычислить количество заказов на клубнику, которые отменены или в стадии ожидания, используйте это выражение:
Как сосчитать числа в интервале.
1. СЧЕТЕСЛИМН для подсчета ячеек между двумя числами
Чтобы узнать, сколько было получено заказов количеством товара от 10 до 20, сделаем так:
Это выражение будет возвращать то же количество, как показано на рисунке выше.
Как использовать ссылки в формулах СЧЕТЕСЛИМН.
При использовании логических операторов, таких как «>», » =» вместе со ссылками на ячейки, не забудьте заключить оператор в «двойные кавычки» и добавить амперсанд (&) перед ссылкой. Иначе говоря, требование к отбору должно быть представлено в виде текста, заключенного в двойные кавычки.
В приведенном примере посчитаем заказы с количеством более 30 единиц, при том что на складе в наличии было менее 50 единиц товара.
если вы записали значения ограничений в определенные клетки, скажем, в G1 и G2, и ссылаетесь на них.
Как использовать СЧЕТЕСЛИМН со знаками подстановки.
Традиционно можно применять следующие символы подстановки:
Примечание. Если вы хотите сосчитать ячейки, в которых есть знак вопроса или звездочка просто как буквы, введите тильду (
) перед звездочкой или знаком вопроса в записи параметра поиска.
Теперь давайте посмотрим, как вы можете использовать символ подстановки.
Предположим, у вас есть список заказов, за которыми персонально закреплены менеджеры. Вы хотите знать, сколько заказов уже кому-то назначено и при этом установлен срок их выполнения. Иначе говоря, имеются ли какие-то значения в столбцах B и Е таблицы.
Нам необходимо узнать количество заказов, для которых заполнены столбцы B и Е:
Несколько условий в виде даты.
Правила работы с датами очень похожи на рассмотренные выше вычисления с числами.
1.Подсчет дат в определенном интервале.
Для подсчета дат, попадающих в определенный временной интервал, вы также можете использовать СЧЕТЕСЛИМН с двумя критериями или же комбинацию двух функций СЧЕТЕСЛИ.
Следующие выражения подсчитывают в области с D2 по D21 количество дат, приходящихся на период с 1 по 7 февраля 2020 года включительно:
=СЧЁТЕСЛИМН(D2:D21;»>=01.02.2020″;D2:D21;» =»&H3;D2:D21;»
Как обычно, запишем двумя способами: со ссылками и без них:
3. Подсчет дат с различными критериями на основе текущей даты
Вы можете использовать функцию СЕГОДНЯ() для подсчета дат по отношению к сегодняшнему дню.
Эта формула с двумя областями и двумя критериями ответит вам, сколько товаров уже куплено, но еще не доставлено.
Она допускает множество возможных вариаций. В частности, вы можете настроить ее, чтобы подсчитать, сколько заказов было оформлено более недели назад и пока еще не доставлено:
Вот такими способами можно сосчитать ячейки, удовлетворяющие различным условиям.
Я надеюсь, что вы найдете эти примеры и советы полезными. В любом случае, я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге ещё не раз.