Для тех кто боялся, но все же готов попробовать. (Excel)

в 11:13, , рубрики: Excel, ms office, vba, Алгоритмы, КодоБред, ненормальное программирование, Программирование, программирование для чайников, функциональное программирование

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

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

Источник

* - обязательные к заполнению поля


https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js