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

Подсчет заполненных ячеек в Microsoft Excel

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

Подсчет ячеек

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

Способ 1: счетчик на строке состояния

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

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

Способ 2: функция СЧЁТЗ

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

Выделяем область, в которую будет выводиться результат подсчета. Кликаем по значку «Вставить функцию».

Открывается окно Мастера функций. Ищем в представленном списке элемент «СЧЁТЗ». После того, как это наименование выделено, жмем на кнопку «OK».

Запускается окно аргументов. Аргументами этой функции являются ссылки на ячейки. Ссылку на диапазон можно прописать вручную, но лучше установить курсор в поле «Значение1», куда нужно ввести данные, и выделить соответствующую область на листе. Если нужно произвести подсчет заполненных ячеек в нескольких удаленных друг от друга диапазонах, то координаты второго, третьего и последующего диапазона нужно вводить в поля под названием «Значение2», «Значение3» и т.д. Когда все данные введены. Жмем на кнопку «OK».

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

Способ 3: функция СЧЁТ

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

Как и в предыдущем случае, выделяем ячейку, куда будут выводиться данные и таким же способом запускаем Мастер функций. В нём выбираем оператора с наименованием «СЧЁТ». Жмем на кнопку «OK».

Запускается окно аргументов. Аргументы те же самые, что и при использовании предыдущего способа. В их роли выступают ссылки на ячейки. Вставляем координаты диапазонов на листе, в которых нужно подсчитать количество заполненных ячеек числовыми данными. Жмем кнопку «OK».

Для ручного введения формулы придерживаемся следующего синтаксиса:

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

Способ 4: функция СЧЁТЕСЛИ

Данная функция позволяет подсчитать не просто количество ячеек, заполненных числовыми выражениями, но только такие из них, которые соответствуют определенному условию. Например, если задать условие «>50», то будут учитываться только такие ячейки, в которых содержится значение больше числа 50. Также можно задавать значения « » (не равно) и т.д.

После того, как выделили ячейку для вывода результата и запустили Мастер функций, выбираем запись «СЧЁТЕСЛИ». Кликаем по кнопке «OK».

Открывается окно аргументов. У данной функции два аргумента: диапазон, где происходит подсчет ячеек, и критерий, то есть, условие, о котором мы говорили выше. В поле «Диапазон» вводим координаты обрабатываемой области, а в поле «Критерий» вписываем условия. После этого жмем на кнопку «OK».

Для ручного ввода шаблон выглядит следующим образом:

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

Способ 5: функция СЧЁТЕСЛИМН

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

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

Происходит открытие окна аргументов. Собственно, аргументы функции те же самые, что и у предыдущей – «Диапазон» и «Условие». Разница только в том, что диапазонов и соответствующих им условий может быть много. Вводим адреса диапазонов и соответствующие им условия, а потом жмем на кнопку «OK».

Синтаксис у этой функции следующий:

После этого приложение производит подсчет заполненных ячеек указанных диапазонов, которые соответствуют установленным условиям. Результат выводится в заранее отмеченную область.

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

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

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

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

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

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

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

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

Эксперимент

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Excel works!

Excel работает за вас

Excel works!

Thanks for Visiting

Как посчитать количество пустых ячеек Excel? Функция СЧИТАТЬПУСТОТЫ

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

Пустые ячейки не считаются многими формулами, например при расчете среднего значения, функцией СРЗНАЧ пустая ячейка не учитывается (необходимо заменить на 0). Соответственно существуют специальные формулы для подсчета именно незаполненных ячеек

Считаем количество пустых ячеек функцией СЧИТАТЬПУСТОТЫ

В формуле необходимо задать диапазон, как показано на картинке

Такая формула выдаст значение = 3.

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

В версиях Excel ниже 2007 не было функции СЧИТАТЬПУСТОТЫ, поэтому мы часто пользовались СЧЁТЕСЛИ.

Запишем формулу для того же диапазона

Как определить пустая ли конкретная ячейка? ЕПУСТО

Важное замечание по пустым ячейкам

Если вы использовали в формулах замену на пустое значение обозначенное как «» (например, =ЕСЛИОШИБКА(A:A;»»)), то после копирования значения такой ячейки она не будет считаться пустой. Хотя на вид она выглядит как незаполненная.

Для определения только пустых ячеек применяется функция

Как выделить цветом пустые ячейки в Excel?

Конечно же условным форматированием . Выделяем необходимый диапазон, в верхней панели во вкладке Главная находим Условное форматирование — Создать правило…

В открывшемся окне выбираем Форматировать только ячейки, которые содержат и ниже Пустые. Выбираем как нужно форматировать, по кнопке Формат… Я выбрал заливку розовым цветом, что показано в окне Образец:

Ячейки выбранного диапазона окрасились цветом

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