Салют! На связи Ганзюк Владимир. Тружусь инженером по нормативно-справочной информации (НСИ) в компании Bimeister.

Хочу поделиться с вами опытом работы с Excel и рассказать, как можно ускорить выполнение рутинных задач при работе с составлением наименований согласно нормативно-технической документации (НТД).

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

Терпения… Автоматизация при помощи VBA начнется чуть позже, а сейчас я хочу рассказать, как при помощи обычного выпадающего списка можно повысить эффективность работы в Excel.

Суть работы:

Имеем шаблон с проработанными данными для загрузки в систему. Проработка шаблона включает в себя множество рутинных задач:

• Ввод однотипных наименований материалов в шаблон;

• Подготовка шаблона для загрузки в систему (удаление лишних вкладок в книге, форматирование таблицы, проверка введенных данных на корректность);

• Проверка на соответствие с данными в нормативно-технической документации (НТД).

Для унификации материалов нашими специалистами был создан файл примеров ранее заведенных материалов, который хранится в облачном хранилище.

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

Так как файл хранится в облачном хранилище, почему бы не подгружать данные из файла примеров прямо в сам шаблон для загрузки?  Для этого воспользуемся силой Power Query.

Для начала создадим ещё один лист в шаблоне под названием «Примеры».

На вкладке «Данные» нажимаем «Получить данные». Далее «Из других источников» и финальный аккорд «Из интернета».

Прошу обратить внимание, что конечный путь на файл заканчивается только форматом самого файла «.xlsx». При наличии параметра в ссылке на файл «?web=1» его следует удалить.

После ввода ссылки на файл появится окно Power Query, где мы жмем на кнопку «Загрузить». И вот уже появляются подгруженные данные из облачного хранилища.

В свойствах запроса листа «Примеры» советую поставить галочку на «Обновление при открытии файла». Также можно обновлять данные с заданным Вам диапазоном времени. В моем случае достаточно каждых 60 минут.

И вот самое интересное: по итогу мы получаем лист в шаблоне, который имеет актуальные данные. В шаблоне на вкладке «Данные» находим кнопку «Проверка данных» и выбираем необходимый столбец с наименованиями листа «Примеры».

Обязательно на ракурсе «Сообщение об ошибке» убрать галочку «Выводить сообщение об ошибке», чтобы можно было редактировать наименование примера.

Результат

Мы получаем перечень материалов в выпадающем списке, который можно редактировать, но самое удобное – искать материалы можно в самой ячейке. А сам файл примеров теперь защищен паролем и редактирует его только один человек, который и вносит актуальные данные.

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

Суть рутины заключается в том, что необходимо каждый раз в Excel:

1. Удалять ненужные листы при импорте шаблона;

2. Сохранять шаблон в формате «.xlsx»;

3. Переводить введенные данные в формат «умной» таблицы;

4. Удалять комментарии, внесенные во время работы в шаблоне;

5. Проверять на корректность введенных данных (Лишние пробелы в наименовании элементов, проставлены ли обязательные условия для импорта: количество элементов, единица измерения, подкласс и др).

Решить данную проблему помог 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, намного легче.