Считать, если Excel не пустой

Обновлено: 21.11.2024

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

Ячейка Excel СЧЁТЕСЛИ не имеет пустого синтаксиса

Вот синтаксис COUNTIF, который следует использовать для подсчета непустых ячеек в Excel

"<>" – логический оператор, соответствующий условию "не равно"

Пример формулы Excel СЧЁТЕСЛИ не пустой

Для следующего примера СЧЁТЕСЛИ не пустой Excel мы импортировали данные из BigQuery. Если вам также необходимо автоматизировать экспорт запросов, используйте Coupler.io для подключения BigQuery к Excel. Это инструмент для интеграции различных источников, таких как Google Sheets или Harvest to Excel.

Узнайте больше о Coupler.io и ознакомьтесь с доступными интеграциями с Excel, которые будут полезны для вашего проекта.

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

Вы можете легко посчитать пустые ячейки самостоятельно, однако количество непустых ячеек подсчитать сложнее. Вот почему вы должны использовать следующую формулу СЧЁТЕСЛИ, а не пустую формулу Excel:

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

Напротив СЧЁТЕСЛИ Excel не пустой — формула для вычисления пустых ячеек

Если вы замените логический оператор формулы Excel СЧЁТЕСЛИ непустой на "" , вы сможете подсчитать количество пустых ячеек в диапазоне:

Давайте посмотрим, как это выглядит в нашем примере набора данных:

Несмотря на то, что эта формула работает очень хорошо, создатели Excel решили предоставить специальную функцию для подсчета пустых ячеек — СЧИТАТЬПУСТОТЫ.

Excel СЧИТАТЬПУСТОТЫ для подсчета количества пустых ячеек

СЧИТАТЬПУСТОТЫ подсчитывает количество пустых ячеек в заданном диапазоне. Вот синтаксис:

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

Зачем считать пустые или непустые ячейки?

Упомянутые выше функции являются статистическими функциями в Excel. Они широко используются в статистическом анализе и других операциях с данными для повышения точности результатов. Удачи с вашими данными!

Это будет очень простой пост, здесь наша цель — найти количество ячеек, содержащих данные, ячейки, которые пусты или пусты, должны быть исключены. Задача довольно проста, но возникает вопрос «как это сделать?». Вы знаете, что хотите, чтобы Excel подсчитывал для вас определенный тип ячеек, но если вы задаетесь вопросом, что поместить в свои листы, чтобы получить этот подсчет, вы скоро это узнаете.

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

Оглавление

Подсчет непустых ячеек в Excel

Здесь мы поговорим о трех функциях Excel, которые могут помочь нам подсчитать непустые ячейки в диапазоне.

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

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

Попробуем понять это на примере. Итак, у нас есть набор данных, как показано ниже:

Используя этот список скидок на товары, мы попытаемся определить, на сколько товаров действует скидка.В терминологии Excel это означает — мы выясняем, сколько непустых ячеек в столбце D (непустые ячейки представляют собой скидки на товары, а пустые ячейки — товары без скидки).

Этого легко добиться с помощью следующей формулы СЧЁТЕСЛИ:

И наш окончательный результат выглядит примерно так.

Здесь в формулу введен диапазон столбца D (D3:D14). Критерием поиска в столбце D является "<>", который является индикатором непустых ячеек ("" для пустых ячеек).

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

Функция СЧЁТЕСЛИ может использоваться только для одного условия. Для нескольких условий (например, непустые ячейки и скидка более 20%) можно использовать функцию СЧЁТЕСЛИМН.

Следующая функция, которую мы будем использовать для подсчета непустых ячеек, — это функция COUNTA.

Использование функции COUNTA

По своей природе функция COUNTA подсчитывает непустые ячейки диапазона. Это функция с одним аргументом (в простейшей форме), требующая только диапазона, из которого нужно подсчитать непустые ячейки.

Мы будем использовать его в нашем последнем примере и просто добавим наш диапазон D3:D14 в функцию COUNTA следующим образом:

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

Для единственной цели подсчета непустых ячеек функция COUNTA предлагает простое и простое решение.

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

Почему это проблема?

Технически символ пробела или формула, возвращающая пустую строку, сильно отличается от пустой ячейки. Хотя они выглядят одинаково, технически это две разные вещи.

