Функция СЧЁТЕСЛИ и подсчет количества значения ячейки в Excel
Функция СЧЁТЕСЛИ в Excel используется для подсчета количества ячеек в рассматриваемом диапазоне, содержащиеся данные в которых соответствуют критерию, переданному в качестве второго аргумента данной функции, и возвращает соответствующее числовое значение.
Функция СЧЁТЕСЛИ может быть использована для анализа числовых значений, текстовых строк, дат и данных другого типа. С ее помощью можно определить количество неповторяющихся значений в диапазоне ячеек, а также число ячеек с данными, которые совпадают с указанным критерием лишь частично. Например, таблица Excel содержит столбец с ФИО клиентов. Для определения количества клиентов-однофамильцев с фамилией Иванов можно ввести функцию =СЧЁТЕСЛИ(A1:A300;”*Иванов*”). Символ «*» указывает на любое количество любых символов до и после подстроки «Иванов».
Примеры использования функции СЧЁТЕСЛИ в Excel
Пример 1. В таблице Excel содержатся данные о продажах товаров в магазине бытовой техники за день. Определить, какую часть от проданной продукции составляет техника фирмы Samsung.
Вид исходной таблицы данных:
Для расчета используем формулу:
=СЧЁТЕСЛИ(C3:C17;"Samsung")/A17
Описание аргументов:
- C3:C17 – диапазон ячеек, содержащих названия фирм проданной техники;
- "Samsung" – критерий поиска (точное совпадение);
- A17 – ячейка, хранящая номер последней продажи, соответствующий общему числу продаж.
Результат расчета:
Доля проданной продукции техники фирмы Samsung в процентах составляет – 40%.
Подсчет количества определенного значения ячейки в Excel при условии
Пример 2. По итогам сдачи экзаменов необходимо составить таблицу, в которой содержатся данные о количестве студентов, сдавших предмет на 5, 4, 3 балла соответственно, а также тех, кто не сдал предмет.
Вид исходной таблицы:
Предварительно выделим ячейки E2:E5, введем приведенную ниже формулу:
=СЧЁТЕСЛИ(B3:B19;D2:D5)
Описание аргументов:
- B3:B19 – диапазон ячеек с оценками за экзамен;
- D2:D5 – диапазон ячеек, содержащих критерии для подсчета числа совпадений.
В результате получим таблицу:
Статистический анализ посещаемости с помощью функции СЧЁТЕСЛИ в Excel
Пример 3. В таблице Excel хранятся данные о просмотрах страниц сайта за день пользователями. Определить число пользователей сайта за день, а также сколько раз за день на сайт заходили пользователи с логинами default и user_1.
Вид исходной таблицы:
Поскольку каждый пользователь имеет свой уникальный идентификатор в базе данных (Id), выполним расчет числа пользователей сайта за день по следующей формуле массива и для ее вычислений нажмем комбинацию клавиш Ctrl+Shift+Enter:
Выражение 1/СЧЁТЕСЛИ(A3:A20;A3:A20) возвращает массив дробных чисел 1/количество_вхождений, например, для пользователя с ником sam это значение равно 0,25 (4 вхождения). Общая сумма таких значений, вычисляемая функцией СУММ, соответствует количеству уникальных вхождений, то есть, числу пользователей на сайте. Полученное значение:
Для определения количества просмотренных страниц пользователями default и user_1 запишем формулу:
В результате расчета получим:
Особенности использования функции СЧЁТЕСЛИ в Excel
Функция имеет следующую синтаксическую запись:
=СЧЕТЕСЛИ(диапазон; критерий)
Описание аргументов:
- диапазон – обязательный аргумент, принимающий ссылку на одну либо несколько ячеек, в которых требуется определить число совпадений с указанным критерием.
- критерий – условие, согласно которому выполняется расчет количества совпадений в рассматриваемом диапазоне. Условием могут являться логическое выражение, числовое значение, текстовая строка, значение типа Дата, ссылка на ячейку.
Примечания:
- При подсчете числа вхождений в диапазон в соответствии с двумя различными условиями, диапазон ячеек можно рассматривать как множество, содержащее два и более непересекающихся подмножеств. Например, в таблице «Мебель» необходимо найти количество столов и стульев. Для вычислений используем выражение =СЧЁТЕСЛИ(B3:B200;"*стол*")+СЧЁТЕСЛИ(B3:B200;"*стул*").
- Если в качестве критерия указана текстовая строка, следует учитывать, что регистр символов не имеет значения. Например, функция СЧЁТЕСЛИ(A1:A2;"Петров") вернет значение 2, если в ячейках A1 и A2 записаны строки «петров» и «Петров» соответственно.
- Если в качестве аргумента критерий передана ссылка на пустую ячейку или пустая строка «», результат вычисления для любого диапазона ячеек будет числовое значение 0 (нуль).
- Функция может быть использована в качестве формулы массива, если требуется выполнить расчет числа ячеек с данными, удовлетворяющим сразу нескольким критериям. Данная особенность будет рассмотрена в одном из примеров.
- Рассматриваемая функция может быть использована для определения количества совпадений как по одному, так и сразу по нескольким критериям поиска. В последнем случае используют две и более функции СЧЁТЕСЛИ, возвращаемые результаты которых складывают или вычитают. Например, в ячейках A1:A10 хранится последовательность значений от 1 до 10. Для расчета количества ячеек с числами больше 3 и менее 8 необходимо выполнить следующие действия:
Скачать примеры функции СЧЁТЕСЛИ для подсчета ячеек в Excel
- записать первую функцию СЧЁТЕСЛИ с критерием «>3»;
- записать вторую функцию с критерием «>=8»;
- определить разницу между возвращаемыми значениями =СЧЁТЕСЛИ(A1:10;">3")-СЧЁТЕСЛИ(A1:A10;">=8"). То есть, вычесть из множества (3;+∞) подмножество [8;+∞).