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

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

Также может появиться необходимость заполнить все пустые ячейки определенным значением, которое находится в верхней ячейке или в голове пользователя. Как можно это сделать? Сейчас вы узнаете 4 классных способа, с помощью которых можно воплотить эту задачу в реальность.

Функция СЧЕТ, СЧЕТЗ, СЧИТАТЬПУСТОТЫ

Это самая простая функция, которая имеет только один аргумент – диапазон значений. На рисунке ниже виден пример, как эта функция работает на практике.

На этом скриншоте берется строка 9, в которой осуществляется расчет значений, касающихся лишь тех учащихся, которые уже прошли экзамен. Если же использовать функцию СЧЕТЗ, то можно определить значения лишь тех экзаменов, которые ученики начали проходить. Ну и наконец, функция СЧИТАТЬПУСТОТЫ будет определять только те экзамены, которые ученики даже не начали проходить. 

Как работают эти функции

Механизм работы данных функций очень прост. Первая (то есть, СЧЕТ) может использоваться исключительно с числовыми значениями. То есть, никаких букв быть не должно. Для ее использования достаточно указать лишь диапазон ячеек, где будет определяться количество ячеек. Так, приведенная в скриншоте ниже формул узнает количество ячеек с числовыми значениями. То есть, сколько ячеек с цифрами есть в указанном диапазоне (в нашем случае – B2:B6).

В отличие от функции СЧЕТ, формула СЧЕТЗ определяет все ячейки, которые содержат хотя бы какое-нибудь значение, в том числе, и текстовое. Здесь тоже достаточно ввести один аргумент – диапазон. Так, эта формула определяет все ячейки, которые содержат хотя бы одно значение, которые расположены в диапазоне B3:E5.

=СЧЁТЗ(B5:E5)

Если же необходимо определить количество пустых ячеек в таблице, то необходимо воспользоваться функцией СЧИТАТЬПУСТОТЫ. Ее синтаксис такой же, как и у предыдущих, но назначение другое. В нашем примере она считает количество пустых ячеек, расположенных в диапазоне B2:E2.

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

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

Способов, как сделать так, чтобы пустые ячейки были быстро заполнены необходимой информацией, довольно много. Некоторые из них полуавтоматические, некоторые – полностью автоматические, и для их запуска достаточно просто написать макрос и запустить его.

Метод 1: заполнение пустых ячеек заданным значением

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

1. Откройте лист, где требуется заполнение пустых ячеек. 
2. Выделите нужный столбец.

   

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

   

4. Затем все ячейки, не содержащие никаких значений, будут выделены. Теперь достаточно просто начать писать (не нажимая ничего перед этим) то, что нужно, а потом нажать комбинацию клавиш Ctrl + Enter.

   

   

Таким методом можно заполнить нужным значением любое количество пустых ячеек. Как видим, даже формул никаких не понадобилось. 

Метод 2: заполнение пустых ячеек значением, расположенным в ячейке выше

Если в ячейке выше уже есть правильное значение, и его нужно перенести теперь во все пустые, то здесь также ничего сложного нет. В Excel предусмотрена возможность делать это. Просто выполните следующие шаги:

1. Откройте лист, в котором есть пустые ячейки, которые нужно заполнить.
2. Выделите диапазон, где нужно вставить информацию из ячейки выше во все пустые.

   

3. Теперь снова найдите кнопку «Найти и выделить» и выберите то же диалоговое окно «Выделить группу ячеек». И снова нужно нажать на радиокнопку рядом с пунктом «Пустые ячейки».
4. Теперь откройте строку формул, нажмите знак ввода формулы (=) и нажмите по той ячейке, значение которой нам нужно. После этого снова нажмите комбинацию Ctrl + Enter. 

Чтобы было более понятно, вот несколько скриншотов для наглядности.

Метод 3: использование макроса

