Функция пустая ячейка (ЕПУСТО) в Excel: пошаговая инструкция

Счет пустых ячеек в excel Подсчет пустых ячеек в MS EXCEL Подсчет пустых ячеек? Имеется в виду ничего не содержащих ячеек? А есть разница? Разбираемся

Другие способы подсчета количества ячеек с данными

  • Подсчет символов в ячейках

  • Подсчет количества вхождений значения

  • Определение количества уникальных значений среди дубликатов

Нужна дополнительная помощь?

Инструмент «Выделить группу ячеек»

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

На ленте меню Главная выбираем Найти и выделить, затем — Bыделить группу ячеек. В открывшемся окне активируем кнопку Пустые ячейки и жмем ОК.

инструмент выделить группу ячеек

В результате все клетки без значений будут выделены.

Вы можете использовать «Цвет заливки» на вкладке «Главная», чтобы изменить цвет фона пустых ячеек и зафиксировать выделение.
Обратите внимание, что этот инструмент не обнаруживает псевдо-пустые позиции — с формулами, возвращающими пустое значение. То есть, они не будут выделены.

Также имейте в виду то, что если вы вводили какие-то данные за пределами вашей таблицы, а затем их удалили за ненадобностью, то Эксель запомнит это. Теперь он будет искать не только в области ваших данных, но также в диапазоне, ограниченном этими «бывшими в употреблении» ячейками. В результате будет выделено много лишнего на вашем листе. Это вряд ли будет полезно.

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

Функция СЧЁТ, СЧЁТЗ и СЧИТАТЬПУСТОТЫ для подсчета ячеек в Excel

Ниже на рисунке представлены разные методы подсчета значений из определенного диапазона данных таблицы:

СЧЁТ.

В строке 9 (диапазон B9:E9) функция СЧЁТ подсчитывает числовые значения только тех учеников, которые сдали экзамен. СЧЁТЗ в столбце G (диапазон G2:G6) считает числа всех экзаменов, к которым приступили ученики. В столбце H (диапазон H2:H6) функция СЧИТАТЬПУСТОТЫ ведет счет только для экзаменов, к которым ученики еще не подошли.

Первое решение

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

=1*(COUNTA(B2:N2)>0)

Ячейки B2 и N2 являются величинами переменными, которые изменяются в зависимости от строки. Функция COUNTA() – это английское название функции СЧЕТЗ().

Результат работы этой формулы таков. Если хотя бы в одной ячейке строки имеется значение, то строка считается непустой и в соответствующей ячейке дополнительного столбца помещается единица (1). Если же ни в одной ячейке строки нет значения, то строка считается пустой и в ячейке дополнительного столбца помещается значение нуль (0).

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

Результат работы представлен ниже:

Таблица с дополнительным столбцом в Excel

Вроде бы и ничего результат. Все работает. Но выглядит как-то криво. Дополнительный столбец выполняет только одну единственную задачу – определение строки и мешается, занимая место. Конечно, можно скрыть его. Для этого нажимаем правой кнопкой мыши на заголовке дополнительного столбца (О) и в контекстном меню выбираем “Скрыть”. Но конечный результат меня не устраивал. Поэтому было найдено второе решение.

Проверка заполнения данными таблицы с помощью функции СЧЁТЗ в Excel

Пример 2. Для решения спорных ситуаций в случае ДТП в Excel была создана форма для расчета тормозного пути автомобиля. Таблица содержит 4 поля данных, являющихся обязательными для заполнения. Реализовать алгоритм выведения ошибки «Указаны не все данные» в случае, если для расчета не хватает данных.

Исходная таблица:

funkcii-excel121-4.png

Функция, которая будет выводить ошибку, если не все данные введены:

=ЕСЛИ(СЧЁТЗ(A2:A5)<>СЧЁТЗ(B2:B5);”Указаны не все данные”;B4*0,28*B5+(СТЕПЕНЬ(B4*0,28;2)/(2*B3*B2)))

В столбце A:A содержатся пояснения к данным, находящимся в соседнем столбце. Если число заполненных ячеек в A2:A5 и B2:B5 совпадают, будет выполнен расчет по формуле B4*0,28*B5+(СТЕПЕНЬ(B4*0,28;2)/(2*B3*B2)), иначе будет выведена информация об ошибке. В данном случае все поля указаны и заполнены. Результат:

funkcii-excel121-5.png

Если, например, время реакции не указано, будет выведен следующий результат:

funkcii-excel121-6.png

Второе решение

В этом способе была использована пользовательская функция, написанная на VBA. Решение получилось очень компактным и красивым. Код макроса представлен ниже:

Код макроса Excel

Этот код нужно вставить в Excel. Для этого открываем редактор макросов, нажав комбинацию клавиш Alt+F11. Откроется окно, в котором в меню выбираем команды “Insert – Module”. Сохраняем макрос под именем CountRows.

Теперь достаточно вставить в нужную ячейку таблицы формулу:

=CountRows(2:55)

В этой формуле диапазон 2:55 представляет из себя адрес начальной и конечной строки. Конечно, его можно и нужно изменить, в зависимости от потребностей. Например, так:

=CountRows(10:255)

Результат работы макроса приводить не буду, так как он очевиден. Единственное, что нужно сделать в завершении – это сохранить таблицу в формате Excel с поддержкой макросов.

Дополнение

В заключение поставил перед собой задачу отформатировать вывод ячейки с только что созданным макросом. Дело в том, что в окончательном документе-раскрое присутствует запись вида:

10x12000

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

Для форматирования воспользуемся функцией СЦЕПИТЬ (CONCATENATE). Те, кто знаком хотя бы с языком программирования JavaScript, сразу узнают в названии этой функции операцию конкатенации. Принцип действия и использование функции СЦЕПИТЬ() предельно прост, поэтому не буду объяснять, как ее применить. Для тех, кто все же не знает – Google в помощь.

Я воспользовался заменителем функции СЦЕПИТЬ() – символом амперсанда &. Вид формулы будет таким:

=O21&” x “&A1

В этой формуле значение ячейки O21 – количество использованных заготовок, вычисленное макросом CountRows, значение ячейки A1 – исходная длина заготовки. В результате вывод получается в требуемом формате:

12x12000

Excel works!

Excel работает за вас

menu.png

  • cropped-.png

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

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

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

  • Создаем условие, что количество пробелов равно нулю, и помещаем это выражение в логический тест ЕСЛИ:
    СЧИТАТЬПУСТОТЫ(B2:D2)=0
  • Если логический результат оценивается как ИСТИНА, выведите «Нет пустых».
  • Если же — ЛОЖЬ, возвращаем «Пустые».

Полная формула принимает такой вид:

=ЕСЛИ(СЧИТАТЬПУСТОТЫ(B2:D2)=0; “Нет пустых”; “Пустые”)

В результате формула определяет все строки, в которых отсутствует одно или несколько значений:

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

=ЕСЛИ(СЧИТАТЬПУСТОТЫ(B2:D2)=0; СУММ(B2:D2); “Пустые”)

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

P.S.

В приведенной статье была использована программа Apache OpenOffice 3, хотя в описании упоминался Excel. На самом деле разницы в этом нет никакой, так как в обеих программах используется примерно одинаковые стандартные функции электронной таблицы. Единственное, что необходимо учитывать – это применять английские названия функций в OpenOffice:

  • COUNTA() – СЧЕТЗ()
  • CONCATENATE() – СЦЕПИТЬ()
  • SUMM() – СУММ()

На этом все.

Функция СЧЕТ (COUNT) в Excel

​ реализации проекта за​​ этих ячейках отображаются​С помощью различных комбинаций​6​ смежными, если они​), например “25” или​ задать условия в​ итогов в отчете​Число​ справочного материала.​ использовании предыдущего способа.​​ Excel​​ подсчета только ячеек​

excel-schet-esli-ne-pusto_1.png

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...