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

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

    Ну. И что?
    Реклама
    Комментарии 18
    • +1

      Магические константы в 105 символов выглядят совсем уж плохо (как минимум потому что символы в Times New Roman не фиксированной ширины).


      Если Вам нужен только качественный печатный формат, то можете посмотреть в сторону latex: более-менее удобная поддержка макросов и счетчиков, перекрестных ссылок.
      Из минусов: тяжелый порог входа, нет качественного экспорта в MS word, другие люди не захотят пользоваться латехом.


      UPD. К функциональному программированию эта статья мало относится.

      • 0
        Меня не только печатный формат интересует, в конце-концов я думаю что так или иначе можно подвесить на вывод или преобразование в картинку/pdf. Дело в том, что фактически затем планирую добавить месячно-суточный график на основании которого будут заполняться другие типовые формы, например Общий журнал работ (Раздел 3), Акт готовности электромонтажных работ и другие. Можно ли в latex реализовать и парсить информацию с календарных графиков, заполняемых вручную?
        • 0

          Все зависит от того в каком формате будет представлен график, теоретически, если он он будет plain text (csv) то можно и замарочиться

          • 0
            На текущий момент видится мне следующим образом — МСГ в формате Excel, оттуда берутся работы, объемы работ согласно отчетным интервалам (например с 26го текущего по 25е число следующего месяца), в отдельном фильтре указывается какие работы будут упоминаться в каких типах актов, затем данные будут заноситься частично автоматически, частично вручную по нескольким таблицам и уже обработка информации с них, по принципу указанному в статье, будет осуществляться вывод комплекта документации в виде файлов.

            На текущий момент у мя только одна проблема — под каждый новый принтер в системе приходится пересохранять файлы-шаблонов форм актов, т.к. не смотря на первоначальные настройки на разных принтерах даже на одной системе слетают границы. Если latex может решить эту проблему, то можно попробовать использовать его
            • 0

              Есть еще кое-что добавить:


              1. Lualatex — вариант Латеха, в котором (как это не удивительно) встроена поддержка языка программирования Lua. Собственно парсинг удобнее реализовывать на нормальном языке, а не в Латехе.
              2. Вывод в PDF в будет "из коробки" в любом современном дистрибутиве Латеха. Результаты воспроизводимы, печать из PDF будет стабильная с точностью до настроек принтера.
              3. Не находясь в контексте вашего документооборота сложно предлагать решения, я делаю это потому, что являюсь начинающим фанатиком. Принимайте решения о целесообразности самостоятельно.
          • +1

            Можно ж на VBA дергать скрипт компиляции latex, перед этим сгенерировав шаблон

            • 0
              Из другой области, но может будет полезно: для сложных работ (статьи с математикой, таблицами, графиками и алгоритмами в бумажные журналы) использую LyX. Графики делаю, в том числе, и в Excel, первожу в картинки (нпр., JPEG). На мой взгляд LyX проще ворда. На выходе легко получаю pdf.
          • 0
            Вспоминаю те времена когда я на VBA писал хорошие вещи, обработчики со всеми связями, даже добавлял формы красивые, для добавления информации и отображения подробностей. А также использовал WinAPI чтобы это все выглядело как нужно. Это было конечно до тесного знакомства с SQL базами.
            • +2
              И теперь спустя ваш опыт работы со скуэлем, положа руку на сердце, стоит ли базы делать на нём для домашне-офисного пользования? Или всё таки пытаться делать базы на нескольких файликах экселя? Что быстрее работает в простых поисках, что проще изучать, что проще обслуживать?
              • 0
                изучать Excel для программирования в VB…
                может тогда гуглдокс?
                как пример xakep.ru/2015/01/08/google-apps-script
                • 0

                  Гуглдокс незаменим в таких кейсах. +Масса удобств из-за облака.
                  Но я стараюсь все же избавиться от бумаги.

            • 0
              У меня несколько проще все сделано — на листе «исходники» собираются все данные, а потом раскидываются по десятку остальных актов. Печать из excel в принципе норм получается.
              • 0
                Купите себе кусочек фокспро 1С (технологическую платформу) и жуйте те же кактусы, но элегантно и со всеми встроенными удобствами.
                • 0

                  В данном случае выбор обусловлен лишь тем, в чем я умею работать и в чем чаще всего работаю как инженер-строитель

                • 0
                  Раньше (в 2к) просто отключалась отрисовка.
                  И потом, нужно использовать массив типа — ячейка!
                  Из прошлого опыта типа того что тут 2-5 стрпниц А4 готовились с минуту, потом, с отключенной отрисовкой и оформлением массива ячеек в памчти с послежкющей вставкой всего масмива — 2-3 сек.
                  Т.е. не надо городить на странице. Всё делается в оперативной памчти и потом вставляется чохом.
                  • 0
                    Главное, не забыть включить отрисовку потом.
                  • 0
                    А чем ваше решение лучше того что предлагает MS?
                    Для заполнения актов на скрытые пользуюсь связкой word + excel.
                    В ворде есть такая вкладка — рассылки. Там можно связать документ с данными из excel (и не только). И для каждого вхождения данных будет создан документ по шаблону (вкладка найти и объединить -> объединить все. Если подробнее то:
                    1. создаем в excel таблицу — в столбцах данные которые будут импортированы в ворд, первая строка — заголовки полей слияния.
                    2. в ворде создаем шаблон акта (или чего вам там надо) и на вкладке рассылки:
                    — выбрать получателей -> выбрать существующий список -> указываем файл с данными
                    — вставить поле слияния -> раскрывается список полей (названия столбцов в таблице ексель) -> форматируем как нам надо
                    — найти и объединить -> изменить отдельные документы -> все
                    -> ворд создает новый документ по шаблону с данными по таблице из ексель
                    3. Лайфхаки:
                    — поля с датами могут содержать условия, например если данные пустые можно вставить значения по умолчанию (если не заполнена дата вставляем '__'______20__ и тд)
                    — даты передаются как числа, потому правой кнопкой мыши по ним и в код/значениеполей добавляем \@ «dd MMMM yyyy г.» или как вам надо отформатировать)
                    — если в таблице нужно передать длинные строки, то есть одна тонкость. Если первая строка данных в екселе содержит короткую строку (короче 250 символов или около того) то последующие строки этого столбца ворд тоже обрежет. Потому первую строку с данными я заполняю просто длинной строкой типа 12345… и так 1000 символов.
                    — в таблице ексель я создаю поле «печать». Потом в ворде во вкладке рассылки -> изменить список получателей выбираю фильтр по полю «печать»=… и отфильтровываются документы которые нужны сейчас, а не вся таблица. Как правило я создаю в екселе сразу реестр работ по всему объекту, и по мере готовности печатаю.
                    — связать документ ворда можно не только с екселем, но и со многими источниками данных вроде бд и тд
                    — полезно и даже очень изучать инструменты в которых работаешь, особенно когда есть неплохая такая справка в офисе.
                    • 0
                      По выводным формам:
                      1. Мне удобнее и привычнее работать с Excel, чем с Word.
                      2. Когда кол-во пунктов для вывода становится очень много, то привязками в офисе сложнеее отслеживать такие вещи для меня визуально, в то же время я прописываю доп колонку из управляющих символов на латинице, загоняю из в массив, а в шаблоне акта расставляю как мне заблагорассудится, т.е. я могу поместить значение как в отдельную ячейку, так и набрать текст навроде «b6 b7 — b9c0c1c2(b8)» или «a2, a3», моя функция корректно отрабатывает вывод такой вывод. Т.е. Набрать можно абсолютно любой текст на кириллице и, вставив управляющую латиницу, на выходе получить любые сочетания текста с сохранением кириллицы.

                      Я знаю, что в сети есть 2 альтернативных варианта решения проблемы, один работает со слиянием, второй так же выводит в эксель, НО(!), я не собираюсь изначально ограничиваться только выводной формой таблицы. На текущий момент я реализовал следующий функционал:
                      — Заполнение данные для входного контроля и вывод акты входного контроля;
                      — Вывод журнала входного контроля;
                      — Заполнение форм для вывода АОСР с подтягиванием данных из листа с данными по материалам и годовым Месячно-суточным графиком, т.е. даты парсятся с графика согласно отчетных периодов (по умолчанию месяц) и расстановка следующих друг за другом актов в рамках каждого из отчетных периодов для АОСР, пока, берется последовательно с МСГ.
                      — Есть визуальный (пока визуальный) контроль за кол-вом списываемых по актам АОСР материалов.

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

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

                      В планах:
                      — подключить ВСН'овские формы
                      — реестр документов на основании выводимых актов
                      — ОЖР — разделы 3 и 6
                      — доработать ввод данных для АОСР, с тем что бы система отслеживала вывод работ по участкам, возможно делала несколько актов в зависимости от этого.

                      Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.