Функция СЧЁТЕСЛИ в Excel – подсчёт непустых ячеек, ячеек больше/меньше заданного значения или содержащих определённое значение
При работе с листом Excel часто требуется подсчитать количество ячеек: например, пустых или непустых ячеек, ячеек больше или меньше заданного значения, либо ячеек, содержащих определённый текст. Для выполнения таких расчётов можно воспользоваться функцией СЧЁТЕСЛИ в Excel.
Синтаксис функции СЧЁТЕСЛИ в Excel
Функция СЧЁТЕСЛИ для пустых или непустых ячеек
- Подсчёт пустых ячеек с помощью функции СЧЁТЕСЛИ
- Подсчёт непустых ячеек с помощью функции СЧЁТЕСЛИ
- Подсчёт непустых ячеек двумя щелчками
Функция СЧЁТЕСЛИ для ячеек со значениями меньше, больше или равными определённому значению
- СЧЁТЕСЛИ: меньше, больше, равно или не равно с использованием формул
- СЧЁТЕСЛИ: меньше, больше, равно или не равно несколькими щелчками
Функция СЧЁТЕСЛИ для ячеек, содержащих определённое значение
- Подсчёт ячеек, содержащих определённое слово(а) или символ(ы), с помощью функции СЧЁТЕСЛИ
- Подсчёт ячеек, содержащих определённое слово(а) или символ(ы), несколькими щелчками
Синтаксис функции СЧЁТЕСЛИ в Excel
Как одна из статистических функций Excel, СЧЁТЕСЛИ подсчитывает количество ячеек в заданном диапазоне, соответствующих определённым критериям. Синтаксис функции СЧЁТЕСЛИ следующий:
Синтаксис содержит два аргумента –диапазони критерий:
- Диапазон: указывает диапазон ячеек, которые нужно подсчитать.
- критерий: задаёт условие, по которому следует подсчитывать ячейки. Например, можно использовать «>=80» в качестве критерия для поиска ячеек со значениями, которые больше или равны 80, в выбранном диапазоне.
Чтобы применить функцию в Excel, введите =COUNTIF(range, criteria) в целевую ячейку. Например, =COUNTIF(A1:B8,«>,=80»).
Функция СЧЁТЕСЛИ возвращает число — количество ячеек, подлежащих подсчёту.
Теперь, когда вы лучше понимаете функцию СЧЁТЕСЛИ, давайте перейдём к реальным примерам.
Функция СЧЁТЕСЛИ для пустых или непустых ячеек
Например, у меня есть список ячеек с разными типами данных: текстом, логическими значениями (ИСТИНА и ЛОЖЬ), числами, датами и ошибками. Существует ли универсальная формула СЧЁТЕСЛИ для подсчёта только пустых или непустых ячеек — независимо от типа данных в заданном диапазоне?
Подсчёт пустых ячеек с помощью функции СЧЁТЕСЛИ
Теперь представляем вам формулу СЧЁТЕСЛИ. Независимо от количества типов данных в диапазоне, она точно и корректно подсчитывает пустые ячейки:
√Примечание. Отсутствие текста в кавычках означает, что подсчитываются пустые ячейки.
Чтобы использовать функцию СЧЁТЕСЛИ для подсчёта Пустые ячейки, введите формулу =COUNTIF(A1:A9,«»)в целевую ячейку, затем нажмите ВВОД:
 | >>> |  |
☞В Excel также есть функция СЧЁТПУСТОТ, которая возвращает количество пустых ячеек в заданном диапазоне. Её синтаксис: =COUNTBLANK(range). Для приведённого выше примера можно использовать формулу: =COUNTBLANK(A1:A9).
Подсчёт непустых ячеек с помощью функции СЧЁТЕСЛИ
Чтобы подсчитать ячейки, которые не пусты, ниже приведена формула:
√Примечание.<>в Excel означает«не равно». Таким образом, приведённая выше формула подсчитывает все ячейки, которые не равны пустым, то есть непустые ячейки.
Чтобы использовать функцию СЧЁТЕСЛИ для подсчёта ячеек, которые не пусты, введите формулу =COUNTIF(A1:A9,«<,>,»)в целевую ячейку, затем нажмите ВВОД:
 | >>> |  |
☞В Excel также есть функция СЧЁТЗ, которая подсчитывает количество ячеек с любыми значениями в заданном диапазоне. Её синтаксис: =COUNTA(range). Для приведённого выше примера используйте формулу: =COUNTA(A1:A9).
Однако если вы хотите подсчитать только ячейки с текстом, исключив логические значения (ИСТИНА и ЛОЖЬ), числа, даты или ошибки, используйте приведённую ниже формулу:
√ Note: A single asterisk (*) matches only the value in a text form.
 | >>> |  |
