Взгляд НСИ на VBA в Excel и не только
Салют! На связи Ганзюк Владимир. Тружусь инженером по нормативно-справочной информации (НСИ) в компании Bimeister.
Хочу поделиться с вами опытом работы с Excel и рассказать, как можно ускорить выполнение рутинных задач при работе с составлением наименований согласно нормативно-технической документации (НТД).
От простого к сложному
Терпения… Автоматизация при помощи VBA начнется чуть позже, а сейчас я хочу рассказать, как при помощи обычного выпадающего списка можно повысить эффективность работы в Excel.
Суть работы:
Имеем шаблон с проработанными данными для загрузки в систему. Проработка шаблона включает в себя множество рутинных задач:
— Ввод однотипных наименований материалов в шаблон;
— Подготовка шаблона для загрузки в систему (удаление лишних вкладок в книге, форматирование таблицы, проверка введенных данных на корректность);
— Проверка на соответствие с данными в нормативно-технической документации (НТД).
Шаблон для загрузки
Для унификации материалов нашими специалистами был создан файл примеров ранее заведенных материалов, который хранится в облачном хранилище.
Файл примеров
Рутина заключается в том, что файл приходилось постоянно открывать, копировать данные, искать материалы, а кто-то и вовсе мог на облаке удалить пример наименования материала.
Так как файл хранится в облачном хранилище, почему бы не подгружать данные из файла примеров прямо в сам шаблон для загрузки? Для этого воспользуемся силой Power Query.
Для начала создадим ещё один лист в шаблоне под названием «Примеры».
На вкладке «Данные» нажимаем «Получить данные». Далее «Из других источников» и финальный аккорд «Из интернета».
Прошу обратить внимание, что конечный путь на файл заканчивается только форматом самого файла ».xlsx». При наличии параметра в ссылке на файл »? web=1» его следует удалить.
После ввода ссылки на файл появится окно Power Query, где мы жмем на кнопку «Загрузить». И вот уже появляются подгруженные данные из облачного хранилища.
В свойствах запроса листа «Примеры» советую поставить галочку на «Обновление при открытии файла». Также можно обновлять данные с заданным Вам диапазоном времени. В моем случае достаточно каждых 60 минут.
И вот самое интересное: по итогу мы получаем лист в шаблоне, который имеет актуальные данные. В шаблоне на вкладке «Данные» находим кнопку «Проверка данных» и выбираем необходимый столбец с наименованиями листа «Примеры».
Обязательно на ракурсе «Сообщение об ошибке» убрать галочку «Выводить сообщение об ошибке», чтобы можно было редактировать наименование примера.
Результат:
Мы получаем перечень материалов в выпадающем списке, который можно редактировать, но самое удобное — искать материалы можно в самой ячейке. А сам файл примеров теперь защищен паролем и редактирует его только один человек, который и вносит актуальные данные.
Подготовка шаблона к импорту в систему
Суть рутины заключается в том, что необходимо каждый раз в Excel:
Удалять ненужные листы при импорте шаблона;
Сохранять шаблон в формате ».xlsx»;
Переводить введенные данные в формат «умной» таблицы;
Удалять комментарии, внесенные во время работы в шаблоне;
Проверять на корректность введенных данных (Лишние пробелы в наименовании элементов, проставлены ли обязательные условия для импорта: количество элементов, единица измерения, подкласс и др).
Решить данную проблему помог VBA, который одним нажатием решает все 5 задач, указанных выше. Далее указан конечный вариант и шаблон, заполненные специально с «косяками».
Из рисунка видно, что пропущено количество элементов в столбце «E» и единица измерения в столбце «F». После нажатия на макрос появится окно с предупреждением.
В случае ошибок пропущенных значений также появится окно с предупреждением
Как видим, макрос удалил ненужные нам комментарии напротив фланцев, лишние вкладки и выделил те ячейки, которые необходимо заполнить. Также определил последнюю заполненную ячейку и по ней форматировал введенные данные в «умную» таблицу.
Немного кода
В качестве примера приведу функцию, которая во время сохранения шаблона проверяет незаполненные ячейки в столбце «Е», написанные для классов »99_00_Элементы спецификаций». Остальные функции схожи по написанию синтаксиса кода.
Function checkColumnE()
Dim checkMsg As Boolean
checkMsg = False
Set ws = ThisWorkbook.Sheets("Шаблон")
lastRow = ws.Cells(Rows.Count, 7).End(xlUp).Row
Set rng = ws.range("G2:G" & lastRow)
For Each cell In rng.Cells
If cell.Value = "99_00_Элементы спецификаций" And cell.Offset(0, -2).Value = "" Then
If checkMsg = False Then
checkMsg = True
MsgBox "Заполни пустые ячейки в столбце E!"
End If
cell.Offset(0, -2).Interior.Color = RGB(250, 100, 100)
End If
Next cell
End FunctionДоверяй, но проверяй
Каждый день специалист НСИ работает с НТД, проводит сверку значений (например, размеров деталей) в документации заказчика с указанными в ней стандартами.
Невозможно запомнить все НТД. Для этого каждый раз приходится открывать ГОСТы и сверять значения, а под конец рабочего дня глаз может «замылиться», и можно допустить довольно глупую ошибку.
Макрос «Проверить_НТД» работает следующим образом: проверяет, находится ли в столбце «D» необходимый нам ГОСТ, к примеру, пусть это будет ГОСТ 8732–78, в случае нахождения значения, в столбец «С» напротив проверяемого элемента указывается комментарий о совпадении или о том, что по данному ГОСТу изготовление отсутствует, также предусмотрен вариант того, что данного размера и вовсе нет в НТД.
Также наглядно видно, как теперь при использовании вышеописанного макроса «Сохранить шаблон», он просто удалит все ненужные нам комментарии при подготовке шаблона к загрузке.
Сам код:
Покажу работу кода на примере тех же самых труб по ГОСТ 8732–78.
В нашем шаблоне на листе «Доп.сведения» скопирована таблица из открытых источников с размерами труб по ГОСТ 8732–78.
Чуть дальше по коду будет ещё один пример того, что использование функции значительно облегчает работу.
Функция CheckPipes принимает на вход 3 параметра: диапазон проверки Dn (диамтер), S (толщина) трубы и имя НТД.
Private Function CheckPipes(ByVal rngDN, rngS As range, NTD As String)
Dim ws As Worksheet
Dim Arr As Variant
Dim Dn1Arr, S1Arr As Variant
Dim rng As range
Dim WorkRng As range
Dim lastRow As Long
Dim dnOffset As String
Dim sOffset As String
Dim x As range 'Второй цикл
Set WorkRng = Application.Selection
Set ws = ThisWorkbook.Sheets("Допсведения")
dnOffset = WorksheetFunction.Count(rngDN)
sOffset = WorksheetFunction.Count(rngS)
Dim result As range
lastRow = ThisWorkbook.Sheets("Шаблон").Cells(Rows.Count, 4).End(xlUp).Row
Set rng = ThisWorkbook.Sheets("Шаблон").range("D2:D" & lastRow)
For Each cell In rng
'NTD - Поиск НТД в спеке
If InStr(cell, NTD) Then
Dim Dn, Dn1 As String
Dim S, S1 As String
'Массив преобразует "," в ".", поэтому используем Replace
Arr = VBA.Split(Numbers(cell.Value), "х")
Dn1Arr = Str(Arr(0)) 'Ду
S1Arr = Str(Arr(1)) 'Ру
Dn1Arr = Replace(Dn1Arr, ".", ",")
S1Arr = Replace(S1Arr, ".", ",")
Dn1Arr = Trim(Dn1Arr)
S1Arr = Trim(S1Arr)
Dim DnNum, SNum As Double
DnNum = CDbl(Dn1Arr)
SNum = CDbl(S1Arr)Далее определяем последнюю использованную ячейку в столбце «D» для нахождения проверяемого НТД. Если переменная совпадает, то в массив данных Arr присваиваются разбитые значения, но для этого пришлось написать ещё одну функцию «Numbers», которая извлекает из текста только числа и разделяет их символом «х».
В массив под индексом »0» присвоено значение Dn трубы, а под индексом »1» толщина стенки трубы.
Так как при присваивании значений переменных «Dn1Arr» и «S1Arr» типа Variant »,» меняется на ».», и добавляется лишний пробел. Используем метод Replace и Trim чтобы избавиться от ненужных символов.
А далее всё просто: пробегаемся при помощи цикла по проверяемому диапазону и при совпадении «Dn» и «S» из таблицы используем метод Intersect, который возвращает значение при пересечении двух диапазонов.
For Each x In rngDN
If x.Value = DnNum Then
Dn = x.Address & ":" & x.Offset(0, sOffset).Address
Dn1 = x.Value
End If
Next x
For Each x In rngS
If x.Value = SNum Then
S = x.Address & ":" & x.Offset(dnOffset, 0).Address
S1 = x.Value
End If
Next x
If DnNum = Dn1 And SNum = S1 Then
Set result = Application.Intersect(ws.range(Dn), ws.range(S))
Trim (result)
End If
If result Is Nothing Then
cell.Offset(0, -1).Font.Color = RGB(150, 0, 0)
cell.Offset(0, -1).Value = "По ГОСТ отсутствует указанный размер"
ElseIf result.Value = "-" Then
cell.Offset(0, -1).Font.Color = RGB(150, 0, 0)
cell.Offset(0, -1).Value = "По ГОСТ не изготовливают"
Else
cell.Offset(0, -1).Font.Color = RGB(0, 150, 60)
cell.Offset(0, -1).Value = "Данные размеры совпадают с НТД"
End If
End If
Set result = Nothing
Next
End FunctionИ вот, самое главное для тех, кто дочитал…
Как использование функций в макросах упрощает код в дальнейшем?
Мы видим, ниже в макросе нам остается самое простое — присвоить диапазон проверки для переменных и указать в параметрах функции сам НТД.
Sub J__Проверить_ГОСТ()
Dim ws As Worksheet
Dim rngDn1 As range
Dim rngS1 As range
Dim range As range
Set ws = ThisWorkbook.Sheets("Допсведения")
'ГОСТ 8732-78
Set rngDn1 = ws.range("G139:G208")
Set rngS1 = ws.range("H138:BF138")
Call CheckPipes(rngDn1, rngS1, "ГОСТ 8732-78")
'ГОСТ 8734-75
Set rngDn1 = ws.range("G224:G294")
Set rngS1 = ws.range("H223:AT223")
Call CheckPipes(rngDn1, rngS1, "ГОСТ 8734-75")
'ГОСТ 9940-81
Set rngDn1 = ws.range("BK170:BK194")
Set rngS1 = ws.range("BL169:CP169")
Call CheckPipes(rngDn1, rngS1, "ГОСТ 9940-81")
'ГОСТ 9941-81
Set rngDn1 = ws.range("G304:G371")
Set rngS1 = ws.range("H303:AS303")
Call CheckPipes(rngDn1, rngS1, "ГОСТ 9941-81")
'ГОСТ 9941-2022
Set rngDn1 = ws.range("BH304:BH376")
Set rngS1 = ws.range("BI303:DC303")
Call CheckPipes(rngDn1, rngS1, "ГОСТ 9941-2022")
End SubВыводы
Зачем это всё вам?
Я думаю, что мой опыт может быть кому-нибудь полезен. Например тем, кто только хочет начать изучать VBA. VBA — это не так уж и сложно, в отличие от других языков программирования, но он позволит сделать вашу жизнь в работе с Excel, намного легче.
