гугл таблицы счет если не пусто
Cоветы и руководства по Google Таблицам
Мастер Google Таблиц с полезными советами и руководствами
Как подсчитать ячейки с определенным текстом в Google Таблицах (простая формула)
Когда вы работаете с большими наборами данных в Google Таблицах, вам иногда нужно подсчитывать ячейки, содержащие определенный текст. Это могут быть имена, идентификаторы или даже даты. А благодаря классным формулам в Google Таблицах это можно сделать за секунды.
В этом уроке я покажу вам несколько сценариев, в которых вы можете подсчитать ячейки, содержащие определенный текст.
Подсчет ячеек, содержащих определенный текст
Предположим, у вас есть набор данных, показанный ниже, и вы хотите быстро подсчитать, сколько раз имя «Отметка» встречается в столбце A.
Ниже приведена формула, которую вы можете использовать для этого:
СЧЁТЕСЛИ (A2: A13; «Отметка»)
Вот как работает приведенная выше формула СЧЁТЕСЛИ:
Приведенная выше формула выполняет условный подсчет, просматривая все ячейки в диапазоне и подсчитывая те ячейки, в которых присутствует текст критерия.
Обратите внимание, что текст критерия, используемый в этой формуле, не чувствителен к регистру. Итак, используете ли вы MARK, Mark или mark, результат будет одинаковым.
В этом примере я использовал имя, но это мог быть любой текст, например идентификаторы или числа.
Подсчет ячеек, не содержащих определенного текста
Вы можете немного изменить функцию СЧЁТЕСЛИ, чтобы получить количество ячеек, не содержащих определенного текста.
Например, если у меня есть набор данных, как показано ниже, и я хочу подсчитать все ячейки, имя которых не является «Маркой», я могу легко сделать это с помощью функции СЧЁТЕСЛИ.
Ниже приведена формула, по которой будет подсчитано количество всех ячеек, имя которых отличается от Mark:
= СЧЁТЕСЛИ (A2: A13; «<> Отметить»)
Подсчет ячеек, содержащих текст (в любом месте ячейки / частичное совпадение)
В приведенных выше примерах мы проверили все содержимое ячейки.
Поэтому, если мы хотели подсчитать все ячейки, содержащие имя Mark, мы использовали «Mark» в качестве критерия. Это подсчитало все ячейки, в которых все содержимое ячейки было «Отметить».
Но предположим, что у вас есть набор данных с полными именами (или другим текстом) вместе с именем, и вы хотите подсчитать все ячейки, содержащие имя «Отметить», тогда вы можете использовать приведенную выше формулу.
В этом случае вам нужно использовать функцию СЧЁТЕСЛИ с подстановочными знаками.
Ниже приведена формула, которая даст вам количество всех ячеек, в которых есть слово «Отметить».
= СЧЁТЕСЛИ (A2: A13; «* знак *»)
В приведенной выше функции критерий отмечен звездочкой (*) с обеих сторон.
Поскольку знак звездочки (*) — это подстановочный знак, который может представлять любое количество символов в формуле. Это означает, что там, где эта формула проверяет данное условие, может быть любое количество символов / слов до и после критериев.
Проще говоря, если в ячейке присутствует слово Mark (или любой другой критерий), эта формула будет считать ячейку.
Внимание: при использовании знака звездочки (*) в функциях Google Таблиц необходимо помнить одну вещь: условие будет считаться выполненным, как только будет найден текст критерия. Например, в приведенном выше примере, если ячейка содержит текст «Рынок» или «Маркетинг», они будут учитываться, поскольку они также содержат текст «Отметить», который не входит в критерии функции.
Если вы хотите подсчитать ячейки, проверяя наличие нескольких условий, вам необходимо использовать функцию СЧЁТЕСЛИМН.
Я надеюсь, что вы найдете этот урок полезным, и он поможет вам подсчитать ячейки с определенным текстом в Google Таблицах.
Один из вопросов, который я часто задаю, — можно ли использовать функцию СЧЁТЕСЛИ для подсчета ячеек с помощью цветов. К сожалению, нет. Однако вот руководство, в котором я показываю, как легко фильтровать и подсчитывать ячейки на основе цвета.
Cоветы и руководства по Google Таблицам
Мастер Google Таблиц с полезными советами и руководствами
Как использовать функцию COUNTIF (СЧЁТЕСЛИ) в Google Таблицах
Функция СЧЁТЕСЛИ (COUNTIF) в Google Таблицах может использоваться, когда вы хотите получить подсчет при выполнении заданного условия.
Например, если у вас есть список студентов и вы хотите быстро подсчитать всех студентов, набравших более 75 баллов, вы можете использовать функцию СЧЁТЕСЛИ в Google Таблицах.
Прежде чем я покажу вам несколько интересных примеров использования функции СЧЁТЕСЛИ, давайте быстро рассмотрим синтаксис и некоторые важные моменты.
СИНТАКСИС функции COUNTIF (СЧЁТЕСЛИ) в Google Таблицах
Критерием может быть:
Диапазон ячеек может содержать числа или текстовые строки.
Теперь давайте посмотрим на пример использования функции COUNTIF в Google Таблицах.
Функция СЧЁТЕСЛИ в Google Таблицах — Примеры
Вот шесть примеров использования функции СЧЁТЕСЛИ (COUNTIF ) в Google Таблицах.
Пример 1 — Подсчет количества студентов, набравших более 75 баллов
Предположим, у вас есть список учеников, получивших отметки в тесте.
Вот формула, по которой вы подсчитаете студентов, набравших больше 75 баллов.
Примечание:
Пример 2 — Подсчет всех записей транзакций в США
Предположим, у вас есть данные транзакции, как показано ниже:
Вот формула, которая даст нам количество транзакций для США:
Примечание:
Пример 3 — Подсчет всех записей, кроме США
Предположим, у вас есть те же данные транзакции (использованные в примере 3), и вы хотите подсчитать все записи транзакций, кроме записей в США.
Вот формула, которая сделает это:
Приведенная выше формула будет учитывать все транзакции, кроме тех, в которых страна находится в США.
Пример 4 — Подсчет всех пустых / пустых ячеек в диапазоне
Если у вас огромный набор данных и вы хотите получить количество отсутствующих записей или пустых или пустых ячеек, вы можете использовать для этого функцию СЧЁТЕСЛИ.
Предположим, у вас есть набор данных, как показано ниже:
Вот формула, которая даст вам общее количество пустых или пустых ячеек в ней:
В этом примере используется критерий «» (двойные кавычки). Если в качестве критерия используются только двойные кавычки, учитываются только пустые / пустые ячейки. Обратите внимание: если есть ячейка, которая выглядит пустой, но в ней есть пробел, то она не будет учитываться по этой формуле.
Пример 5 — Подсчет всех непустых ячеек в диапазоне
Взяв те же данные из примера 4, в этом примере давайте посмотрим, как подсчитать все непустые ячейки в наборе данных.
Вот формула, по которой будут подсчитаны все непустые ячейки:
В этом примере используется критерий «<>» (не равно знаку в двойных кавычках). Этот критерий означает, что ячейки не равны пустой строке. Обратите внимание: если есть ячейка, которая выглядит пустой, но в ней есть пробел, то она будет подсчитана по этой формуле.
Пример 6 — Подсчет всех экземпляров, в которых встречается ключевое слово
Иногда вам может потребоваться иметь дело с данными, которые содержат варианты одного и того же имени или элемента. Например, в приведенных ниже данных есть варианты Apple как Apple, Apple, Fruit — Apple.
Вот формула, которую вы можете использовать для подсчета общего количества ячеек с ключевым словом Apple:
В приведенном выше примере используется критерий * Apple *. Звездочка (*) — это подстановочный знак, обозначающий любое количество символов. Итак, * Apple * будет считать любую ячейку, в которой есть текст Apple, и она может быть окружена любым количеством символов с обеих сторон.
Cоветы и руководства по Google Таблицам
Мастер Google Таблиц с полезными советами и руководствами
Подсчет ячеек, ЕСЛИ НЕ ПУСТО (непустые ячейки) в Google Таблицах
В Google Таблицах есть отличные формулы, которые позволяют быстро получить информацию о наборе данных. Одной из обычных вещей, которые часто приходится делать многим людям, является подсчет непустых ячеек в наборе данных в Google Таблицах (т. е. Подсчет непустых ячеек).
Хотя это можно сделать вручную, если у вас небольшой набор данных, для больших лучше оставить это замечательным формулам COUNT в Google Таблицах.
И снова, есть несколько способов снять шкуру с этой кошки.
В этом уроке я покажу вам две действительно простые формулы для подсчета ячеек, если они не пустые в Google Таблицах.
Подсчитайте ячейки, если они не пустые, с помощью функции COUNTA
В Google Sheets есть функция COUNTA, которая подсчитывает все непустые ячейки в наборе данных (т. Е. Все ячейки, содержащие любую текстовую строку или число).
Предположим, у вас есть набор данных, показанный ниже, и вы хотите посчитать в нем непустые ячейки.
Вы можете использовать приведенную ниже формулу COUNTA для подсчета всех непустых ячеек в наборе данных:
Хотя формула COUNTA дает правильный результат, в этом случае она может дать вам неправильный результат, если есть:
Пустая строка может быть результатом формулы, и многие люди используют апостроф при вводе чисел (чтобы показать эти числа как текст)
Во всех вышеперечисленных случаях может показаться, что ячейки пусты, но функция COUNTA все равно будет рассматривать их как непустые и подсчитывать их в результате.
Ниже приведен пример, в котором у меня есть пустая строка (= ””) в ячейке A4 и апостроф в ячейке A8. Вы можете видеть, что результат формулы COUNTA — 11 (хотя только девять ячеек заполнены именами — или, по крайней мере, так кажется невооруженным глазом).
Если вы уверены, что в ваших данных нет пустых строк или апострофов, вы можете использовать приведенную выше формулу COUNTA, но в случае, если это возможно, для этого лучше использовать комбинацию формул (описано в следующий раздел).
Подсчет непустых ячеек с помощью функции СУММПРОИЗВ
Замечательно, насколько легко вы можете решать сложные задачи с помощью простого сочетания формул в Google Таблицах.
Когда дело доходит до подсчета непустых ячеек в наборе данных, могут быть ячейки с пустой строкой, пробелами или апострофом.
Хотя вы не можете полностью полагаться на формулу COUNTA, вот простая формула SUMPRODUCT, которая даст вам правильный результат во всех сценариях:
= СУММПРОИЗВ (LEN (TRIM (A2: A13))> 0)
Приведенная выше формула проверяет, есть ли в ячейке хотя бы один символ / число. Это проверяется функцией LEN.
Если длина символов в ячейке больше 0, она засчитывается, иначе не засчитывается.
Это касается двух сценариев — пустых строк и апострофа.
И часть функции TRIM гарантирует, что если в ячейках есть пробелы, они также будут проигнорированы.
Если у вас есть значения ошибок в ячейках, то эта формула выдаст вам ошибку.
[Быстрый СОВЕТ] Получите значение счетчика на панели задач
Если вы хотите быстро получить количество непустых ячеек, вы можете получить это на панели задач.
Все, что вам нужно сделать, это выбрать ячейки, в которых вы хотите получить количество непустых ячеек, и увидеть значение COUNT на панели задач (в правой нижней части документа Google Sheets).
Если в вашем наборе данных есть числа, по умолчанию на панели задач будет отображаться СУММ, а не СЧЁТ. В этом случае просто нажмите на него, и он покажет вам СЧЁТ (вместе с другими данными, такими как Среднее или Макс / Мин).
Обратите внимание, что он будет включать все ячейки, в которых что-либо есть — будь то пустая строка (= ””), апостроф (‘) или пробел.
Это три способа, которыми вы можете быстро подсчитать количество ячеек, если они не пустые, в Google Таблицах.
Как использовать функцию СЧЕТЕСЛИ в Google таблицах
Функция СЧЕТЕСЛИ в Google таблицах – это одна из самых простых функций, но при этом она очень полезна.
Здесь мы подробно рассмотрим, как применяется функция СЧЕТЕСЛИ при работе в Google таблицах, а также какую пользу мы можем получить от ее использования. На примерах мы разберём, как можно указывать критерии подсчета, как можно использовать несколько критериев.
Также мы предложим нестандартный способ использования функции СЧЕТЕСЛИ – для условного форматирования ячеек таблицы.
Что такое функция СЧЕТЕСЛИ в Google таблицах?
Она позволяет подсчитать количество определённых значений в указанном вами диапазоне данных. Эти значения должны удовлетворять определённому условию, которое вы сами задаёте.
Синтаксис функции СЧЕТЕСЛИ в Google таблицах
Вот как выглядит синтаксис этой функции и её аргументы:
=СЧЕТЕСЛИ(диапазон, критерий)
Диапазон – это диапазон данных, в котором мы будем производить поиск нужных нам значений
Критерий – это критерий поиска – значение, которое мы будем искать.
Примеры использования СЧЕТЕСЛИ в Google таблицах.
Несмотря на то, что функция COUTNIF кажется очень простой, на самом деле её возможности достаточно велики. Это множество возможностей состоит в первую очередь в том, как мы будем описывать критерий поиска.
Ведь искать можно не только точно указанные значения, но и значения, которые соответствуют определённым условиям поиска.
Теперь давайте попробуем сделать это вместе и рассмотрим несколько примеров.
Полное совпадение числа или текста в функции СЧЕТЕСЛИ.
Предположим, ваша компания занимается продажей различных сортов шоколада в нескольких регионах и работает с множеством покупателей.
Вот как выглядят данные о ваших продажах, хранящиеся в Google таблицах.
Начнём с самого простого.
Вставим перед таблицей 4 строки, чтобы лучше видеть результаты вычислений.
Подсчитаем количество заявок на продажу молочного шоколада. Устанавливаем курсор в ячейку C3 и вводим знак “=”. Для Google таблиц это означает, что сейчас будет введена формула. Поэтому как только далее будет введена буква “c”, он выведет нам предложение выбрать функцию, начинающуюся с буквы “c”. Выбираем “СЧЕТЕСЛИ”.
В качестве первого аргумента функции СЧЕТЕСЛИ вводим диапазон: D6:D25. Как и в других функциях Google таблиц, адрес диапазона можно не вводить вручную, а просто выделить мышкой. Затем вводим “,” и указываем второй аргумент – условие поиска.
Второй аргумент – это значение, которое мы будем искать в обозначенном диапазоне. Это будет текст “Молочный шоколад”. Не забываем закончить ввод функции, нажав “)” и затем “Enter”.
В результате наша функция выглядит следующим образом:
Получаем результат – 6 продаж.
Примечание.
Функция СЧЕТЕСЛИ работает только с отдельными ячейками либо столбцами. Иначе говоря, в качестве диапазона поиска нельзя использовать несколько различных ячеек либо несколько строк и столбцов.
Неправильно:
=СЧЕТЕСЛИ(D6, D8, D10, D12, D14,”Молочный шоколад”).
Правильно:
=СЧЕТЕСЛИ(C6:C25;”Молочный шоколад”) + СЧЕТЕСЛИ(D6:D25;”Молочный шоколад”)
=СЧЕТЕСЛИ(D6;”Молочный шоколад”) + СЧЕТЕСЛИ(D8;”Молочный шоколад”) + СЧЕТЕСЛИ(D10;”Молочный шоколад”) + СЧЕТЕСЛИ(D12;”Молочный шоколад”) + СЧЕТЕСЛИ(D14;”Молочный шоколад”)
Но указывать в самой формуле критерий поиска не очень удобно – каждый раз придётся её редактировать. Правильным решением будет записать критерий поиска в определённую ячейку таблицы Google таблицах и затем просто ссылаться на нее из функции.
Давайте вместе подсчитаем количество успешных продаж, использовав в формуле функции СЧЕТЕСЛИ ссылку на ячейку.
Наша формула в ячейке C3 теперь будет выглядеть следующим образом:
При работе функции ссылка на ячейку A3 будет заменена её значением “Да”. Как видите, условие поиска корректировать стало намного проще.
Проделаем то же самое для числовых значений. Как видите, количество значений, равное 144, можно успешно подсчитать либо прямо указывая нужное значение в условии функции
либо заменяя его ссылкой на ячейку
Подстановочные знаки в условии функции СЧЕТЕСЛИ
Мы можем производить поиск и подсчет значений не по всей ячейке, а только по её части. Здесь мы можем использовать шаблон поиска (подстановочные знаки).
Как правильно указать, что мы будем искать? Для этого используем знаки “?”, “*”, “
К примеру, для подсчета количества продаж в определённом регионе можно использовать только часть его названия: вводим в ячейку A3 значение “. город”. Символ “?” позволяет заменить собой один любой символ. Мы будем искать названия из 2 слов, первое из которых состоит из 7 букв, включая пробелы.
В ячейку B3 вводим функцию СЧЕТЕСЛИ:
Как мы уже говорили, это будет то же самое, что ввести в ячейку C3
В результате видим, что было получено 12 заказов из «Большой город» и «Средний город».
А теперь в ячейку B4 введём
И в ячейке A4 изменим условие поиска на “Б. город”. Теперь мы будем названия из 2 слов, первое из которых состоит из 7 букв, и начинается с «Б». Результатом выполнения функции будет 5 продаж, так как указанному условию теперь удовлетворяет только «Большой город».
Аналогично можно рассчитать количество продаж определенного товара, использовав символ ”*”, который позволяет заменить собой не один, а любое количество символов.
По условию “*шоколад” мы ищем все названия продуктов, заканчивающиеся на “шоколад”.
По условию “шоколад*” мы ищем все названия продуктов, начинающиеся с “шоколад”. И, как вы уже догадались, если в условии поиска мы напишем “*шоколад*”, то мы будем искать все названия продуктов, содержащие слово “шоколад”.
?*”) – ищем количество значений, в которых есть знак “?”.
Больше, меньше или равно в качестве условия
Функция СЧЕТЕСЛИ может не только определить количество чисел, равных заданному критерию, но и подсчитать, сколько чисел имеют значение больше или меньше заданного, либо просто не равны ему.
Для этого к критерию нужно добавить соответствующий математический знак “=”, “>”, “ =”, “ ”.
Посмотрите сначала, как эти условия можно использовать прямо в формуле функции.
