Как в экселе узнать количество ячеек с определенным значением
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
СЧЁТЕСЛИ (функция СЧЁТЕСЛИ)
С помощью статистической функции СЧЁТЕСЛИ можно подсчитать количество ячеек, отвечающих определенному условию (например, число клиентов в списке из определенного города).
Самая простая функция СЧЁТЕСЛИ означает следующее:
- =СЧЁТЕСЛИ(где нужно искать;что нужно найти)
- =СЧЁТЕСЛИ(A2:A5;»Лондон»)
- =СЧЁТЕСЛИ(A2:A5;A4)
СЧЁТЕСЛИ(диапазон;критерий)
Имя аргумента
диапазон (обязательный)
Группа ячеек, для которых нужно выполнить подсчет. Диапазон может содержать числа, массивы, именованный диапазон или ссылки на числа. Пустые и текстовые значения игнорируются.
критерий (обязательный)
Число, выражение, ссылка на ячейку или текстовая строка, которая определяет, какие ячейки нужно подсчитать.
Например, критерий может быть выражен как 32, «>32», В4, «яблоки» или «32».
В функции СЧЁТЕСЛИ используется только один критерий. Чтобы провести подсчет по нескольким условиям, воспользуйтесь функцией СЧЁТЕСЛИМН.
Примеры
Чтобы использовать эти примеры в Excel, скопируйте данные из приведенной ниже таблицы и вставьте их на новый лист в ячейку A1.
Количество ячеек, содержащих текст «яблоки» в ячейках А2–А5. Результат — 2.
Количество ячеек, содержащих текст «персики» (значение ячейки A4) в ячейках А2–А5. Результат — 1.
Количество ячеек, содержащих текст «яблоки» (значение ячейки A2) и «апельсины» (значение ячейки A3) в ячейках А2–А5. Результат — 3. В этой формуле для указания нескольких критериев, по одному критерию на выражение, функция СЧЁТЕСЛИ используется дважды. Также можно использовать функцию СЧЁТЕСЛИМН.
Количество ячеек со значением больше 55 в ячейках В2–В5. Результат — 2.
Количество ячеек со значением, большим или равным 32 и меньшим или равным 85, в ячейках В2–В5. Результат — 1.
Количество ячеек, содержащих любой текст, в ячейках А2–А5. Подстановочный знак «*» обозначает любое количество любых символов. Результат — 4.
Количество ячеек, строка в которых содержит ровно 7 знаков и заканчивается буквами «ки», в диапазоне A2–A5. Подставочный знак «?» обозначает отдельный символ. Результат — 2.
Распространенные неполадки
Возможная причина
Для длинных строк возвращается неправильное значение.
Функция СЧЁТЕСЛИ возвращает неправильные результаты, если она используется для сопоставления строк длиннее 255 символов.
Для работы с такими строками используйте функцию СЦЕПИТЬ или оператор сцепления &. Пример: =СЧЁТЕСЛИ(A2:A5;»длинная строка»&»еще одна длинная строка»).
Функция должна вернуть значение, но ничего не возвращает.
Аргумент критерий должен быть заключен в кавычки.
Формула СЧЁТЕСЛИ получает #VALUE! ошибка при ссылке на другой лист.
Эта ошибка возникает при вычислении ячеек, когда в формуле содержится функция, которая ссылается на ячейки или диапазон в закрытой книге. Для работы этой функции необходимо, чтобы другая книга была открыта.
Рекомендации
Помните о том, что функция СЧЁТЕСЛИ не учитывает регистр символов в текстовых строках.
Критерий не чувствителен к регистру. Например, строкам «яблоки» и «ЯБЛОКИ» будут соответствовать одни и те же ячейки.
Использование подстановочных знаков
В критериях можно использовать подстановочные знаки — вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому отдельно взятому символу. Звездочка — любой последовательности символов. Если требуется найти именно вопросительный знак или звездочку, следует ввести значок тильды (~) перед искомым символом.
Например, =СЧЁТЕСЛИ(A2:A5;»яблок?») возвращает все вхождения слова «яблок» с любой буквой в конце.
Убедитесь, что данные не содержат ошибочных символов.
При подсчете текстовых значений убедитесь в том, что данные не содержат начальных или конечных пробелов, недопустимых прямых и изогнутых кавычек или непечатаемых символов. В этих случаях функция СЧЁТЕСЛИ может вернуть непредвиденное значение.
Для удобства используйте именованные диапазоны.
ФУНКЦИЯ СЧЁТЕСЛИ поддерживает именованные диапазоны в формуле (например, =COUNTIF(fruit;»>=32″)-COUNTIF(fruit;»>85″). Именованный диапазон может располагаться на текущем листе, другом листе этой же книги или листе другой книги. Чтобы одна книга могла ссылаться на другую, они обе должны быть открыты.
Примечание: С помощью функции СЧЁТЕСЛИ нельзя подсчитать количество ячеек с определенным фоном или цветом шрифта. Однако Excel поддерживает пользовательские функции, в которых используются операции VBA (Visual Basic для приложений) над ячейками, выполняемые в зависимости от фона или цвета шрифта. Вот пример подсчета количества ячеек определенного цвета с использованием VBA.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Подсчет количества определенного значения ячеек
Для подсчета количества ячеек, содержащих значения между двумя числами в диапазоне ячеек, вы можете использовать функцию СЧЁТЕСЛИМН. В общей форме формулы диапазон представляет собой диапазон ячеек, содержащих числа, X представляет собой нижнюю границу, а Y представляет собой верхнюю границу цифры, которые вы хотите посчитать.
В примере, активная ячейка содержит следующую формулу:
Функция СЧЁТЕСЛИМН построена для подсчета клеток, которые отвечают нескольким критериям. В этом случае, так как мы подставляем один и тот же диапазон для двух критериев, каждая ячейка в диапазоне должна соответствовать обоим критериям для того, чтобы быть посчитанной.
Если у вас есть старая версия Excel, которая не имеет функцию СЧЁТЕСЛИМН, вы можете использовать функцию СЧЁТЕСЛИ вместо нее, чтобы формула выглядела так:
= СЧЁТЕСЛИ (диапазон; «> = X») — СЧЁТЕСЛИ (диапазон «> Y»)
Первая функция СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, который больше или равен X (который, по определению, также включает в себя значения больше Y, так как Y больше, чем Х). Вторая функция СЧЁТЕСЛИ подсчитывает количество ячеек со значениями больше, чем Y. Это второе число затем вычитается из первого номера, что дает конечный результат — количество ячеек, содержащих значения между X и Y.
Создание переменной критериев
Если вы хотите использовать значение в другой ячейке как часть критериев, используйте амперсанд (&) символ конъюнкции следующим образом:
Если значение в ячейке A1 является «5», критерии будут «> 5» после конкатенации.
Между датами
Для подсчета количества ячеек, содержащих даты между двумя датами, вы можете использовать функцию СЧЁТЕСЛИМН. В примере, в ячейке F5, содержит следующую формулу:
Эта формула подсчитывает дни рождения в 2000 году, которые появляются в диапазоне С5: С11.
Функция СЧЁТЕСЛИМН построена для подсчета ячеек, которые отвечают нескольким критериям. В этом случае мы должны создать два критерия: один критерий для более ранней даты и один для более позднего срока. Мы поставляем названные даты диапазона (C5: C11) для обоих критериев.
Для построения каждой даты, мы используем функцию ДАТА:
ДАТА (E5; 1; 1) // формула для первого дня года
ДАТА (E5; 12; 31) // формула для последнего дня года
Функция ДАТА позволяет легко создать даты, задавая аргументы год, месяц и день, которые либо прописываем руками или используем ссылки на ячейки. В примере, месяц и день мы прописали сами, а год получаем год из ячеек столбца E.
О том, как посчитать количество ячеек с определенными значениями (словами) в программе Excel
Одним из распространенных вопросов у начинающих пользователей программы Excel является вопрос: «как посчитать количество ячеек содержащих определенное слово или набор слов? » .
Ответ на этот вопрос довольно прост, прозаичен и не требует особых навыков работы в «Эксель».
Для примера рассмотрим перечень товаров (прайс-лист)
| Наименование товара | Цена |
| Принтер лазерный | 10 |
| Принтер универсальный | 56 |
| Многофункциональный принтер | 23 |
| Дрель электрическая | 555 |
| Монитор ЖК | 55 |
| Термопринтер | 52 |
| Клавиатура цифровая | 52 |
В первой колонке указаны наименования товаров. Нам необходимо посчитать количество принтеров. То есть, посчитать количество ячеек содержащих слово «принтер». Обратите внимание, что слово принтер может стоять как вначале, так и в конце словосочетания, а так же быть частью составного слова «Термопринтер».
Решение задачи выполняется при помощи одной единственной функции СЧЕТЕСЛИ.
Запишем в ячейку с результатом эту функцию в следующем виде: СЧЕТЕСЛИ(A:A; “*принтер*”),
где A:A – диапазон, в котором находим нужное слово;
“*принтер*” – искомое слово;
* — символ «*» позволяет учесть слова находящиеся внутри словосочетаний и в составе сложных слов.
После нажатия клавиши «Enter» приведенная выше функция посчитает количество принтеров в списке.