СЧИТАТЬПУСТОТЫ и другие функции для подсчета пустых ячеек в Excel
В учебнике обсуждаются синтаксис и основные способы использования функции СЧИТАТЬПУСТОТЫ для подсчета количества пустых ячеек в Excel.
В нескольких недавних сообщениях мы обсуждали различные способы определения пустых ячеек и выделения пустых ячеек в Excel. Однако в некоторых ситуациях вам может понадобиться узнать, в скольких ячейках ничего нет. В Microsoft Excel для этого тоже есть специальная функция. Этот учебник покажет вам самые быстрые и удобные способы получения количества пустых ячеек в диапазоне, а также полностью пустых строк.
Excel функция СЧИТАТЬПУСТОТЫ
Функция СЧИТАТЬПУСТОТЫ в Excel предназначена для подсчета пустых ячеек в указанном диапазоне. Он относится к категории статистических функций и доступен во всех версиях Excel для Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 и Excel 2007.
Синтаксис этой функции очень прост и требует всего один аргумент:
СЧИТАТЬПУСТО(диапазон)
Где диапазон это диапазон ячеек, в которых должны быть подсчитаны пробелы.
Вот пример формулы СЧИТАТЬПУСТОТЫ в Excel в самом простом виде:
=СЧИТАТЬПУСТО(A2:D2)
Формула, введенная в E2 и скопированная в E7, определяет количество пустых ячеек в столбцах от A до D в каждой строке и возвращает следующие результаты:
Кончик. Чтобы подсчитать непустые ячейки в Excel, используйте функцию СЧЁТ.
Функция СЧИТАТЬПУСТОТЫ – 3 вещи, которые нужно помнить
Чтобы эффективно использовать формулу Excel для подсчета пустых ячеек, важно понимать, какие ячейки функция СЧИТАТЬПУСТОТЫ считает «пустыми».
- Ячейки, содержащие любой текст, числа, даты, логические значения, пробелы или ошибки, не учитываются.
- Ячейки, содержащие нули, считаются непустыми и не учитываются.
- Ячейки, содержащие формулы, возвращающие пустые строки (“”), считаются пустыми и учитываются.
Глядя на скриншот выше, обратите внимание, что ячейка A7, содержащая формулу, возвращающую пустую строку, считается дважды:
- СЧИТАТЬПУСТОТЫ считает строку нулевой длины пустой ячейкой, потому что она кажется пустой.
- COUNTA обрабатывает строку нулевой длины как непустую ячейку, поскольку она фактически содержит формулу.
Это может звучать немного нелогично, но Excel работает именно так 🙂
Как посчитать пустые ячейки в Excel — примеры формул
СЧИТАТЬПУСТОТЫ — самый удобный, но не единственный способ подсчета пустых ячеек в Excel. Следующие примеры демонстрируют несколько других методов и поясняют, какую формулу лучше всего использовать в каком сценарии.
Подсчет пустых ячеек в диапазоне с COUNTBLANK
Всякий раз, когда вам нужно подсчитать пробелы в Excel, СЧИТАТЬПУСТОТЫ — это первая функция, которую стоит попробовать.
Например, чтобы получить количество пустых ячеек в каждой строке в таблице ниже, мы вводим следующую формулу в F2:
=СЧИТАТЬПУСТОТЫ(A2:E2)
Поскольку мы используем относительные ссылки для диапазона, мы можем просто перетащить формулу вниз, и ссылки будут автоматически корректироваться для каждой строки, что приведет к следующему результату:
Как подсчитать пустые ячейки в Excel, используя COUNTIFS или COUNTIF
Другой способ подсчета пустых ячеек в Excel — использование функции СЧЁТЕСЛИ или СЧЁТЕСЛИМН или с пустой строкой (“”) в качестве критерия.
В нашем случае формулы будут такими:
=СЧЁТЕСЛИ(B2:E2, “”)
Или же
=СЧЁТЕСЛИМН(B2:E2, “”)
Как вы можете видеть на снимке экрана ниже, результаты СЧЁТЕСЛИМН точно такие же, как и у СЧИТАТЬПУСТОТЫ, поэтому какую формулу использовать в этом сценарии, зависит от ваших личных предпочтений.
Подсчет пустых ячеек с условием
В ситуации, когда вы хотите подсчитать пустые ячейки на основе некоторого условия, COUNTIFS — это правильная функция для использования, поскольку ее синтаксис предусматривает несколько критериев.
Например, чтобы определить количество ячеек, в которых есть «яблоки» в столбце A и пробелы в столбце C, используйте следующую формулу:
=СЧЁТЕСЛИМН(A2:A9, “яблоки”, C2:C9, “”)
Или введите условие в предопределенную ячейку, скажем, F1, и обратитесь к этой ячейке как к критерию:
=СЧЁТЕСЛИМН(A2:A9, F1, C2:C9, “”)
ЕСЛИ СЧИТАТЬПУСТО в Excel
В некоторых случаях вам может понадобиться не просто подсчитать пустые ячейки в диапазоне, но и предпринять какие-то действия в зависимости от того, есть пустые ячейки или нет.
Хотя в Excel нет встроенной функции ЕСЛИ СЧИТАТЬПУСТОТЫ, вы можете легко создать свою собственную формулу, используя функции ЕСЛИ и СЧИТАТЬПУСТОТЫ вместе. Вот как:
- Проверьте, равно ли количество пробелов нулю, и поместите это выражение в логическую проверку ЕСЛИ:
СЧИТАТЬПУСТОТЫ(B2:D2)=0 - Если логическая проверка дает ИСТИНА, выведите «Без пробелов».
- Если логическая проверка дает FALSE, выведите «Пробелы».
Полная формула принимает следующий вид:
=ЕСЛИ(СЧИТАТЬПУСТОТЫ(B2:D2)=0, “Без пробелов”, “Пробелы”)
В результате формула идентифицирует все строки, в которых отсутствует одно или несколько значений:
Или вы можете запустить другую функцию в зависимости от количества пробелов. Например, если в диапазоне B2:D2 нет пустых ячеек (т. е. если СЧЕТПУСТОТЫ возвращает 0), то суммируйте значения, в противном случае возвращайте «Пробелы»:
=ЕСЛИ(СЧИТАТЬПУСТОТЫ(B2:D2)=0, СУММ(B2:D2), “Пробелы”)
Как посчитать пустые строки в Excel
Предположим, у вас есть таблица, в которой некоторые строки содержат информацию, а другие строки совершенно пусты. Вопрос – как получить количество строк, в которых ничего не содержится?
Самое простое решение, которое приходит на ум, — это добавить вспомогательный столбец и заполнить его формулой Excel СЧИТАТЬПУСТОТЫ, которая находит количество пустых ячеек в каждой строке:
=СЧИТАТЬПУСТОТЫ(A2:E2)
А затем используйте функцию СЧЁТЕСЛИ, чтобы узнать, во скольких строках все ячейки пусты. Поскольку наша исходная таблица содержит 5 столбцов (от A до E), мы подсчитываем строки с 5 пустыми ячейками:
=СЧЁТЕСЛИ(F2:F8, 5))
Вместо того, чтобы «жестко задавать» количество столбцов, вы можете использовать функцию СТОЛБЦЫ для его автоматического вычисления:
=СЧЁТЕСЛИ(F2:F8, СТОЛБЦЫ(A2:E2))
Если вы не хотите искажать структуру красиво оформленного рабочего листа, вы можете добиться того же результата с помощью гораздо более сложной формулы, которая, однако, не требует ни вспомогательных столбцов, ни даже ввода массива:
=СУММ(–(МММУЛЬМ(–(A2:E8<>“”), СТРОКА(ДВССЫЛ(“A1:A”&СТОЛБЦЫ(A2:E8))))=0))
Работая изнутри наружу, вот что делает формула:
- Сначала вы проверяете весь диапазон на наличие непустых ячеек, используя такое выражение, как A2:E8<>“”, а затем приводите возвращаемые логические значения TRUE и FALSE к единицам и нулям с помощью двойного унарного оператора (–) . Результатом этой операции является двумерный массив единиц (не пробелов) и нулей (пробелов).
- Целью части ROW является создание вертикального массива числовых ненулевых значений, в котором количество элементов равно количеству столбцов диапазона. В нашем случае диапазон состоит из 5 столбцов (A2:E8), поэтому мы получаем такой массив: {1;2;3;4;5}
- Функция МУМНОЖ вычисляет матричное произведение вышеуказанных массивов и выдает результат вида: {11;0;15;8;0;8;10}. В этом массиве единственное, что имеет значение для нас, — это 0 значений, представляющих строки, в которых все ячейки пусты.
- Наконец, вы сравниваете каждый элемент приведенного выше массива с нулем, приводите ИСТИНА и ЛОЖЬ к 1 и 0, а затем суммируете элементы этого окончательного массива: {0;1;0;0;1;0;0}. Имея в виду, что 1 соответствуют пустым строкам, вы получите желаемый результат.
Если приведенная выше формула кажется вам слишком сложной для понимания, вам может понравиться эта:
=СУММ(–(СЧЁТЕСЛИ(ДВССЫЛ(“A”&СТРОКА(A2:A8) & “:E”&СТРОКА(A2:A8)), “<>“&””)=0))
Здесь вы используете функцию СЧЁТЕСЛИ, чтобы найти, сколько непустых ячеек есть в каждой строке, а ДВССЫЛ “подает” строки в СЧЁТЕСЛИ одну за другой. Результатом этой операции является массив типа {4;0;5;3;0;3;4}. Проверка на 0 преобразует приведенный выше массив в {0;1;0;0;1;0;0}, где 1 представляют пустые строки, поэтому вам просто нужно их сложить.
Подсчет действительно пустых ячеек, исключая пустые строки
Во всех предыдущих примерах мы подсчитывали пустые ячейки, включая те, которые только кажутся пустыми, но на самом деле содержат пустые строки (“”), возвращаемые некоторыми формулами. Если вы хотите исключить строки нулевой длины из результата, вы можете использовать эту общую формулу:
РЯДЫ(диапазон) * КОЛОННЫ(диапазон) – СЧЁТ(диапазон)
Что делает формула, так это умножает количество строк на количество столбцов, чтобы получить общее количество ячеек в диапазоне, из которого вы вычитаете количество непустых значений, возвращаемое COUNTA. Как вы, возможно, помните, функция СЧЕТЧИК в Excel рассматривает пустые строки как непустые ячейки, поэтому они не будут включены в окончательный результат.
Например, чтобы определить, сколько абсолютно пустых ячеек в диапазоне A2:A8, используйте следующую формулу:
=СТРОКИ(A2:A8) * СТОЛБЦЫ(A2:A8) – СЧЁТ(A2:A8)
На скриншоте ниже показан результат:
Вот как считать пустые ячейки в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Доступные загрузки
Примеры формулы подсчета пустых ячеек
