Функция СЧЁТЕСЛИ в EXCEL – Подсчет ячеек, которые не пусты, больше/меньше или содержат определенное значение
При работе с листом Excel подсчет количества ячеек, таких как пустые или непустые ячейки, ячейки больше или меньше заданного значения, или ячейки, содержащие определенный текст, может быть одной из распространенных задач для большинства из нас. Для выполнения этих вычислений функция СЧЁТЕСЛИ в Excel может оказать вам помощь.
Синтаксис функции СЧЁТЕСЛИ в Excel
Функция СЧЁТЕСЛИ для пустых или непустых ячеек
- Подсчет пустых ячеек с помощью функции СЧЁТЕСЛИ
- Подсчет непустых ячеек с помощью функции СЧЁТЕСЛИ
- Подсчет непустых ячеек за два клика
Функция СЧЁТЕСЛИ для ячеек меньше, больше или равных определенному значению
- СЧЁТЕСЛИ меньше, больше, равно или не равно с формулами
- СЧЁТЕСЛИ меньше, больше, равно или не равно за несколько кликов
Функция СЧЁТЕСЛИ для ячеек, содержащих определенное значение
- Подсчет ячеек, содержащих определенные слова или символы, с помощью функции СЧЁТЕСЛИ
- Подсчет ячеек, содержащих определенные слова или символы, за несколько кликов
Синтаксис функции СЧЁТЕСЛИ в Excel
Как одна из статистических функций в Excel, СЧЁТЕСЛИ подсчитывает ячейки, соответствующие заданным критериям в указанном диапазоне. Синтаксис функции СЧЁТЕСЛИ следующий:
Синтаксис содержит два аргумента – диапазон и критерии:
- диапазон: относится к диапазону ячеек, который вы хотите подсчитать.
- критерии: относится к условию, которое вы хотите использовать для подсчета ячеек. Например, вы можете использовать “>=80” как критерий для поиска ячеек со значениями, которые больше или равны 80 в выбранном диапазоне.
Чтобы применить функцию в Excel, вы должны ввести =СЧЁТЕСЛИ(диапазон; критерии) в целевую ячейку. Например, =СЧЁТЕСЛИ(A1:B8;">=80").
Функция СЧЁТЕСЛИ возвращает числовое значение – количество ячеек, которые вы хотели подсчитать.
Теперь, когда у нас есть более четкое понимание функции СЧЁТЕСЛИ, давайте перейдем к некоторым реальным примерам.
Функция СЧЁТЕСЛИ для пустых или непустых ячеек
Например, у меня есть список ячеек, содержащих разные типы данных, такие как текст, логические значения (ИСТИНА и ЛОЖЬ), числа, даты и ошибки. Существует ли универсальная формула СЧЁТЕСЛИ для подсчета только пустых или непустых ячеек, независимо от того, какие типы данных существуют в вашем указанном диапазоне?
Подсчет пустых ячеек с помощью функции СЧЁТЕСЛИ
Теперь позвольте мне представить вам формулу СЧЁТЕСЛИ. С помощью этой формулы, независимо от того, сколько типов данных существует в диапазоне, она сообщит вам точное и правильное количество пустых ячеек:
√ Примечание: В кавычках нет текста, что означает, что ячейки, которые вы хотите подсчитать, пусты.
Чтобы использовать функцию СЧЁТЕСЛИ для подсчета пустых ячеек, введите формулу =СЧЁТЕСЛИ(A1:A9;"") в целевую ячейку, затем нажмите ENTER:
 | >>> |  |
☞ В Excel также есть функция СЧЁТПУСТ, которая возвращает количество пустых ячеек в указанном диапазоне. Её синтаксис: =СЧЁТПУСТ(диапазон). Для приведенного выше примера вы можете использовать формулу: =СЧЁТПУСТ(A1:A9).
Подсчет непустых ячеек с помощью функции СЧЁТЕСЛИ
Для подсчета ячеек, которые не пусты, позвольте мне показать вам формулу:
√ Примечание: <> в Excel означает не равно. Таким образом, приведенная выше формула подсчитывает все ячейки, которые не равны пустым, или можно сказать, не пусты.
Чтобы использовать функцию СЧЁТЕСЛИ для подсчета ячеек, которые не пусты, введите формулу =СЧЁТЕСЛИ(A1:A9;"<>") в целевую ячейку, затем нажмите ENTER:
 | >>> |  |
