Примеры функций СЧЁТЗ И СЧИТАТЬПУСТОТЫ в Excel для пустых ячеек
Функция СЧЁТЗ используется для подсчета количества ячеек, содержащих данные любого типа, и возвращает соответствующее числовое значение.
Примеры использования функций СЧЁТЗ и СЧИТАТЬПУСТОТЫ в Excel
Пример 1. В магазине ведется учет проданных товаров за день и соответствующие данные записываются в таблицу Excel. Некоторые товары проданы не были, поэтому ячейки в столбце «Количество» заполнены не были. Определить количество товаров, которые не пользовались спросом на протяжении дня.
Таблица данных:
Формула для расчета:
=СЧИТАТЬПУСТОТЫ(B3:B10)
Описание аргумента:
- B3:B10 – диапазон, в котором подсчитывается число пустых ячеек.
Результат вычислений:
В и тоге получилось 3 вида непроданных товаров в розничной точке на протяжении целого торгового дня.
Проверка заполнения данными таблицы с помощью функции СЧЁТЗ в Excel
Пример 2. Для решения спорных ситуаций в случае ДТП в Excel была создана форма для расчета тормозного пути автомобиля. Таблица содержит 4 поля данных, являющихся обязательными для заполнения. Реализовать алгоритм выведения ошибки «Указаны не все данные» в случае, если для расчета не хватает данных.
Исходная таблица:
Функция, которая будет выводить ошибку, если не все данные введены:
=ЕСЛИ(СЧЁТЗ(A2:A5)<>СЧЁТЗ(B2:B5);"Указаны не все данные";B4*0,28*B5+(СТЕПЕНЬ(B4*0,28;2)/(2*B3*B2)))
В столбце A:A содержатся пояснения к данным, находящимся в соседнем столбце. Если число заполненных ячеек в A2:A5 и B2:B5 совпадают, будет выполнен расчет по формуле B4*0,28*B5+(СТЕПЕНЬ(B4*0,28;2)/(2*B3*B2)), иначе будет выведена информация об ошибке. В данном случае все поля указаны и заполнены. Результат:
Если, например, время реакции не указано, будет выведен следующий результат:
Подсчет всех пустых и непустых ячеек в диапазоне с числами и без
Пример 3. Как посчитать количество непустых и пустых ячеек в диапазоне Excel?
После написания бизнес-плана запуска и реализации проекта фирмы был составлен график активности выполненных действий, которые очень важно выполнить на начальных этапах. На против каждой даты в графике следует заполнять выполненные важные действия. Если сделка переносилась на другую дату и в этот день не было времени и возможности завершить запланированную задачу, тогда поле остается пустым и не заполоняться. Для отчетности по активности реализации проекта за определенный период времени необходимо контролировать динамику и уровень активности руководителя фирмы. Схематический график заполнения выпаленными действиями с готовым результатом выглядит следующим образом:
Для контроля динамики и уровня активности определим процент результативных ней за данный период времени. Для этого нам нужно суммировать все активные дни и разделить их на суммарное количество всех дней (активных + пассивных) за данный период времени. Используем формулу:
=СЧЁТЗ(B3:B16)/(СЧЁТЗ(B3:B16)+СЧИТАТЬПУСТОТЫ(B3:B16))
В результате получим:
Немного изменим формулу, чтобы посчитать долю в процентах пассивных дней, которая не должна превышать 50-ти процентов для реализации проекта в заранее установленные сроком:
=СЧИТАТЬПУСТОТЫ(B3:B16)/(СЧЁТЗ(B3:B16)+СЧИТАТЬПУСТОТЫ(B3:B16))
А после добавим соответствующую гистограмму для визуального анализа и контроля:
Особенности использования функций СЧЁТЗ и СЧИТАТЬПУСТОТЫ в Excel
Функция СЧЁТЗ имеет следующий синтаксис:
= СЧЁТЗ(значение1;[значение2];...)
Описание аргументов:
- значение1 – обязательный аргумент, характеризующий первый элемент диапазона, в котором необходимо определить количество непустых ячеек;
- [значение2];… - второй и последующие необязательные аргументы (максимальное количество – 255), характеризующие второй и последующие ячейки из диапазона, в котором требуется выполнить подсчет ячеек, содержащих данные.
Примечания:
- В качестве аргумента удобнее использовать ссылку на диапазон, например: =СЧЁТЗ(A1:D10).
- При подсчете не учитываются ячейки, в которых не содержатся данные любого типа.
- В отличие от функции СЧЁТ, которая выполняет подсчет количества ячеек из диапазона, содержащих только числовые значения, функция СЧЁТЗ учитывает ячейки, содержащие данные любого типа.
- Функции СЧЁТЕСЛИ и СЧЁТЕСЛИМН позволяют использовать более сложные критерии подсчета пустых и непустых ячеек.
- Если в качестве аргументов функции были переданы текстовые, числовые, логические данные или данные другого типа, результатом выполнения будет числовое значение, соответствующее количеству аргументов функции. Например, =СЧЁТЗ(2;«текст»;ЛОЖЬ) вернет значение 3.
Функция СЧИТАТЬПУСТОТЫ имеет следующую синтаксическую запись:
= СЧИТАТЬПУСТОТЫ (диапазон)
Единственным и обязательным для заполнения аргументом является диапазон, в котором требуется определить число пустых ячеек.
Примечания 1:
- Ячейки, содержащие нулевые значения, при подсчете учтены не будут.
- Функция принимает в качестве аргумента только данные ссылочного типа. Выполнение записи типа =СЧИТАТЬПУСТОТЫ(АДРЕС(1;1)) приведет к возникновению ошибки (диалоговое окно) и функция выполнена не будет.
- При использовании функции рекомендуется отключать «Итеративные вычисления». Для этого следует снять отметку напротив соответствующего во вкладке «Формулы» окна параметров:
Примечание 2: при подсчете количества ячеек с данными учитываются также те ячейки, в которых содержатся коды ошибок (например, #ЗНАЧ!, #ДЕЛ/0! и другие), и те, в которые выведены пустые строки «». Например, функция =СЧЁТЗ(A1;A2) вернет значение 2, если в ячейке A1 была введена функция =ЧЗНАЧ(«текст»), а в ячейке A2 – функция =ЕСЛИ(2>0;«»;ЛОЖЬ).
Функция СЧИТАТЬПУСТОТЫ определяет число ячеек, не содержащих данные, и возвращает соответствующее числовое значение.
Примечание 3: функция СЧИТАТЬПУСТОТЫ при подсчете также учитывает ячейки, содержащие пустые текстовые строки. Например, если в ячейке A1 была записана формула =ТЕКСТ("";""), результатом выполнения функции =СЧИТАТЬПУСТОТЫ(A1) будет число 1.