Счет непустых ячеек в excel

Как посчитать количество заполненных и пустых ячеек в Excel

Excel предлагает несколько функций для считывания и подсчета значений в диапазоне ячеек: СЧЁТ(), СЧЁТЗ и СЧИТАТЬПУСТОТЫ. Каждая из этих функций по-своему считывает и считает значения, в зависимости о т того, является ли значение числом, текстом или просто пустой ячейкой. Рассмотрим все эти функции в действии на практическом примере.

Функция СЧЁТ, СЧЁТЗ и СЧИТАТЬПУСТОТЫ для подсчета ячеек в Excel

Ниже на рисунке представлены разные методы подсчета значений из определенного диапазона данных таблицы:

В строке 9 (диапазон B9:E9) функция СЧЁТ подсчитывает числовые значения только тех учеников, которые сдали экзамен. СЧЁТЗ в столбце G (диапазон G2:G6) считает числа всех экзаменов, к которым приступили ученики. В столбце H (диапазон H2:H6) функция СЧИТАТЬПУСТОТЫ ведет счет только для экзаменов, к которым ученики еще не подошли.

Принцип счета ячеек функциями СЧЁТ, СЧЁТЗ и СЧИТАТЬПУСТОТЫ

Функция СЧЁТ подсчитывает количество только для числовых значений в заданном диапазоне. Данная формула для совей работы требует указать только лишь один аргумент – диапазон ячеек. Например, ниже приведенная формула подсчитывает количество только тех ячеек (в диапазоне B2:B6), которые содержат числовые значения:

СЧЁТЗ подсчитывает все ячейки, которые не пустые. Данную функцию удобно использовать в том случаи, когда необходимо подсчитать количество ячеек с любым типом данных: текст или число. Синтаксис формулы требует указать только лишь один аргумент – диапазон данных. Например, ниже приведенная формула подсчитывает все непустые ячейки, которые находиться в диапазоне B5:E5.

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

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

Excel — как подсчитать количество непустых строк

March 14, 2013

Стоит задача — подсчитать количество непустых строк в таблице Excel.

Собственно, таблица представляет из себя полуавтоматическую программу по составлению раскроя металлопрофиля. На “плечи” таблицы возложено вычисление остатков (отходов) при раскрое с учетом допусков-припусков, углов пила и ширины пила.

В таблице каждая строка символически представляет собой одну заготовку исходной длины. Ячейки, входящие в состав одной строки представляют собой детали требуемой длины. Так вот, при составлении раскроя половина процесса автоматизирована. Но потребовалось еще улучшить работу таблицы, а именно — автоматически подсчитывать количество непустых строк. Если в строке хотя бы одна ячейка непустая, то есть в ней присутствует значение, то такая строка считается непустой. Если же в строке все строки пустые (не имеют значения), то и строка считается пустой.

Первоначально пробовал решить задачу с помощью стандартной функции Excel —

Первое решение

В этом способе применяется функция

Результат работы этой формулы таков. Если хотя бы в одной ячейке строки имеется значение, то строка считается непустой и в соответствующей ячейке дополнительного столбца помещается единица (1). Если же ни в одной ячейке строки нет значения, то строка считается пустой и в ячейке дополнительного столбца помещается значение нуль (0).

Читать еще:  Макрос из excel в excel

Осталось самое простое — подсчитать значения дополнительного столбца, сумма которого и будет числом непустых строк в таблице.

Результат работы представлен ниже:

Вроде бы и ничего результат. Все работает. Но выглядит как-то криво. Дополнительный столбец выполняет только одну единственную задачу — определение строки и мешается, занимая место. Конечно, можно скрыть его. Для этого нажимаем правой кнопкой мыши на заголовке дополнительного столбца (О) и в контекстном меню выбираем “Скрыть”. Но конечный результат меня не устраивал. Поэтому было найдено второе решение.

Второе решение

В этом способе была использована пользовательская функция, написанная на VBA. Решение получилось очень компактным и красивым. Код макроса представлен ниже:

Этот код нужно вставить в Excel. Для этого открываем редактор макросов, нажав комбинацию клавиш Alt+F11 . Откроется окно, в котором в меню выбираем команды “Insert — Module”. Сохраняем макрос под именем

Теперь достаточно вставить в нужную ячейку таблицы формулу:

Подсчет ячеек в Excel, используя функции СЧЕТ и СЧЕТЕСЛИ

Очень часто при работе в Excel требуется подсчитать количество ячеек на рабочем листе. Это могут быть пустые или заполненные ячейки, содержащие только числовые значения, а в некоторых случаях, их содержимое должно отвечать определенным критериям. В этом уроке мы подробно разберем две основные функции Excel для подсчета данных – СЧЕТ и СЧЕТЕСЛИ, а также познакомимся с менее популярными – СЧЕТЗ, СЧИТАТЬПУСТОТЫ и СЧЕТЕСЛИМН.

Статистическая функция СЧЕТ подсчитывает количество ячеек в списке аргументов, которые содержат только числовые значения. Например, на рисунке ниже мы подсчитали количество ячеек в диапазоне, который полностью состоит из чисел:

В следующем примере в двух ячейках диапазона содержится текст. Как видите, функция СЧЕТ их игнорирует.

А вот ячейки, содержащие значения даты и времени, учитываются:

Функция СЧЕТ может подсчитывать количество ячеек сразу в нескольких несмежных диапазонах:

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

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

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

Следующая формула возвращает количество ячеек, значение которых больше содержимого ячейки А4.

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

