Как подсчитать количество пустых и непустых ячеек в Excel
Если ваша задача состоит в том, чтобы заставить Excel считать пустые ячейки на листе, прочитайте эту статью, чтобы найти 3 способа сделать это. Узнайте, как искать и выбирать между ними, используя стандартные инструменты поиска или просто используя формулу.
Примеры в этой статье покажут вам, как можно быстро подсчитать пустые и заполненные ячейки в электронной таблице Excel.
- Стандартный инструмент «Выделить группу ячеек»
- Функция поиска и замены может быть полезна
- Эффективное использование функции СЧИТАТЬПУСТОТЫ
- Применить СЧЁТЕСЛИ и СЧЁТЕСЛИМН
- Подсчет пустых ячеек с условием
- Использовать количество пустых ячеек в качестве условия в формуле
- Как считать пустые строки
- Формула для подсчета действительно плохих клеток
Предположим, вы поставляете товары в несколько магазинов. У вас есть таблица с названиями магазинов и количеством проданных товаров. Некоторые позиции в графе «Продажи» не заполнены.
Вы должны подсчитать пустые ячейки на листе или найти и выбрать их, чтобы увидеть, сколько магазинов не предоставили требуемые данные. Если список большой, ручной подсчет займет слишком много времени. Поэтому выбирайте один из вариантов, которые я покажу вам в этой статье.
Инструмент «Выделить группу ячеек»
Если вам нужно быстро найти все пустые ячейки в Excel, воспользуйтесь этой операцией. Он быстро выделит все пустые ячейки, чтобы вы могли легко найти их на листе. Но она не считает их количество.
На ленте меню «Пуск» выберите «Найти и выбрать», затем выберите «Группа ячеек». В открывшемся окне активируйте кнопку Пустые ячейки и нажмите ОК.
В результате будут выбраны все ячейки без значений.
Вы можете использовать «Цвет заливки» на вкладке «Главная», чтобы изменить цвет фона пустых ячеек и исправить выделение.
Обратите внимание, что этот инструмент не обнаруживает псевдопустые позиции с формулами, которые возвращают пустое значение. То есть они не будут выделены.
Также учтите, что если вы ввели какие-то данные за пределами своей таблицы, а потом удалили их за ненадобностью, то Excel их запомнит. Теперь он будет искать не только вашу область данных, но и диапазон, ограниченный этими «используемыми» ячейками. В результате на вашем листе будет выделено много лишнего. Это вряд ли будет полезно.
Кроме того, я думаю, вы заметили, что ячейки, которые не пусты, могут быть выбраны таким же образом. Вам просто нужно выбрать другой объект поиска, либо Константы, либо Формулы.
Функция поиска и замены.
Вы можете использовать стандартное диалоговое окно Excel «Найти и заменить» для подсчета пустых ячеек в таблице. Этот инструмент отобразит список найденных ячеек рядом с их адресами на вашем листе. Это также позволяет вам перейти к любому из них, щелкнув ссылку в списке.
- Выберите диапазон, в котором следует подсчитывать пробелы. Если вы не укажете диапазон, поиск будет выполняться по всей таблице. Затем нажмите на ленту «Найти и выделить» или используйте сочетание клавиш Ctrl + F.
- Оставьте поле поиска пустым.
- Нажмите «Параметры» и установите флажок «Вся ячейка.
- Выберите формулы или значения из раскрывающегося списка Область поиска:
- Если вы выберете «Найти значения», инструмент подсчитает все пустые ячейки, включая псевдопустые ячейки со значениями типа «».
- Выберите опцию «Формулы», чтобы найти и подсчитать абсолютно пустые позиции. «Пустые» формулы учитываться не будут.
- Нажмите кнопку «Найти все», чтобы увидеть результаты. Вы увидите найденную сумму в левом нижнем углу.
При необходимости с помощью вкладки «Заменить» можно заполнить их все нулями или каким-либо текстом («Нет данных», например).
Как и в предыдущем методе, вы можете найти и подсчитать все непустые ячейки в заранее выделенном диапазоне. Просто введите * (звездочка) в поле поиска. Все элементы, содержащие хотя бы некоторые данные, будут выделены. Но значения («») будут игнорироваться.
Формулы для подсчета пустых ячеек.
Функция СЧИТАТЬПУСТОТЫ.
Функция СЧИТАТЬПУСТОТЫ предназначена для подсчета пустых ячеек в указанном диапазоне. Он относится к категории статистических функций и доступен во всех версиях Excel с 2007 года.
Синтаксис этой функции очень прост и требует только одного аргумента:
СЧИТАТЬПУСТО(диапазон)
Где диапазон — это область вашего рабочего листа, где должны учитываться позиции без данных.
Вот пример формулы в самом простом виде:
=СЧИТАТЬПУСТО(C2:C17)
Чтобы эффективно использовать эту функцию, важно понимать, что именно имеет значение.
- Содержимое в виде текста, чисел, дат, логических значений или ошибок игнорируется.
- Нули также игнорируются, даже если они скрыты форматом.
- Учитываются формулы, возвращающие пустые значения.
Глядя на рисунок выше, обратите внимание, что A7, содержащий формулу, возвращающую пустое значение, вычисляется иначе:
- СЧИТАТЬПУСТОТЫ считает его пустым, потому что он визуально выглядит пустым.
- COUNTA рассматривает его как содержимое, потому что на самом деле оно содержит формулу.
Это может показаться немного нелогичным, но именно так работает Excel 🙂
Как вы можете видеть на рисунке выше, функция COUNT отлично подходит для подсчета непустых ячеек:
=СЧЁТ(A2:A8)
СЧИТАТЬПУСТОТЫ — Самый удобный, но не единственный способ подсчета пустых ячеек в Excel. В следующих примерах демонстрируются различные другие методы и поясняется, какую формулу лучше всего использовать в каждом сценарии.
Применяем СЧЁТЕСЛИ или СЧЁТЕСЛИМН.
Другой способ подсчета пустых ячеек в Excel — использование функций СЧЁТЕСЛИ или СЧЁТЕСЛИ С пустой строкой («») в качестве критерия.
В нашем случае формулы выглядят так:
=СЧЁТЕСЛИ(B2:D2; «»)
или
=СЧЕТЧИКИ(B2:D2; «»)
Возвращаясь к сказанному выше, можно также использовать выражение
=СЧИТАТЬПУСТО(B2:D2)
Результаты трех формул, представленных выше, будут точно такими же. Так что какой из них использовать — дело личных предпочтений.
Подсчёт пустых ячеек с условием.
В ситуации, когда вы хотите подсчитать пустые ячейки на основе некоторого условия, функция СЧЁТЕСЛИМН очень подходит, поскольку ее синтаксис допускает несколько критериев.
Например, чтобы определить количество позиций, у которых в столбце A есть «бананы», а в столбце C ничего нет, используйте следующую формулу:
=СЧЁТЕСЛИ(A2:A9, «Бананы», C2:C9; «»)
Или введите условие в предопределенной позиции, скажем, F1, что гораздо правильнее:
=СЧЁТЕСЛИ(A2:A9, F1, C2:C9; «»)
Количество пустых ячеек как условие.
В некоторых случаях может потребоваться не только подсчет пустых ячеек в диапазоне, но и выполнение некоторых действий в зависимости от того, есть они или нет. Самый популярный случай — подсчет итогов только в том случае, если все данные в таблице заполнены.
Хотя в Excel нет встроенной функции ЕСЛИСЧИТАТЬПУСТОТЫ, вы можете легко создать свою собственную формулу, используя вместе функции ЕСЛИ и СЧИТАТЬПУСТОТЫ. Вот как:
- Создадим условие, что количество пробелов равно нулю, и подставим это выражение в логическую проверку ЕСЛИ:
СЧИТАТЬ ПУСТО (B2:D2)=0 - Если логическое значение оценивается как ИСТИНА, оно выводит «Не пусто».
- Если FALSE, мы возвращаем «Пусто».
Полная формула выглядит так:
=ЕСЛИ(СЧЕТЧИК ПУСТОЙ(B2:D2)=0, «Не пусто», «Пусто»)
В результате формула определяет все строки, в которых отсутствует одно или несколько значений:
Или вы можете создать другой расчет на основе количества открытых позиций. Например, если в диапазоне нет пробелов (то есть, если СЧЕТПУСТОТЫ возвращает 0), добавьте количество продаж; в противном случае показать предупреждение:
=ЕСЛИ(СЧИТАТЬПУСТОТЫ(B2:D2)=0, СУММ(B2:D2), «Пусто»)
То есть сумма за квартал будет рассчитана только тогда, когда будут заполнены все данные за месяцы.
Как подсчитать пустые строки в Excel.
Предположим, у вас есть таблица, в которой некоторые строки содержат информацию, а другие совершенно пусты. Возникает вопрос: как получить количество строк, которые ничего не содержат?
Самое простое решение, которое приходит на ум, — это добавить вспомогательный столбец E и заполнить его формулой СЧИТАТЬПУСТОТЫ, которая находит количество чистых позиций в каждой строке:
= ПУСТЫЕ СЧЕТЧИКИ(A2:E2)
А затем используйте функцию COUNTIF, чтобы узнать, сколько строк все позиции пусты. Поскольку наша исходная таблица имеет 4 столбца (от A до D), мы считаем строки с четырьмя пустыми ячейками:
=СЧЁТЕСЛИ(E2:E10,4)
Вместо того, чтобы жестко задавать количество столбцов, вы можете использовать функцию COLUMNS для автоматического расчета:
=СЧЁТЕСЛИ(E2:E10;СТОЛБЦ(A2:D10))
Если вы не хотите испортить структуру хорошо спроектированного рабочего листа, вы можете добиться того же результата с помощью гораздо более сложной формулы, которая, однако, не требует вспомогательных столбцов:
=СУММ(—(НЕСКОЛЬКО(—(A2:D10″»);СТРОКА(ДВССЫЛ(«A1:A»&СТОЛБЦ(A2:D10))))=0))
Давайте посмотрим, как это работает:
- Сначала он проверяет весь диапазон на наличие непустых ячеек с помощью выражения типа A2: D10 «», а затем преобразует возвращенные логические значения TRUE и FALSE в 1 и 0, используя двойное отрицание (—). Результатом этой операции является двумерный массив из единиц (то есть непустых ячеек) и нулей (пустых).
- ROW создает вертикальный массив ненулевых числовых значений, где количество элементов равно количеству столбцов в диапазоне. В нашем случае диапазон состоит из 4 столбцов (A2:B10), поэтому мы получаем такой массив: {1; два; 3; 4}
- Функция MULTIP вычисляет матричное произведение предыдущих матриц и выдает такой результат: {7; 10; 6; 0; пять; 6; 0; пять; 6}. В этом массиве нас интересуют только нулевые значения, которые указывают строки, в которых все ячейки пусты.
- Наконец, он сравнивает каждый элемент предыдущего массива с нулем, преобразует ИСТИНА и ЛОЖЬ в 1 и 0, а затем добавляет элементы последнего массива: {0; 0; 0; а; 0; 0; а; 0; 0}. Принимая во внимание, что 1 соответствуют пустым строкам, вы получите желаемый результат.
Если приведенная выше формула кажется вам слишком сложной для понимания, вам может понравиться эта:
{=СУММ(—(СЧЁТЕСЛИ(ДВССЫЛ(«A»&СТРОКА(A2:A10) & «:D»&СТРОКА(A2:A10)),»»&»»)=0)) }
Здесь вы используете функцию СЧЁТЕСЛИ, чтобы узнать, сколько значений в каждой строке, а ДВССЫЛ вводит строки в СЧЁТЕСЛИ одну за другой. Результатом этой операции является массив типа {3; 4; 3; 0; два; 3; 0; два; 3}. Проверка на 0 преобразует указанный выше массив в {0; 0; 0; а; 0; 0; а; 0; 0}, где единицы представляют пустые строки. Вам просто нужно добавить эти числа.
Также обратите внимание, что это формула массива.
На скриншоте выше вы можете увидеть результат этих двух формул.
Также следует отметить важную особенность этих выражений, работающих с псевдопустыми ячейками. Добавить в С5
=ЕСЛИ(1=1; «»)
Внешне таблица никак не изменится, так как эта формула возвращает пустую. Однако второй вариант подсчета обнаружит его присутствие. Ведь если что-то пишется, то ячейка уже не пустая. По этой причине результат количества пустых строк будет изменен с 2 на 1.
Подсчет действительно пустых ячеек.
Во всех приведенных выше примерах мы подсчитывали позиции, в том числе те, которые кажутся пустыми, но на самом деле содержат пустое пространство («»), возвращаемое некоторыми формулами. Если вы хотите исключить их из результата подсчета, вы можете использовать эту общую формулу:
ROW(диапазон) * NUMBER(диапазон) — COUNT(диапазон)
Формула умножает количество строк на количество столбцов, чтобы получить общее количество ячеек в диапазоне, из которого затем вычитается количество непустых значений, возвращаемых COUNT. Как вы помните, функция COUNT в Excel обрабатывает значения «» как непустые ячейки. Поэтому они не будут включены в окончательный результат.
Например, чтобы определить количество абсолютно пустых ячеек в диапазоне A2:A8, используйте следующую формулу:
=СТРОКИ(A2:A8)*СТОЛБЦ(A2:A8)-СЧЁТ(A2:A8)
На следующем снимке экрана показан результат:
Как видите, псевдопустая ячейка с формулой здесь не учитывается.
Вот как подсчитать пустые и непустые ячейки в Excel. Спасибо за чтение, и мы надеемся увидеть вас в нашем блоге на следующей неделе!
Не стесняйтесь делиться любыми другими советами, которые у вас могут быть. Будьте счастливы и преуспейте в Excel!