- PVSM.RU - https://www.pvsm.ru -
Зачастую, когда говорят о работе в офисных приложениях, подразумевается использование ПК в качестве печатной машинки [1] и, если честно, то это достаточно дорогая замена физическим механизмам, если не пользоваться возможностями который дает нам ПК. Давайте об этом и поговорим.
На мой взгляд ПК появился как инструмент для решение принципа автоматизации рутинных процессов и в этом он чертовски хорош. Кроме того развитие функциональных программ есть история создания инструментов для реализации перевода авральных задач в рутинные действия, как следствие углубление разделения труда. Однако есть области, в т.ч. и документооборота, где на сегодняшний день ПК используется именно как печатная машинка в т.ч. и потому, что серьезных разработок не велось в силу того, что оклады людей сидят в накладных расходах и, как следствие, не велики, что не скажешь о возрастающих объемах документооборота.
Итак, поговорим про строительство, конкретно про составление Исполнительной документации [2] (далее ИД).
Большая часть работ закрывается по форме Акта освидетельствования скрытых работ [3] (утвержденным приказом Федеральной службы по экологическому, технологическому и атомному надзору от 26 декабря 2006 года N 1128 (В редакции, введенной в действие с 6 марта 2016 года приказом Ростехнадзора от 26 октября 2015 года №42. Далее АОСР).
По-этому возьмем форму АОСР за основу. Итак, у нас есть шаблон документа, в который заносится следующая информация:
— постфикс номера акта;
— наименование объекта капитального строительства;
— юридические данные об участниках строительства (Застройщик или Заказчик; Лицо, осуществляющее строительство; Лицо, осуществляющее подготовку проектной документации;
Лицо, осуществляющее строительство, выполнившее работы, подлежащие освидетельствованию; иные лица.)
— перечень лиц с наименованием организаций, должностей и приказов подтверждающих их полномочия;
— наименование выполненных работ;
— сроки выполнения работ;
— перечень работ, входящих в выполненные работы;
— ссылки на НТД и разделы проекта/тех.документации;
— ссылки на исполнительные схемы, акты испытаний (если таковые требуются);
— перечень используемых материалов с сылками на документы подтверждающими их соответствие (паспорта, сертификаты и т.п.)
Итак, в первом приближении, можно просто создать наглядную таблицу, в которой назначив соответствующие однотипные поля каждому акту мы получим наглядную портянку схемы выполнения работ на объекте. И в этом нет ничего нового. Итак, нам необходимо связать форму с ячейками в таблицах данных и здесь есть 2 варианта:
1. Слияние с файлом Word
2. Заполнение при помощи макросов шаблона на базе Excel.
Каждый из этих способов имеет свои плюсы и минусы, но т.к. слияние производит замену в реальном времени, то мною было принято решение выбрать второй пункт, который в реальном времени слияние не обеспечивает и каждый раз необходимо будет осуществлять вывод актов, в случае корректировки данных, заново. Это связано с тем, что мне зачастую необходима история моих действий.
Итак, теперь мы упираемся в 2 задачи:
1. Заполнение шаблона на основе табличных данных
2. Какие поля достаточно ввести один раз, какие будет меняться время от времени и какие поля будут отличаться в каждом акте.
Решая задачу №2 мы вынесем на отдельный лист те данные, которые будут едины в рамках объекта/раздела проекта строительства — это:
На текущем листе заполним лишь один раз, а для остальных актов просто проставим ссылки на эти значения:
И поля, которые будут меняться в каждом акте:
Теперь об удобствах, если записать ФИО ответственных лиц, их организацию, приказ о назначении с датой, то при помощи инструмента «проверка данных» можно занести их фамилии в спойлер [4], а формулой подтягивать их регалии.
=ЕСЛИОШИБКА(ДВССЫЛ(СЦЕПИТЬ("'Данные для проекта'!";АДРЕС((ПОИСКПОЗ(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 и удаляю дублирующий лист, что бы не мешался.
Автор: LuchS-lynx
Источник [5]
Сайт-источник PVSM.RU: https://www.pvsm.ru
Путь до страницы источника: https://www.pvsm.ru/programmirovanie/271001
Ссылки в тексте:
[1] печатной машинки: https://ru.wikipedia.org/wiki/%D0%9F%D0%B8%D1%88%D1%83%D1%89%D0%B0%D1%8F_%D0%BC%D0%B0%D1%88%D0%B8%D0%BD%D0%BA%D0%B0
[2] Исполнительной документации: https://ru.wikipedia.org/wiki/%D0%98%D1%81%D0%BF%D0%BE%D0%BB%D0%BD%D0%B8%D1%82%D0%B5%D0%BB%D1%8C%D0%BD%D0%B0%D1%8F_%D0%B4%D0%BE%D0%BA%D1%83%D0%BC%D0%B5%D0%BD%D1%82%D0%B0%D1%86%D0%B8%D1%8F
[3] Акта освидетельствования скрытых работ: http://www.consultant.ru/law/podborki/akt_osvidetelstvovaniya_skrytyh_rabot/
[4] спойлер: https://support.office.com/ru-ru/article/%D0%9F%D1%80%D0%B8%D0%BC%D0%B5%D0%BD%D0%B5%D0%BD%D0%B8%D0%B5-%D0%BF%D1%80%D0%BE%D0%B2%D0%B5%D1%80%D0%BA%D0%B8-%D0%B4%D0%B0%D0%BD%D0%BD%D1%8B%D1%85-%D0%BA-%D1%8F%D1%87%D0%B5%D0%B9%D0%BA%D0%B0%D0%BC-29fecbcc-d1b9-42c1-9d76-eff3ce5f7249
[5] Источник: https://habrahabr.ru/post/344956/?utm_campaign=344956
Нажмите здесь для печати.