Макрос обработки прайсов из 1С

Программа обработки прайсов успешно справляется с обработкой прайс-листов, выгруженных из 1С, — благодаря привязке к форматам ячеек, можно настроить извлечение данных в разные столбцы в зависимости от уровня группировки строк, форматирования и цвета заливки ячеек, и т.д.

Но, изредка, из 1С выгружаются прайсы с категориями и товарами разной степени вложенности, причем может получиться так, что никак не задать условие, по которому программа сможет отличить строку с наименованием товара от строки с опцией. (под опцией подразумевается строка, в которой написан цвет или размер, без наименования товара)

В этой инструкции мы рассмотрим как раз такой вариант прайс-листа, настройка программы под который сопровождается следующими проблемами:

  1. в прайсе содержатся как товары с опциями (есть доп. строки под названием товара), так и товары без опций (есть только название товара)
  2. названия товаров и названия опций находятся на разных уровнях группировки строк (уровни 4, 5 и 6), т.е. к уровню группировки строк никак не привязаться
  3. заливка ячеек и отступы (количество пробелов) так же не позволяют нам отличить строку товара от строки опции
  4. и последний шанс — отличить строки по содержимому — тоже в данном случае отсутствует: и то и другое может начинаться с цифр, содержать скобки, и т.д.

Таким образом, мы не можем обработать этот прайс-лист так, чтобы в один столбец вывелись названия товаров, а в соседний столбец — цены (не говоря уж о выводе доступных цветов / размеров):

 

Наша задача — получить пригодный для обработки прайс, где в отдельных столбцах будет наименование, артикул, опция (цвет / размер), цена, а также подкатегория последнего уровня. 

Промежуточная задача — придумать алгоритм, позволяющий отличить одни строки от других, и написать макрос для преобразования таблицы в вид, подходящий для обработки программой Unification.

 

Сформулировать критерии отличия строк товаров от строк с опциями и прочих строк (подзаголовков) можно так:

  • Анализируем уровень группировки строки (число от 1 до 9), и выделение полужирным для столбца с наименованием.
  • Если строка выделена полужирным — считаем её подзаголовком, и не обрабатываем.
  • Для каждой из остальных строк смотрим её уровень группировки, и уровень группировки строки ниже неё.
    Если в строке ниже уровень группировки на 1 больше (вложенная строка), то запоминаем название товара.

 

Макрос в этом случае будет непростой, но код достаточно универсальный, и вы легко сможете адаптировать этот макрос под свой прайс-лист (достаточно поменять номера столбцов в первых 4 строках макроса):

