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

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

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

Первоначально пробовал решить задачу с помощью стандартной функции Excel -

1
СЧЕТЗ()
. В конечном счете эта функция помогла в решении, но само решение получилось несколько корявым. А вот второе решение получилось красивым и компактным.

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

В этом способе применяется функция

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

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

Ячейки

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

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

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

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

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

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

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

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

Код макроса Excel

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

1
CountRows
.

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

=CountRows(2:55)

В этой формуле диапазон

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

=CountRows(10:255)

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

Дополнение

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

10 x 12000

где

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

Для форматирования воспользуемся функцией

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

Я воспользовался заменителем функции

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

=O21&" x "&A1

В этой формуле значение ячейки

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

12 x 12000

P.S.

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

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

На этом все.


MongoDB - создание документа

![MongoDB]({{site.url}}/images/uploads/2017/05/mongodb-logo.jpg "MongoDB")Приступили к самому основному - операциям создания, чтения, изм...… Continue reading

MongoDB - документы

Published on May 23, 2017