Как посчитать количество пустых ячеек
Как посчитать количество заполненных и пустых ячеек в Excel
Excel предлагает несколько функций для считывания и подсчета значений в диапазоне ячеек: СЧЁТ(), СЧЁТЗ и СЧИТАТЬПУСТОТЫ. Каждая из этих функций по-своему считывает и считает значения, в зависимости о т того, является ли значение числом, текстом или просто пустой ячейкой. Рассмотрим все эти функции в действии на практическом примере.
Функция СЧЁТ, СЧЁТЗ и СЧИТАТЬПУСТОТЫ для подсчета ячеек в Excel
Ниже на рисунке представлены разные методы подсчета значений из определенного диапазона данных таблицы:
В строке 9 (диапазон B9:E9) функция СЧЁТ подсчитывает числовые значения только тех учеников, которые сдали экзамен. СЧЁТЗ в столбце G (диапазон G2:G6) считает числа всех экзаменов, к которым приступили ученики. В столбце H (диапазон H2:H6) функция СЧИТАТЬПУСТОТЫ ведет счет только для экзаменов, к которым ученики еще не подошли.
Принцип счета ячеек функциями СЧЁТ, СЧЁТЗ и СЧИТАТЬПУСТОТЫ
Функция СЧЁТ подсчитывает количество только для числовых значений в заданном диапазоне. Данная формула для совей работы требует указать только лишь один аргумент – диапазон ячеек. Например, ниже приведенная формула подсчитывает количество только тех ячеек (в диапазоне B2:B6), которые содержат числовые значения:
СЧЁТЗ подсчитывает все ячейки, которые не пустые. Данную функцию удобно использовать в том случаи, когда необходимо подсчитать количество ячеек с любым типом данных: текст или число. Синтаксис формулы требует указать только лишь один аргумент – диапазон данных. Например, ниже приведенная формула подсчитывает все непустые ячейки, которые находиться в диапазоне B5:E5.
Функция СЧИТАТЬПУСТОТЫ подсчитывает исключительно только пустые ячейки в заданном диапазоне данных таблицы. Данная функция также требует для своей работы, указать только лишь один аргумент – ссылка на диапазон данных таблицы. Например, ниже приведенная формула подсчитывает количество всех пустых ячеек из диапазона B2:E2:
Как видно из выше приведенных примеров, каждая из функций обладает своим специальным предназначением и упрощает пользователю процесс обработки данных, предоставив возможность обойти сложные формулы логических комбинаций с функцией ЕСЛИ.
Как посчитать количество пустых и непустых ячеек в Excel
Примеры в этой статье покажут вам, как можно быстро подсчитать пустые и заполненные ячейки в таблице Excel.
Предположим, вы поставляете товары в несколько магазинов. У вас есть таблица с названиями магазинов и количеством проданных товаров. Некоторые позиции в столбце «Продажи» не заполнены.
Вам нужно посчитать пустые ячейки на листе или же найти и выбрать их, чтобы увидеть, сколько магазинов не предоставили необходимые данные. Если список большой, то ручной подсчет займет слишком много времени. Поэтому выбирайте один из вариантов, которые я показываю в этой статье.
Инструмент «Выделить группу ячеек»
Если вам нужно быстро найти все пустые ячейки в Excel, используйте эту операцию. Она быстро выделит все незаполненные клетки, чтобы вы могли легко найти их на своем листе. Но вот количество их она не сосчитает.
На ленте меню Главная выбираем Найти и выделить, затем — Bыделить группу ячеек. В открывшемся окне активируем кнопку Пустые ячейки и жмем ОК.
В результате все клетки без значений будут выделены.
Вы можете использовать «Цвет заливки» на вкладке «Главная», чтобы изменить цвет фона пустых ячеек и зафиксировать выделение.
Обратите внимание, что этот инструмент не обнаруживает псевдо-пустые позиции — с формулами, возвращающими пустое значение. То есть, они не будут выделены.
Также имейте в виду то, что если вы вводили какие-то данные за пределами вашей таблицы, а затем их удалили за ненадобностью, то Эксель запомнит это. Теперь он будет искать не только в области ваших данных, но также в диапазоне, ограниченном этими «бывшими в употреблении» ячейками. В результате будет выделено много лишнего на вашем листе. Это вряд ли будет полезно.
Также вы, я думаю, заметили, что аналогичным образом можно выделить и непустые ячейки. Просто нужно выбрать другой объект поиска — либо Константы, либо Формулы.
Функция поиска и замены.
Вы можете использовать стандартный диалог Excel «Найти и заменить» для подсчета пустых ячеек в таблице. Этот инструмент отобразит список найденных ячеек рядом с их адресами на вашем листе. Он также позволяет вам перейти к любой из них, щелкнув ссылку в списке.
При необходимости, используя вкладку Заменить, вы можете заполнить всех их нулями или каким-либо текстом («Нет данных», к примеру).
Как и в предыдущем методе, вы можете найти и сосчитать все непустые ячейки в заранее выделенном диапазоне. Просто в поле Найти введите * (звездочка). Все позиции, в которых есть хоть какие-то данные, будет выделены. Но значения («») будут проигнорированы.
Формулы для подсчета пустых ячеек.
Функция СЧИТАТЬПУСТОТЫ.
Функция СЧИТАТЬПУСТОТЫ предназначена для подсчета пустых ячеек в указанном диапазоне. Она относится к категории статистических функций и доступна во всех версиях Excel начиная с 2007.
Синтаксис этой функции очень прост и требует только одного аргумента:
СЧИТАТЬПУСТОТЫ(диапазон)
Вот пример формулы в самой простейшей форме:
Чтобы эффективно использовать эту функцию, важно понимать, что именно она подсчитывает.
Глядя на рисунок выше, обратите внимание, что A7, содержащая формулу, возвращающую пустое значение, подсчитывается по-разному:
Это может показаться немного нелогичным, но Excel действительно так работает 🙂
Как вы видите на рисунке выше, для подсчета непустых ячеек отлично подходит функция СЧЁТЗ:
Применяем СЧЁТЕСЛИ или СЧЁТЕСЛИМН.
Другим способом подсчета пустых ячеек в Excel является использование функций СЧЁТЕСЛИ или СЧЁТЕСЛИМН с пустой строкой («») в качестве критериев.
В нашем случае формулы выглядят следующим образом:
Возвращаясь к ранее сказанному, вы можете также использовать выражение
Результаты всех трёх формул, представленных выше, будут совершенно одинаковыми. Поэтому какую из них использовать — дело ваших личных предпочтений.
Подсчёт пустых ячеек с условием.
В ситуации, когда вы хотите подсчитать пустые ячейки на основе некоторого условия, функция СЧЁТЕСЛИМН является весьма подходящей, поскольку ее синтаксис предусматривает несколько критериев.
Например, чтобы определить количество позиций, в которых записано «Бананы» в столбце A и ничего не заполнено в столбце C, используйте эту формулу:
Или введите условие в предопределенную позицию, скажем F1, что будет гораздо правильнее:
Количество пустых ячеек как условие.
В некоторых случаях вам может потребоваться не просто пересчитать пустые ячейки в диапазоне, но предпринять некоторые действия в зависимости от того, есть ли таковые, либо нет. Самый популярный случай — подсчитать итоги, только если все данные в таблице заполнены.
Хотя в Excel нет встроенной функции ЕСЛИСЧИТАТЬПУСТОТЫ, вы можете легко создать свою собственную формулу, используя вместе функции ЕСЛИ и СЧИТАТЬПУСТОТЫ. Вот как:
Полная формула принимает такой вид:
В результате формула определяет все строки, в которых отсутствует одно или несколько значений:
Или вы можете создать другой расчет в зависимости от количества незаполненных позиций. Например, если в диапазоне нет пустот (т.е. если СЧИТАТЬПУСТОТЫ возвращает 0), сложите цифры продаж, в противном случае покажите предупреждение:
То есть, сумма за квартал будет рассчитана только тогда, когда будут заполнены все данные по месяцам.
Как подсчитать пустые строки в Excel.
Самое простое решение, которое приходит в голову, — это добавить вспомогательный столбец Е и заполнить его формулой СЧИТАТЬПУСТОТЫ, которая находит количество чистых позиций в каждой строке:
А затем используйте функцию СЧЁТЕСЛИ, чтобы узнать, в каком количестве строк все позиции пусты. Поскольку наша исходная таблица содержит 4 столбца (от A до D), мы подсчитываем строки с четырьмя пустыми клетками:
Вместо жесткого указания количества столбцов вы можете использовать функцию ЧИСЛСТОЛБ (COLUMNS в английской версии) для его автоматического вычисления:
Если вы не хотите портить структуру красиво оформленного рабочего листа, вы можете добиться того же результата с помощью гораздо более сложной формулы, которая, однако, не требует никаких вспомогательных столбцов:
Разберём, как это работает:
Если приведенная выше формула кажется вам слишком сложной для понимания, возможно, вам больше понравится эта:
Здесь вы используете функцию СЧЁТЕСЛИ, чтобы узнать, сколько значений содержится в каждой строке, а ДВССЫЛ «подает» строки в СЧЁТЕСЛИ одну за другой. Результатом этой операции является массив вида <3; 4; 3; 0; 2; 3; 0; 2; 3>. Проверка на 0 преобразует указанный выше массив в <0; 0; 0; 1; 0; 0; 1; 0; 0>, где единицы представляют пустые строки. Вам остается просто сложить эти цифры.
Обратите также внимание, что это формула массива.
На скриншоте выше вы можете увидеть результат работы этих двух формул.
Также следуем отметить важную особенность работы этих выражений с псевдо-пустыми ячейками. Добавим в C5
Внешне таблица никак не изменится, поскольку эта формула возвращает пустоту. Однако, второй вариант подсчёта обнаружит её присутствие. Ведь если что-то записано, значит, ячейка уже не пустая. По этой причине результат количества пустых строк будет изменён с 2 на 1.
Подсчет действительно пустых ячеек.
Во всех предыдущих примерах мы подсчитывали позиции, включая те, которые кажутся пустыми, но на самом деле содержат пустоту («»), возвращаемую некоторыми формулами. Если вы хотите исключить их из результата подсчета, вы можете использовать эту общую формулу:
Формула умножает количество строк на количество столбцов, чтобы получить общее количество клеток в диапазоне, из которого вы затем вычитаете количество непустых значений, возвращаемых СЧЁТЗ. Как вы помните, функция СЧЁТ в Excel рассматривает значения «» как непустые ячейки. Поэтому они не будут включены в окончательный результат.
Например, чтобы определить количество абсолютно пустых клеток в диапазоне A2: A8, используйте следующую формулу:
На скриншоте ниже показан результат:
Как видите, псевдо-пустая ячейка с формулой здесь не учитывается.
Вот как можно сосчитать пустые и непустые ячейки в Excel. Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Не стесняйтесь делиться любыми другими подсказками, которые могут у вас возникнуть. Будьте счастливы и преуспевайте в Excel!
Подсчет пустых ячеек в EXCEL
history 19 апреля 2013 г.
Подсчет пустых ячеек? Имеется в виду ничего не содержащих ячеек? А есть разница? Разбираемся подробнее.
Но не все так просто.
Если ячейка содержит формулу, результатом которой является значение «» (Пустой текст ), то начинаются сложности, т.к. одни функции считают, что это пустая ячейка, а другие с ними не соглашаются и считают, что Пустой текст – это текстовая строка. Еще более все усложняет то, что ячейка с Пустым текстом выглядит как пустая (если Пустой текст результат вычисления формулы или запись =»»).
Эксперимент
Для иллюстрации приведем пример того как рассматривают ячейку с Пустым текстом Условное форматирование и функция ЕПУСТО() (см. Файл примера ).
Чтобы не запутаться с подсчетом пустых ячеек в диапазоне С2:С11 при наличии значений Пустой текст, приведем обобщающую таблицу:
Формула или средство EXCEL
Комментарий
пустая ячейка и ячейка со значением Пустой текст считаются тождественными
подсчитает все пустые ячейки и ячейки, содержащие Пустой текст
Как посчитать пустые ячейки
Допустим у нас есть таблица с данными. Нужно посчитать количество пустых ячеек.
Для этого используем функцию СЧИТАТЬПУСТОТЫ.
Нажимаем на кнопочку Вставить функцию (слева от строки формул).
В появившемся окне в поле Категория выбираем Статистические.
В поле Выберете функцию выбираем СЧИТАТЬПУСТОТЫ.
Нажимаем ОК.
В появившемся окне мышкой встаем в поле Диапазон и мышкой выделяем данные в которых нужно посчитать пустые ячейки. Нажимаем ОК.
Функция СЧИТАТЬПУСТОТЫ подсчитывает не только пустые ячейки, но и ячейки выдающие как результат пустую строку.
Добавим еще один столбец и пропишем в нем формулу, которая выдает пустую ячейку.
Для примера воспользуемся функцией ЕСЛИ.
Введем данные в поля:
Протянем нашу формулу на следующие ячейки. Наша табличка примет вид.
Формулу СЧИТАТЬПУСТОТЫ протянем на следующий столбец. Результат будет тот же.
Но допустим нам не надо считать пустые ячейки с формулами.
Бывает так, что нужно добавить в таблицу новые строки и нужно проверить во всех ли есть формула.
Для подсчета только пустых ячеек используем функции СУММПРОИЗВ и ЕПУСТО.
Для начала вызовем функцию СУММПРОИЗВ. Для этого нажмем на кнопочку Вставить функцию (Fx). В появившемся окне выберем категорию Математические, в поле Выберете функцию, выбрать СУММПРОИЗВ. Нажать ОК.
Можно в строке формул написать =СУММПРОИЗВ(, встать на название функции курсором и нажать Вставить функцию (Fx). Появится окно с аргументами функции.
В поле Массив1 вписываем: –ЕПУСТО(В2:В10)
Функция ЕПУСТО определяет пустая ячейка или нет и возвращает логическое значение ИСТИНА или ЛОЖЬ.
— (два минуса) преобразуют функцию ЕПУСТО в математические значения 1 или 0. Если поставить один минус, формула вернет отрицательное значение. Два минуса дадут положительное значение.
При нажатии на ОК формула выдаст такой же результат, что и функция СЧИТАТЬПУСТОТЫ.
При протягивании формулы на следующий столбец результат формулы будет равен 0, т.к. столбец содержит не пустые ячейки, а формулы возвращающие пустые ячейки.
Добавим в данные пустую строку. Результат изменится на 1.
Если нужно добавить верхнюю или нижнюю строку в данные, нужно будет изменить диапазон в формуле. Для этого нужно дважды щелкнуть мышкой на ячейке с формулой и поместить мышку на угол выделенного диапазона так, чтобы курсор принял вид двойной стрелочки и протянуть на нужный диапазон. Нажать Enter.
Диапазон в формуле автоматически изменится.
Добавить комментарий Отменить ответ
Для отправки комментария вам необходимо авторизоваться.
Примеры функций СЧЁТЗ И СЧИТАТЬПУСТОТЫ в Excel для пустых ячеек
Функция СЧЁТЗ используется для подсчета количества ячеек, содержащих данные любого типа, и возвращает соответствующее числовое значение.
Примеры использования функций СЧЁТЗ и СЧИТАТЬПУСТОТЫ в Excel
Пример 1. В магазине ведется учет проданных товаров за день и соответствующие данные записываются в таблицу Excel. Некоторые товары проданы не были, поэтому ячейки в столбце «Количество» заполнены не были. Определить количество товаров, которые не пользовались спросом на протяжении дня.
Формула для расчета:
В и тоге получилось 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?
После написания бизнес-плана запуска и реализации проекта фирмы был составлен график активности выполненных действий, которые очень важно выполнить на начальных этапах. На против каждой даты в графике следует заполнять выполненные важные действия. Если сделка переносилась на другую дату и в этот день не было времени и возможности завершить запланированную задачу, тогда поле остается пустым и не заполоняться. Для отчетности по активности реализации проекта за определенный период времени необходимо контролировать динамику и уровень активности руководителя фирмы. Схематический график заполнения выпаленными действиями с готовым результатом выглядит следующим образом:
Для контроля динамики и уровня активности определим процент результативных ней за данный период времени. Для этого нам нужно суммировать все активные дни и разделить их на суммарное количество всех дней (активных + пассивных) за данный период времени. Используем формулу:
В результате получим:
Немного изменим формулу, чтобы посчитать долю в процентах пассивных дней, которая не должна превышать 50-ти процентов для реализации проекта в заранее установленные сроком:
А после добавим соответствующую гистограмму для визуального анализа и контроля:
Особенности использования функций СЧЁТЗ и СЧИТАТЬПУСТОТЫ в Excel
Функция СЧЁТЗ имеет следующий синтаксис:
= СЧЁТЗ( значение1 ;[значение2];. )
Функция СЧИТАТЬПУСТОТЫ имеет следующую синтаксическую запись:
Единственным и обязательным для заполнения аргументом является диапазон, в котором требуется определить число пустых ячеек.
Примечание 2: при подсчете количества ячеек с данными учитываются также те ячейки, в которых содержатся коды ошибок (например, #ЗНАЧ!, #ДЕЛ/0! и другие), и те, в которые выведены пустые строки «». Например, функция =СЧЁТЗ(A1;A2) вернет значение 2, если в ячейке A1 была введена функция =ЧЗНАЧ(«текст»), а в ячейке A2 – функция =ЕСЛИ(2>0;«»;ЛОЖЬ).
Функция СЧИТАТЬПУСТОТЫ определяет число ячеек, не содержащих данные, и возвращает соответствующее числовое значение.
Примечание 3: функция СЧИТАТЬПУСТОТЫ при подсчете также учитывает ячейки, содержащие пустые текстовые строки. Например, если в ячейке A1 была записана формула =ТЕКСТ(«»;»»), результатом выполнения функции =СЧИТАТЬПУСТОТЫ(A1) будет число 1.