Для тех кто боялся, но все же готов попробовать. (Excel)
Зачастую, когда говорят о работе в офисных приложениях, подразумевается использование ПК в качестве печатной машинки и, если честно, то это достаточно дорогая замена физическим механизмам, если не пользоваться возможностями который дает нам ПК. Давайте об этом и поговорим.
Введение.
На мой взгляд ПК появился как инструмент для решение принципа автоматизации рутинных процессов и в этом он чертовски хорош. Кроме того развитие функциональных программ есть история создания инструментов для реализации перевода авральных задач в рутинные действия, как следствие углубление разделения труда. Однако есть области, в т.ч. и документооборота, где на сегодняшний день ПК используется именно как печатная машинка в т.ч. и потому, что серьезных разработок не велось в силу того, что оклады людей сидят в накладных расходах и, как следствие, не велики, что не скажешь о возрастающих объемах документооборота.
Итак, поговорим про строительство, конкретно про составление Исполнительной документации (далее ИД).
Большая часть работ закрывается по форме Акта освидетельствования скрытых работ (утвержденным приказом Федеральной службы по экологическому, технологическому и атомному надзору от 26 декабря 2006 года N 1128 (В редакции, введенной в действие с 6 марта 2016 года приказом Ростехнадзора от 26 октября 2015 года №42. Далее АОСР).
Исходные данные для автоматизации.
По-этому возьмем форму АОСР за основу. Итак, у нас есть шаблон документа, в который заносится следующая информация:
— постфикс номера акта;
— наименование объекта капитального строительства;
— юридические данные об участниках строительства (Застройщик или Заказчик; Лицо, осуществляющее строительство; Лицо, осуществляющее подготовку проектной документации;
Лицо, осуществляющее строительство, выполнившее работы, подлежащие освидетельствованию; иные лица.)
— перечень лиц с наименованием организаций, должностей и приказов подтверждающих их полномочия;
— наименование выполненных работ;
— сроки выполнения работ;
— перечень работ, входящих в выполненные работы;
— ссылки на НТД и разделы проекта/тех.документации;
— ссылки на исполнительные схемы, акты испытаний (если таковые требуются);
— перечень используемых материалов с сылками на документы подтверждающими их соответствие (паспорта, сертификаты и т.п.)
Мысли о принципиальных методах решения задачи
Итак, в первом приближении, можно просто создать наглядную таблицу, в которой назначив соответствующие однотипные поля каждому акту мы получим наглядную портянку схемы выполнения работ на объекте. И в этом нет ничего нового. Итак, нам необходимо связать форму с ячейками в таблицах данных и здесь есть 2 варианта:
1. Слияние с файлом Word
2. Заполнение при помощи макросов шаблона на базе Excel.
Каждый из этих способов имеет свои плюсы и минусы, но т.к. слияние производит замену в реальном времени, то мною было принято решение выбрать второй пункт, который в реальном времени слияние не обеспечивает и каждый раз необходимо будет осуществлять вывод актов, в случае корректировки данных, заново. Это связано с тем, что мне зачастую необходима история моих действий.
Итак, теперь мы упираемся в 2 задачи:
1. Заполнение шаблона на основе табличных данных
2. Какие поля достаточно ввести один раз, какие будет меняться время от времени и какие поля будут отличаться в каждом акте.
Решая задачу №2 мы вынесем на отдельный лист те данные, которые будут едины в рамках объекта/раздела проекта строительства — это:
— юридические данные об участниках строительства (Застройщик или Заказчик; Лицо, осуществляющее строительство; Лицо, осуществляющее подготовку проектной документации;
Лицо, осуществляющее строительство, выполнившее работы, подлежащие освидетельствованию; иные лица.)
— перечень лиц с наименованием организаций, должностей и приказов подтверждающих их полномочия;
На текущем листе заполним лишь один раз, а для остальных актов просто проставим ссылки на эти значения:
— Постфикс к номеру акта;
И поля, которые будут меняться в каждом акте:
— сроки выполнения работ;
— перечень работ, входящих в выполненные работы;
— ссылки на исполнительные схемы, акты испытаний (если таковые требуются);
— перечень используемых материалов с сылками на документы подтверждающими их соответствие (паспорта, сертификаты и т.п.)
Теперь об удобствах, если записать ФИО ответственных лиц, их организацию, приказ о назначении с датой, то при помощи инструмента «проверка данных» можно занести их фамилии в спойлер, а формулой подтягивать их регалии.
=ЕСЛИОШИБКА(ДВССЫЛ(СЦЕПИТЬ("'Данные для проекта'!";АДРЕС((ПОИСКПОЗ(E30;'Данные для проекта'!$G$15:$G$34;0))+14;6)));"-")
Т.е. на листе 'Данные для проекта' в диапазоне $G$15:$G$34, в 6 м столбце ищем значение в ячейке Е30, и как только находим нехитрым методом преобразуем в адрес, который будет преобразован в ссылку формулами.
Новая проблема уже упирается в длину строки, если использовать шрифт Times New Roman №10, то длина текста, которая будет выводится на печать, не превышает 105 символов. Т.е. мы упираемся в необходимость создавать костыль для переносов. Итак код функции на VBA:
Function PatrOfString(StringOfTable As String, Nnumber As Byte) As String
Dim МассивБлоков(1 To 10) As String
Dim i As Integer '
Dim j As Integer '
Dim k As Integer '
Dim p As Integer '
For i = 1 To 10
Let МассивБлоков(i) = " "
Next i
Let k = 1
Let p = Len(StringOfTable)
Let p1 = Len(StringOfTable)
For i = 1 To Round(Len(StringOfTable) / 105) + 1 Step 1
If p > 0 And p < 105 Then
If k <= p1 Then Let МассивБлоков(i) = Mid$(StringOfTable, k, p)
Else
If Mid(StringOfTable, k, 1) = " " Then
If k <= p1 Then Let МассивБлоков(i) = Mid$(StringOfTable, k, 105)
Let p = p - 105
k = k + 105
Else
j = 105 * i
If j - k >= 105 Then
j = k + 105
End If
Do
j = j - 1
Loop While Mid$(StringOfTable, j, 1) <> " "
Let МассивБлоков(i) = Mid$(StringOfTable, k, j - k + 1)
Let p = p - (j - k + 1)
Let k = j + 1
End If
End If
Next i
If Nnumber - 1 > 0 Then
If МассивБлоков(Nnumber) = МассивБлоков(Nnumber - 1) Then МассивБлоков(Nnumber) = " "
End If
PatrOfString = МассивБлоков(Nnumber)
Т.е. мы сперва забираем текст, потом вырезаем 105 символов, ищем с конца первый символ пробел, как его находим, то закидываем в первую строку массива текст длиной с первого символа, по номер найденного пробела. Затем продолжаем операцию до тех пор пока текст не закончится или не забьется выводной массив. На данном этапе он ограничен памятью в 10 строк. Затем мы выводим по ссылке содержание нужной строки из 1–10. Из минусов решения — забивается память, и для каждого нового запроса осуществляется пересчет заново. Но костыль работает.
Теперь вывод в типовой шаблон АОСР. Есть опять 2 варианта, либо вручную прописывать сопоставлению столбцу (/строки в случае горизонтально расположенных данных под каждый акт), то это будет долго и ресурсоемко подгонять каждый новый шаблон или изменения под таблицы данных. Потому делаем оптимизацию. Данные на каждый один акт будут располагаться вертикально, а сопоставление управляющей комбинации символов (на латинице, т.к. сами акты сплошь на кириллице) будут строки с информацией в этих актах, таким образом в двойном вложенном цикле поиском в тексте управляющих символов мы сопоставим нужное значение из колонки.
Do
wb.Worksheets("Пример акта входного контроля").Copy after:=Worksheets(Worksheets.Count)
Set новыйЛист = wb.Worksheets(Worksheets.Count)
For x = 1 To 15 Step 1 ' Перебираем столбцы в листе "Пример акта входного контроля"
For y = 1 To 71 Step 1 ' Перебираем строки в листе "Пример акта входного контроля"
If Sheets(новыйЛист.Name).Cells(y, 20) = 1 Then
Let k = CStr(Sheets(новыйЛист.Name).Cells(y, x)) ' Ищем только если в ячейке что-то есть
If k <> "" Then
For i = 1 To Кол_воЭл_овМассиваДанных Step 1
Let k = Replace(k, arrСсылкиДанных(i), Worksheets("БД для входного контроля (2)").Cells(i, НомерСтолбца))
Next i
новыйЛист.Cells(y, x) = k
End If
End If
Next y
Next x
' Если Вы захотите добавить новые данные для автозаполнения, продолжайте этот список.
' Ячейки имеют координаты формата Cells(3, 2), где 3 - пример номера строки, 2 - пример номера столбца
' Чтобы легко узнавать номер столбца, можно включить стиль ссылок R1C1
' (Файл -> Параметры -> Формулы -> галочка на поле "Стиль ссылок R1C1")
' либо задавать координаты в формате Cells(1, "A"), где 1 - пример номера строки, "А" - пример буквенного обозначения столбца
Rem -= Уточняем имя файла и текущий путь к папке откуда запущен макрос =-
Let ИмяФайла = ИмяФайла + CStr(Worksheets("БД для входного контроля (2)").Cells("1", НомерСтолбца)) + "-"
Let ИмяФайла = ИмяФайла + CStr(Worksheets("БД для входного контроля (2)").Cells("2", НомерСтолбца)) + ".xlsx"
НовыйПуть = Replace(ThisWorkbook.FullName, ThisWorkbook.Name, ИмяФайла)
Application.DisplayAlerts = False ' выключаем вывод предупреждений
Sheets(новыйЛист.Name).Copy ' Копируем текущий лист в новую книгу
ActiveWorkbook.SaveAs Filename:=НовыйПуть, _
FileFormat:=51
ActiveWindow.Close
Sheets(новыйЛист.Name).Delete ' Удаляем созданный лист
Application.DisplayAlerts = True ' Включаем вывод предупреждений обратно
Let НомерСтолбца = НомерСтолбца + 1
Loop While НомерСтолбца <= КонечныйНомерСтолбца
End Sub
Ну и последний момент на данном этапе — мы упираемся в производительность системы и при большом кол-ве актов их вывод будет занимать часы. Дабы ускорить процесс я использую следующий вариант: Копирую содержимое листа с данными в новый лист макросом, он получает в своем имени циферку (2), затем запускается еще один макрос, ускоряющий Excel, но отключающий ряд функционала:
'Ускоряем Excel путём отключения всего "тормозящего"
Public Sub AccelerateExcel()
'Больше не обновляем страницы после каждого действия
Application.ScreenUpdating = False
'Расчёты переводим в ручной режим
Application.Calculation = xlCalculationManual
'Отключаем события
Application.EnableEvents = False
'Не отображаем границы ячеек
If Workbooks.Count Then
ActiveWorkbook.ActiveSheet.DisplayPageBreaks = False
End If
'Отключаем статусную строку
Application.DisplayStatusBar = False
'Отключаем сообщения Excel
Application.DisplayAlerts = False
End Sub
А после вывода всех данных из форм я прогоняю аналогичный макрос где тем же самым переменным присваиваю значение true и удаляю дублирующий лист, что бы не мешался.