Но поскольку нашей целью здесь является подсчет непустых ячеек, мы можем захотеть, чтобы ячейки, которые кажутся пустыми, считались пустыми ячейками (технически, даже если они не пустые). Для этого вместо использования функций СЧЁТЕСЛИ или СЧЁТЗ лучше использовать функцию СУММПРОИЗВ. И это то, что мы увидим в следующем разделе.

Совет. Если вы не хотите использовать функцию СЧЁТЕСЛИ или СЧЁТЕСЛИ для подсчёта непустых ячеек, можно также отфильтровать данные и избавиться от ячеек со скрытыми значениями. Это улучшит результаты без включения ложных пустых ячеек. Сейчас мы перейдем к функции СУММПРОИЗВ.

Использование функции СУММПРОИЗВ

Функция СУММПРОИЗВ возвращает сумму произведений предоставленных диапазонов или массивов. Чтобы подсчитать непустые ячейки с помощью функции СУММПРОИЗВ, мы можем использовать следующую формулу:

Давайте сначала попробуем разобраться в формуле, а затем сравним ее с функциями СЧЁТЕСЛИ и СЧЁТЕСЛИ.

В приведенной выше формуле мы прежде всего проверяем, равны ли значения в диапазоне C2:C13 пустой строке (ничего). Это возвращает массив логических значений, подобных этому –

Теперь, поскольку функция СУММПРОИЗВ не может суммировать логические значения, мы преобразуем их в 0 и 1, используя двойное отрицание. Таким образом, формула еще более упрощается до чего-то вроде этого —

Функция СУММПРОИЗВ складывает все эти 1 и 0 и возвращает окончательный результат.

Важно отметить, что приведенная выше формула не рассматривает символ пробела как пустую строку (ничего), поэтому он все равно будет считаться непустой ячейкой. Чтобы посчитать их как пустые, мы можем использовать функцию TRIM, и окончательная формула будет выглядеть так –

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

Теперь давайте возьмем функции СЧЁТЕСЛИ, СЧЁТЕСЛИ и СУММПРОИЗВ рядом и проанализируем их:

Из серийной нумерации видно, что в нашем наборе данных 12 строк. C5 — это пустая ячейка, которая правильно интерпретируется как пустая ячейка COUNTIF и COUNTA.Похоже, что есть еще 2 пустые ячейки (C8, содержащая символ пробела, и C10, содержащая формулу, которая возвращает пустую строку, т.е. ="") в диапазоне, что означает, что есть 9 непустых ячейки.

В то время как функции COUNTIF и COUNTA обрабатывают ячейку C5 как пустую ячейку, но они обрабатывают две другие ячейки C8 и C10 как непустые ячейки и, таким образом, возвращают 11 в качестве счетчика.

С другой стороны, формула на основе СУММПРОИЗВ правильно определяет все такие ячейки и, следовательно, возвращает 9 в качестве количества непустых ячеек.

Подсчет пустых ячеек

Настала очередь подсчета пустых ячеек. У нас впереди 2 очень простых способа подсчета пустых ячеек с помощью функции СЧИТАТЬПУСТОТЫ и СЧЁТЕСЛИ.

Использование функции СЧИТАТЬПУСТОТЫ

Функция СЧИТАТЬПУСТОТЫ, говорящая сама за себя, подсчитывает количество пустых/пустых ячеек в указанном диапазоне. Мы будем использовать функцию напрямую, чтобы получить наши результаты:

Мы вставили диапазон D3:D14 в формулу, чтобы увидеть, сколько пустых ячеек есть в столбце D, который покажет нам количество товаров без скидки. Результатом функции СЧИТАТЬПУСТОТЫ являются "4" пустых ячейки.

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

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

Для подсчета пустых ячеек можно использовать функцию СЧЁТЕСЛИ:

В формуле, состоящей из диапазона и критериев, мы поменяли местами критерии подсчета непустых ячеек (т. е. "<>") на критерии подсчета пустых ячеек (т. е. ""). С диапазоном, заданным как D3:D14, функция СЧЁТЕСЛИ возвращает в качестве результата "4" пустых ячейки, указывая на 4 товара без скидки.