Sub Price1C()
    On Error Resume Next
    Const EMPTY_COLUMN& = 8 ' указываем здесь номер пустого столбца
    Const NAME_COLUMN& = 2 ' указываем здесь номер столбца с НАЗВАНИЯМИ товаров
    Const ART_COLUMN& = 3 ' указываем здесь номер столбца с АРТИКУЛОМ
    Const PRICE_COLUMN& = 4 ' указываем здесь номер столбца с ЦЕНОЙ товара
    
    ' © 2019 ExcelVBA.ru
    ' excelvba.ru/programmes/Unification/manuals/config/macro/price_1C
    Dim ra As Range, cell As Range, arr, i&, res, txt$, TYPE_COLUMN&, HasChildren As Boolean
    Dim section$, art$, title$, title_level&, current_value$
    Set ra = Range(Cells(2, NAME_COLUMN&), Cells(Rows.Count, NAME_COLUMN&).End(xlUp))
    arr = ra.Resize(, 2).Value ' массив размером (кол-во строк ; 2)
    TYPE_COLUMN& = EMPTY_COLUMN& + 1
 
    Application.ScreenUpdating = False
    For Each cell In ra.Cells
        i& = i& + 1
        arr(i&, 1) = cell.EntireRow.OutlineLevel
        arr(i&, 2) = IIf(cell.Font.Bold, "заголовок", "данные")
        cell.EntireRow.Cells(ART_COLUMN&) = "'" & cell.EntireRow.Cells(ART_COLUMN&).Text
    Next cell
    ' выводим массив с уровнями группировки строк в пустой столбец
    Cells(2, EMPTY_COLUMN&).Resize(UBound(arr), 2).Value = arr
 
    ' выводим заголовки дополнительных столбцов
    With Cells(1, EMPTY_COLUMN&).Resize(, 8)
        .Value = Array("Группировка", "Тип строки", "Тип данных", "Артикул", "Название", "Опция", "Цена", "Подраздел")
        .Font.Bold = True: .Interior.Color = vbGreen
    End With
 
    ' считываем данные в массив для быстрой обработки
    arr = Range(Range("a2"), Cells(Rows.Count, NAME_COLUMN&).End(xlUp).Offset(1)).Resize(, EMPTY_COLUMN& + 1).Value
    ReDim res(1 To UBound(arr), 1 To 6)  ' массив для результатов
    
    For i = LBound(arr) To UBound(arr) - 1 ' перебираем все строки прайс-листа
        
        If (arr(i, TYPE_COLUMN&) = "данные") And (Trim(arr(i, NAME_COLUMN&)) <> "") Then
 
            ' проверяем, есть ли вложенные опции (уровень группировки больше на 1)
            HasChildren = Val(arr(i + 1, EMPTY_COLUMN&)) = Val(arr(i, EMPTY_COLUMN&)) + 1
 
            If HasChildren And (arr(i + 1, TYPE_COLUMN&) = "данные") Then
                ' только запоминаем название товара
                title$ = Application.Trim(arr(i, NAME_COLUMN&))
                title_level& = Val(arr(i, EMPTY_COLUMN&))
            Else
                ' выводим данные по товару в доп столбцы
                art$ = Application.Trim(arr(i, ART_COLUMN&))
                current_value$ = Application.Trim(arr(i, NAME_COLUMN&))
                res(i, 2) = "'" & art$
                res(i, 5) = arr(i, PRICE_COLUMN&)
                res(i, 6) = section$
 
                If Val(arr(i, EMPTY_COLUMN&)) = title_level& + 1 Then
                    ' строка с опцией товара
                    res(i, 1) = "опция"
                    res(i, 3) = title$
                    If res(i, 3) Like art$ & " *" Then res(i, 3) = Mid(res(i, 3), Len(art$) + 2)
                    res(i, 4) = current_value$
 
                Else
                    ' строка с товаром без опций
                    res(i, 1) = "товар"
                    res(i, 3) = current_value$
                    If res(i, 3) Like art$ & " *" Then res(i, 3) = Mid(res(i, 3), Len(art$) + 2)
                    res(i, 4) = ""
                    title$ = "": title_level& = 0
                End If
            End If
 
        Else ' строка заголовка
            section$ = Application.Trim(arr(i, NAME_COLUMN&))
            title$ = "": title_level& = 0
        End If
 
    Next i
 
    ' выводим массив с результатами в пустые столбцы справа
    Cells(2, EMPTY_COLUMN& + 2).Resize(UBound(res), 6).Value = res
 
    With Cells(1, EMPTY_COLUMN&).Resize(, 8)
        .EntireColumn.AutoFit:     .WrapText = True
        .Resize(, 4).EntireColumn.HorizontalAlignment = xlCenter
    End With
    Application.ScreenUpdating = True
End Sub

Добавим этот макрос в файл macro.xla, и для начала протестируем работу макроса, открыв в Excel исходный прайс-лист, и нажав кнопку F5 в редакторе макросов для запуска нашего макроса.

Во вложении к статье вы найдете файл Excel с образцом прайса из этого примера, и в этом же файле присутствует макрос, описанный в этой статье.

Если вы всё сделали правильно, то в прайс-лист справа будут добавлены несколько столбцов:

Как мы видим, макрос отработал как надо, и теперь этот прайс-лист может быть обработан надстройкой Unification
(в обработчике прайса мы будем указывать в качестве источника данных столбцы с 11 по 15, игнорируя данные в исходных столбцах)

Осталось указать название макроса Price1C в настройках обработчика прайса на подвкладке Макросы, и теперь этот макрос будет автоматически запускаться программой перед обработкой прайсов, выгруженных из программы 1С.

ВложениеРазмерЗагрузкиПоследняя загрузка
price_1C.xlsb23.32 КБ0Ещё не загружался