Excel — как подсчитать количество непустых строк
Стоит задача — подсчитать количество непустых строк в таблице Excel.
Собственно, таблица представляет из себя полуавтоматическую программу по составлению раскроя металлопрофиля. На “плечи” таблицы возложено вычисление остатков (отходов) при раскрое с учетом допусков-припусков, углов пила и ширины пила.
Первоначально пробовал решить задачу с помощью стандартной функции Excel — . В конечном счете эта функция помогла в решении, но само решение получилось несколько корявым. А вот второе решение получилось красивым и компактным.
Первое решение
В этом способе применяется функция . Для этого создаем дополнительный столбец, в каждую ячейку которого вставляем формулу:
Ячейки и являются величинами переменными, которые изменяются в зависимости от строки. Функция — это английское название функции .
Результат работы этой формулы таков. Если хотя бы в одной ячейке строки имеется значение, то строка считается непустой и в соответствующей ячейке дополнительного столбца помещается единица (1). Если же ни в одной ячейке строки нет значения, то строка считается пустой и в ячейке дополнительного столбца помещается значение нуль (0).
Осталось самое простое — подсчитать значения дополнительного столбца, сумма которого и будет числом непустых строк в таблице.
Второе решение
В этом способе была использована пользовательская функция, написанная на VBA. Решение получилось очень компактным и красивым. Код макроса представлен ниже:
Этот код нужно вставить в Excel. Для этого открываем редактор макросов, нажав комбинацию клавиш Alt+F11 . Откроется окно, в котором в меню выбираем команды “Insert — Module”. Сохраняем макрос под именем .
Теперь достаточно вставить в нужную ячейку таблицы формулу:
В этой формуле диапазон представляет из себя адрес начальной и конечной строки. Конечно, его можно и нужно изменить, в зависимости от потребностей. Например, так:
Результат работы макроса приводить не буду, так как он очевиден. Единственное, что нужно сделать в завершении — это сохранить таблицу в формате Excel с поддержкой макросов.
Дополнение
В заключение поставил перед собой задачу отформатировать вывод ячейки с только что созданным макросом. Дело в том, что в окончательном документе-раскрое присутствует запись вида:
где — это количество использованных заготовок исходной длины, — длина одной заготовки в мм. В моей таблице раскроя имеется ячейка со значением исходной длины заготовки, которая используется при раскрое. И глупо было бы не использовать эту ячейку при форматировании.
Для форматирования воспользуемся функцией . Те, кто знаком хотя бы с языком программирования JavaScript, сразу узнают в названии этой функции операцию конкатенации. Принцип действия и использование функции предельно прост, поэтому не буду объяснять, как ее применить. Для тех, кто все же не знает — Google в помощь.
Я воспользовался заменителем функции — символом амперсанда . Вид формулы будет таким:
В этой формуле значение ячейки — количество использованных заготовок, вычисленное макросом , значение ячейки — исходная длина заготовки. В результате вывод получается в требуемом формате:
В приведенной статье была использована программа Apache OpenOffice 3, хотя в описании упоминался Excel. На самом деле разницы в этом нет никакой, так как в обеих программах используется примерно одинаковые стандартные функции электронной таблицы. Единственное, что необходимо учитывать — это применять английские названия функций в OpenOffice:
Что такое БФФ
«Back-end for front-end» — сервис-прослойка между фронтом и бэком, который обращается к микросервисам и собирает из них данные в удобной . … Continue reading
Вернуть пустую ячейку из формулы в Excel — CodeRoad
- Я сделал «copy» всех данных и вставил данные как «values».
- Затем я выделил вставленные данные и сделал «replace» ( Ctrl — H ) пустых ячеек с какой-то буквой, я выбрал q , так как его нигде не было в моем листе данных.
- Наконец, я сделал еще один «replace» и заменил q ничем.
Первоначально пробовал решить задачу с помощью стандартной функции Excel — . В конечном счете эта функция помогла в решении, но само решение получилось несколько корявым. А вот второе решение получилось красивым и компактным.
[expert_bq id=»1570″]Формула также может отображаться как текст, если в её синтаксисе были допущены ошибки, например, пропущена или изменена буква. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq]
где — это количество использованных заготовок исходной длины, — длина одной заготовки в мм. В моей таблице раскроя имеется ячейка со значением исходной длины заготовки, которая используется при раскрое. И глупо было бы не использовать эту ячейку при форматировании.
Как заполнить пустые ячейки в Excel?
- A — запись когда число положительное;
- B — запись когда число отрицательное;
- C — запись когда число равно нулю;
- D — запись если в ячейке не число, а текст (обычно для чисел не используется).
Если в вашем наборе данных есть числа, по умолчанию на панели задач будет отображаться СУММ, а не СЧЁТ. В этом случае просто нажмите на него, и он покажет вам СЧЁТ (вместе с другими данными, такими как Среднее или Макс / Мин).
17 ответов
Если цель состоит в том, чтобы иметь возможность отображать ячейку как пустую, когда она на самом деле имеет нулевое значение, то вместо использования формулы, которая приводит к пустой или пустой ячейке (поскольку функции empty() нет), вместо этого,
[expert_bq id=»1570″]Действительно, как мы видим таблица стала чуть более наглядной, более удобной для восприятия данных и наше внимание не отвлекается от ненужных деталей. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq]
Если нам нужно убрать нули в каждой без исключения ячейке листа, то перейдем в панели вкладок Файл -> Параметры -> Дополнительно (так как эти настройки относятся в целом к работе со всей книгой):
Дополнительные сведения
В этом случае нужно проверить, правильно ли записаны данные в ячейках, на которые ссылается выражение, нет ли в них ошибок в синтаксисе или не заложено ли в самой формуле какое-либо некорректное действие (например, деление на 0).
