Если вы хотите подсчитать количество элементов в диапазоне ячеек на основе определенных критериев, то следует использовать функцию СЧЁТЕСЛИ в Google Таблицах.
Если вы хотите подсчитать количество элементов в диапазоне ячеек на основе определенных критериев, то следует использовать функцию СЧЁТЕСЛИ в Google Таблицах.
Вы можете подсчитать количество появлений чего-либо, используя критерии для чисел или текста в диапазоне ячеек.
В этой статье вы познакомитесь с синтаксисом функции СЧЁТЕСЛИ, а также с несколькими примерами.
Синтаксис COUNTIF в Google Таблицах
Во-первых, давайте посмотрим, как вам нужно структурировать функцию СЧЁТЕСЛИ, чтобы она работала правильно.
Есть только два параметра:
СЧЁТЕСЛИ (диапазон, критерий)
Эти параметры определяют диапазон для поиска и условия, которые вы хотите использовать для подсчета.
Если данные, которые вы ищете, являются текстовыми, вы можете либо ввести точный текст, который хотите найти, либо включить подстановочные знаки. Текстовые символы подстановки включают:
Теперь, когда вы понимаете параметры, давайте посмотрим, как функция СЧЁТЕСЛИ выполняет поиск в диапазоне.
Использование СЧЁТЕСЛИ для диапазонов
Рассмотрим сценарий, в котором у вас есть небольшой диапазон ячеек, содержащий список имен учащихся и их оценок за тест.
В этом случае диапазон, который вы хотите использовать в функции СЧЁТЕСЛИ, — A2: B10.
Если вы хотите найти все оценки старше 80, функция СЧЁТЕСЛИ будет выглядеть так: = СЧЁТЕСЛИ (A1: B10; «> 80»)
Если вы введете это в одну из пустых ячеек и нажмете Войти ключ, СЧЁТЕСЛИ выполняет поиск следующим образом:
При использовании этой функции помните, что если в обеих строках есть числа, в таком случае подсчет будет включать числа в любом столбце. Так что тщательно разрабатывайте свой критерий.
Пример 1: числовые ограничения
В приведенном выше примере был один из методов подсчета элементов в диапазоне, когда данные числовые. В приведенном выше примере используются операторы сравнения. Вы также можете выполнить поиск по точному номеру.
Например, если вы наберете = СЧЁТЕСЛИ (A1: B10; «100») и нажмите Enter, вы увидите список того, сколько сотен было в списке.
В данном случае их два.
Вы также можете подсчитать все числа, которые не равны 100, набрав = СЧЁТЕСЛИ (A1: B10; «<> 100»)
В этом случае результат будет 18, потому что 18 чисел не равно 100. Как видите, использовать СЧЁТЕСЛИ с числовыми данными очень просто!
Пример 2: Подсчет определенных записей
Если вы работаете с текстовыми данными, операторы сравнения работать не будут. В этих случаях вам нужно будет ввести именно тот текст, который вы ищете. СЧЁТЕСЛИ скажет вам, сколько раз повторялся этот точный текст. Это полезно, когда вы ищете определенные записи в списке данных.
Пример ниже представляет собой список школ и их национальные баллы по SAT.
Невозможно узнать, сколько раз появляется школа, без поиска в столбце «Название школы» вручную.
Вместо этого вы можете использовать функцию СЧЁТЕСЛИ, чтобы узнать, сколько раз появляется конкретная школа.
Для этого введите: = СЧЁТЕСЛИ (A1: B10, «средняя школа»). Когда вы нажимаете Войти, вы увидите, что результат равен 1.
Это означает, что «средняя школа» появляется в списке школ только один раз.
Вы также можете использовать специальные символы для поиска частичных записей. Например, если вы наберете = СЧЁТЕСЛИ (A1: B10; «* колледж *»), вы увидите количество записей, содержащих слово «колледж».
В данном случае это 14 случаев, когда название школы содержит слово «колледж».
В качестве альтернативы вы также можете использовать знак «?» персонаж, чтобы найти школы, содержащие слова «из» или «если», набрав: = СЧЁТЕСЛИ (A1: B10; «*? F *»).
Заметка: Используемый вами поисковый текст не чувствителен к регистру, поэтому вы можете использовать заглавные или строчные буквы, и он все равно будет находить правильные результаты.
Пример 3: Фильтрация пробелов
Есть два способа обработки пробелов с помощью функции СЧЁТЕСЛИ. Вы можете подсчитывать пустые ячейки или подсчитывать непустые ячейки.
Имейте в виду, что если вы ищете весь диапазон, функция СЧЁТЕСЛИ будет считать каждый пустая ячейка.
Однако, если вас интересует только количество строк с пустыми ячейками, вам следует вместо этого сделать диапазон одной строкой.
Итак, в этом случае вы должны ввести: = СЧЁТЕСЛИ (C1: C460; «»).
Это фокусирует ваш поиск на одном столбце и возвращает все пробелы.
Вы также можете выполнить поиск по количеству непустых строк.
Для этого введите следующее: = СЧЁТЕСЛИ (C1: C460; «<>»).
Нажмите Enter, и вы увидите количество непустых ячеек в столбце.
Выше приведены примеры всех параметров, которые можно использовать с функцией СЧЁТЕСЛИ.
Если вам интересно узнать, как искать диапазоны с использованием нескольких критериев, вы также можете изучить использование функции СЧЁТЕСЛИ. Вы также можете изучить аналогичные действия с функциями в Excel, если хотите.
Как использовать функцию СЧЕТЕСЛИ в Google таблицах
Функция СЧЕТЕСЛИ в Google таблицах – это одна из самых простых функций, но при этом она очень полезна.
Здесь мы подробно рассмотрим, как применяется функция СЧЕТЕСЛИ при работе в Google таблицах, а также какую пользу мы можем получить от ее использования. На примерах мы разберём, как можно указывать критерии подсчета, как можно использовать несколько критериев.
Также мы предложим нестандартный способ использования функции СЧЕТЕСЛИ – для условного форматирования ячеек таблицы.
Что такое функция СЧЕТЕСЛИ в Google таблицах?
Она позволяет подсчитать количество определённых значений в указанном вами диапазоне данных. Эти значения должны удовлетворять определённому условию, которое вы сами задаёте.
Синтаксис функции СЧЕТЕСЛИ в Google таблицах
Вот как выглядит синтаксис этой функции и её аргументы:
=СЧЕТЕСЛИ(диапазон, критерий)
Диапазон – это диапазон данных, в котором мы будем производить поиск нужных нам значений
Критерий – это критерий поиска – значение, которое мы будем искать.
Примеры использования СЧЕТЕСЛИ в Google таблицах.
Несмотря на то, что функция COUTNIF кажется очень простой, на самом деле её возможности достаточно велики. Это множество возможностей состоит в первую очередь в том, как мы будем описывать критерий поиска.
Ведь искать можно не только точно указанные значения, но и значения, которые соответствуют определённым условиям поиска.
Теперь давайте попробуем сделать это вместе и рассмотрим несколько примеров.
Полное совпадение числа или текста в функции СЧЕТЕСЛИ.
Предположим, ваша компания занимается продажей различных сортов шоколада в нескольких регионах и работает с множеством покупателей.
Вот как выглядят данные о ваших продажах, хранящиеся в Google таблицах.
Начнём с самого простого.
Вставим перед таблицей 4 строки, чтобы лучше видеть результаты вычислений.
Подсчитаем количество заявок на продажу молочного шоколада. Устанавливаем курсор в ячейку C3 и вводим знак “=”. Для Google таблиц это означает, что сейчас будет введена формула. Поэтому как только далее будет введена буква “c”, он выведет нам предложение выбрать функцию, начинающуюся с буквы “c”. Выбираем “СЧЕТЕСЛИ”.
В качестве первого аргумента функции СЧЕТЕСЛИ вводим диапазон: D6:D25. Как и в других функциях Google таблиц, адрес диапазона можно не вводить вручную, а просто выделить мышкой. Затем вводим “,” и указываем второй аргумент – условие поиска.
Второй аргумент – это значение, которое мы будем искать в обозначенном диапазоне. Это будет текст “Молочный шоколад”. Не забываем закончить ввод функции, нажав “)” и затем “Enter”.
В результате наша функция выглядит следующим образом:
Получаем результат – 6 продаж.
Примечание.
Функция СЧЕТЕСЛИ работает только с отдельными ячейками либо столбцами. Иначе говоря, в качестве диапазона поиска нельзя использовать несколько различных ячеек либо несколько строк и столбцов.
Но указывать в самой формуле критерий поиска не очень удобно – каждый раз придётся её редактировать. Правильным решением будет записать критерий поиска в определённую ячейку таблицы Google таблицах и затем просто ссылаться на нее из функции.
Давайте вместе подсчитаем количество успешных продаж, использовав в формуле функции СЧЕТЕСЛИ ссылку на ячейку.
Наша формула в ячейке C3 теперь будет выглядеть следующим образом:
При работе функции ссылка на ячейку A3 будет заменена её значением “Да”. Как видите, условие поиска корректировать стало намного проще.
Проделаем то же самое для числовых значений. Как видите, количество значений, равное 144, можно успешно подсчитать либо прямо указывая нужное значение в условии функции
либо заменяя его ссылкой на ячейку
Подстановочные знаки в условии функции СЧЕТЕСЛИ
Мы можем производить поиск и подсчет значений не по всей ячейке, а только по её части. Здесь мы можем использовать шаблон поиска (подстановочные знаки).
Как правильно указать, что мы будем искать? Для этого используем знаки “?”, “*”, “
К примеру, для подсчета количества продаж в определённом регионе можно использовать только часть его названия: вводим в ячейку A3 значение “. город”. Символ “?” позволяет заменить собой один любой символ. Мы будем искать названия из 2 слов, первое из которых состоит из 7 букв, включая пробелы.
В ячейку B3 вводим функцию СЧЕТЕСЛИ:
Как мы уже говорили, это будет то же самое, что ввести в ячейку C3
В результате видим, что было получено 12 заказов из «Большой город» и «Средний город».
А теперь в ячейку B4 введём
И в ячейке A4 изменим условие поиска на “Б. город”. Теперь мы будем названия из 2 слов, первое из которых состоит из 7 букв, и начинается с «Б». Результатом выполнения функции будет 5 продаж, так как указанному условию теперь удовлетворяет только «Большой город».
Аналогично можно рассчитать количество продаж определенного товара, использовав символ ”*”, который позволяет заменить собой не один, а любое количество символов.
По условию “*шоколад” мы ищем все названия продуктов, заканчивающиеся на “шоколад”.
По условию “шоколад*” мы ищем все названия продуктов, начинающиеся с “шоколад”. И, как вы уже догадались, если в условии поиска мы напишем “*шоколад*”, то мы будем искать все названия продуктов, содержащие слово “шоколад”.
?*”) – ищем количество значений, в которых есть знак “?”.
Больше, меньше или равно в качестве условия
Функция СЧЕТЕСЛИ может не только определить количество чисел, равных заданному критерию, но и подсчитать, сколько чисел имеют значение больше или меньше заданного, либо просто не равны ему.
Для этого к критерию нужно добавить соответствующий математический знак “=”, “>”, “ =”, “ ”.
Посмотрите сначала, как эти условия можно использовать прямо в формуле функции.
Критерий
Пример формулы
Описание
Число больше чем
=СЧЕТЕСЛИ(E7:E15,”>100″)
Подсчитывает число ячеек, значение которых больше 100
Число меньше чем
= СЧЕТЕСЛИ (E7:E15,” 100″)
Подсчитывает число ячеек, значение которых не равно 100
Число больше или равно
=СЧЕТЕСЛИ(E7:E15,”>=100″)
Подсчитывает число ячеек, значение которых больше или равно 100
Число меньше или равно
=СЧЕТЕСЛИ(E7:E15,”
Чтобы мы могли изменять критерий подсчета, не меняя формулу, мы здесь также можем использовать ссылки на ячейки.
В ячейке В3 запишем формулу, которая ссылается на ячейку A3, как мы уже делали ранее.
Для записи более сложных критериев используется оператор объединения (&).
К примеру, в ячейке B4 мы записали формулу, которая подсчитает число ячеек в диапазоне F9:F18, значение которых больше 150.
В ячейке В5 записано то же самое условие, но в ссылках на ячейки находится не только число, но и математический знак. Это ещё больше упрощает изменение функции СЧЕТЕСЛИ, если это будет необходимо.
Использование нескольких критериев в функции СЧЕТЕСЛИ
Бывает необходимо подсчитать количество значений, удовлетворяющих сразу двум либо нескольким критериям. Для этого в ячейке мы используем сразу несколько функций СЧЕТЕСЛИ.
Подсчитаем количество продаж шоколада с орехами и марки «супер». Для этого используем в ячейке В4 формулу
Обратите внимание на использование шаблона поиска (*), чтобы слова «орех» и «супер» были подсчитаны независимо от того, где они находятся в названии – в начале, в середине или в конце.
Можно записать эту же формулу с использованием ссылок на ячейки. Это сделано нами в ячейке В3.
Как видите, результат получился одинаковым.
Используя несколько критериев, можно подсчитать также количество чисел, находящихся в определённом диапазоне.
В нашем примере мы подсчитаем количество продаж, стоимость которых находится в диапазоне от 100 до 200.
Из количества продаж, величина которых меньше 200, вычитаем количество продаж, которые меньше 100. Используем формулу:
Чтобы подсчитать количество пустых ячеек, функцию СЧЕТЕСЛИ запишем следующим образом:
Количество ячеек с текстом подсчитываем так:
В ячейках A3, A4, A5 мы записали критерии отбора.
Как видим, 3 заказа пока не имеют никаких отметок и, следовательно, не завершены.
Использование СЧЕТЕСЛИ для условного форматирования ячеек
Google таблицы дают пользователям интересную возможность – изменять формат ячейки (например цвет) в зависимости от выполнения определённых условий.
К примеру, значения, которые встречаются чаще, могут быть отмечены определённым цветом.
Для этого также можно использовать функцию СЧЕТЕСЛИ.
Выделите при помощи мышки диапазон ячеек, которые вы хотите форматировать особым образом в зависимости от условия. Нажмите меню Format – Условное форматирование. Введите условия форматирования, выбрав пункт “Ваша формула”
Введите туда выражение
Это означает, что условие будет выполняться, если значение ячейки В10 встречается в нашем диапазоне данных B10:B39 более чем в 40% случаев.
Аналогичным образом в правила условного форматирования добавляем ещё 2 условия – значение ячейки встречается чаще чем в 25% случаев и чаще чем 15%.
Обратите внимание, что сначала проверяется первое условие, и если оно выполнено, то остальные условия в ячейке не проверяются. Поэтому начинать нужно с самых редких значений, переходя на более часто встречающиеся. Если же значение ячейки не будет удовлетворять ни одному из условий, то её формат не изменится.
Вы видите, что цвет ячеек B10:B39 изменился в соответствии с установленными нами условиями.
Для проверки мы также рассчитали в ячейках A1:C7, как часто встречаются определённые значения, используя уже описанные ранее способы использования функции СЧЕТЕСЛИ.
Эти расчёты подтверждают, что условное форматирование при помощи функции СЧЕТЕСЛИ мы выполнили правильно.
Итак, мы с вами смогли убедиться, что функция Google таблиц СЧЕТЕСЛИ действительно дает нам множество возможностей для работы с данными.
Другие примеры использования функций Google таблиц:
Функция IF (ЕСЛИ) в Google таблицах — на практике одна из самых востребованных. В зависимости от условий она возвращает указанный результат (значение или другую формулу со своими параметрами).
Новички без особых знаний (математика за 3 класс) с помощью данной функции могут построить простые отчеты, которые уже будут удовлетворять потребности в визуальном восприятии результата работы.
Функция IF (ЕСЛИ) в Google таблицах
Логическая функция имеет следующий синтаксис:
Примеры функции IF (ЕСЛИ) в Google таблицах
Функция IF (ЕСЛИ) практически всегда используется в связке с другими функциями. Чаще всего она является составным элементом в сложной аналитической структуре. Но ее можно использовать и в простейших вариантах.
Простой пример функции IF (ЕСЛИ)
Рассмотрим очень простой пример проверки продаж отдельных продуктов. По условию, нам нужно пометить, какие продукты хорошо продаются, а какие нет. Критерий определения — больше 30 кг в день. Если в день мы продаем больше 30 кг — Хорошо, если меньше — Плохо.
В таком случае синтаксис формулы будет выглядеть так:
Все текстовые выражения помещаются в кавычки (слова, числа в виде текста и т.д.).
Пошагово: IF (ЕСЛИ) мы продали моркови больше 30 кг за 1 день, то выводится значение_если_истина — «Хорошо» (да, мы продали 35 кг > 30 кг), если меньше — «Плохо» ( [значение_если_ложь] — альтернативное значение, если условие не соблюдается).
Более сложный пример функции IF (ЕСЛИ)
Допустим, у нас на складе есть определенное количество овощей и нам нужно рассчитать сколько товаров останется после недели торговли. Для этого нам нужно умножить значения дневных продаж на 7 (дни недели), а зачем вычесть полученный результат из имеющихся складских остатков.
Мы столкнулись с ситуацией, когда математический результат продаж наших товаров оказался отрицательным, а все понимают, что остатков на складе не может быть меньше нуля. Чтобы прогноз был корректным, нужно заменить отрицательные значения нулями. С такой задачей прекрасно справится функция IF (ЕСЛИ). Она проверит результат вычислений и если он окажется меньше нуля — выдаст ответ «0», а если больше — отобразит результат вычислений.
Таким образом мы получили корректный результат и в прогнозе нет больше отрицательных значений.
Чаще всего на практике требуется применять формулу где условий более чем 2 — 3, 4, 5… В таком случае можно использовать функцию, но уже вкладывая одну в одну, как матрешку, указывая все условия по очереди.
Рассмотрим пример многомерной функции IF (ЕСЛИ) на живом примере начисления премии по KPI продаж. Система начислений следующая:
Фактически у нас 4 конкретных условия, которые могут быть указаны по следующей логической структуре:
Цветом выделены 4 условия, по которым формула выводит результат заданной функции. Важно понимать, что комбинация функций IF (ЕСЛИ) работает так, что при выполнении какого-либо указанного условия — следующие уже не проверяются. Поэтому их нужно указать в правильной последовательности.
При создании формульной логической цепочки очень легко запутаться. Новичкам рекомендуется либо абстрактно принимать переменные (условия) за «Х», «Y», «Z», …, либо смотреть на всплывающие подсказки. Плюсом, система сама подсвечивает разные переменные и условия, а так же, доставляет закрывающие скобки формулы функций.
Функция IFS (ЕСЛИМН) в Google таблицах
В более поздних версиях Excel и Google таблицах (с 2016 года) появилась функция IFS (ЕСЛИМН). Она фиксирует сразу множество условий, что значительно оптимизирует серверную нагрузку (формулы считаются быстрее и наши таблицы не тормозят так, как это было бы, если бы сервер просчитывал каждое вложенное условие IF (ЕСЛИ)) и разгружает наше абстрактное мышление, упраздняя повторяющиеся простые условия IF (ЕСЛИ). По сути, это та же функция ЕСЛИ, только в разы упрощенная, к тому же, максимальное число условий увеличилось до 512.
В качестве примера возьмем имеющиеся значения из примеров выше и рассчитаем начисление премий уже более простой, но не менее функциональной функцией:
Формулы функции IFS (ЕСЛИМН) выполняют одно и то же действие, отличаясь завершающим последним обязательным (в отличии от обычной функции IF (ЕСЛИ)) аргументом. В первом варианте мы конкретно указали какое значение у аргумента должно выводиться, если выполняется 4 условие функции. Можно избежать конкретного действия, прописав значение «TRUE» (ИСТИНА), тогда будет выводиться последнее альтернативное значение 4 условия.
P.S.: первый вариант мне нравится больше.
Вы познакомились с функциями IF (ЕСЛИ) и IFS (ЕСЛИМН), увидели где их можно применить на практике, и, я уверен, сможете применить их в своей работе. Поделитесь, пожалуйста в комментариях своими идеями или случаями применения рассматриваемых функций.
Как считать пустые или пустые ячейки в Google Sheets
Когда вы анализируете данные в электронной таблице, подсчет пустых или пустых ячеек может помочь вам сосредоточиться на конкретных областях. Вот почему такие функции, как COUNTBLANK, COUNTIF, COUNTIFS и SUMPRODUCT, так важны в Google Sheets.
Слово предупреждения, однако. Если у вас есть ячейка, которая содержит пустую текстовую строку («») или имеет формулу, которая возвращает аналогичный результат, эта ячейка будет пустой, но технически она не будет пустой. Если вы хотите узнать количество действительно пустых ячеек, вам нужно использовать комбинацию функций SUM, ROWS, COLUMNS и COUNTIF.
Использование COUNTBLANK
Вы можете попробовать функцию COUNTBLANK, чтобы подсчитать количество пустых ячеек в электронной таблице Google Sheets. Это самый быстрый способ узнать количество пустых, но не пустых ячеек.
Ячейки, содержащие цифры или текст, не будут учитываться, включая ячейки с нулевым номером. Однако, как мы уже упоминали, если ячейка выглядит пустой, но содержит пустую текстовую строку («»), это будет засчитано.
В приведенном выше примере ячейки от A3 до H24 используются в пределах диапазона. Этот диапазон содержит четыре пустых ячейки (B4, C4, D4 и E4), что соответствует цифре, которую COUNTBLANK сообщает в ячейке A1.
Использование COUNTIF и COUNTIFS
Хотя COUNTBLANK возвращает количество пустых ячеек, вы также можете использовать COUNTIF или COUNTIFS для достижения того же результата.
COUNTIF подсчитывает количество ячеек, которые соответствуют критериям, которые вы определяете в самой формуле. Поскольку вы хотите считать пустые ячейки, вы можете использовать пустую текстовую строку в качестве критерия.
В приведенном выше примере три пустые ячейки (B4, C4 и D4) находятся в диапазоне от A3 до H24, а функция COUNTIF в ячейке A1 возвращает одинаковое количество пустых ячеек.
В приведенном выше примере были обнаружены четыре пустые ячейки в диапазоне ячеек от A3 до H24.
Использование SUMPRODUCT
Функция SUMPRODUCT предлагает немного более сложный способ подсчета количества пустых ячеек. Он подсчитывает количество ячеек, соответствующих определенным критериям, которые в этом случае были бы пустой текстовой строкой («»).
Приведенный выше пример показывает, что в диапазоне ячеек от А2 до Н24 были обнаружены две пустые ячейки (В4 и С4).
Подсчет пустых клеток
Все функции, перечисленные выше, подсчитывают пустые ячейки, но технически не пустые. Если функция возвращает нулевой или пустой результат или если в ячейке есть пустая текстовая строка («»), то эти ячейки считаются пустыми.
Временное решение этой проблемы — использовать COUNTIF для подсчета количества ячеек с числовым значением, а затем использовать вторую формулу COUNTIF для подсчета количества ячеек, содержащих текст или пустые текстовые строки.
Затем вы можете добавить результаты этих расчетов и вычесть их из числа ячеек в вашем диапазоне данных. Сначала вам нужно узнать количество ячеек в вашем диапазоне. Чтобы выяснить это, вы можете использовать функции ROWS и COLUMNS.
Во второй пустой ячейке введите =COUNTIF(range,»>=0″) чтобы подсчитать количество ячеек с числовым значением. Еще раз замените range на соответствующий диапазон ячеек для ваших данных.
Для поиска пустых ячеек или ячеек, содержащих текст, введите =COUNTIF(range,»*») в третьей пустой ячейке. Замените range при необходимости.
Затем вы можете использовать SUM, чтобы сложить два значения COUNTIF, вычтя эту цифру из числа ячеек в вашем диапазоне, рассчитанных с использованием функций ROWS и COLUMNS.
В нашем примере общее количество ячеек можно найти в ячейке B8, количество ячеек с числовым значением в B9 и количество ячеек, которые содержат текст или пустую текстовую строку в B10.
Заменив эти значения ячеек своими собственными, вы можете использовать =B8-SUM(B9:10) чтобы определить количество действительно пустых ячеек в вашем диапазоне.
Как показано в примере выше, в диапазоне 20 ячеек (от А2 до Е5) было обнаружено, что в 19 ячейках имеется либо число, либо текст, либо пустая текстовая строка. Только одна ячейка, Е4, была полностью пустой.
