Подсчет ячеек, ЕСЛИ НЕ ПУСТО (непустые ячейки) в 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 Таблицах.
Как посчитать количество ячеек в гугл таблице?
Первый способ. Когда все ячейки заполнены данными.
Когда во всех ячейках есть данные, то посчитать их количество можно с помощью специальной функции «СЧЁТЗ(Х)», где Х- это диапазон ячеек, которые подсчитываем. Внизу представлен пример, как использовать данную функцию.
Второй способ. Когда в списке присутствуют ячейки как с данными, так и пустые.
В этом случае нужно помимо функции «СЧЁТЗ(Х)» использовать функцию «СЧЁТЕСЛИ(Х;У)», где Х- это диапазон ячеек, которые подсчитываем, У – условия. Данная функция позволяет досчитать пустые ячейки, для этого необходимо установить условия проверить пустая ячейка или нет, сделать это можно просто поставив две кавычки. В нашем примере нужно просто записать функцию следующим образом: =СЧЁТЕСЛИ(A1:A8;"")+СЧЁТЗ(A1:A8).
Как в Google Spreadsheets (Google Таблицы) подсчитать количество строк с одинаковым значением?
Добрый день.
Прошу помощи, не могу разобраться.
Как в сервисе Google Spreadsheets (Google Таблицы) подсчитать количество строк с одинаковым значением?
К примеру: в таблице есть столбец «Направление», и каждая ячейка в нем имеет одно из значений, к примеру «Москва», «Минск», «Киев».
Нужна либо функция которая сама будет именно в этом столбце искать одинаковые значения и выводить их количество. Или я укажу какие именно значения, а функция мне подсчитает количество.
Если есть возможность ввести функцию на одном листе, а результаты на другом — это даже лучше.
Как использовать функцию СЧËТЕСЛИ в Google таблицах
Функция СЧЕТËСЛИ в Google таблицах – это одна из самых простых функций, но при этом она очень полезна.
Здесь мы подробно рассмотрим, как применяется функция СЧËТЕСЛИ при работе в Google таблицах, а также какую пользу мы можем получить от ее использования. На примерах мы разберём, как можно указывать критерии подсчета, как можно использовать несколько критериев.
Также мы предложим нестандартный способ использования функции СЧЕТЕСЛИ – для условного форматирования ячеек таблицы.
Что такое функция СЧËТЕСЛИ в Google таблицах?
Она позволяет подсчитать количество определённых значений в указанном вами диапазоне данных. Эти значения должны удовлетворять определённому условию, которое вы сами задаёте.
Синтаксис функции СЧËТЕСЛИ в Google таблицах
Вот как выглядит синтаксис этой функции и её аргументы:
=СЧËТЕСЛИ(диапазон, критерий)
Диапазон – это диапазон данных, в котором мы будем производить поиск нужных нам значений
Критерий – это критерий поиска – значение, которое мы будем искать.
Примеры использования СЧËТЕСЛИ в Google таблицах.
Несмотря на то, что функция СЧËТЕСЛИ кажется очень простой, на самом деле её возможности достаточно велики. Это множество возможностей состоит в первую очередь в том, как мы будем описывать критерий поиска.
Ведь искать можно не только точно указанные значения, но и значения, которые соответствуют определённым условиям поиска.
Теперь давайте попробуем сделать это вместе и рассмотрим несколько примеров.
Полное совпадение числа или текста в функции СЧËТЕСЛИ.
Предположим, ваша компания занимается продажей различных сортов шоколада в нескольких регионах и работает с множеством покупателей.
Вот как выглядят данные о ваших продажах, хранящиеся в Google таблицах.
Начнём с самого простого.
Вставим перед таблицей 4 строки, чтобы лучше видеть результаты вычислений.
Подсчитаем количество заявок на продажу молочного шоколада. Устанавливаем курсор в ячейку C3 и вводим знак “=”. Для Google таблиц это означает, что сейчас будет введена формула. Поэтому как только далее будет введена буква “c”, он выведет нам предложение выбрать функцию, начинающуюся с буквы “c”. Выбираем “СЧËТЕСЛИ”.
В качестве первого аргумента функции СЧËТЕСЛИ вводим диапазон: D6:D25. Как и в других функциях Google таблиц, адрес диапазона можно не вводить вручную, а просто выделить мышкой. Затем вводим “,” и указываем второй аргумент – условие поиска.
Второй аргумент – это значение, которое мы будем искать в обозначенном диапазоне. Это будет текст “Молочный шоколад”. Не забываем закончить ввод функции, нажав “)” и затем “Enter”.
Также не забывайте, что при использовании в формуле текстовых значений нужно использовать кавычки <””>. Для числовых значений этого делать не нужно.
В результате наша функция выглядит следующим образом:
Получаем результат – 6 продаж.
Примечание.
Функция СЧËТЕСЛИ работает только с отдельными ячейками либо столбцами. Иначе говоря, в качестве диапазона поиска нельзя использовать несколько различных ячеек либо несколько строк и столбцов.
Неправильно:
Правильно:
=СЧËТЕСЛИ(D6;”Молочный шоколад”) + СЧËТЕСЛИ(D8;”Молочный шоколад”) + СЧËТЕСЛИ(D10;”Молочный шоколад”) + СЧËТЕСЛИ(D12;”Молочный шоколад”) + СЧËТЕСЛИ(D14;”Молочный шоколад”)
Но указывать в самой формуле критерий поиска не очень удобно – каждый раз придётся её редактировать. Правильным решением будет записать критерий поиска в определённую ячейку таблицы 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,”<>100″) | Подсчитывает число ячеек, значение которых не равно 100 |
| Число больше или равно | =СЧЕТЕСЛИ(E7:E15,”>=100″) | Подсчитывает число ячеек, значение которых больше или равно 100 |
| Число меньше или равно | =СЧЕТЕСЛИ(E7:E15,”<=100″) | Подсчитывает число ячеек, значение которых меньше или равно 100 |
Чтобы мы могли изменять критерий подсчета, не меняя формулу, мы здесь также можем использовать ссылки на ячейки.
В ячейке В3 запишем формулу, которая ссылается на ячейку A3, как мы уже делали ранее.
Для записи более сложных критериев используется оператор объединения (&).
К примеру, в ячейке B4 мы записали формулу, которая подсчитает число ячеек в диапазоне F9:F18, значение которых больше 150.
В ячейке В5 записано то же самое условие, но в ссылках на ячейки находится не только число, но и математический знак. Это ещё больше упрощает изменение функции СЧЕТЕСЛИ, если это будет необходимо.
Использование нескольких критериев в функции СЧЕТЕСЛИ
Бывает необходимо подсчитать количество значений, удовлетворяющих сразу двум либо нескольким критериям. Для этого в ячейке мы используем сразу несколько функций СЧЕТЕСЛИ.
Подсчитаем количество продаж шоколада с орехами и марки «супер». Для этого используем в ячейке В4 формулу
Обратите внимание на использование шаблона поиска (*), чтобы слова «орех» и «супер» были подсчитаны независимо от того, где они находятся в названии – в начале, в середине или в конце.
Можно записать эту же формулу с использованием ссылок на ячейки. Это сделано нами в ячейке В3.
Как видите, результат получился одинаковым.
Используя несколько критериев, можно подсчитать также количество чисел, находящихся в определённом диапазоне.
В нашем примере мы подсчитаем количество продаж, стоимость которых находится в диапазоне от 100 до 200.
Из количества продаж, величина которых меньше 200, вычитаем количество продаж, которые меньше 100. Используем формулу:
В результате получим количество продаж, которые больше 100, но меньше 200.
Если использовать ссылки на ячейки А3 и А4, в которых записаны эти критерии выбора, то тогда формула будет такая:
В ячейке А3 укажем критерий “<=200”, в ячейке А4 – критерий “<=100”.
Записываем формулы двумя способами в ячейки В3 и В4, получаем результат – 5 продаж находятся в нужном нам интервале.
Подсчитываем количество пустых и непустых ячеек
При помощи функции СЧËТЕСЛИ мы можем также подсчитать количество пустых либо количество непустых ячеек в заданном диапазоне.
Предположим, после удачно завершенной продажи в колонке «Выполнен» мы делаем отметку “Да”, если покупатель отказался от товара – пишем в ячейке “Нет” или ставим 0 (ноль), а если сделка не завершена, то ячейка пока остаётся пустой.
Для подсчета пустых и непустых ячеек используем шаблон поиска.
Если необходимо подсчитать количество непустых текстовых значений ячеек, то используем формулу:
Чтобы подсчитать количество пустых ячеек, функцию СЧЕТЕСЛИ запишем следующим образом:
Количество ячеек с текстом подсчитываем так:
В ячейках A3, A4, A5 мы записали критерии отбора.
Как видим, 3 заказа пока не имеют никаких отметок и, следовательно, не завершены.
Использование СЧËТЕСЛИ для условного форматирования ячеек
Google таблицы дают пользователям интересную возможность – изменять формат ячейки (например цвет) в зависимости от выполнения определённых условий.
К примеру, значения, которые встречаются чаще, могут быть отмечены определённым цветом.
Для этого также можно использовать функцию СЧЕТЕСЛИ.
Выделите при помощи мышки диапазон ячеек, которые вы хотите форматировать особым образом в зависимости от условия. Нажмите меню Формат – Условное форматирование. Введите условия форматирования, выбрав пункт “Ваша формула”
Введите туда выражение
Это означает, что условие будет выполняться, если значение ячейки В10 встречается в нашем диапазоне данных B10:B39 более чем в 40% случаев.
Аналогичным образом в правила условного форматирования добавляем ещё 2 условия – значение ячейки встречается чаще чем в 25% случаев и чаще чем 15%.
Обратите внимание, что сначала проверяется первое условие, и если оно выполнено, то остальные условия в ячейке не проверяются. Поэтому начинать нужно с самых редких значений, переходя на более часто встречающиеся. Если же значение ячейки не будет удовлетворять ни одному из условий, то её формат не изменится.
Вы видите, что цвет ячеек B10:B39 изменился в соответствии с установленными нами условиями.
Для проверки мы также рассчитали в ячейках A1:C7, как часто встречаются определённые значения, используя уже описанные ранее способы использования функции СЧËТЕСЛИ.
Эти расчёты подтверждают, что условное форматирование при помощи функции СЧËТЕСЛИ мы выполнили правильно.
Итак, мы с вами смогли убедиться, что функция Google таблиц СЧËТЕСЛИ действительно дает нам множество возможностей для работы с данными.
Другие примеры использования функций Google таблиц:
Функция ЕСЛИ в Google таблице – примеры использования — Функция ЕСЛИ в Google таблицах – это одна из самых простых функций, но при этом она очень полезна. Она относится к десятку функций, которые нужно обязательно знать и уметь применять.…
Функция ВПР (VLOOKUP) в Google таблицах – синтаксис и примеры — Рассмотрим, как правильно использовать поиск в Google таблицах при помощи формулы ВПР. Это одна из самых часто используемых функций Google таблиц. В этом материале мы расскажем, что такое функция поиска…
Почему функция ВПР (VLOOKUP) не работает в Google таблицах? — Рассматривая синтаксис функции ВПР (VLOOKUP), мы уже отмечали, что в случае, если поиск завершится неудачей, функция возвратит ошибку «#Н/Д» (#N/A). Давайте постараемся вместе попробовать ответить на вопрос: «Почему функция ВПР не…
Полезные примеры использования функции ВПР в Google таблицах — Ранее мы уже рассмотрели, что такое функция поиска ВПР в таблицах Google, а теперь изучим приёмы, которые помогут вам использовать её максимально эффективно. Каждый пример использования функции ВПР в ваших…