Взгляд НСИ на 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 As range, 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 'Второй цикл On Error Resume Next 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, намного легче.

Теги:

Взгляд НСИ на VBA в Excel и не только

От простого к сложному

Результат

Подготовка шаблона к импорту в систему

Немного кода

Доверяй, но проверяй

Выводы