Макрос – это набор команд, предназначенных для достижения определенной цели. Он позволяет в дальнейшем выполнять нужную последовательность действий буквально в один клик. Заполнение пустых ячеек не является исключением. Приведенным нами макрос также умеет определять активный диапазон, который потом заполняет значениями из ячейки выше. 

Как же реализовать этот метод на практике? Следуйте инструкции:

1. Запустите документ, с которым собираетесь работать и откройте нужный лист. 
2. Нажмите комбинацию клавиш Alt + F11 для того, чтобы запустить среду разработки макросов. 

Далее в поле нужно вставить такой код.

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.

Далее нам нужно сохранить наш макрос. Чтобы это сделать, нажмите комбинацию клавиш Ctrl + S. После этого появится предупреждение, на которое можно не обращать внимания. Поэтому смело нажимайте «Нет». Затем надо задать комбинацию клавиш, по которой будет активироваться макрос. Для этого нужно перейти в меню «Разработчик» – «Макросы». После этого появится окно, в котором надо выбрать тот макрос, который будет использоваться, после чего нажать на кнопку «Параметры». Затем откроется еще одно окно, в котором нужно указать букву, которая будет нажиматься вместе с клавишей Ctrl, в маленьком квадратике. 

Все, теперь можно смело говорить о том, что Excel настроен на заполнение всех пустых ячеек нужного диапазона значениями из ячейки выше. Далее достаточно просто выделить нужные данные и нажать правильную комбинацию клавиш.

При использовании макросов нужно учитывать то, что примененные изменения отменить автоматически уже нельзя. Только вручную вновь делать ячейки пустыми. 

Метод 4: использование надстройки Power Query

Power Query – это набор очень полезных функций, который впервые появился в качестве надстройки еще в Excel 2013 версии. Начиная с 2016-й, он уже встроен в программу и представлен в виде группы «Скачать и преобразовать» на вкладке «Данные».

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

Данный аддон имеет огромное количество различных преимуществ, таких как:

1. Возможность очень быстро обрабатывать сразу огромный массив информации. Он даже более удобный, чем макросы, для реализации этой цели, поскольку последние могут неплохо затормозить работу программы.
2. Если исходная информация претерпевает изменения, достаточно просто обновить первоначальный запрос. Если же использовать приведенные выше методы, приходится все преобразования осуществлять заново. 

Перед тем, как использовать эту функциональную надстройку, нужно выполнить одну из следующих действий:

1. Дать имя диапазону через вкладку «Формулы – Диспетчер имен»
2. Отформатировать его в «умную таблицу». Чтобы это сделать, нужно нажать комбинацию клавиш Ctrl + T.

   

После того, как мы выполним какое-то из этих действий, необходимо перейти на вкладку «Данные» и нажимаем на кнопку «Из таблицы/диапазона». Если же используется Excel более старых версий, то нужно найти отдельную вкладку с названием Power Query.

Далее мы увидим окно, называемое редактором запросов. Нам нужно выделить нужный столбец и открыть вкладку «Преобразование». Там находится пункт «Заполнить – Заполнить вниз».

Все, осталось всего немного – выгрузить таблицу назад в документ, воспользовавшись командой Главная — Закрыть и загрузить — Закрыть и загрузить в….

Если же исходная таблица претерпевала какие-то изменения, то нужно просто перейти на вкладку «Данные» и нажать на кнопку «Обновить все». После этого информация будет автоматически обновлена. 

Выводы

Теперь вы умеете определять количество пустых (а также заполненных числами или буквами) ячеек, а также вставлять необходимое значение туда. Можно проявить фантазию и воспользоваться формулами. Например, при использовании метода 1 можно указать какую-то формулу, после чего она будет автоматически вставлена во все остальные ячейки. 

Макрос также можно изменять под свои задачи. Профессиональное умение любым инструментом (а Excel, безусловно, является таковым) подразумевает способность творчески подходить к процессу. Так что желаем вам творчества и легкости в использовании этого и другого инструментария Excel.