Как посчитать непустые ячейки в excel
Перейти к содержимому

Как посчитать непустые ячейки в excel

  • автор:

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

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

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

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

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

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

Эксперимент

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

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

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

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

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

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

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

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

Комментарий

пустая ячейка и ячейка со значением Пустой текст считаются тождественными

подсчитает все пустые ячейки и ячейки, содержащие Пустой текст

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

Функция СЧЕТЗ подсчитывает количество непустых ячеек с любыми данными в любых форматах, в том числе ячейки содержащие формулу с пустой строкой (=””), формулы результаты которых отображают ноль, и формулы содержащие ошибку.

Синтаксис
СЧЕТЗ(значение1;значение2 и т.д.)

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

Какие данные считает СЧЕТЗ

Для того, чтобы вставить функцию, нажмите на кнопочку Вставить функцию (слева от строки формул). В появившемся окне выберете категорию Статистические. В поле Выберете функцию, выбрать СЧЕТЗ. Нажать ОК.

Как вставить функцию СЧЕТЗ

В появившемся окне встаньте мышкой в поле Значение1 и выделите нужный диапазон (можно ввести вручную). Нажмите ОК.

Как ввести аргументы функции СЧЕТЗ

В нашем случае Excel не возьмет только 1 пустую ячейку.

Результат функции СЧЕТЗ

Автор Alfi Опубликовано 20.02.2019 20.02.2019 Рубрики Функции

Добавить комментарий Отменить ответ

Для отправки комментария вам необходимо авторизоваться.

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

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

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

Пример COUNTA

ФУНКЦИЯ COUNTA игнорирует пустые значения в D3, D4, D8 и D11 и подсчитывает только ячейки, содержащие значения в столбце D. Функция находит шесть ячеек в столбце D, содержащих значения, и отображает 6 в качестве выходных данных.

Как посчитать пустые ячейки

Допустим у нас есть таблица с данными. Нужно посчитать количество пустых ячеек.

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

Для этого используем функцию СЧИТАТЬПУСТОТЫ.

Нажимаем на кнопочку Вставить функцию (слева от строки формул).
В появившемся окне в поле Категория выбираем Статистические.
В поле Выберете функцию выбираем СЧИТАТЬПУСТОТЫ.
Нажимаем ОК.

Где найти функцию СЧИТАТЬПУСТОТЫ

В появившемся окне мышкой встаем в поле Диапазон и мышкой выделяем данные в которых нужно посчитать пустые ячейки. Нажимаем ОК.

Ввод функции СЧИТАТЬПУСТОТЫ

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

Добавим еще один столбец и пропишем в нем формулу, которая выдает пустую ячейку.

Для примера воспользуемся функцией ЕСЛИ.
Введем данные в поля:

ячейка,содержащая пустой текст

Протянем нашу формулу на следующие ячейки. Наша табличка примет вид.

Формулу СЧИТАТЬПУСТОТЫ протянем на следующий столбец. Результат будет тот же.

СЧИТАТЬПУСТОТЫ в Excel

Но допустим нам не надо считать пустые ячейки с формулами.
Бывает так, что нужно добавить в таблицу новые строки и нужно проверить во всех ли есть формула.

Для подсчета только пустых ячеек используем функции СУММПРОИЗВ и ЕПУСТО.

Для начала вызовем функцию СУММПРОИЗВ. Для этого нажмем на кнопочку Вставить функцию (Fx). В появившемся окне выберем категорию Математические, в поле Выберете функцию, выбрать СУММПРОИЗВ. Нажать ОК.

Где найти функцию СУММПРОИЗВ

Можно в строке формул написать =СУММПРОИЗВ(, встать на название функции курсором и нажать Вставить функцию (Fx). Появится окно с аргументами функции.

Вызвать функцию СУММПРОИЗВ из строки формул

В поле Массив1 вписываем: –ЕПУСТО(В2:В10)

Функция ЕПУСТО определяет пустая ячейка или нет и возвращает логическое значение ИСТИНА или ЛОЖЬ.

— (два минуса) преобразуют функцию ЕПУСТО в математические значения 1 или 0. Если поставить один минус, формула вернет отрицательное значение. Два минуса дадут положительное значение.

Ввод функции ЕПУСТО в СУММПРОИЗВ

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

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

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

Добавим в данные пустую строку. Результат изменится на 1.

Ввод дополнительных данных

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

Изменение диапазона в функции

Диапазон в формуле автоматически изменится.

Результат функций СУММПРОИЗВ и ЕПУСТО

Автор Alfi Опубликовано 21.02.2019 21.02.2019 Рубрики Функции

Добавить комментарий Отменить ответ

Для отправки комментария вам необходимо авторизоваться.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *