Автоматизация составления исполнительной документации по строительству в Excel

Как выжать из штатного компьютера чуть больше при формировании документации.
image

             Автоматизация составления исполнительной документации по строительству в Excel

Зачастую, когда говорят о работе в офисных приложениях, подразумевается использование ПК в качестве печатной машинки и, если честно, то это достаточно дорогая замена физическим механизмам, если не пользоваться возможностями который дает нам ПК. Давайте об этом и поговорим.
image

Введение.

На мой взгляд ПК появился как инструмент для решение принципа автоматизации рутинных процессов и в этом он чертовски хорош. Кроме того развитие функциональных программ есть история создания инструментов для реализации перевода авральных задач в рутинные действия, как следствие углубление разделения труда. Однако есть области, в т.ч. и документооборота, где на сегодняшний день ПК используется именно как печатная машинка в т.ч. и потому, что серьезных разработок не велось в силу того, что оклады людей сидят в накладных расходах и, как следствие, не велики, что не скажешь о возрастающих объемах документооборота.

Итак, поговорим про строительство, конкретно про составление Исполнительной документации (далее ИД).

об Исполнительной документации

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

Большая часть работ закрывается по форме Акта освидетельствования скрытых работ (утвержденным приказом Федеральной службы по экологическому, технологическому и атомному надзору от 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. Из минусов решения — забивается память, и для каждого нового запроса осуществляется пересчет заново. Но костыль работает.

Подводные камни использования Excel Power Query и MySQL для автоматизации отчетности

Теперь вывод в типовой шаблон АОСР. Есть опять 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 и удаляю дублирующий лист, что бы не мешался.