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