Мы надеемся, что в конце этого руководства вы не почувствуете себя опустошенными, пытаясь заставить Excel подсчитывать пустые и непустые ячейки. Пока вы читаете, мы даем дополнительные инструкции!

Подпишитесь и станьте частью нашей семьи, насчитывающей более 15 000 человек!

Теперь подпишитесь на Excel Trick и получите бесплатную копию нашей электронной книги "200+ ярлыков Excel" (формат для печати), которая резко повысит вашу продуктивность.

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

Функции Excel, формулы, диаграммы, форматирование, создание информационной панели Excel и др.

Синтаксис для СЧЁТЕСЛИ не пустой в Excel

СЧЁТЕСЛИ(Диапазон, Критерий)

Синтаксис для функции COUNTIF включает 2 следующих параметра:

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

Критерии = Критерии должны быть любым точным словом или числом, которое нам нужно посчитать.

СЧЁТЕСЛИ в Excel возвращает положительное число. Значение может быть нулевым или отличным от нуля.

Как использовать?

Использовать Excel Countif Not Blank очень просто. Здесь мы увидим, как использовать функцию СЧЁТЕСЛИ, чтобы узнать, сколько ячеек не пусто на листе Excel. Давайте разберемся с работой функции СЧЁТЕСЛИ в Excel на некоторых примерах, приведенных ниже.

Excel Advanced Training (16 курсов, более 23 проектов) 16 онлайн-курсов | 23 практических проекта | 140+ часов | Поддающийся проверке сертификат об окончании | Пожизненный доступ
4,8 (10 130 оценок)

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

Как видно на снимке экрана выше, в столбце A у нас есть данные, начиная с A2 до A7, и между ними есть пустая ячейка в A6. Теперь, чтобы подсчитать общее количество ячеек, но не пустых, используйте СЧЁТЕСЛИ. Для этого перейдите в любую ячейку, в которой вы хотите увидеть вывод, и нажмите fx (вкладка для вставки функций в Excel), как показано ниже.

Эта вкладка эффектов находится под строкой меню. Как только мы нажмем на нее, мы получим окно «Вставить функцию», в котором есть все встроенные функции, предоставленные Microsoft в ячейке. Найдите функцию СЧЁТЕСЛИ, прокручивая её вверх и вниз, а затем нажмите «ОК», как показано на снимке экрана ниже.

Как видно из приведенного выше окна «Вставить функцию», есть вкладка «Или выберите категорию», в которой есть все категории для определенных функций. Отсюда мы можем перейти к выбору всех параметров, как показано на снимке экрана выше, или мы можем выбрать статистическую категорию, где мы найдем функцию СЧЁТЕСЛИ, как показано на снимке экрана ниже. Затем нажмите "ОК".

После того, как мы нажмем «ОК», появится другое окно «Аргументы функции», где нам нужно будет определить диапазон и критерии. Здесь мы выбрали диапазон от A2 до A7 и критерии как «<>» &, что означает, что ячейка, содержащая значение больше и меньше любого пробела, должна быть подсчитана. И нажмите "ОК".

Критерии могут быть любыми, но для непустой ячейки нам нужно выбрать ячейку с любым значением, большим или меньшим, чем пустое. Для этого мы использовали «<>»&»”

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

Кроме того, на приведенном ниже снимке экрана мы получили число непустых ячеек, равное 6. Поскольку ячейка A6 пуста, СЧЁТЕСЛИ проигнорировал эту пустую ячейку и выдал количество оставшихся ячеек, которое имеет некоторое значение. значение (число или текст).

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

Нажатие «=» (знак равенства) в любой ячейке включает все функции, доступные в Excel. И даже если мы введем выборочные слова (скажем, «Счет»), как показано на скриншоте ниже, это даст все возможные функции, доступные для использования. Оттуда мы также можем выбрать функцию в соответствии с нашим требованием.

Как вы можете видеть на снимке экрана выше, функция СЧЁТЕСЛИ имеет диапазон и критерии для назначения, которые также были в предыдущем примере. Таким образом, мы назначим тот же диапазон, что и от A2 до A7, и критерии как «<>»&»», как показано ниже.

