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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Код макроса Excel

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

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

=CountRows(2:55)

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

=CountRows(10:255)

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

Дополнение

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

10 x 12000

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

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

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

=O21&" x "&A1

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

12 x 12000

P.S.

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

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

На этом все.


Различие между exports и module.exports

Попытка разобраться, в чем различие между exports и module.exports, основанная на статье Understanding module.exports ...Continue reading

Комментарии

Защита от автоматических сообщений
CAPTCHA
Введите слово на картинке