Как посчитать пустые ячейки в excel
Как посчитать пустые ячейки
Допустим у нас есть таблица с данными. Нужно посчитать количество пустых ячеек.
Для этого используем функцию СЧИТАТЬПУСТОТЫ.
Нажимаем на кнопочку Вставить функцию (слева от строки формул).
В появившемся окне в поле Категория выбираем Статистические.
В поле Выберете функцию выбираем СЧИТАТЬПУСТОТЫ.
Нажимаем ОК.
В появившемся окне мышкой встаем в поле Диапазон и мышкой выделяем данные в которых нужно посчитать пустые ячейки. Нажимаем ОК.
Функция СЧИТАТЬПУСТОТЫ подсчитывает не только пустые ячейки, но и ячейки выдающие как результат пустую строку.
Добавим еще один столбец и пропишем в нем формулу, которая выдает пустую ячейку.
Для примера воспользуемся функцией ЕСЛИ.
Введем данные в поля:
Протянем нашу формулу на следующие ячейки. Наша табличка примет вид.
Формулу СЧИТАТЬПУСТОТЫ протянем на следующий столбец. Результат будет тот же.
Но допустим нам не надо считать пустые ячейки с формулами.
Бывает так, что нужно добавить в таблицу новые строки и нужно проверить во всех ли есть формула.
Для подсчета только пустых ячеек используем функции СУММПРОИЗВ и ЕПУСТО.
Для начала вызовем функцию СУММПРОИЗВ. Для этого нажмем на кнопочку Вставить функцию (Fx). В появившемся окне выберем категорию Математические, в поле Выберете функцию, выбрать СУММПРОИЗВ. Нажать ОК.
Можно в строке формул написать =СУММПРОИЗВ(, встать на название функции курсором и нажать Вставить функцию (Fx). Появится окно с аргументами функции.
В поле Массив1 вписываем: –ЕПУСТО(В2:В10)
Функция ЕПУСТО определяет пустая ячейка или нет и возвращает логическое значение ИСТИНА или ЛОЖЬ.
— (два минуса) преобразуют функцию ЕПУСТО в математические значения 1 или 0. Если поставить один минус, формула вернет отрицательное значение. Два минуса дадут положительное значение.
При нажатии на ОК формула выдаст такой же результат, что и функция СЧИТАТЬПУСТОТЫ.
При протягивании формулы на следующий столбец результат формулы будет равен 0, т.к. столбец содержит не пустые ячейки, а формулы возвращающие пустые ячейки.
Добавим в данные пустую строку. Результат изменится на 1.
Если нужно добавить верхнюю или нижнюю строку в данные, нужно будет изменить диапазон в формуле. Для этого нужно дважды щелкнуть мышкой на ячейке с формулой и поместить мышку на угол выделенного диапазона так, чтобы курсор принял вид двойной стрелочки и протянуть на нужный диапазон. Нажать Enter.
Диапазон в формуле автоматически изменится.
Добавить комментарий Отменить ответ
Для отправки комментария вам необходимо авторизоваться.
Как посчитать количество пустых ячеек в Excel
Во время работы с электронными таблицами может появиться ситуация, когда понадобится посчитать, сколько ячеек заполнено в данный момент, и сколько пустых. Для этого существует сразу несколько функций, каждая из которых имеет свои особенности работы.
Также может появиться необходимость заполнить все пустые ячейки определенным значением, которое находится в верхней ячейке или в голове пользователя. Как можно это сделать? Сейчас вы узнаете 4 классных способа, с помощью которых можно воплотить эту задачу в реальность.
Функция СЧЕТ, СЧЕТЗ, СЧИТАТЬПУСТОТЫ
Это самая простая функция, которая имеет только один аргумент – диапазон значений. На рисунке ниже виден пример, как эта функция работает на практике.
На этом скриншоте берется строка 9, в которой осуществляется расчет значений, касающихся лишь тех учащихся, которые уже прошли экзамен. Если же использовать функцию СЧЕТЗ, то можно определить значения лишь тех экзаменов, которые ученики начали проходить. Ну и наконец, функция СЧИТАТЬПУСТОТЫ будет определять только те экзамены, которые ученики даже не начали проходить.
Как работают эти функции
Механизм работы данных функций очень прост. Первая (то есть, СЧЕТ) может использоваться исключительно с числовыми значениями. То есть, никаких букв быть не должно. Для ее использования достаточно указать лишь диапазон ячеек, где будет определяться количество ячеек. Так, приведенная в скриншоте ниже формул узнает количество ячеек с числовыми значениями. То есть, сколько ячеек с цифрами есть в указанном диапазоне (в нашем случае – B2:B6).
2
3
Каждая из этих функций может использоваться и в составе других формул.Но иногда появляется такая ситуация, когда надо пустые ячейки быстро заполнить определенными значениями. Давайте рассмотрим, что нужно делать для того, чтобы реализовать эту задачу.
Как заполнить пустые ячейки
Способов, как сделать так, чтобы пустые ячейки были быстро заполнены необходимой информацией, довольно много. Некоторые из них полуавтоматические, некоторые – полностью автоматические, и для их запуска достаточно просто написать макрос и запустить его.
Метод 1: заполнение пустых ячеек заданным значением
Пользователь Excel может заполнить пустые ячейки определенным значением, для чего достаточно просто воспользоваться встроенными функциями этой программы. Причем для использования этого метода не требуется никаких специальных знаний. Выполняйте следующие шаги:
Таким методом можно заполнить нужным значением любое количество пустых ячеек. Как видим, даже формул никаких не понадобилось.
Метод 2: заполнение пустых ячеек значением, расположенным в ячейке выше
Если в ячейке выше уже есть правильное значение, и его нужно перенести теперь во все пустые, то здесь также ничего сложного нет. В Excel предусмотрена возможность делать это. Просто выполните следующие шаги:
Чтобы было более понятно, вот несколько скриншотов для наглядности.
9 
10 
11
Метод 3: использование макроса
Макрос – это набор команд, предназначенных для достижения определенной цели. Он позволяет в дальнейшем выполнять нужную последовательность действий буквально в один клик. Заполнение пустых ячеек не является исключением. Приведенным нами макрос также умеет определять активный диапазон, который потом заполняет значениями из ячейки выше.
Как же реализовать этот метод на практике? Следуйте инструкции:
Далее в поле нужно вставить такой код.
Sub FillBlankCells()
Static rngUR As Range: Set rngUR = ActiveWorkbook.ActiveSheet.UsedRange
Dim rngBlank As Range: Set rngBlank = rngUR.Find(«»)
While Not rngBlank Is Nothing
rngBlank.Value = rngBlank.Offset(-1, 0).Value
Set rngBlank = rngUR.Find(«», rngBlank)
Wend
End Sub
Видим, что вышеприведенный фрагмент кода имеет совсем небольшие размеры. В результате, у нас получится следующий результат в редакторе VBA.
12
Далее нам нужно сохранить наш макрос. Чтобы это сделать, нажмите комбинацию клавиш Ctrl + S. После этого появится предупреждение, на которое можно не обращать внимания. Поэтому смело нажимайте «Нет». Затем надо задать комбинацию клавиш, по которой будет активироваться макрос. Для этого нужно перейти в меню «Разработчик» – «Макросы». После этого появится окно, в котором надо выбрать тот макрос, который будет использоваться, после чего нажать на кнопку «Параметры». Затем откроется еще одно окно, в котором нужно указать букву, которая будет нажиматься вместе с клавишей Ctrl, в маленьком квадратике.
Все, теперь можно смело говорить о том, что Excel настроен на заполнение всех пустых ячеек нужного диапазона значениями из ячейки выше. Далее достаточно просто выделить нужные данные и нажать правильную комбинацию клавиш.
При использовании макросов нужно учитывать то, что примененные изменения отменить автоматически уже нельзя. Только вручную вновь делать ячейки пустыми.
Метод 4: использование надстройки Power Query
Power Query – это набор очень полезных функций, который впервые появился в качестве надстройки еще в Excel 2013 версии. Начиная с 2016-й, он уже встроен в программу и представлен в виде группы «Скачать и преобразовать» на вкладке «Данные».
Эта надстройка позволяет совершать огромное количество полезных действий с информацией: скачивать огромный ее набор с интернета, подгружать из файлов почти любых типов, удалять ненужные строки, техническую информацию компании, осуществлять еще много других преобразований, включая вставку информации в пустые ячейки.
Данный аддон имеет огромное количество различных преимуществ, таких как:
Перед тем, как использовать эту функциональную надстройку, нужно выполнить одну из следующих действий:
После того, как мы выполним какое-то из этих действий, необходимо перейти на вкладку «Данные» и нажимаем на кнопку «Из таблицы/диапазона». Если же используется Excel более старых версий, то нужно найти отдельную вкладку с названием Power Query.
Далее мы увидим окно, называемое редактором запросов. Нам нужно выделить нужный столбец и открыть вкладку «Преобразование». Там находится пункт «Заполнить – Заполнить вниз».
14
Все, осталось всего немного – выгрузить таблицу назад в документ, воспользовавшись командой Главная — Закрыть и загрузить — Закрыть и загрузить в….
Если же исходная таблица претерпевала какие-то изменения, то нужно просто перейти на вкладку «Данные» и нажать на кнопку «Обновить все». После этого информация будет автоматически обновлена.
Выводы
Теперь вы умеете определять количество пустых (а также заполненных числами или буквами) ячеек, а также вставлять необходимое значение туда. Можно проявить фантазию и воспользоваться формулами. Например, при использовании метода 1 можно указать какую-то формулу, после чего она будет автоматически вставлена во все остальные ячейки.
Макрос также можно изменять под свои задачи. Профессиональное умение любым инструментом (а Excel, безусловно, является таковым) подразумевает способность творчески подходить к процессу. Так что желаем вам творчества и легкости в использовании этого и другого инструментария Excel.
Подсчет непустых ячеек с помощью счёта СЧЁТА
Предположим, вам нужно узнать, ввели ли участники группы все часы работы над проектом на этом компьютере. Другими словами, необходимо подсчитать количество ячеек с данными. А чтобы сделать так, чтобы данные не были числными. Некоторые участники группы могли ввели значения-замещего значения, такие как «TBD». Для этого используйте функцию СЧЁТ.
Эта функция подсчитывает только ячейки с данными, но имейте в виду, что «данные» могут включать пробелы, которые не видны. Да, в этом примере пробелы можно подсчитать самостоятельно, но представьте, что книга велика. Итак, чтобы использовать формулу:
Определить диапазон ячеек, которые нужно подсчитать. В приведенном примере это ячейки с B2 по D6.
Выделить ячейку, в которой нужно отобразить результат. Назовем ее ячейкой результата.
Ввести формулу в ячейке результата или строке формул и нажать клавишу ВВОД:
Можно также подсчитать ячейки из нескольких диапазонов. В этом примере подсчитываются ячейки в ячейках b2–D6 и b9–D13.
Вы увидите, Excel диапазоны ячеек выделяются, а при нажатии ввода появляется результат:
Если известно, что нужно учесть только числа и даты, но не текстовые данные, используйте функцию СЧЕТ.
Как посчитать количество пустых и непустых ячеек в 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
Комментарий
пустая ячейка и ячейка со значением Пустой текст считаются тождественными
подсчитает все пустые ячейки и ячейки, содержащие Пустой текст