Логическое условие функции СЧЕТЕСЛИ может содержать групповые символы: * (звездочку) и ? (вопросительный знак). Звездочка обозначает любое количество произвольных символов, а вопросительный знак – один произвольный символ.

Читать еще:  Написать макрос в excel для новичков чайников

Например, чтобы подсчитать количество ячеек, содержащих текст, который начинается с буквы Н (без учета регистра), можно воспользоваться следующей формулой:

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

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

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

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

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

Функции Excel для подсчета данных очень полезны и могут пригодиться практически в любой ситуации. Надеюсь, что данный урок открыл для Вас все тайны функций СЧЕТ и СЧЕТЕСЛИ, а также их ближайших соратников – СЧЕТЗ, СЧИТАТЬПУСТОТЫ и СЧЕТЕСЛИМН. Возвращайтесь к нам почаще. Всего Вам доброго и успехов в изучении Excel.

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

Подсчет пустых ячеек? Имеется в виду ничего не содержащих ячеек? А есть разница? Разбираемся подробнее.

Под пустой ячейкой понимается ячейка, которая не содержит значения или формулы. Определить пустую ячейку можно с помощью функции ЕПУСТО() .

Если необходимо подсчитать пустые ячейки в диапазоне A1:D4, то можно использовать формулу =СЧИТАТЬПУСТОТЫ(A1:D4) или =СУММПРОИЗВ(—ЕПУСТО(A1:D4)) .

Но не все так просто.

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

Что это еще за Пустой текст и откуда он берется? Значение Пустой текст (две кавычки («»), между которыми ничего нет) может быть результатом, например, вычисления формулы с условием: =ЕСЛИ(F1>0;»больше 0″;»») .
Т.е. разработчик намеренно использует значение Пустой текст. Это удобно, т.к. результат Пустой текст обладает замечательным свойством: ячейка выглядит пустой. Этого результата можно, конечно, добиться с помощью Условного форматирования или Пользовательского формата, но гораздо быстрее просто ввести «». Но, этот подход имеет и свою цену: некоторые функции и средства EXCEL интерпретирует ячейку, содержащую Пустой текст, как пустую ячейку, а другие, как содержащую текстовое значение.

Эксперимент

Для иллюстрации приведем пример того как рассматривают ячейку с Пустым текстом Условное форматирование и функция ЕПУСТО() (см. Файл примера ).

Рассмотрим диапазон A1:D4, содержащий числа, текст, пустые ячейки и Пустой текст «» (наиболее общий случай).

Ячейка С4 содержит значение Пустой текст (введено как результат вычисления формулы =ЕСЛИ(1>2;1;»») ) и выделена жирной границей. Условное форматирование с правилом «Форматировать только те ячейки, которые пустые» выделит действительно пустые ячейки и ячейку со значением Пустой текст!

Функция ЕПУСТО() не разделяет такого подхода и говорит, что в С4, что-то есть (формула =ЕПУСТО(C4) введенная в ячейку С5 возвращает ЛОЖЬ).

Читать еще:  Транспонирование матрицы в excel

Функция СЧИТАТЬПУСТОТЫ() также как и Условное форматирование, учитывает при подсчете ячейки со значением Пустой текст вместе с пустыми ячейками.

Чтобы не запутаться с подсчетом пустых ячеек в диапазоне С2:С11 при наличии значений Пустой текст, приведем обобщающую таблицу:

Формула или средство EXCEL

Различает ли пустую ячейку и ячейку со значением Пустой текст?

Excel: Функция СЧЕТ

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

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

Значение1 – обязательный аргумент, все остальные аргументы являются необязательными и могут быть опущены.

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

В более ранних версиях Excel (например, Excel 2003 года), функция Счет обрабатывала 30 аргументов. Начиная с версии Excel 2007, можно использовать до 255 аргументов.

Числа, даты и время учитываются функцией Счет как числовые.

Ссылки на пустые ячейки, ошибки, текстовые или логические значения игнорируются.

Если число сохранено как текст, оно не будет посчитано!

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

  1. Подсчет количества ячеек в диапазоне, который полностью состоит из чисел:

  1. В диапазоне есть пустые ячейки, ячейки содержащие числа и текст.

Функция Счет игнорирует пустые ячейки, и ячейки содержащие текст.

  1. Ячейки, содержащие значения даты и времени функция Счет учитывает:

  1. Функция Счет может подсчитывать количество ячеек сразу в нескольких несмежных диапазонах:

II .Чтобы посчитать количество непустых ячеек в диапазоне, воспользуйтесь функцией Счётз.

Функция Счётз

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

Функция Счётз учитывает данные любого типа, включая ошибки.

Ячейки, содержащие пустые строки =«« (две кавычки, между которыми ничего нет), засчитываются как Непустые.

Например, в ячейке С2 — пустая строка, т.е. С2=««. Функция Счётз учтет эту ячейку при подсчете.

III . Если требуется посчитать количество пустых ячеек в Excel, используйте функцию Считатьпустоты .

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

Ячейки, содержащие пустые строки (=««), засчитываются как ПУСТЫЕ.

Например, в ячейке С2 — пустая строка, т.е. С2=««. Функция Считатьпустоты учтет эту ячейку при подсчете.

Понравилось? Поделись с друзьями

Семинары. Вебинары. Конференции

Актуальные темы. Лучшие лекторы Москвы и РФ. Сертификаты ИПБР. Более 30 тематик в месяц.

Похожие статьи

Ссылка на основную публикацию
Похожие публикации
Adblock
detector