Подсчет пустых ячеек в EXCEL
history 19 апреля 2013 г.
- Группы статей
- Значение Пустой текст ("")
- Подсчет ячеек
- Пользовательский формат
- Пустые ячейки
- Условное форматирование
Подсчет пустых ячеек? Имеется в виду ничего не содержащих ячеек? А есть разница? Разбираемся подробнее.
Под пустой ячейкой понимается ячейка, которая не содержит значения или формулы. Определить пустую ячейку можно с помощью функции ЕПУСТО() .
Если необходимо подсчитать пустые ячейки в диапазоне A1:D4 , то можно использовать формулу =СЧИТАТЬПУСТОТЫ(A1:D4) или =СУММПРОИЗВ(—ЕПУСТО(A1:D4)) .
Но не все так просто.
Если ячейка содержит формулу, результатом которой является значение "" (Пустой текст ), то начинаются сложности, т.к. одни функции считают, что это пустая ячейка, а другие с ними не соглашаются и считают, что Пустой текст – это текстовая строка. Еще более все усложняет то, что ячейка с Пустым текстом выглядит как пустая (если Пустой текст результат вычисления формулы или запись ="").
Что это еще за Пустой текст и откуда он берется? Значение Пустой текст (две кавычки (""), между которыми ничего нет) может быть результатом, например, вычисления формулы с условием: =ЕСЛИ(F1>0;"больше 0";"") . Т.е. разработчик намеренно использует значение Пустой текст . Это удобно, т.к. результат Пустой текст обладает замечательным свойством: ячейка выглядит пустой. Этого результата можно, конечно, добиться с помощью Условного форматирования или Пользовательского формата , но гораздо быстрее просто ввести "". Но, этот подход имеет и свою цену: некоторые функции и средства EXCEL интерпретирует ячейку, содержащую Пустой текст , как пустую ячейку, а другие, как содержащую текстовое значение.
Эксперимент
Для иллюстрации приведем пример того как рассматривают ячейку с Пустым текстом Условное форматирование и функция ЕПУСТО() (см. Файл примера ).
Рассмотрим диапазон A1:D4 , содержащий числа, текст, пустые ячейки и Пустой текст "" (наиболее общий случай).
Ячейка С4 содержит значение Пустой текст (введено как результат вычисления формулы =ЕСЛИ(1>2;1;"") ) и выделена жирной границей. Условное форматирование с правилом « Форматировать только те ячейки, которые пустые » выделит действительно пустые ячейки и ячейку со значением Пустой текст !
Функция ЕПУСТО() не разделяет такого подхода и говорит, что в С4 , что-то есть (формула =ЕПУСТО(C4) введенная в ячейку С5 возвращает ЛОЖЬ).
Функция СЧИТАТЬПУСТОТЫ() также как и Условное форматирование , учитывает при подсчете ячейки со значением Пустой текст вместе с пустыми ячейками.
Чтобы не запутаться с подсчетом пустых ячеек в диапазоне С2:С11 при наличии значений Пустой текст, приведем обобщающую таблицу:
Формула или средство EXCEL
Различает ли пустую ячейку и ячейку со значением Пустой текст ?
Комментарий
пустая ячейка и ячейка со значением Пустой текст считаются тождественными
подсчитает все пустые ячейки и ячейки, содержащие Пустой текст
Подсчет непанковых ячеек
Чтобы подсчитать количество ячеек в диапазоне, содержащих значения, используйте функцию СЧЁТЗ.
Иногда при подсчете ячеек удобнее пропускать пустые ячейки, поскольку смысловую нагрузку несут только ячейки со значениями. Например, вы хотите подсчитать общее количество продавцов, которые сделали продажу (столбец D).
СчётА игнорирует пустые значения в ячейках D3, D4, D8 и D11 и подсчитывают только ячейки, содержащие значения в столбце D. Функция находит шесть ячеек в столбце D, содержащих значения, и выводит 6.