С помощью статистической функции СЧЁТЕСЛИ можно подсчитать количество ячеек, отвечающих определенному условию (например, число клиентов в списке из определенного города).
Самая простая функция СЧЁТЕСЛИ означает следующее:
- =СЧЁТЕСЛИ(где нужно искать;что нужно найти)
Например:
- =СЧЁТЕСЛИ(A2:A5;"Лондон")
- =СЧЁТЕСЛИ(A2:A5;A4)
Синтаксис
СЧЁТЕСЛИ(диапазон;критерий)
| Имя аргумента | Описание |
|---|---|
| диапазон (обязательный) | Группа ячеек, для которых нужно выполнить подсчет.
Диапазон может содержать числа, массивы, именованный диапазон или ссылки, содержащие числа. Пустые и текстовые значения игнорируются. Узнайте, как выбирать диапазоны на листе. |
| критерий (обязательный) | Число, выражение, ссылка на ячейку или текстовая строка, которая определяет, какие ячейки нужно подсчитать. Например, можно использовать число, например 32, сравнение типа ">32", ячейку, например B4, или слово, например "яблоки". В функции СЧЁТЕСЛИ используется только один критерий. Чтобы провести подсчет по нескольким условиям, воспользуйтесь функцией СЧЁТЕСЛИМН. |
Примеры использования COUNTIF в Excel
Чтобы использовать эти примеры в Excel, скопируйте данные из приведенной ниже таблицы и вставьте их на новый лист в ячейку A1.
| Данные. | Данные. |
|---|---|
| яблоки | 32 |
| апельсины | 54 |
| персики | 75 |
| яблоки | 86 |
| Формула | Описание |
| =СЧЁТЕСЛИ(A2:A5;"яблоки") | Количество ячеек, содержащих текст "яблоки" в ячейках А2–А5. Результат — 2. |
| =СЧЁТЕСЛИ(A2:A5;A4) | Количество ячеек, содержащих текст "персики" (значение ячейки A4) в ячейках А2–А5. Результат — 1. |
| =СЧЁТЕСЛИ(A2:A5;A2)+СЧЁТЕСЛИ(A2:A5;A3) | Количество ячеек, содержащих текст "яблоки" (значение ячейки A2) и "апельсины" (значение ячейки A3) в ячейках А2–А5. Результат — 3. В этой формуле для указания нескольких критериев, по одному критерию на выражение, функция СЧЁТЕСЛИ используется дважды. Также можно использовать функцию СЧЁТЕСЛИМН. |
| =СЧЁТЕСЛИ(B2:B5;">55") | Количество ячеек со значением больше 55 в ячейках В2–В5. Результат — 2. |
| =СЧЁТЕСЛИ(B2:B5;"<>"&B4) | Количество ячеек со значением, не равным 75, в ячейках В2–В5. Амперсанд (&) объединяет оператор сравнения для не равно (<>) и значение в B4 для чтения =СЧЁТЕСЛИ(B2:B5,"<>75"). Результат — 3. |
| =СЧЁТЕСЛИ(B2:B5,">=32")-COUNTIF(B2:B5,"<=85") | Подсчитывает количество ячеек со значением больше (>) или равно (=) 32 и меньше (<) или равно (=) 85 в ячейках от B2 до B5. Результат — 1. |
| =СЧЁТЕСЛИ(A2:A5;"*") | Количество ячеек, содержащих любой текст, в ячейках А2–А5. Подстановочный знак "*" обозначает любое количество любых символов. Результат — 4. |
| =СЧЁТЕСЛИ(A2:A5;"????ки") | Количество ячеек, строка в которых содержит ровно 7 знаков и заканчивается буквами "ки", в диапазоне A2–A5. Подставочный знак "?" обозначает отдельный символ. Результат — 2. |
Устранение распространенных ошибок COUNTIF в Excel
| Проблема | Возможная причина |
|---|---|
| Для длинных строк возвращается неправильное значение. | Функция СЧЁТЕСЛИ возвращает неправильные результаты, если она используется для сопоставления строк длиннее 255 символов. Для работы с такими строками используйте функцию СЦЕПИТЬ или оператор сцепления &. Пример: =СЧЁТЕСЛИ(A2:A5;"длинная строка"&"еще одна длинная строка"). |
| Функция должна вернуть значение, но ничего не возвращает. | Обязательно заключите аргумент условия в кавычки. |
| Формула СЧЁТЕСЛИ получает #VALUE! ошибка при ссылке на другой лист. | Эта ошибка возникает при вычислении ячеек, когда в формуле содержится функция, которая ссылается на ячейки или диапазон в закрытой книге. Для работы этой функции необходимо, чтобы другая книга была открыта. |
Рекомендации по использованию COUNTIF в Excel
| Действия | Результат |
|---|---|
| Помните о том, что функция СЧЁТЕСЛИ не учитывает регистр символов в текстовых строках. | В критериях регистр не учитывается. Например, строкам "яблоки" и "ЯБЛОКИ" будут соответствовать одни и те же ячейки. |
| Использование подстановочных знаков | В критериях можно использовать подстановочные знаки — вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому отдельно взятому символу. Звездочка — любой последовательности символов. Если требуется найти именно вопросительный знак или звездочку, следует ввести значок тильды (~) перед искомым символом. Например, =СЧЁТЕСЛИ(A2:A5;"яблок?") возвращает все вхождения слова "яблок" с любой буквой в конце. |
| Убедитесь, что данные не содержат ошибочных символов. | При подсчете текстовых значений убедитесь в том, что данные не содержат начальных или конечных пробелов, недопустимых прямых и изогнутых кавычек или непечатаемых символов. В этих случаях функция СЧЁТЕСЛИ может вернуть непредвиденное значение. Попробуйте воспользоваться функцией ПЕЧСИМВ или функцией СЖПРОБЕЛЫ. |
| Для удобства используйте именованные диапазоны. | СЧЁТЕСЛИ поддерживает именованные диапазоны в формуле (например, =COUNTIF(fruit,">=32")-COUNTIF(fruit,">85"). Именованный диапазон может располагаться на текущем листе, другом листе этой же книги или листе другой книги. Чтобы одна книга могла ссылаться на другую, они обе должны быть открыты. |
Примечание
С помощью функции СЧЁТЕСЛИ нельзя подсчитать количество ячеек с определенным фоном или цветом шрифта. Однако Excel поддерживает пользовательские функции, в которых используются операции VBA (Visual Basic для приложений) над ячейками, выполняемые в зависимости от фона или цвета шрифта. Ниже приведен пример того, как можно подсчитать количество ячеек с определенным цветом ячейки с помощью VBA.
Дополнительные сведения
Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществах.