Как посчитать пустые ячейки в excel

Способы подсчета количества ячеек в диапазоне с данными

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

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

Совет: Чтобы ввести нескольких условий, используйте вместо этого функцию СЧЁТЕСЛИМН.

Подсчет пустых ячеек в EXCEL

Под пустой ячейкой понимается ячейка, которая не содержит значения или формулы. Определить пустую ячейку можно с помощью функции ЕПУСТО() .

Если необходимо подсчитать пустые ячейки в диапазоне A1:D4 , то можно использовать формулу =СЧИТАТЬПУСТОТЫ(A1:D4) или =СУММПРОИЗВ(—ЕПУСТО(A1:D4)) .

Но не все так просто.

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

Что это еще за Пустой текст и откуда он берется? Значение Пустой текст (две кавычки («»), между которыми ничего нет) может быть результатом, например, вычисления формулы с условием: =ЕСЛИ(F1>0;»больше 0″;»») . Т.е. разработчик намеренно использует значение Пустой текст . Это удобно, т.к. результат Пустой текст обладает замечательным свойством: ячейка выглядит пустой. Этого результата можно, конечно, добиться с помощью Условного форматирования или Пользовательского формата , но гораздо быстрее просто ввести «». Но, этот подход имеет и свою цену: некоторые функции и средства EXCEL интерпретирует ячейку, содержащую Пустой текст , как пустую ячейку, а другие, как содержащую текстовое значение.

Эксперимент

Для иллюстрации приведем пример того как рассматривают ячейку с Пустым текстом Условное форматирование и функция ЕПУСТО() (см. Файл примера ).

Рассмотрим диапазон A1:D4 , содержащий числа, текст, пустые ячейки и Пустой текст «» (наиболее общий случай).

Ячейка С4 содержит значение Пустой текст (введено как результат вычисления формулы =ЕСЛИ(1>2;1;»») ) и выделена жирной границей. Условное форматирование с правилом « Форматировать только те ячейки, которые пустые » выделит действительно пустые ячейки и ячейку со значением Пустой текст !

Функция ЕПУСТО() не разделяет такого подхода и говорит, что в С4 , что-то есть (формула =ЕПУСТО(C4) введенная в ячейку С5 возвращает ЛОЖЬ).

Функция СЧИТАТЬПУСТОТЫ() также как и Условное форматирование , учитывает при подсчете ячейки со значением Пустой текст вместе с пустыми ячейками.

Чтобы не запутаться с подсчетом пустых ячеек в диапазоне С2:С11 при наличии значений Пустой текст, приведем обобщающую таблицу:

Формула или средство EXCEL

Различает ли пустую ячейку и ячейку со значением Пустой текст ?

Комментарий

пустая ячейка и ячейка со значением Пустой текст считаются тождественными

подсчитает все пустые ячейки и ячейки, содержащие Пустой текст

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

Используйте функцию COUNTBLANK, одну из статистических функций, чтобы подсчитать количество пустых ячеек в диапазоне ячеек.

Синтаксис

COUNTBLANK (диапазон)

Аргументы функции COUNTBLANK приведены ниже.

Диапазон — обязательный аргумент. Диапазон, в котором требуется подсчитать количество пустых ячеек.

Замечания

Ячейки с формулами, которые возвращают значение «» (пустой текст), также учитываются при подсчете. Ячейки с нулевыми значениями не учитываются.

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel.

Подсчитывает пустые ячейки в указанном выше диапазоне.

Примечание: Копирование из некоторых браузеров может добавить апостроф в начало формулы при вставке в Excel. Если формула не отображает результат, выделите ячейку, нажмите клавишу F2, удалите апостроф, а затем нажмите клавишу ВВОД.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Как подсчитать пустые ячейки в Excel (4 удобных способа)

Hugh West

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

Скачать Рабочую тетрадь для практических занятий

Рабочую тетрадь для практических занятий можно скачать отсюда.

Count Empty Cells.xlsm

4 плодотворных способа подсчета пустых ячеек в Excel

Мы будем использовать следующий набор данных, чтобы объяснить способы подсчета пустых ячеек в Excel.

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

1. подсчет пустых ячеек путем вставки формул Excel с функциями COUNTIF, COUNTBLANK, SUMPRODUCT и т.д.

В Excel есть несколько полезных формул для подсчитать пустые ячейки в наборе данных. Функции типа COUNTBLANK, COUNTIF, SUM, SUMPRODUCT, и т.д. используются для составления таких формул. Давайте рассмотрим формулы по очереди.

i. Вставка COUNTBLANK для подсчета пустых ячеек

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

Формула для данного набора данных:

=COUNTBLANK(B5:C5)

Использование Заправочная ручка мы можем узнать результат для остальных строк в наборе данных.

Перетащите знак плюс (+) в правой нижней части ячейки ( B5 ).

Результат смотрите на рисунке ниже.

Описание формулы:

=COUNTBLANK(range)

Здесь ассортимент указывает набор данных, в котором нужно подсчитать пустые ячейки.

Вы также можете использовать вложенные Формулы IF и COUNTBLANK чтобы определить, является ли строка полностью пустой или нет.

Формула будет такой:

=IF(COUNTBLANK(B5:C5)=0, «Не пустой», «Пустой»)

Следуйте приведенному ниже рисунку, чтобы узнать, как это сделать.

Описание формулы:

Синтаксис вложенной формулы:

=IF(logical_test,[value_if_true],[value_if_false])

Вот, логический_тест принимает COUNTBLANK функцию и проверяет, равно ли оно нулю или нет.

значение_если_истина принимает текст, который будет отображаться, если тест истинен.

значение_если_ложно принимает текст, который будет выведен на экран, если тест окажется ложным.

ii. Вставка COUNTIF или COUNTIFS для подсчета пустых ячеек

Вы также можете использовать функцию COUNTIF или COUNTIFS. Оба варианта дадут одинаковый результат.

Формула будет такой:

=COUNTIF(B5:C5,»»)

или,

=COUNTIFS(B5:C5,»»)

Затем перетащите знак плюс (+) в правой нижней части ячейки, чтобы найти количество для остальных строк в наборе данных.

Смотрите также: Как подсчитать дубликаты в Excel (6 простых способов)

Первый столбец «Пустые ячейки» в Колонка D использует функция COUNTIF в то время как второй в Колонка E использует функция COUNTIFS.

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

Формула Объяснение:

=COUNTIF(диапазон, критерии)

=COUNTIFS(criteria_range1, criteria1, [criteria_range2], [criteria2]. )

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

Функция COUNTIFS может взять многочисленные критерии и диапазоны в то время как функция COUNTIFS занимает всего один ассортимент и критерии .

iii. Вставка SUM со строками и столбцами для подсчета пустых ячеек

Более того, существует еще одна вложенная формула, использующая SUM , РЯДЫ, и КОЛУМНЫ функции и т.д. для подсчета пустых строк в наборе данных.

=SUM(—MMULT(—(B5:C11″»),ROW(INDIRECT(«B1:B»&COLUMNS(B5:C11))))=0)

Результат показывает, что есть две пустые строки в наборе данных.

Примечание: Формула считает пустой, если весь ряд пустой. Поэтому она игнорирует ячейку B8.

Формула Объяснение:

Индивидуальный синтаксис вложенной формулы вместе с пояснениями:

=SUM(number1, [number2]. )

Формула принимает числа в качестве аргументов и выдает сумму в качестве результата.

=MMULT(array1,array2)

Здесь он принимает ряд массивов набора данных.

Смотрите также: Excel не прокручивается с помощью клавиш со стрелками (4 подходящих решения)
=ROW([ссылка])

Формула с ROW функция принимает ссылки на строки в наборе данных.

=INDIRECT(ref_text,[a1])

Для этого нужен текст ссылки.

=COLUMNS(array)

Формула с КОЛУМНЫ функция принимает массив набора данных.

Вот, двойной знак минус (-) используется для принудительного преобразования булева значения ИСТИНА или ЛОЖЬ к числовым значениям 1 или 0.

iv. Вставка SUMPRODUCT для подсчета пустых ячеек

Кроме того, SUMPRODUCT также является полезной формулой для подсчета пустых ячеек.

Формула для данного набора данных будет иметь вид:

=SUMPRODUCT(—B5:C11=»»)

Результат показывает, что в данном наборе данных есть 5 пустых ячеек.

Примечание: Считаются пустые ячейки, а не строки, в отличие от метод c .

Формула Объяснение:

=SUMPRODUCT(array1, [array2]. )

Здесь функция используется для приема нескольких массивов и получения суммы этих массивов.

В этом случае у нас есть только один набор массивы и формула принимает диапазон набора данных, только если он равен blank.

Затем, используя двойной знак минус (-) мы преобразовали его в числовое значение, чтобы получить результат.

Читать далее: Как подсчитать пустые ячейки в Excel с помощью условия

2. Подсчет пустых ячеек с помощью команды «Перейти к специальному

С другой стороны, мы можем использовать специальную команду Go To на вкладке Home для поиска пустых ячеек.

Выполните следующие шаги, чтобы узнать, как подсчитать пустые ячейки с помощью Go To Special:

Выберите набор данных.
Выберите Перейти к специальному с сайта Найти и выбрать Вы найдете Найти и выбрать с сайта опции редактирования присутствующий в вкладка Главная .

Вы также можете нажать F5 на клавиатуре, чтобы найти Специальный оттуда.

Появится новое поле. В этом поле выберите Заготовки и нажмите OK.

Вы заметите, что пустые ячейки выделяются автоматически.

Чтобы выделить пустые ячейки из Главная выбор вкладки Цвет заливки и выберите понравившийся вам цвет из выпадающего меню.

Цвет, который вы выбрали, заполнит выбранные пустые ячейки. Давайте пока выберем синий. Результат будет выглядеть следующим образом.

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

Подробнее: Как подсчитать заполненные ячейки в Excel

3. подсчет пустых ячеек с помощью команды Найти и заменить

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

Для его использования необходимо выполнить следующие действия.

Выберите набор данных.
Выберите Найти с сайта Найти и выбрать Если вы не можете его найти, следуйте за картинкой.

Появится новое поле. Оставьте это место пустым в Найдите то, что : вариант.
Затем нажмите на Опции>> .

Появятся новые опции,
- Отметьте опцию Сопоставить все содержимое ячейки .
- С сайта В пределах: в раскрывающемся списке выберите Лист .
- В Поиск: в раскрывающемся списке выберите По колоннам.
- С сайта Загляните в раскрывающемся списке выберите Значения или формулы. (Мы будем выбирать значения, поскольку в нашем наборе данных нет формул). В любом случае, оба варианта будут работать одинаково.
- Сайт Найти и заменить окно должно выглядеть, как показано на рисунке ниже. Нажмите Найти все и результат будет показан на дно коробки.
Читать далее: Подсчет ячеек, содержащих определенный текст в Excel

4. подсчет пустых ячеек с помощью макросов Excel VBA

Наконец, в Макросы VBA можно использовать для подсчета пустых ячеек.

Для этого необходимо выполнить следующие действия:
- Выберите набор данных.
- Выберите лист, на котором присутствует выбранный набор данных.
С сайта Вставка выбрать Модуль .
- Внутри окно Общие напишите приведенный ниже код.
Код:
```
 Sub CountBlanks() 'Updateby20140310 Dim rng As Range Dim WorkRng As Range Dim total As Long On Error Resume Next xTitleId = "Количество пустых ячеек" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8) For Each rng In WorkRng If IsEmpty(rng.Value) Then total = total + 1 End If Next MsgBox "There are " & total & " blank cells inэтот диапазон." End Sub 
```
- Нажмите F5 с клавиатуры, чтобы запустить код.
- Откроется окно с именем » Количество пустых ячеек «.
- Проверьте Диапазон вашего набора данных и, если все в порядке, нажмите кнопку OK.
- Появится новое окно, в котором будет показан результат.
О чем следует помнить
- Не забудьте выбрать диапазон данных, прежде чем начать применять методы с помощью инструментов Excel.
- Для формул напишите формулы, сохраняя синтаксис формулы, а также строки и столбцы ваших наборов данных.
Заключение

В статье описаны четыре плодотворных способа подсчета пустых ячеек в Excel с помощью различных формул и инструментов Excel. Формулы включают такие функции, как COUNTBLANK, COUNTIF, SUMPRODUCT, ROWS, и т.д. Инструменты Excel, используемые в методах, следующие Перейти к разделу Специальный, Найти и заменить команды из Главная вкладка , и макросы VBA чтобы выполнить там коды для подсчета пустых ячеек в Excel. Вы можете ознакомиться с соответствующей темой в разделе Связанное чтение Надеюсь, эта статья была полезной для вас. Если у вас возникли дополнительные вопросы, вы можете задать их в разделе комментариев. Также не забудьте посетить сайт наш сайт для получения более информативных статей.

Предыдущий пост Как умножить столбец в Excel на константу (4 простых способа)
Следующий пост Как рассчитать растущий аннуитет в Excel (2 простых способа)

Hugh West

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