Функция "СЧЕТЕСЛИ" - одна из наиболее часто употребимых функций в мощном табличном процессоре "Эксель" от компании "Майкрософт". Для чего она нужна? Это понятно уже из ее названия. Она поможет любому пользователю, задавшемуся вопросом «Как посчитать ячейки в Excel по заданному критерию?», получить нужные цифры.
Аргументы функции
Данная функция принимает два обязательных аргумента. Первый из них – диапазон, в котором будет подсчитываться количество ячеек, удовлетворяющих условию.
Второй аргумент – условие, которое может задаваться в форме числа или слова, а также в форме условия.
Подготовим диапазон данных и на его основе рассмотрим, как выглядят конкретные по функции "СЧЕТЕСЛИ" в Excel примеры. Допустим, у нас есть список овощей и фруктов, хранящихся на складе с перечнем их закупочной цены, продажной надбавки и датой закупки. Начинается работа с таблицами в Excel - переносим на рабочий лист следующие данные:
А | В | С | D | |
1 | Фрукты/овощи | Цена закупочная (кг) | Надбавка (%) | Дата закупки |
2 | Яблоки голд | 74,5 | 28 | 01.05.2017 |
3 | Яблоки фуджи | 69,8 | 50 | 16.05.2017 |
4 | Бананы | 41 | 75 | 04.05.2017 |
5 | Бананы мини | 74,5 | 42 | 14.05.2017 |
6 | Яблоки глостер | 54,2 | 34 | 15.05.2017 |
7 | Огурец китайский | 64 | 17 | 07.05.2017 |
8 | Огурец опыляемый | 110,6 | 28 | 16.05.2017 |
9 | Огурец неопыляемый | 132,9 | 28 | 01.05.2017 |
Важное дополнение
Второй аргумент функции представляет собой условие. Если мы хотим в качестве аргумента указать число или адрес ячейки, то можно производить запись прямо так. Если же хотим построить сложное условие со знаками сравнения, выполнить поиск текста или конкретной даты, то аргумент необходимо заключить в кавычки. Чуть ниже будет также рассмотрено, каким образом с помощью знака "амперсанд" можно комбинировать в условии различные функции.
Простые условия для функции
Введенный в предыдущем разделе набор данных поможет разобраться с функцией "СЧЕТЕСЛИ" в Excel. Примеры, рассматриваемые в текущем разделе, будут довольно простыми, однако они основополагающие, и рассмотреть их необходимо, прежде чем приступать к более сложным.
В табличной форме сведем формулы, результаты их выполнения, а так же напишем краткий поясняющий комментарий. Все ответы основаны на табличных данных, введенных выше, и выполняются на дату 16.05.2017.
Пример | Ответ | Комментарий |
=СЧЁТЕСЛИ(В2:В9; 74,5) | 2 | Выявляет, сколько товаров на складе закуплено по цене 74.5 |
=СЧЁТЕСЛИ(А2:А9;А2) | 1 | Если данную формулу «протянуть» до конца таблицы, то увидим, нет ли у нас дублей по наименованию товаров (если 1 – то дублей нет) |
=СЧЁТЕСЛИ(D2:D9; СЕГОДНЯ()) | 2 | Количество ячеек с товарами, поступившими на склад сегодня (используется системная дата компьютера) |
=СЧЁТЕСЛИ(С2:С9; "") | 0 | Подсчитывает количество незаполненных ячеек в диапазоне. Внимание! Если в ячейке поставлен пробел, то она не считается пустой |
=СЧЁТЕСЛИ(В2:В9; "<55") | 2 | Количество ячеек со значением менее 55. Аналогичным образом выполняется сравнение больше ">" и не равно "<>" |
=СЧЁТЕСЛИ(В2:В9; "<55") + СЧЁТЕСЛИ(В2:В9; ">100") | 4 | Суммарное количество найденных ячеек по комбинации двух условий |
Подстановочные знаки
Так называемые подстановочные знаки широко используются с функцией "СЧЕТЕСЛИ" в Excel. Примеры подстановочных знаков:
Знак | Описание |
* | Текст (любое количество символов) |
? | Текст (количество символов соответствует количеству знаков вопроса). Работает только с текстовыми данными! Если в ячейке нет ни одной буквы, покажет всегда 0 |
Полноценная работа с таблицами в Excel и анализ данных, хранящихся в них, практически невозможен без использования подстановочных знаков.
Условия для функции с подстановочными знаками. Комбинирование функций
Комбинирование с другими функциями с целью создания нужного условия происходит посредством соединения логического условия ("больше", "меньше", "не равно") и нужной функции знаком "амперсанд" (&).
В табличной форме рассмотрим более сложные примеры использования функции "СЧЕТЕСЛИ".
Пример | Ответ | Комментарий |
=СЧЁТЕСЛИ(А2:А9;"яблоки") | 0 | Количество ячеек, содержащих только слово «Яблоки» (нечувствительно к регистру) и все |
=СЧЁТЕСЛИ(А2:А9;"яблоки*") | 3 | Количество ячеек, начинающихся со слова «Яблоки» и любым окончанием |
=СЧЁТЕСЛИ(А2:А9;"*й*") | 3 | Количество ячеек, содержащих букву «й» в любой части текста. Часто используется для поиска и дальнейшей замены «й» на «и» и «ё» на «е» |
=СЧЁТЕСЛИ(А2:А9; "??????") | 1 | Товары с наименованием длиной всего в 6 символов |
=СЧЁТЕСЛИ(D2:D9; "<" & (СЕГОДНЯ()-10)) | 3 | Количество ячеек с датой закупки старше десяти дней назад |
=СЧЁТЕСЛИ(С2:С9; ">" & СРЗНАЧ(С2:С9)) | 3 | Число ячеек со значением продажной надбавки больше среднего |
На этом мы заканчиваем рассматривать функцию "СЧЕТЕСЛИ" в Excel.
Примеры, содержащиеся в статье, помогут вам понять базовые принципы построения и комбинации условий, используемых в качестве аргумента к функции, и грамотно применять их на практике.