☞ В Excel также есть функция СЧЁТЗ, которая возвращает количество ячеек, содержащих любые значения в указанном диапазоне. Её синтаксис: =СЧЁТЗ(диапазон). Для приведенного выше примера вы можете использовать формулу: =СЧЁТЗ(A1:A9).
Однако, если вы хотите подсчитать только ячейки с текстом и исключить логические значения (ИСТИНА и ЛОЖЬ), числа, даты или ошибки, используйте следующую формулу:
√Примечание: Одиночная звездочка (*) соответствует только значению в текстовой форме.
 | >>> |  |
Подсчет непустых ячеек за два клика
Если у вас установлен Kutools for Excel, с его функцией Выбрать Непустые Ячейки вы можете выбрать все непустые ячейки сразу и получить нужное число.
1. Перейдите на вкладку Kutools в Excel, найдите Выбрать и нажмите Выбрать Непустые Ячейки в выпадающем списке. Смотрите скриншот:
2. Все непустые ячейки будут выбраны, и появится окно с указанием количества непустых ячеек, как показано на скриншоте ниже:
Функция СЧЁТЕСЛИ для ячеек меньше, больше или равных определенному значению
Здесь мы перечислили два основных метода для подсчета ячеек со значениями, которые меньше, больше, равны или не равны определенному значению в Excel.
СЧЁТЕСЛИ меньше, больше, равно или не равно с формулами
Чтобы использовать формулу для подсчета ячеек со значениями, которые меньше, больше, равны или не равны определенному значению, вы должны хорошо использовать Операторы Сравнения (<, >, =, ≠).
√ Примечание: Этот метод также можно использовать для подсчета ячеек с датами, которые раньше (меньше <), позже (больше >) или равны (=) указанной вами дате.
Теперь давайте рассмотрим некоторые реальные примеры согласно таблице ниже:
| Чтобы подсчитать количество студентов, чей общий балл выше (>) 140, используйте формулу: =СЧЁТЕСЛИ(E2:E8;">140") >>> Результат равен 5 |
| Чтобы подсчитать количество студентов, чей балл по математике ниже (<) балла Коко (значение в ячейке D3), используйте формулу: =СЧЁТЕСЛИ(D2:D8;"<"&D3) >>> Результат равен 2 √ Примечание: Чтобы использовать ссылку на ячейку в функции СЧЁТЕСЛИ с операторами сравнения, вы должны поместить операторы в кавычки и добавить амперсанд (&) перед ссылкой на ячейку. |
| Чтобы подсчитать количество студентов, чей балл по английскому языку выше или равен (≥) баллу Эдди (значение в ячейке C4), используйте формулу: =СЧЁТЕСЛИ(C2:C8;">="&C4)-1 >>> Результат равен 5 √ Примечание: Причина вычитания 1 заключается в том, что критерии C2:C8;">="&C4 просят СЧЁТЕСЛИ подсчитать все совпадающие ячейки, включая ту, где находится балл Эдди по английскому языку, поэтому нам нужно вычесть её из формулы. Если вам нужно включить Эдди, удалите “-1” из формулы. |
| Чтобы подсчитать количество студентов, которые из (=) Индии, используйте формулу: =СЧЁТЕСЛИ(B2:B8;"Индия") ИЛИ =СЧЁТЕСЛИ(B2:B8;B2) >>> Результат равен 3 √ Примечание: Значение в ссылке на ячейку B2 – это Индия, поэтому “Индия” и B2 одинаково подходят для использования в качестве критериев здесь. В функции СЧЁТЕСЛИ критерии не чувствительны к регистру. Поэтому строка “Индия” и “ИНДИЯ” не сделают различий в результате формулы. |
| Чтобы подсчитать количество студентов, которые не из (≠) Индии, используйте формулу: =СЧЁТЕСЛИ(B2:B8;"<>Индия") ИЛИ =СЧЁТЕСЛИ(B2:B8;"<>"&B2) >>> Результат равен 4 |
☞ Функция СЧЁТЕСЛИ работает только при наличии одного условия, для ситуаций с несколькими условиями следует использовать функцию СЧЁТЕСЛИМН. Например, чтобы подсчитать количество студентов, чей балл по английскому языку находится между 60 и 90 (>60, <90), используйте формулу: =СЧЁТЕСЛИМН(B2:B8;">60";B2:B8;"<90").
Нажмите, чтобы узнать больше о функции СЧЁТЕСЛИМН…
СЧЁТЕСЛИ меньше, больше, равно или не равно за несколько кликов
Чтобы подсчитать ячейки со значениями, которые меньше, больше, равны или не равны определенному значению более удобным способом, вы можете применить функцию Выбрать Конкретные Ячейки из Kutools f or Excel.
После установки Kutools for Excel выполните следующие шаги:
1. Найдите вкладку Kutools, затем перейдите к Выбрать и нажмите Выбрать Конкретные Ячейки в выпадающем списке.
2. Теперь вы увидите всплывающее окно, выполните следующие действия:
Ниже приведен пример подсчета количества студентов, чей балл по математике ниже (<) балла Коко с помощью Kutools.
√ Примечание: Программа вернет результат подсчета и выберет ячейки, соответствующие вашим критериям. Вы можете скопировать их, если необходимо.
Функция СЧЁТЕСЛИ для ячеек, содержащих определенное значение
Чтобы использовать СЧЁТЕСЛИ для подсчета ячеек, содержащих определенное значение (например, ячейки с буквой Y), или даже для подсчета ячеек с определенным значением в указанной позиции (например, ячейки начинаются с буквы Y), мы должны знать о символах-подстановках.
В Excel доступны три символа-подстановки для функции СЧЁТЕСЛИ: звездочка (*), вопросительный знак (?) и тильда (~):
• Звездочка (*) - Соответствует любому количеству символов. Например, *ice может означать nice, service, ice, @#$ice и т.д.
• Вопросительный знак (?) - Соответствует любому одному символу. Например, mo?? может означать more, moon, mo&%, moaa и т.д.
• Тильда (~) - Соответствует самому символу-подстановке. Например, ~* означает литеральный символ *, ~~ означает литеральную тильду.
√ Примечание: Логические значения (ИСТИНА и ЛОЖЬ), числа, даты и ошибки не считаются символами. Поэтому, если в выбранном диапазоне ячеек есть такие элементы, вам придется преобразовать их в текст.
☞ Дополнительная информация:
Как изменить или преобразовать число в текст в Excel?
Как преобразовать дату в числовую строку или текстовый формат в Excel?
Как заменить # формулы ошибок на 0, пустое место или определенный текст в Excel?
Как преобразовать логическое ИСТИНА/ЛОЖЬ в число (1/0) или текст в Excel?
Подсчет ячеек, содержащих определенные слова или символы, с помощью функции СЧЁТЕСЛИ
Так как мы уже знаем о символах-подстановках, пришло время научиться использовать формулу СЧЁТЕСЛИ для подсчета ячеек, содержащих определенные символы или слова. Давайте посмотрим на примеры ниже:
| Чтобы подсчитать количество мальчиков в классе, используйте формулу: =СЧЁТЕСЛИ(B2:B9;"MALE") >>> Результат равен 5 |
| Чтобы подсчитать количество имен, содержащих символы “jeff” (значение в ячейке A6), используйте формулу: =СЧЁТЕСЛИ(A2:A9;"*jeff*") ИЛИ =СЧЁТЕСЛИ(A2:A9;"*"&A6&"*") >>> Результат равен 2 |
| Чтобы подсчитать количество имен, содержащих букву “e”, используйте формулу: =СЧЁТЕСЛИ(A2:A9;"*e*") >>> Результат равен 5 |
| Чтобы подсчитать количество имен, которые не содержат букву “e”, используйте формулу: =СЧЁТЕСЛИ(A2:A9;"<>*e*") >>> Результат равен 3 |
| Чтобы подсчитать количество имен, начинающихся с буквы “e”, используйте формулу: =СЧЁТЕСЛИ(A2:A9;"e*") >>> Результат равен 2 |
| Чтобы подсчитать количество имен, заканчивающихся буквой “e”, используйте формулу: =СЧЁТЕСЛИ(A2:A9;"*e") >>> Результат равен 1 |
| Чтобы подсчитать количество имен с буквой “m” как третий символ, используйте формулу: =СЧЁТЕСЛИ(A2:A9;"??m*") >>> Результат равен 2 |
Подсчет ячеек, содержащих определенные слова или символы, за несколько кликов
После установки нашего дополнения для Excel выполните следующие действия:
1. Перейдите на вкладку Kutools, найдите Выбрать и нажмите Выбрать Конкретные Ячейки в выпадающем списке.
2. В диалоговом окне Выбрать Конкретные Ячейки выберите диапазон ячеек, который вы хотите подсчитать, затем нажмите Ячейка под Тип выбора, выберите одно условие в выпадающем списке Тип условия в соответствии с вашими потребностями.
Здесь я приведу вам два примера использования Kutools для подсчета ячеек, содержащих определенные слова или символы.
• Чтобы подсчитать количество мальчиков в классе, нажмите Равно в выпадающем списке и введите Male в правое поле ввода, или вы можете выбрать ячейку со значением “Male” в рабочем листе, нажав 
кнопку.
Программа вернет результат подсчета и выберет ячейки, соответствующие вашим критериям. Вы можете скопировать их, если необходимо.
• Чтобы подсчитать количество имен, содержащих символы “jeff”, нажмите Содержит в выпадающем списке и введите jeff, или вы можете выбрать ячейку A6 в рабочем листе, нажав кнопку.
Программа вернет результат подсчета и выберет ячейки, соответствующие вашим критериям. Вы можете скопировать их, если необходимо.
Лучшие инструменты для повышения производительности Office
Kutools для Excel решает большинство ваших проблем и увеличивает вашу продуктивность на 80%
- Супер строка формул (легкое редактирование нескольких строк текста и формул); Режим чтения (удобное чтение и редактирование большого количества ячеек); Вставка в отфильтрованный диапазон...
- Объединение ячеек/строк/столбцов с сохранением данных; Разделение содержимого ячеек; Объединение дублирующихся строк с подсчетом суммы/среднего значения... Предотвращение дублирования ячеек; Сравнение диапазонов...
- Выбор дублирующихся или уникальных строк; Выбор пустых строк (все ячейки пустые); Супер поиск и нечеткий поиск во многих книгах; Случайный выбор...
- Точное копирование нескольких ячеек без изменения ссылок на формулы; Автоматическое создание ссылок на несколько листов; Вставка маркеров, флажков и многое другое...
- Избранные и быстрая вставка формул, диапазонов, диаграмм и изображений; Шифрование ячеек с паролем; Создание списка рассылки и отправка электронных писем...
- Извлечение текста, добавление текста, удаление по позиции, удаление пробелов; Создание и печать статистики страниц; Преобразование между содержимым ячеек и комментариями...
- Супер фильтр (сохранение и применение схем фильтрации к другим листам); Расширенная сортировка по месяцу/неделе/дню, частоте и другим параметрам; Специальный фильтр по жирному шрифту, курсиву...
- Объединение книг и листов; Объединение таблиц на основе ключевых столбцов; Разделение данных на несколько листов; Пакетное преобразование xls, xlsx и PDF...
- Группировка сводной таблицы по номеру недели, дню недели и другим параметрам... Отображение разблокированных, заблокированных ячеек разными цветами; Выделение ячеек, содержащих формулы/имена...
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов в новых вкладках одного окна, а не в новых окнах.
- Увеличивает вашу продуктивность на 50% и экономит сотни кликов мышью каждый день!
