Перейти к содержимому

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

  • автор:

Excel — как подсчитать количество непустых строк

Стоит задача — подсчитать количество непустых строк в таблице Excel.

Собственно, таблица представляет из себя полуавтоматическую программу по составлению раскроя металлопрофиля. На “плечи” таблицы возложено вычисление остатков (отходов) при раскрое с учетом допусков-припусков, углов пила и ширины пила.

В таблице каждая строка символически представляет собой одну заготовку исходной длины. Ячейки, входящие в состав одной строки представляют собой детали требуемой длины. Так вот, при составлении раскроя половина процесса автоматизирована. Но потребовалось еще улучшить работу таблицы, а именно — автоматически подсчитывать количество непустых строк. Если в строке хотя бы одна ячейка непустая, то есть в ней присутствует значение, то такая строка считается непустой. Если же в строке все строки пустые (не имеют значения), то и строка считается пустой.

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

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

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

Ячейки и являются величинами переменными, которые изменяются в зависимости от строки. Функция — это английское название функции .

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

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

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

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

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

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

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

Код макроса Excel

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

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

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

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

Дополнение

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

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

Для форматирования воспользуемся функцией . Те, кто знаком хотя бы с языком программирования JavaScript, сразу узнают в названии этой функции операцию конкатенации. Принцип действия и использование функции предельно прост, поэтому не буду объяснять, как ее применить. Для тех, кто все же не знает — Google в помощь.

Я воспользовался заменителем функции — символом амперсанда . Вид формулы будет таким:

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

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

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

Jest — использование skip

В Karma\Jasmine есть варианы для игнорирования выборочных тестов — xit, xdescribe. В этом посте — разберусь, какие есть варианты для этог. … Continue reading

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

Предположим, вам нужно узнать, ввели ли участники группы все часы работы над проектом на этом компьютере. Другими словами, необходимо подсчитать количество ячеек с данными. А чтобы сделать так, чтобы данные не были числными. Некоторые участники группы могли ввели значения-замещего значения, такие как «TBD». Для этого используйте функцию СЧЁТ.

Функция СЧЁТЗ

Эта функция подсчитывает только ячейки с данными, но имейте в виду, что «данные» могут включать пробелы, которые не видны. Да, в этом примере пробелы можно подсчитать самостоятельно, но представьте, что книга велика. Итак, чтобы использовать формулу:

Определить диапазон ячеек, которые нужно подсчитать. В приведенном примере это ячейки с B2 по D6.

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

Ввести формулу в ячейке результата или строке формул и нажать клавишу ВВОД:

Можно также подсчитать ячейки из нескольких диапазонов. В этом примере подсчитываются ячейки в ячейках b2–D6 и b9–D13.

Использование функции СЧЁТЗ для подсчета ячеек в двух диапазонах

Вы увидите, Excel диапазоны ячеек выделяются, а при нажатии ввода появляется результат:

Результат функции СЧЁТЗ

Если известно, что нужно учесть только числа и даты, но не текстовые данные, используйте функцию СЧЕТ.

Как посчитать количество заполненных и пустых ячеек в Excel

Excel предлагает несколько функций для считывания и подсчета значений в диапазоне ячеек: СЧЁТ(), СЧЁТЗ и СЧИТАТЬПУСТОТЫ. Каждая из этих функций по-своему считывает и считает значения, в зависимости о т того, является ли значение числом, текстом или просто пустой ячейкой. Рассмотрим все эти функции в действии на практическом примере.

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

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

СЧЁТ.

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

Принцип счета ячеек функциями СЧЁТ, СЧЁТЗ и СЧИТАТЬПУСТОТЫ

Функция СЧЁТ подсчитывает количество только для числовых значений в заданном диапазоне. Данная формула для совей работы требует указать только лишь один аргумент – диапазон ячеек. Например, ниже приведенная формула подсчитывает количество только тех ячеек (в диапазоне B2:B6), которые содержат числовые значения:

СЧЁТЗ.

СЧЁТЗ подсчитывает все ячейки, которые не пустые. Данную функцию удобно использовать в том случаи, когда необходимо подсчитать количество ячеек с любым типом данных: текст или число. Синтаксис формулы требует указать только лишь один аргумент – диапазон данных. Например, ниже приведенная формула подсчитывает все непустые ячейки, которые находиться в диапазоне B5:E5.

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

СЧИТАТЬПУСТОТЫ.

Как видно из выше приведенных примеров, каждая из функций обладает своим специальным предназначением и упрощает пользователю процесс обработки данных, предоставив возможность обойти сложные формулы логических комбинаций с функцией ЕСЛИ.

Как посчитать в Эксель (Excel) количество непустых ячеек?

Как в программе Эксель (Excel) посчитать непустые (заполненные) ячейки?

Какие существуют способы?

Можно воспользоваться разными способами. Например, если в нужных ячейках только числа, то можно воспользоваться функцией счет. Чтобы ее вызвать щелкаем вверху fx — при этом появляется диалоговое окно мастера функций. Далее в разделе статистические и ищем нужную функцию. Не забыть выделить нужный диапазон. Она выдает нам количество ячеек в диапазоне с числами.

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

Количество непустых ячеек в Excel

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

Иначе подсчёт может быть некорректным — ведь если в ячейке стоит символ пробела, то эта ячейка тоже будет учитываться при подсчёте, хотя в ней по факту нет никаких данных.

Для удаления пробелов можно воспользоваться командой "Найти" — "Заменить".

Что касается самого подсчёта, то его можно сделать несколькими способами.

Рассмотрим некоторые из них.

1 способ

Можно использовать одну из самых известных функций в Excel — СЧЁТЕСЛИ.

1 аргумент этой функции — диапазон, в котором нужно посчитать непустые ячейки.

2 аргумент этой функции — критерий, он выглядит так:

Как мы видим, в столбцах B и C оказалось суммарно 6 непустых ячеек.

2 способ

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

3 способ

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

Она называется СЧЁТЗ.

В качестве аргумента указываем диапазон ячеек (см. скриншот).

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

Функция СЧЕТ3 (Счет Значений) считает количество непустых ячеек в указанном диапазоне.

Работает она так.

Проще всего: в ячейке, где хотим увидеть результат, ставим "=" и набираем первые буквы названия функции (Сч). Тут же выскакивает выпадающее окно с перечнем функций, начинающихся этими буквами. Щелкаем по нужной.

Далее выделяем диапазон: столбец, строку, массив или вовсе разрозненные группы ячеек (через клавишу Ctrl). После выделения диапазона нужно закрыть скобкой ")" окончание выражения.

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

Сама по себе программа Эксель очень удобна тем, что практически все функции в ней можно выполнить нажав определенные клавиши или символы.

Проще всего выделить все ячейки, в которых надо узнать количество заполненных, внизу появится надпись "Количество" и рядом появится цифра. Именно она и будет означать количество заполненных ячеек в выделенной таблице. Только здесь есть один нюанс, если когда-то что-то было в ячейке написано, вы удалили, но на этом месте остался пробел, то программа засчитает его, как символ.

В случае, если вам надо Эксель узнать количество заполненных ячеек, то можно воспользоваться и функцией "СЧЕТ3". Но для меня это очень долго, поэтому я выбираю первый способ.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *