Функция СЧЕТЕСЛИ в Google таблицах – это одна из самых простых функций, но при этом она очень полезна.

Здесь мы подробно рассмотрим, как применяется функция СЧЕТЕСЛИ при работе в Google таблицах, а также какую пользу мы можем получить от ее использования. На примерах мы разберём, как можно указывать критерии подсчета, как можно использовать несколько критериев.

Также мы предложим нестандартный способ использования функции СЧЕТЕСЛИ – для условного форматирования ячеек таблицы.

Что такое функция СЧЕТЕСЛИ в Google таблицах?

Она позволяет подсчитать количество определённых значений в указанном вами диапазоне данных. Эти значения должны удовлетворять определённому условию, которое вы сами задаёте.

Синтаксис функции СЧЕТЕСЛИ в Google таблицах

Вот как выглядит синтаксис этой функции и её аргументы:

[box type="info"] =СЧЕТЕСЛИ(диапазон, критерий)[/box]

Диапазон – это диапазон данных, в котором мы будем производить поиск нужных нам значений

Критерий – это критерий поиска - значение, которое мы будем искать.

Примеры использования СЧЕТЕСЛИ в Google таблицах.

Несмотря на то, что функция COUTNIF кажется очень простой, на самом деле её возможности достаточно велики. Это множество возможностей состоит в первую очередь в том, как мы будем описывать критерий поиска.

Ведь искать можно не только точно указанные значения, но и значения, которые соответствуют определённым условиям поиска.

Теперь давайте попробуем сделать это вместе и рассмотрим несколько примеров.

Полное совпадение числа или текста в функции СЧЕТЕСЛИ.

Предположим, ваша компания занимается продажей различных сортов шоколада в нескольких регионах и работает с множеством покупателей.

Вот как выглядят данные о ваших продажах, хранящиеся в Google таблицах.

исходные данные

Начнём с самого простого.

Вставим перед таблицей 4 строки, чтобы лучше видеть результаты вычислений.

Подсчитаем количество заявок на продажу молочного шоколада. Устанавливаем курсор в ячейку C3 и вводим знак “=”. Для Google таблиц это означает, что сейчас будет введена формула. Поэтому как только далее будет  введена буква “c”, он выведет нам предложение выбрать функцию, начинающуюся с буквы “c”. Выбираем “СЧЕТЕСЛИ”.

В качестве первого аргумента функции СЧЕТЕСЛИ вводим диапазон: D6:D25. Как и в других функциях Google таблиц, адрес диапазона можно не вводить вручную, а просто выделить мышкой. Затем вводим “,” и указываем второй аргумент – условие поиска.

Второй аргумент – это значение, которое мы будем искать в обозначенном диапазоне. Это будет текст “Молочный шоколад”.  Не забываем закончить ввод функции, нажав “)” и затем “Enter”.

Также не забывайте, что при использовании в формуле текстовых значений нужно использовать кавычки <””>. Для числовых значений этого делать не нужно.

В результате наша функция выглядит следующим образом:

=СЧЕТЕСЛИ(D6:D25;"Молочный шоколад ")

Получаем результат  – 6 продаж.

синтаксис функции счетесли

Примечание.

Функция СЧЕТЕСЛИ работает только с отдельными ячейками либо столбцами. Иначе говоря, в качестве диапазона поиска нельзя использовать несколько различных ячеек либо несколько строк и столбцов.

Неправильно:

=СЧЕТЕСЛИ(C6:D25;"Молочный шоколад")

=СЧЕТЕСЛИ(D6, D8, D10, D12, D14,"Молочный шоколад").

Правильно:

=СЧЕТЕСЛИ(C6:C25;"Молочный шоколад") + СЧЕТЕСЛИ(D6:D25;"Молочный шоколад")

=СЧЕТЕСЛИ(D6;"Молочный шоколад") + СЧЕТЕСЛИ(D8;"Молочный шоколад") + СЧЕТЕСЛИ(D10;"Молочный шоколад") + СЧЕТЕСЛИ(D12;"Молочный шоколад") + СЧЕТЕСЛИ(D14;"Молочный шоколад")

Но указывать в самой формуле критерий поиска не очень удобно – каждый раз придётся её редактировать. Правильным решением будет записать критерий поиска в определённую ячейку таблицы 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; “*~?*”)  - ищем количество значений, в которых есть знак “?”.

Больше, меньше или равно в качестве условия

Функция СЧЕТЕСЛИ может не только определить количество чисел, равных заданному критерию, но и подсчитать, сколько чисел имеют значение больше или меньше заданного, либо просто не равны ему.

Для этого к критерию нужно добавить соответствующий математический знак “=”, “>”, “<”, “>=”, “<=”, “<>”.

Посмотрите сначала, как эти условия можно использовать прямо в формуле функции.

Критерий Пример формулы Описание
Число больше чем =СЧЕТЕСЛИ(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)

динамически изменяемые критерии

Использование нескольких критериев в функции СЧЕТЕСЛИ

Бывает необходимо подсчитать количество значений, удовлетворяющих сразу двум либо нескольким критериям. Для этого в ячейке мы используем сразу несколько функций СЧЕТЕСЛИ.

Подсчитаем количество продаж шоколада с орехами и марки «супер». Для этого используем в ячейке В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 продаж находятся в нужном нам интервале.

Подсчитываем количество пустых и непустых ячеек

  При помощи функции СЧЕТЕСЛИ мы можем также подсчитать количество пустых либо количество непустых ячеек в заданном диапазоне.

Предположим, после удачно завершенной продажи в колонке «Выполнен» мы делаем отметку “Да”, если покупатель отказался от товара – пишем в ячейке “Нет” или ставим 0 (ноль), а если сделка не завершена, то ячейка пока остаётся пустой.

Для подсчета пустых и непустых ячеек используем шаблон поиска.

Если необходимо подсчитать количество непустых текстовых значений ячеек, то используем формулу:

=СЧЕТЕСЛИ(G8:G27;"<>") или

=СЧЕТЕСЛИ(G8:G27;A3).

Чтобы подсчитать количество пустых ячеек, функцию СЧЕТЕСЛИ запишем следующим образом:

=СЧЕТЕСЛИ(G8:G27;"") или

=СЧЕТЕСЛИ(G8:G27;A4).

Количество ячеек с текстом подсчитываем так:

=СЧЕТЕСЛИ(G8:G27;"*") или

=СЧЕТЕСЛИ(G8:G27;A5).

В ячейках A3, A4, A5 мы записали критерии отбора.

подсчитать пустые и непустые ячейки

Как видим, 3 заказа пока не имеют никаких отметок и, следовательно, не завершены.

Использование СЧЕТЕСЛИ для условного форматирования ячеек

Google таблицы дают пользователям интересную возможность – изменять формат ячейки (например цвет) в зависимости от выполнения определённых условий.

К примеру, значения, которые встречаются чаще, могут быть отмечены определённым цветом.

Для этого также можно использовать функцию СЧЕТЕСЛИ.

Выделите при помощи мышки диапазон ячеек, которые вы хотите форматировать особым образом в зависимости от условия. Нажмите меню Format – Условное форматирование. Введите условия форматирования, выбрав пункт “Ваша формула”

Введите туда выражение

=СЧЕТЕСЛИ($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 таблиц СЧЕТЕСЛИ действительно дает нам множество возможностей для работы с данными.