Подсчёт непустых ячеек двумя щелчками
Если у вас установлено Kutools для Excel в Excel, с помощью функции Выбрать непустые ячейки можно мгновенно выделить все непустые ячейки и получить нужное количество.
1. Перейдите на вкладку Kutools в Excel, найдите раздел Выделить и нажмите Выбрать непустые ячейки в раскрывающемся списке. См. снимок экрана:
2. Все непустые ячейки будут выделены, и появится окно с указанием их количества, как показано на снимке экрана ниже:
Функция СЧЁТЕСЛИ для ячеек со значениями меньше, больше или равными определённому значению
Здесь представлены два основных метода подсчёта ячеек со значениями, которые меньше, больше, равны или не равны заданному значению в Excel.
СЧЁТЕСЛИ: меньше, больше, равно или не равно с использованием формул
Чтобы использовать формулу для подсчёта ячеек со значениями, которые меньше, больше, равны или не равны определённому значению, правильно применяйте операторы сравнения (<, >, =, ≠).
√Примечание. Этот метод также подходит для подсчёта ячеек с датами, которые меньше указанной вами даты или равны ей.
Теперь рассмотрим несколько практических примеров на основе приведённой ниже таблицы:
| Чтобы подсчитать количество учащихся, чей общий балл превышает (>)140, используйте формулу: =COUNTIF(E2:E8,«>,140»)>>> Результат:5 |
| Чтобы подсчитать количество учащихся, чей балл по математике меньше (<)балла Коко (значение в ячейке D3), используйте формулу: =COUNTIF(D2:D8,«<,»&,D3)>>> Результат:2 √Примечание. Чтобы использовать Ссылка на ячейку в функции СЧЁТЕСЛИ с операторами сравнения, необходимо заключить операторы в кавычки и добавить амперсанд (&) перед ссылкой на ячейку. |
√ Примечание. Единицу вычитают потому, что условие C2:C8;="&C4 заставляет функцию СЧЁТЕСЛИ учитывать все совпадающие ячейки, включая ячейку с баллом Эдди по английскому языку, — поэтому её и вычитают из формулы. Если вы хотите включить Эдди в подсчёт, просто удалите из формулы «-1». |
| Чтобы подсчитать количество учащихся, которые родом из (=)Индии, используйте формулу: =COUNTIF(B2:B8,«India»)ИЛИ =COUNTIF(B2:B8,B2)>>> Результат:3 √Примечание. Значение в ссылке на ячейку B2 — Индия, поэтому как «Индия», так и B2могут использоваться в качестве условия. В функции СЧЁТЕСЛИ условия не учитывают регистр. Таким образом, строки «Индия» и «ИНДИЯ» не повлияют на результат формулы. |
| Чтобы подсчитать количество учащихся, которые не из (≠) Индии, используйте формулу: =COUNTIF(B2:B8,«<,>,India»)ИЛИ =COUNTIF(B2:B8,«<,>,»&,B2)>>> Результат:4 |
☞Функция СЧЁТЕСЛИ работает только с одним условием. Если нужно задать несколько условий, используйте функцию СЧЁТЕСЛИМН. Например, чтобы подсчитать количество учащихся, у которых балл по английскому находится в диапазоне от 60 до 90 (>60 и <90), примените формулу:=COUNTIFS(B2:B8,«>,60»,B2:B8,«<,90»).
Подробнее о функции СЧЁТЕСЛИМН…
СЧЁТЕСЛИ: меньше, больше, равно или не равно несколькими щелчками
Чтобы удобнее подсчитывать ячейки со значениями, которые меньше, больше, равны или не равны заданному значению, можно воспользоваться функцией Выбрать определенные ячейкииз набора инструментов Kutools для Excel.
После установки Kutools для Excel выполните следующие действия:
1. Найдите вкладку Kutools, перейдите к разделу Выделить и нажмите Выбрать определенные ячейки в раскрывающемся списке.
2. Теперь откроется всплывающее окно. Выполните следующие действия:
Ниже приведён пример подсчёта количества учащихся, у которых балл по математике ниже (<) балла Коко, с помощью Kutools.
√ Примечание. Программа вернёт результат подсчёта и выделит ячейки, соответствующие заданным критериям. При необходимости их можно скопировать.
Функция СЧЁТЕСЛИ для ячеек, содержащих определённое значение
To use COUNTIF to count the cells that contain a specific value (for example the cells with a letter Y), а также для подсчёта ячеек со значением в определённой позиции (например, ячейки, начинающиеся с буквы Y), необходимо знать о символах подстановки.
В Excel для функции СЧЁТЕСЛИ доступны три символа подстановки:звёздочка(*),вопросительныйзнак(?), и тильда(~):
•Звёздочка (*) — соответствует любому количеству символов. Например, *ice может означать nice, service, ice, @#$ice и т.д.
•Вопросительный знак (?) — соответствует любому одному символу. Например, mo?? может означать more, moon, mo&%, moaa и т.д.
•Тильда (~) — используется для поиска самого символа подстановки. Например, ~* означает буквальную звёздочку, а ~~ — буквальную тильду.
√Примечание. Логические значения (ИСТИНА и ЛОЖЬ), числа, даты и ошибки не считаются текстовыми символами. Поэтому, если в выбранном диапазоне ячеек содержатся такие элементы, их необходимо преобразовать в текст.
☞Дополнительная информация:
Как изменить или преобразовать Число в текст в Excel?
Как преобразовать дату в числовую строку или в Формат текста в Excel?
Как заменить ошибки формул # на 0, пустые ячейки или определённый текст в Excel?
Как преобразовать логические значения ИСТИНА/ЛОЖЬ в число (1/0) или текст в Excel?
Подсчёт ячеек, содержащих определённое слово(а) или символ(ы), с помощью функции СЧЁТЕСЛИ
Теперь, когда мы уже знаем о символах подстановки, пришло время научиться использовать формулу СЧЁТЕСЛИ для подсчёта ячеек, содержащих определённые символы или слова. Рассмотрим приведённые ниже примеры:
| Чтобы подсчитать количество мальчиков в классе, используйте формулу: =COUNTIF(B2:B9,«MALE»)>>> Результат:5 |
| Чтобы подсчитать количество имён, содержащих символы «jeff» (значение в ячейке A6), используйте формулу: =COUNTIF(A2:A9,«*jeff*»)ИЛИ =COUNTIF(A2:A9,«*»&,A6&,«*»)>>> Результат:2 |
| Чтобы подсчитать количество имён, содержащих букву «e», используйте формулу: =COUNTIF(A2:A9,"*e*") >>> The result is 5 |
| Чтобы подсчитать количество имён, не содержащих букву «e», используйте формулу: =COUNTIF(A2:A9,«<,>,*e*»)>>> Результат:3 |
| Чтобы подсчитать количество имён, начинающихся с буквы «e», используйте формулу: =COUNTIF(A2:A9,«e*»)>>> Результат:2 |
| Чтобы подсчитать количество имён, оканчивающихся на букву «e», используйте формулу: =COUNTIF(A2:A9,«*e»)>>> Результат:1 |
| Чтобы подсчитать количество имён, в которых третьим символом является буква «m», используйте формулу: =COUNTIF(A2:A9,«??m*»)>>> Результат:2 |
Подсчёт ячеек, содержащих определённое слово(а) или символ(ы), несколькими щелчками
После установки нашего надстройки для Excelвыполнитеследующие действия:
1. Перейдите на вкладку Kutools, найдите раздел Выделить и нажмите Выбрать определенные ячейки в раскрывающемся списке.
2. В диалоговом окне Выбрать определенные ячейки выберите диапазон ячеек для подсчёта, затем в разделе Выбрать тип нажмите Ячейка и укажите нужное условие в раскрывающемся списке раздела Указать тип в соответствии с вашими требованиями.
Приведу два примера использования Kutools для подсчёта ячеек, содержащих определённые слова или символы.
• Чтобы подсчитать количество мальчиков в классе, выберите Равно в раскрывающемся списке и введите Мужской в поле справа или просто щёлкните по ячейке со значением «Мужской» на листе, нажав кнопку 
.
Программа вернёт результат подсчёта и выделит ячейки, соответствующие заданным критериям. При необходимости их можно скопировать.
• Чтобы подсчитать количество имён, содержащих «jeff», выберите в раскрывающемся списке пункт Содержит и введите jeff или просто нажмите кнопку , чтобы выбрать ячейку A6 на листе.
Программа вернёт результат подсчёта и выделит ячейки, соответствующие заданным критериям. При необходимости их можно скопировать.
Лучшие инструменты для повышения продуктивности в офисе
Kutools для Excel решает большинство ваших задач и повышает продуктивность на 80 %
- Супер строка формул (удобное редактирование многострочного текста и формул); Режим чтения (удобный просмотр и редактирование большого количества ячеек); Вставка в диапазон фильтрации…
- Объединение ячеек, строк или столбцов с сохранением данных; разделение содержимого ячеек;Объединение дублирующихся строк с суммированием или усреднением… предотвращение дублирования записей в ячейках;Сравнение диапазонов…
- Выбор дубликатов или уникальных строк; выбор пустых строк (все ячейки пустые); улучшенный и нечёткий поиск по множеству книг; случайный выбор…
- Точная копия нескольких ячеек без изменения ссылок в формулах; автоматическое создание ссылок на несколько листов; вставка маркеров, флажков и многое другое…
- Избранные и быстрая вставка формул, диапазонов, диаграмм и изображений; Защита ячеек паролем; Создание списка рассылкии отправка электронных писем…
- Извлечение текста, добавление текста, удаление символов в определённой позиции, удаление пробелов; создание и печать статистики по странице данных; преобразование между содержимым ячеек и примечаниями…
- Суперфильтр (сохранение и применение схем фильтрации к другим листам); расширенная сортировка по месяцам, неделям, дням, частоте и другим параметрам; специальный фильтр по полужирному, курсиву…
- Объединение книг и листов; объединение таблиц на основе ключевого столбца; разделение данных на несколько листов; пакетное преобразование XLS, XLSX и PDF…
- Сводная таблица: группировка по номеру недели, дню недели и многому другому… Показ разблокированных и заблокированных ячеек разными цветами; Выделение ячеек, содержащих формулы или имена…
- Включите вкладки для редактирования и чтения в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — это удобнее, чем использовать отдельные окна.
- Повышает продуктивность на 50 % и экономит сотни кликов мышью каждый день!
