СЧЁТЕСЛИ в Excel — примеры функций с одним и несколькими условиями
В этой статье мы сосредоточимся на функции Excel СЧЁТЕСЛИ (СЧЁТЕСЛИ на английском языке), которая предназначена для подсчета ячеек с определенным условием. Сначала мы кратко рассмотрим синтаксис и общее использование, затем приведем несколько примеров и предупредим вас о возможных причудах при одновременном использовании нескольких критериев или с определенными типами данных.
По сути, они одинаковы во всех версиях, поэтому вы можете использовать примеры в MS Excel 2016, 2013, 2010 и 2007.
Функция СЧЁТЕСЛИ в Excel используется для подсчета количества ячеек в указанном диапазоне, которые соответствуют указанному условию.
Например, вы можете использовать его, чтобы узнать, сколько ячеек на вашем листе содержит число, плюс или минус указанное значение. Другое распространенное использование — подсчет ячеек с определенным словом или определенной буквой или буквами).
СЧЁТЕСЛИ (диапазон; критерий)
Как видите, здесь всего 2 аргумента, оба обязательны:
- диапазон: определяет одну или несколько ячеек для подсчета. Введите диапазон в формулу, как обычно, например A1: A20.
- критерий — определяет условие, определяющее, что именно считать. Это может быть число, текстовая строка, ссылка или выражение. Например, вы можете использовать следующие критерии: «10», A2, «> = 10», «какой-то текст».
Что нужно помнить?
- В аргументе «критерий» условие всегда должно быть написано в кавычках, если не используется ссылка или функция.
- Любой аргумент относится к диапазону другой книги Excel, поэтому эта книга должна быть открытой.
- Буквы не чувствительны к регистру.
- Вы также можете использовать подстановочные знаки * и? (подробнее об этом позже).
- Во избежание ошибок текст не должен содержать непечатаемых символов.
Как видите, синтаксис очень простой. Однако он допускает множество возможных условных вариаций, включая подстановочные знаки, другие значения ячеек и даже другие функции Excel. Такое разнообразие делает функцию СЧЁТЕСЛИ действительно мощной и полезной для многих целей, как вы увидите в примерах ниже.
Примеры работы функции СЧЕТЕСЛИ.
Для подсчета текста.
Давайте узнаем, как это работает. На фото ниже представлен список заказов, выполненных менеджерами. Выражение = СЧЁТЕСЛИ (B2: B22, «Никитенко») вычисляет, сколько раз этот сотрудник был в списке:
Комментарий. Критерий не чувствителен к регистру, поэтому вы можете вводить как прописные, так и строчные буквы.
Если ваши данные содержат более одного варианта слов, которые вы хотите подсчитать, вы можете использовать подстановочные знаки для подсчета всех ячеек, содержащих определенное слово, фразу или букву, как часть своего содержимого.
Например, в нашей таблице несколько клиентов «Корона» из разных городов. Нам нужно посчитать общее количество заказов Crown независимо от города.
= СЧЁТЕСЛИ (A2: A22; «* Коро*»)
Мы подсчитали количество заказов, в которых «коро» фигурирует в имени клиента в любом реестре. Звездочка (*) используется для поиска ячеек с любой последовательностью начальных и конечных символов, как показано в предыдущем примере. Если вам нужно заменить один символ, введите вместо него вопросительный знак (?).
Также не совсем рационально указывать условие прямо в формуле, так как если вам нужно будет рассчитать другие значения, вам нужно будет изменить его. И это не очень удобно.
Рекомендуется записать условие в ячейку, а затем ссылаться на него. Это то, что мы сделали в H9. Вы также можете использовать подстановочные знаки со ссылками, используя оператор конкатенации (&). Например, вместо того, чтобы указывать «* Koro *» непосредственно в формуле, вы можете записать его где-нибудь и использовать следующую конструкцию для подсчета ячеек, содержащих «Koro»:
= СЧЁТЕСЛИ (A2: A22; «*» & H8&»*»)
Подсчет ячеек, начинающихся или заканчивающихся определенными символами
вы можете использовать подстановочный знак звездочки (*) или вопросительного знака (?) В зависимости от того, чего вы хотите достичь.
Если вы хотите узнать количество ячеек, начинающихся или заканчивающихся определенным текстом, независимо от количества других символов, используйте:
= СЧЁТЕСЛИ (A2: A22; «К *») — считывает значения, начинающиеся с «К» .
= COUNTIF (A2: A22; «* p») — считает те, которые заканчиваются буквой «p».
Если вы ищете количество ячеек, которые начинаются или заканчиваются определенными буквами и содержат точное количество символов, поставьте вопросительный знак (?):
= СЧЁТЕСЛИ (C2: C22; «???? д») — находит число с буквой «d» в конце и текст, в котором оно состоит из 5 букв, включая пробелы.
= СЧЁТЕСЛИ (C2: C22; «??») — подсчитывает количество двух символов, включая пробелы.
Примечание. Чтобы узнать количество ячеек, содержащих в тексте вопросительный знак или звездочку, вставьте тильду (~) перед? или *.
Например, = COUNTIF (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. |
В нашем примере
= СЧЁТЕСЛИ (G2: D22; «> 10000»)
Мы подсчитываем количество крупных заказов более 10 000. Обратите внимание, что здесь мы записываем условие подсчета в виде текстовой строки, а затем заключаем его в кавычки.
Вы также можете использовать все вышеперечисленные параметры для подсчета ячеек на основе значения другой ячейки. Вам просто нужно заменить номер ссылкой.
Комментарий. При использовании ссылки необходимо заключить математический оператор в кавычки и добавить перед ним амперсанд (&). Например, чтобы подсчитать числа в диапазоне D2: D9, которые больше D3, используйте = COUNTIF (D2: D9, «>» & D3)
Если вы хотите подсчитать записи, которые содержат математический оператор как часть своего содержимого, то есть символ «>», «
Например, = COUNTIF (D2: D9, «*> 5 *») будет подсчитывать все товары в диапазоне D2: D9 с таким содержанием, как «Доставка> 5 дней» или «> 5 единиц на складе».
Примеры с датами.
Если вы хотите подсчитать ячейки с датами больше, меньше или равными указанной дате, вы можете использовать уже знакомый метод, используя формулы, подобные тем, которые мы обсуждали выше. Все вышеперечисленное работает как для дат, так и для чисел.
Приведу несколько примеров:
критерии |
Описание |
|
Даты равные указанной дате. |
= СЧЁТЕСЛИ (E2: E22, «01.02.2019″) |
Подсчитывает количество ячеек в диапазоне E2: E22 с датой 1 июня 2014 г. |
Даты больше или равны другой дате. |
= СЧЁТЕСЛИ (E2: E22, «> = 01.02.2019») |
Подсчитайте количество ячеек в диапазоне E2: E22 с датой больше или равной 1/6/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 вы можете встретить советы по подсчету непустых ячеек, например:
СЧЁТЕСЛИ (диапазон;»*»)
Но дело в том, что приведенное выше выражение учитывает только ячейки, содержащие текстовые значения. Это означает, что те, которые включают даты и числа, будут рассматриваться как пустые (игнорироваться) и не будут включены в общую сумму!
Если вам нужно универсальное решение для подсчета всех непустых ячеек в указанном диапазоне, введите:
СЧЁТЕСЛИ (диапазон;»» & «»)
Он правильно работает со всеми типами значений — текстом, датами и числами — как вы можете видеть на изображении ниже.
Также можно подсчитать непустые ячейки в диапазоне:
= СЧЁТ (MI2: MI22).
Пустой.
Если вы хотите подсчитать пустые позиции в определенном диапазоне, вы должны следовать тому же подходу: использовать подстановочные знаки для текстовых значений в условиях и параметр «» для подсчета всех пустых ячеек.
Подсчитаем ячейки, в которых нет текста:
СЧЁТЕСЛИ (диапазон; «» & «*»)
Поскольку звездочка (*) соответствует любой последовательности текстовых символов, учитываются ячейки, которые не равны *, т. Е. Не содержат текста в указанном диапазоне.
Чтобы подсчитать пустые ячейки (все типы значений):
= СЧЁТЕСЛИ (E2: E22;»»)
Конечно, для этих случаев есть специальная функция
= СЧЁТ (E2: E22)
Но не все знают о его существовании. Но теперь вы знаете …
Нулевые строки.
Также обратите внимание, что COUNTIF и COUNTBLANK подсчитывают ячейки с пустыми строками, которые кажутся пустыми только на первый взгляд.
Что это за пустые строки? Также они часто появляются при импорте данных из других программ (например, 1С). Внешне в них ничего нет, но на самом деле их нет. Если попытаться найти такие «пустышки» (F5 -Выбрать — Пустые ячейки) — они не обнаруживаются. Но слайсер видит их как пустые и фильтрует их как пустые.
Дело в том, что существует такая вещь, как «строка нулевой длины» (или «нулевая строка»). Пустая строка появляется, когда программе нужно вставить значение, но вставлять нечего.
Проблемы начинаются, когда вы пытаетесь провести с ним какую-то математику (вычитание, деление, умножение и т.д.). Вы получаете сообщение об ошибке #VALUE! В этом случае функции СУММ и СЧЁТ игнорируют их, как если бы это был какой-то текст. А внешне нет.
И самое интересное, что если вы наведете на него указатель мыши и нажмете «Удалить» (или вкладку «Главная» — «Изменить» — «Удалить содержимое»), то он станет действительно пустым, и формулы Excel и другие функции начнут работать с ним без ошибок.
Если вы не хотите считать их пустыми, используйте следующее выражение для подсчета фактических пустых ячеек:
= СТРОКИ (E2: E22) * ЧИСЛОСТОЛБЕЦ (E2: E22) -СЧЕТЕСЛИ (E2: E22;»»&»»)
Откуда берутся пустые строки в ячейках? Здесь может быть несколько вариантов:
- Изначально он есть, потому что так настроена загрузка и создание файлов в сторонней программе (например, 1С). В некоторых случаях такие стоки настроены таким образом, что нет пустых ячеек как таковых — они просто заполняются строкой нулевой длины.
- была создана формула, производящая текст нулевой длины. Самый простой случай:
= ЕСЛИ (MI1 = 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. Сколько совпадений между двумя столбцами?
Сравните list2 со list1. В столбце E мы последовательно берем каждое значение из list2 и подсчитываем, сколько раз оно появляется в list1. Если совпадений нет, это уникальное значение. На рисунке они выделены цветом с использованием условного форматирования.
Выражение = СЧЁТЕСЛИ ($ A $ 2: $ A $ 24, C2) копировать столбец E.
вы можете выполнить аналогичный расчет и наоборот: взять значения из первого списка и искать дубликаты во втором.
вы можете использовать комбинацию функций СУММПРОИЗВ и СЧЁТЕСЛИ, чтобы легко определить количество дубликатов.
= СУММПРОИЗВ ((СЧЁТЕСЛИ (A2: A24; C2: C24)> 0) * (C2: C24″»))
Подсчитаем количество уникальных значений в list2:
= СУММПРОИЗВ ((СЧЁТЕСЛИ (A2: A24; C2: C24) = 0) * (C2: C24″»))
Получаем 7 уникальных записей и 16 дубликатов, как видно на рисунке.
Полезный. Если вы хотите выделить повторяющиеся позиции или целые строки, содержащие повторяющиеся записи, вы можете создать правила условного форматирования на основе формул COUNTIF, как показано в этом руководстве — Правила условного форматирования Excel.
3. Сколько дубликатов и уникальных значений в строке?
Если вам нужно подсчитать повторяющиеся или уникальные значения в определенной строке вместо столбца, используйте одну из следующих формул. Они могут быть полезны, например, для анализа истории розыгрыша лотереи.
Подсчитаем количество дубликатов:
= СУММПРОИЗВ ((СЧЁТЕСЛИ (A2: K2; A2: K2)> 1) * (A2: K2″»))
Мы видим, что 13 сняли 2 раза.
Подсчитайте уникальные значения:
= СУММПРОИЗВ ((СЧЁТЕСЛИ (A2: K2; A2: K2) = 1) * (A2: K2″»))
Часто задаваемые вопросы и проблемы.
Я надеюсь, что эти примеры помогли вам получить представление о функции Excel СЧЁТЕСЛИ. Если вы пробовали использовать любую из приведенных выше формул для своих данных и не смогли заставить их работать, или у вас возникла проблема, ознакомьтесь со следующими 5 наиболее распространенными проблемами. Есть большая вероятность, что вы найдете там ответ или полезный совет.
- можно ли считать в диапазоне несмежных ячеек?
Вопрос: Как я могу использовать СЧЁТЕСЛИ для несмежного диапазона или ячеек?
Ответ: Он не работает с несмежными диапазонами, синтаксис не позволяет указывать несколько одиночных ячеек в качестве первого параметра. Вместо этого вы можете использовать комбинацию нескольких функций СЧЁТЕСЛИ:
Неправильно: = СЧЁТЕСЛИ (A2; B3; C4; «> 0»)
Правильно: = СЧЁТЕСЛИ (A2; «> 0») + СЧЁТЕСЛИ (B3; «> 0») + СЧЁТЕСЛИ (C4, «> 0»)
Альтернативный способ — использовать функцию ДВССЫЛ для создания массива несмежных ячеек. Например, оба следующих варианта дают тот же результат, который вы видите на изображении:
= СУММ (СЧЁТЕСЛИ (КОСВЕННО ({«B2: B11», «D2: D11»}), «= 0»))
ИЛИ
= СЧЁТЕСЛИ ($ B2: $ B11,0) + СЧЁТЕСЛИ ($ D2: $ D11,0)
- Амперсанд и кавычки в формулах СЧЁТЕСЛИ
В: Когда мне следует использовать амперсанд?
Ответ: Это, наверное, самая сложная часть функции СЧЁТЕСЛИ, которая меня тоже смущает. Хотя, если подумать, вы увидите: амперсанд и кавычки необходимы для построения текстовой строки для аргумента.
Итак, вы можете придерживаться этих правил:
- Если вы используете число или ссылку на ячейку в критериях точного соответствия, амперсанды или кавычки не требуются. Например:
= СЧЁТЕСЛИ (A1: A10; 10) или = СЧЁТЕСЛИ (A1: A10; C1)
- Если условия содержат текст, подстановочный знак или логический оператор с числом, заключите его в кавычки. Например:
= СЧЁТЕСЛИ (A2: A10; «яблоко») o = СЧЁТЕСЛИ (A2: A10; «*») o = СЧЁТЕСЛИ (A2: A10, «> 5″)
- Если ваш критерий является ссылочным выражением или какой-либо другой функцией Excel, вы должны использовать кавычки («»), чтобы начать текстовую строку, и амперсанд (&), чтобы объединить (объединить) и закончить строку. Например:
= СЧЁТЕСЛИ (A2: A10, «>» & D2) или = СЧЁТЕСЛИ (A2: A10, «
Если вы не уверены, нужен ли вам амперсанд или нет, попробуйте оба способа. В большинстве случаев амперсанд работает нормально.
Например, = СЧЁТЕСЛИ (C2: C8; «
- Как посчитать клетки по цвету?
Вопрос: Как посчитать ячейки по цвету заливки или шрифта, а не по значениям?
Ответ: К сожалению, синтаксис функции не позволяет использовать форматы в качестве условия. Единственный возможный способ суммирования ячеек на основе их цвета — использовать макрос или, точнее, пользовательскую функцию Excel VBA.
- Ошибка # ИМЯ?
Проблема: я продолжаю получать ошибку #NAME? Как я могу решить эту проблему?
Ответ: Скорее всего, вы указали не тот диапазон. Пожалуйста, проверьте пункт 1 выше.
- Формула не работает
Проблема: моя формула не работает! Что я сделал не так?
Ответ: Если вы написали формулу, которая на первый взгляд кажется правильной, но не работает или дает неправильный результат, начните с проверки наиболее очевидных вещей, таких как диапазоны, условия, ссылки, амперсанды и кавычки.
Будьте очень осторожны с пробелами. Когда я создал одну из формул для этой статьи, я был готов выдернуть волосы, потому что правильный дизайн (я точно знал, что он правильный!) Не работал. Как оказалось, проблема была на поверхности… Например, посмотрите на это: = СЧЁТЕСЛИ (A4: A13; «Лимонад»). На первый взгляд, нет ничего плохого в лишнем пробеле после вступительной цитаты. Программа отлично проглотит все без сообщения об ошибке, предупреждения или какой-либо другой индикации. Но если вы действительно хотите посчитать продукты, в которых есть слово «Лимонад» и основное пробел, то вы будете очень разочарованы….
Если вы используете функцию с несколькими критериями, разделите формулу на несколько частей и проверьте каждую часть отдельно.
На сегодня все.