Джемма Попржедзинская (Мацакова)Всем привет!
Иногда технологам приходится быть не только проектировщиком и знать санитарные правила, нормы проектирования, но и познавать запутанные дебри формул в Excel, дабы упростить себе жизнь при очередных требованиях к оформлению документов в экспертизе.
Итак, делюсь своей историей и, надеюсь, нужной информацией.
При прохождении экспертизы столкнулась с требованием эксперта прописать в ВОРах (Ведомости объемов работ) номер листа, на котором расположена строка с позицией оборудования в спецификации. При этом это должно выглядеть в следующей связке:
00002233-ИОС6.3, лист_1;поз.Г2
Где,
00002233-ИОС6.3 – номер тома со спецификацией сводной;
Лист 1 – номер листа спецификации, на которой находится данная позиция
Поз.Г2 – номер позиции в спецификации.
Чтобы собрать такую текстовую конструкцию не вручную, пришлось хорошенько помудрить с формулами в EXCEL.
Кратко схема выглядит так:
- Размещаем нашу спецификацию в шаблоне excel. (Шаблоном с нами любезно поделился Kirill Danilushkin. Спасибо!)
- Прописываем формулу, чтобы возле каждой строчки проставлялся номер листа, на котором она расположена.
- Формируем общую фразу, которая должна быть в ВОР.
Теперь подробно по каждому процессу:
- Размещаем спецификацию во вкладке «СВОДНАЯ», далее ваши данные автоматически проставляются в ячейки на оформленные листы по ГОСТу во вкладке «Спецификация».
- Теперь формула, чтобы проставлялись номера листов возле каждой строчки.
В Excel для того чтобы проставить номер листа на котором расположена строка, можно использовать пользовательскую функцию на языке VBA (Visual Basic for Applications), так как стандартными формулами Excel это сделать невозможно.
Как это можно сделать:
1. Нажмите Alt + F11, чтобы открыть редактор VBA.
2. В меню выберите Insert > Module, чтобы вставить новый модуль.
3. Вставьте следующий код в окно модуля:
Function GetPageNumber(rng As Range) As Long
Dim ws As Worksheet
Dim pageNum As Long
Dim rngCell As Range
Dim i As Long, j As Long
Set ws = rng.Worksheet
pageNum = 1
Application.ScreenUpdating = False
‘ Iterate through each cell in the specified range
For Each rngCell In ws.UsedRange
i = rngCell.Row
j = rngCell.Column
‘ Check if the cell is on a new page
If ws.HPageBreaks.Count > 0 Then
For Each brk In ws.HPageBreaks
If brk.Location.Row <= i Then
pageNum = pageNum + 1
End If
Next brk
End If
If ws.VPageBreaks.Count > 0 Then
For Each brk In ws.VPageBreaks
If brk.Location.Column <= j Then
pageNum = pageNum + 1
End If
Next brk
End If
‘ If the cell matches the target range, return the page number
If rngCell.Address = rng.Address Then
GetPageNumber = pageNum
Exit Function
End If
pageNum = 1
Next rngCell
Application.ScreenUpdating = True
End Function
4. Закройте редактор VBA и вернитесь в Excel.
Теперь вы можете использовать эту функцию в ваших ячейках.
В ячейке, где вы хотите увидеть номер страницы, введите формулу:
=PageNumber(С5)
Здесь C5 — это ячейка, для которой вы хотите определить номер страницы.
Обратите внимание, что макрос может работать медленно на больших таблицах, так как он проверяет каждую ячейку в используемом диапазоне.
В приложенном файле номера страниц проставлены во вкладке «Спецификация» в столбце V
Номер страницы получили, осталось собрать общую фразу. Для этого воспользуемся формулой
«=СЦЕПИТЬ»
Для начала готовим окончание фразы (хотя последовательность можете установить сами).
Берем код позиции и добавляем приписку «поз.»
=СЦЕПИТЬ($J$1; A4)
Где – $J$1 – это ячейка, в которой у вас прописана сама фраза «поз.», а А4 – это сам код позиции.
Знак $ не забываем, чтобы ячейка автоматически не «съезжала» ниже.
Далее к номеру листа добавляем разграничитель «;», используя такую же формулу. Данные про номер листа мы получили ранее. Проще скопировать все на отдельную вкладку с сохранением только значений.
Формула:
=СЦЕПИТЬ(H4; “;”)
Где H4 – ячейка, в которой расположен номер страницы.
И финал: собираю финальную фразу в 2 захода:
В одной ячейке пишем текстом номер тома на который ссылаемся в ВОР (это тот том, где ваша спецификация), далее сразу же вручную дописываем «лист_». Нижнее подчеркивание обязательно, т.к. следующая функция будет по этому знаку искать место, куда ему надо добавить текст. Если у вас в номере тома используется уже нижнее подчеркивание, выберете любое другое обозначение: тире, двоеточие и пр). Экспертизе это не так важно.
См. приложенный скрин что с чем объединяю.
Формула для сбора ниже:
=ЛЕВСИМВ(F4; НАЙТИ(“_”; F4)) & I4 & ПСТР(F4; НАЙТИ(“_”; F4) + 1; ДЛСТР(F4) – НАЙТИ(“_”; F4))
И второй этап «сборки»
На этом все. Далее оформляем наш ВОР (мне было проще скопировать все в WORD).
Наверное, если у вас до 10 страниц спеки – такое проще сделать вручную, но если более 1000 страниц, как было в моем случае – делать такое возможно только автоматически.
Важное дополнение: все формулы, указанные здесь – для русскоязычной версии Excel. если у вас Англоязычная\Валирийская\Дотракийская\Клингонская или любая другая версия Excel – важно менять функции на аналогичные.
Не претендую на истину, это лишь то решение, до которого дошла сама, в попытках быстро сделать то, что просят эксперты. Если вы знаете способ проще – давайте делиться наработками?
Я со своей стороны выкладываю тот самый файл со всеми вписанными формулами. Пользуйтесь и оптимизируйте свое рабочее время. Если с чем-то не разберетесь – пишите, я постараюсь помочь.
