Функция СЧЕТËСЛИ в Google таблицах позволяет производить подсчет значений, которые соответствуют какому-либо условию.
Здесь мы подробно рассмотрим, как применяется функция СЧËТЕСЛИ при работе в Google таблицах, а также какую пользу мы можем получить от ее использования. На примерах мы разберём, как можно указывать критерии подсчета, как можно использовать несколько критериев.
Также мы предложим нестандартный способ использования функции СЧËТЕСЛИ – для условного форматирования ячеек таблицы.
Что такое функция СЧËТЕСЛИ в Google таблицах?
Она позволяет подсчитать количество определённых значений в указанном вами диапазоне данных. Эти значения должны удовлетворять определённому условию, которое вы сами задаёте.
Синтаксис функции СЧËТЕСЛИ в Google таблицах
Вот как выглядит синтаксис этой функции и её аргументы:
=СЧËТЕСЛИ(диапазон; критерий)
Диапазон – это диапазон данных, в котором мы будем производить поиск нужных нам значений
Критерий – это критерий поиска - значение, которое мы будем искать.
Примеры использования СЧËТЕСЛИ в Google таблицах.
Несмотря на то, что функция СЧËТЕСЛИ кажется очень простой, на самом деле её возможности достаточно велики. Это множество возможностей состоит в первую очередь в том, как мы будем описывать критерий поиска.
Ведь искать можно не только точно указанные значения, но и значения, которые соответствуют определённым условиям поиска.
Теперь давайте попробуем сделать это вместе и рассмотрим несколько примеров.
Полное совпадение числа или текста в функции СЧËТЕСЛИ.
Предположим, ваша компания занимается продажей различных сортов шоколада в нескольких регионах и работает с множеством покупателей.
Вот как выглядят данные о ваших продажах, хранящиеся в Google таблицах.
Начнём с самого простого.
Вставим перед таблицей 4 строки, чтобы лучше видеть результаты вычислений.
Подсчитаем количество заявок на продажу молочного шоколада. Устанавливаем курсор в ячейку C3 и вводим знак “=”. Для Google таблиц это означает, что сейчас будет введена формула. Поэтому как только далее будет введена буква “c”, он выведет нам предложение выбрать функцию, начинающуюся с буквы “c”. Выбираем “СЧËТЕСЛИ”.
В качестве первого аргумента функции СЧËТЕСЛИ вводим диапазон: D6:D25. Как и в других функциях Google таблиц, адрес диапазона можно не вводить вручную, а просто выделить мышкой. Затем вводим “,” и указываем второй аргумент – условие поиска.
Второй аргумент – это значение, которое мы будем искать в обозначенном диапазоне. Это будет текст “Молочный шоколад”. Не забываем закончить ввод функции, нажав “)” и затем “Enter”.
Также не забывайте, что при использовании в формуле текстовых значений нужно использовать кавычки <””>. Для числовых значений этого делать не нужно.
В результате наша формула СЧËТЕСЛИ выглядит следующим образом:
=СЧËТЕСЛИ(D6:D25;"Молочный шоколад ")
Получаем результат – 6 продаж.
Примечание.
Функция СЧËТЕСЛИ работает только с отдельными ячейками либо столбцами Google таблицы. Иначе говоря, в качестве диапазона поиска нельзя использовать несколько различных ячеек либо несколько строк и столбцов.
Неправильно:
=СЧËТЕСЛИ(C6:D25;"Молочный шоколад")
=СЧËТЕСЛИ(D6, D8, D10, D12, D14,"Молочный шоколад").
Правильно:
=СЧËТЕСЛИ(C6:C25;"Молочный шоколад") + СЧЕТЕСЛИ(D6:D25;"Молочный шоколад")
=СЧËТЕСЛИ(D6;"Молочный шоколад") + СЧËТЕСЛИ(D8;"Молочный шоколад") + СЧËТЕСЛИ(D10;"Молочный шоколад") + СЧËТЕСЛИ(D12;"Молочный шоколад") + СЧËТЕСЛИ(D14;"Молочный шоколад")
Но указывать в самой формуле критерий поиска не очень удобно – каждый раз придётся её редактировать. Правильным решением будет записать критерий поиска в определённую ячейку таблицы Google таблицах и затем просто ссылаться на нее из функции.
Давайте вместе подсчитаем количество успешных продаж, использовав в формуле функции СЧЕТЕСЛИ ссылку на ячейку Google таблицы.
Наша формула в ячейке C3 теперь будет выглядеть следующим образом:
=СЧЁТЕСЛИ(G6:G25;A3)
При работе функции ссылка на ячейку A3 будет заменена её значением “Да”. Как видите, условие поиска корректировать стало намного проще.
Проделаем то же самое для числовых значений. Как видите, количество значений, равное 144, можно успешно подсчитать либо прямо указывая нужное значение в условии функции
=СЧËТЕСЛИ(E7:E26;144),
либо заменяя его ссылкой на ячейку
=СЧËТЕСЛИ(E7:E26;A3).
Подстановочные знаки в условии функции СЧЕТЕСЛИ
Мы можем производить поиск и подсчет значений не по всему значению ячейки, а только по его части. Здесь мы можем использовать шаблон поиска (подстановочные знаки).
Как правильно указать, что мы будем искать? Для этого используем знаки “?”, “*”, “~”.
К примеру, для подсчета количества продаж в определённом регионе можно использовать только часть его названия: вводим в ячейку A3 значение “??????? город”. Символ “?” позволяет заменить собой один любой символ. Мы будем искать названия из 2 слов, первое из которых состоит из 7 букв, включая пробелы.
В ячейку B3 вводим функцию СЧËТЕСЛИ:
=СЧЁТЕСЛИ(C7:C26;A3)
Как мы уже говорили, это будет то же самое, что ввести в ячейку C3
=СЧЁТЕСЛИ(C7:C26; “??????? город”)
В результате видим, что было получено 12 заказов из «Большой город» и «Средний город».
А теперь в ячейку B4 введём
=СЧЁТЕСЛИ(C7:C26;A4)
И в ячейке A4 изменим условие поиска на “Б?????? город”. Теперь мы будем названия из 2 слов, первое из которых состоит из 7 букв, и начинается с «Б». Результатом выполнения функции будет 5 продаж, так как указанному условию теперь удовлетворяет только «Большой город».
Аналогично можно рассчитать количество продаж определенного товара, использовав символ ”*”, который позволяет заменить собой не один, а любое количество символов.
По условию “*шоколад” мы ищем все названия продуктов, заканчивающиеся на “шоколад”.
По условию “шоколад*” мы ищем все названия продуктов, начинающиеся с “шоколад”. И, как вы уже догадались, если в условии поиска мы напишем “*шоколад*”, то мы будем искать все названия продуктов, содержащие слово “шоколад”.
Примечание. Если вам необходимо найти и подсчитать количество слов, которые содержат в себе символы * и ?, то используйте в вашей формуле знак тильда (~), поставив его перед этими символами. Тогда функция СЧËТЕСЛИ будет считать * и ? обычными символами, а не шаблоном поиска. Например,
=СЧËТЕСЛИ(D7:D26; “*~?*”) - ищем количество значений, в которых есть знак “?”.
Больше, меньше или равно в качестве условия
Функция СЧËТЕСЛИ в Google таблице может не только определить количество чисел, равных заданному критерию, но и подсчитать, сколько чисел имеют значение больше или меньше заданного, либо просто не равны ему.
Для этого к критерию нужно добавить соответствующий математический знак “=”, “>”, “<”, “>=”, “<=”, “<>”.
Посмотрите сначала, как эти условия можно использовать прямо в формуле функции.
| Критерий | Пример формулы | Описание |
| Число больше чем | =СЧЕТЕСЛИ(E7:E15,">100") | Подсчитывает число ячеек, значение которых больше 100 |
| Число меньше чем | = СЧЕТЕСЛИ (E7:E15,"<100") | Подсчитывает число ячеек, значение которых меньше 100 |
| Число равно | =СЧЕТЕСЛИ(E7:E15,"=100") | Подсчитывает число ячеек, значение которых равно 100. |
| Число не равно | =СЧЕТЕСЛИ(E7:E15,"<>100") | Подсчитывает число ячеек, значение которых не равно 100 |
| Число больше или равно | =СЧЕТЕСЛИ(E7:E15,">=100") | Подсчитывает число ячеек, значение которых больше или равно 100 |
| Число меньше или равно | =СЧЕТЕСЛИ(E7:E15,"<=100") | Подсчитывает число ячеек, значение которых меньше или равно 100 |
Чтобы мы могли изменять критерий подсчета, не меняя формулу, мы здесь также можем использовать ссылки на ячейки.
В ячейке В3 запишем формулу, которая ссылается на ячейку A3, как мы уже делали ранее.
=СЧËТЕСЛИ(F9:F28;A3)
Для записи более сложных критериев используется оператор объединения (&).
К примеру, в ячейке B4 мы записали формулу, которая подсчитает число ячеек в диапазоне F9:F18, значение которых больше 150.
=СЧËТЕСЛИ(E9:E28;">="&A4)
В ячейке В5 записано то же самое условие, но в ссылках на ячейки находится не только число, но и математический знак. Это ещё больше упрощает изменение функции СЧЕТЕСЛИ, если это будет необходимо.
=СЧËТЕСЛИ(E9:E28;A6&A5)
Использование нескольких критериев в функции СЧЕТЕСЛИ
Бывает необходимо подсчитать количество значений в Google таблице, удовлетворяющих сразу двум либо нескольким критериям. Для этого в ячейке мы используем сразу несколько функций СЧЕТЕСЛИ.
Подсчитаем количество продаж шоколада с орехами и марки «супер». Для этого используем в ячейке В4 формулу
=СЧЁТЕСЛИ(D7:D26;"*орех*")+СЧЁТЕСЛИ(D7:D26;"*супер*")
Обратите внимание на использование шаблона поиска (*), чтобы слова «орех» и «супер» были подсчитаны независимо от того, где они находятся в названии – в начале, в середине или в конце.
Можно записать эту же формулу с использованием ссылок на ячейки. Это сделано нами в ячейке В3.
=СЧЁТЕСЛИ(D7:D26;A3)+СЧЁТЕСЛИ(D7:D26;A4)
Как видите, результат получился одинаковым.
Используя несколько критериев, можно подсчитать также количество чисел, находящихся в определённом диапазоне.
В нашем примере мы подсчитаем количество продаж, стоимость которых находится в диапазоне от 100 до 200.
Из количества продаж, величина которых меньше 200, вычитаем количество продаж, которые меньше 100. Используем формулу:
=СЧЁТЕСЛИ(E7:E26;"<=200")-СЧЁТЕСЛИ(E7:E26;"<=100")
В результате получим количество продаж, которые больше 100, но меньше 200.
Если использовать ссылки на ячейки А3 и А4, в которых записаны эти критерии выбора, то тогда формула будет такая:
=СЧЁТЕСЛИ(E7:E26;A3)-СЧЁТЕСЛИ(E7:E26;A4)
В ячейке А3 укажем критерий “<=200”, в ячейке А4 – критерий “<=100”.
Записываем формулы двумя способами в ячейки В3 и В4, получаем результат – 5 продаж находятся в нужном нам интервале.
Подсчитываем количество пустых и непустых ячеек
При помощи функции СЧËТЕСЛИ мы можем также подсчитать количество пустых либо количество непустых ячеек в заданном диапазоне Google таблицы.
Предположим, после удачно завершенной продажи в колонке «Выполнен» мы делаем отметку “Да”, если покупатель отказался от товара – пишем в ячейке “Нет” или ставим 0 (ноль), а если сделка не завершена, то ячейка пока остаётся пустой.
Для подсчета пустых и непустых ячеек используем шаблон поиска.
Если необходимо подсчитать количество непустых текстовых значений ячеек, то используем формулу:
=СЧËТЕСЛИ(G8:G27;"<>") или
=СЧËТЕСЛИ(G8:G27;A3).
Чтобы подсчитать количество пустых ячеек, функцию СЧЕТЕСЛИ запишем следующим образом:
=СЧËТЕСЛИ(G8:G27;"") или
=СЧËТЕСЛИ(G8:G27;A4).
Количество ячеек с текстом подсчитываем так:
=СЧËТЕСЛИ(G8:G27;"*") или
=СЧËТЕСЛИ(G8:G27;A5).
В ячейках A3, A4, A5 мы записали критерии отбора.
Как видим, 3 заказа пока не имеют никаких отметок и, следовательно, не завершены.
Использование СЧËТЕСЛИ для условного форматирования ячеек
Google таблицы дают пользователям полезную возможность – изменять формат ячейки (например цвет) в зависимости от выполнения определённых условий. Подробнее об этом вы можете прочитать здесь: Как использовать условное форматирование в Google таблицах.
К примеру, значения, которые встречаются чаще, могут быть отмечены определённым цветом.
Для этого также можно использовать функцию СЧЕТЕСЛИ.
Выделите при помощи мышки диапазон ячеек, которые вы хотите форматировать особым образом в зависимости от условия. Нажмите меню Формат – Условное форматирование. Введите условия форматирования, выбрав пункт “Ваша формула”
Введите туда выражение
=СЧËТЕСЛИ($B$10:$B$39;B10)/СЧЕТЕСЛИ($B$10:$B$39;"*")>0.4
Это означает, что условие будет выполняться, если значение ячейки В10 встречается в нашем диапазоне данных B10:B39 более чем в 40% случаев.
Аналогичным образом в правила условного форматирования добавляем ещё 2 условия - значение ячейки встречается чаще чем в 25% случаев и чаще чем 15%.
=СЧËТЕСЛИ($B$10:$B$39;B10)/СЧЕТЕСЛИ($B$10:$B$39;"*")>0.25
=СЧËТЕСЛИ($B$10:$B$39;B10)/СЧЕТЕСЛИ($B$10:$B$39;"*")>0.15
Обратите внимание, что сначала проверяется первое условие, и если оно выполнено, то остальные условия в ячейке не проверяются. Поэтому начинать нужно с самых редких значений, переходя на более часто встречающиеся. Если же значение ячейки не будет удовлетворять ни одному из условий, то её формат не изменится.
Вы видите, что цвет ячеек B10:B39 изменился в соответствии с установленными нами условиями.
Для проверки мы также рассчитали в ячейках A1:C7, как часто встречаются определённые значения, используя уже описанные ранее способы использования функции СЧËТЕСЛИ.
Эти расчёты подтверждают, что условное форматирование при помощи функции СЧËТЕСЛИ мы выполнили правильно.
Итак, мы с вами смогли убедиться, что функция Google таблиц СЧËТЕСЛИ действительно дает нам множество возможностей для работы с данными.