И нажмите клавишу Enter. Мы получим количество ячеек со значением «6», но мы выбрали 7 ячеек, включая пустую ячейку A6. Здесь также функции COUNTIF подсчитывают общее количество непустых ячеек.

Но если мы поставим неправильные критерии, то можем получить сообщение об ошибке, объясняющее возникшую проблему, как показано на скриншоте ниже. Здесь мы удалили « » (кавычки) из критериев и получили ошибку для тестирования.

Если мы не знаем, как устранить ошибку, мы можем нажать кнопку «Справка», показанную на снимке экрана ниже, что приведет нас непосредственно к окну справки Microsoft Excel, где мы сможем правильно указать аргументы функции.

Примечание:

" " (Перевернутая запятая) в Excel используется, когда есть текст или пустая ячейка, которую необходимо захватить. Здесь <> — это текст, а « » вместе используются для захвата ячейки с любым значением, но не пустым.
Используя «&» в формуле, мы можем добавить дополнительные критерии в соответствии с нашими требованиями.

Плюсы Excel СЧЁТЕСЛИ НЕ ПРОПУСК в Excel

Для больших наборов данных, где применение фильтра занимает так много времени, они используют функцию Excel Countif Not Blank, которая полезна и экономит время для подсчета ячеек, которые не являются пустыми.
Это дает мгновенный и точный результат.
Формула СЧЁТЕСЛИ полностью автоматическая; его легко и быстро использовать.
Это очень полезно в бухгалтерской работе.

Что нужно помнить

Всегда проверяйте данные, если они перенесены из другого источника. Есть вероятность, что данные могут содержать пустые ячейки со скрытыми значениями. В этом случае отфильтруйте пустую ячейку и удалите значения ячейки, чтобы избежать неправильного вывода.
Всегда отображайте лист или столбец, чтобы получить точный результат.

Метод COUNTA

В Google Sheets функция COUNTA подсчитывает количество всех ячеек в диапазоне, содержащих значения или текстовую строку. Он игнорирует пустые ячейки при завершении подсчета.

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

Чтобы подсчитать количество ячеек, которые не пусты, выполните следующие действия.

Выберите пустую ячейку и введите функцию =COUNTA, включая диапазон ячеек, который вы хотите подсчитать. Например, мы использовали =СЧЁТЗ(A2:A11).
Просто нажмите клавишу ВВОД, и функция СЧЕТЧИК автоматически подсчитает непустые ячейки.
Теперь у вас есть общее количество ячеек со значениями!

<Р>

Метод СУММПРОИЗВ

Функция COUNTA очень полезна для подсчета ячеек, которые пусты или не содержат данных. Однако могут быть случаи, когда COUNTA подсчитывает ячейки, которые кажутся пустыми. Это связано с тем, что технически эти ячейки не пусты, а просто имеют пустое пространство.Это происходит, например, когда вы используете условное форматирование, из-за которого оно выглядит как пустое, или прячете в нем функцию =””!

<Р>

Чтобы этого не произошло, используйте вместо этого СУММПРОИЗВ, выполнив следующие действия:

Нажмите на пустую ячейку и введите функцию =СУММПРОИЗВ(--(ДЛСТР(диапазон)>0)) для подсчета ячеек, которые не кажутся пустыми. При этом функция ДЛСТР возвращает значение больше нуля при подсчете количества символов, которые появляются на листе.
Просто нажмите Enter, и количество ячеек со значениями появится автоматически.

<Р>

Метод СЧЁТЕСЛИ

Если вы читали предыдущие руководства, Google Таблицы скрывают некоторые специальные символы из-за их специальных функций: это включает апостроф, который используется для указания того, что числовые символы должны интерпретироваться как строка (мы использовали это, чтобы ввод телефонных номеров в Google Sheets). Чтобы решить эту проблему, мы узнаем, как использовать СЧЕТЕСЛИ для подсчета ячеек, которые не являются пустыми и не имеют скрытых специальных символов.

В качестве примера рассмотрим эту таблицу:

Сколько ячеек имеют записи в таблице? В этой таблице девять (9) записей. Однако, если вы примените функцию COUNTA для подсчета записей, результатом будет тринадцать (13):

Информация о драйвере, что это такое