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