Настройка прайса: группы товаров

1 Где в программе увидеть группы из прайса?
2 Как настраиваются группы?
3 Группы в прайсе сформированы стандартной группировкой Excel
4 Группы в прайсе указаны в виде 2-х колонок, в одной группа, во второй - вышестоящая группа.
5 Группа в прайсе указана в отдельной строке, без группировки средствами Excel
6 Группа в одной ячейке в виде полного пути, с разделителем
7 Определение группировки по форматированию
- 7.1 Макрос
8 Удаление всех групп из прайса

Где в программе увидеть группы из прайса?

Группы в прайсах нужны для удобного сопоставления товаров, например, чтобы можно было выбрать какую-то одну группу товаров и связывать ее + нужны для автоматического добавления товаров в свой прайс, по соответствию между вашими группами и группами поставщиков. Если ни одно ни второе вам не нужно, то не стоит тратить время на настройку групп из прайсов поставщиков, если там не какой-то простой вариант.

1. Перейти во вкладку Прайсы и выбрать слева прайс, справа над таблицей товаров должно быть установлено "1 выбранный"

2. В левом нижнем углу перейти во вкладку Группы

Как настраиваются группы?

Для настройки групп можно указать 2 колонки:

"Название группы" - это колонка, в которой находится название группы товара

"Название вышестоящей группы" - это колонка, в которой находится вышестоящая (родительская) группа.

Если и то и то в прайсе находится в одной колонке, то загрузить целиком структуру групп можно только если в прайсе они сгруппированы средствами эксель или с помощью макроса, который разрабатывается специально под каждый прайс за дополнительную плату (т.е. нет одинакового макроса для всех прайсов).

Товары Основного прайса (вашего каталога товаров, который загружается во вкладку Продукция) можно распределить по группам так же по какому-то ИД группы, который указан в прайсе. В этом случае прайс может быть без Названия группы.

Чтобы товары распределились, нужно заполнить в группах основного прайса поле Код группы, которое будет соответствовать этому ИД из прайса. Заполнить поле можно или вручную или через Импорт групп.

Колонка, в которой указан ИД/Код группы указывается так:

Код группы можно загружать так же, если в прайсе указаны и код группы и название группы. В этом случае можно указать загрузку групп по Коду и они не будут дублироваться, в случае, если в прайсе меняется название какой-то группы.

Для управления загрузкой групп в настройках прайса есть пункт 6. Группы.

Здесь выбирается по какому полю загружаются группы, возможные варианты:

"Не загружать группы" - из прайса не будут загружаться группы вообще, а товар попадет в группу "Без группы"

"Загружать по коду" - можно выбирать, если в прайсе присутствует код группы и поставщик его не меняет.

"Загружать по наименованию" - используется для большинства прайсов, в этом случае при загрузке группа будет проверяться по названию, есть такая в базе или еще нет.

Птичка "Группа в отдельной строке" используется для указания программе места расположения групп в прайсе.

Если установить птичку, то программа будет в прайсе искать строки в которых есть текст в колонке "Название группы" и нет текста в колонке "Цена" или "Рекомендованная цена", если в прайсе есть только РРЦ.

Если убрать птичку, то группы будет программа искать в каждой строке, а не в отдельных строках без цены, пример:

Т.е. в строках всех товаров указано название группы.

Птичка “Дерево” - устанавливается, если прайс сгруппирован стандартными средствами Excel.

Группы в прайсе сформированы стандартной группировкой Excel

Пример прайса:

1. Нужно указать поля "Название группы" и "Название вышестоящей группы".

Обычно в таких прайсах они в одной колонке, на скриншете выше это колонка B

2. Поставить птичку "Группа в отдельной строке"

3. Поставить птичку "Дерево"

4. Загружать прайс нужно начиная со строки, в которой идет первая группа

В результате вы получите группы 1 в 1 как они идут в прайсе

Группы в прайсе указаны в виде 2-х колонок, в одной группа, во второй - вышестоящая группа.

Пример прайса

в поле "Название группы" можно указать колонку С

в поле "Название вышестоящей группы" - колонку B или A, или, так как в этом прайсе 3 колонки, то можно название вышестоящей группы объединить из двух колонок A+B

Загрузить дерево групп из 3х уровней при такой структуре прайса нельзя, только группа и вышестоящая группа. Или делать специальный макрос, который из 3х колонок сделает две.

При настройке этого прайса нужно убрать так же птичку "Группа в отдельной строке", так как группы в этом прайсе указаны в каждой строке.

Группа в прайсе указана в отдельной строке, без группировки средствами Excel

пример прайса:

Как видно из примера - это такой же прайс как в первом пункте этой инструкции, только он не сгруппирован в экселе.

Из такого прайса можно загрузить только самую нижнюю группу. Вышестоящую группу не получится загрузить, так как нельзя определить к какому уровню группировки относится строка с группой.

Здесь указывается просто поле "Название группы" с колонкой B. Вот такие группы будут у товаров в программе:

Нужно так же установить птичку "Группа в отдельной строке".

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

Группа в одной ячейке в виде полного пути, с разделителем

Данный способ подойдет в случае, когда полный путь группы (группа и все вышестоящие группы) указаны в одной ячейке, через какой-то разделитель, для каждой строчки с товаром в прайсе.

Или если в прайсе есть больше 2х колонок групп, и с помощью макроса их можно объединить в одну (так же для каждой строки с товаром).

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

Dim idx_row, idx_group, j, k, idx_from, idx_to As Long
Dim group_full As String
Dim group_col As Integer

'индекс листа
SHEET_IDX = 1

'строка с которой начинается прайс
START_ROW = 13
CLM_LAST = "ZZ" 'последняя колонка в прайсе, буквами

CLMS_LIST = Array(1) 'список индексов колонок, в каком порядке их нужно объединять, индексы колонок вводятся через запятую
'CLMS_LIST = Array(3, 4, 5) 'пример если несколько колонок групп

CHR_DELIMITER = " >> " 'разделитель между группами

CLM_GROUP_IDX = 2 'номер колонки с группой (в нее помещается объединенная группа)
CLM_GROUP_L = "B" 'буква колонки с группой (соответствует CLM_GROUP_IDX, только буква)
CLM_GROUP_LEVEL = 20 'номер колонки с уровнем группировки
CLM_PRICE = 9 'колонка, в которой указана цена товара

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False

Set SHT = Sheets(SHEET_IDX)
SHT.Select
Cells.Select
Selection.ClearOutline
Selection.UnMerge

SHT.Cells(1, 1).Select

row_count = SHT.UsedRange.Rows.Count
prev_level = Array("", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "")

'формируем группы из нескольких колонок
For i = START_ROW To row_count
	group_text = ""
	If Sht.Cells(i, CLM_PRICE).Value <> "" Then
		For j = LBound(CLMS_LIST) To UBound(CLMS_LIST)
			clm_group = CLMS_LIST(j)
			
			If SHT.Cells(i, clm_group).Value <> "" Then
				If (group_text = "") Then
					group_text = Trim(SHT.Cells(i, clm_group).Value)
				Else
					group_text = group_text & CHR_DELIMITER & Trim(SHT.Cells(i, clm_group).Value)
				End If
			Else
			   Exit For
			End If
		Next j
				
		If group_text <> "" Then
			SHT.Cells(i, CLM_GROUP_IDX).Value = group_text
		Else
			SHT.Cells(i, CLM_GROUP_IDX).Value = "не распределено"
		End If
	Else
		Sht.Cells(i, CLM_GROUP_IDX).Value = ""
	End if
Next i


'сортировка по колонке с группой
Columns(CLM_GROUP_L & ":" & CLM_GROUP_L).Select
SHT.Sort.SortFields.Clear
SHT.Sort.SortFields.Add Key:=Range(CLM_GROUP_L & "1"), _
	SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With SHT.Sort
	.SetRange Range("A" & CStr(START_ROW) & ":" & CLM_LAST & CStr(row_count))
	.Header = xlNo
	.MatchCase = False
	.Orientation = xlTopToBottom
	.SortMethod = xlPinYin
	.Apply
End With


'Добавление строчек с группами
idx_row = START_ROW

Do While ActiveSheet.Cells(idx_row, CLM_GROUP_IDX).Value <> ""
	group_full = ActiveSheet.Cells(idx_row, CLM_GROUP_IDX).Value
	ActiveSheet.Cells(idx_row, CLM_GROUP_LEVEL).Value = ""
	
	'Разделитель
	group_part = Split(group_full, CHR_DELIMITER)
			
	For i = LBound(group_part) To UBound(group_part)
		If group_part(i) <> prev_level(i) Then
			Rows(CStr(idx_row) & ":" & CStr(idx_row)).Select
			Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
			
			ActiveSheet.Cells(idx_row, CLM_GROUP_LEVEL).Value = i + 1
			ActiveSheet.Cells(idx_row, CLM_GROUP_IDX).Value = group_part(i)
			prev_level(i) = group_part(i)
			
			idx_row = idx_row + 1
		End If
	Next i
	
	idx_row = idx_row + 1
Loop

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

Перед копированием макроса из инструкции переключите раскладку клавиатуры на русский язык

Макрос добавляется в карточке прайса во вкладке “Макрос”

Нужно указать значения настроек для вашего прайса. В комментариях рядом со значением указано, что туда нужно вводить.

Пример прайса (группы в каждой строке в первых трех колонках):

Пример настройки (результат помещается в колонку А):

Если прайс большой, то макрос может выполняться долго

После выполнения макроса в файле будет такой результат:

Группы вынесены в отдельные строки, а в 20-ой колонке указан уровень, на котором находится группа.

Для его загрузки указываются следующие настройки:

В Название группы и Название вышестоящей группы указывается колонка А, т.е. та колонка, в которую записывался результат макроса (CLM_GROUP_IDX )

Колонка 20 (CLM_GROUP_LEVEL из макроса) указывается как Код вышестоящей группы.

Устанавливаются птички Группа в отдельной строке и Дерево. Загрузка группы по наименованию.

В строку, с которой начинается загрузка прайса, указывается строка, в которой название первой группы.

Определение группировки по форматированию

Часто в прайсах указывают группы, которые не сгруппированы средствами Excel, но текст в каждом уровне имеет разное форматирование (разный шрифт, или цвет шрифта, или цвет ячейки, или разные отступы от начала ячейки). В этом случае группы можно загрузить, но для этого нужно составить макрос:

Макрос

###DECLARE###  
    Public Type GroupLevel
        ColIndex As Integer 'номер колонки, в которой указано название группы
        InterColor As Long 'цвет ячейки
        IsBold As Integer '1 - жирный 0 - нет
        IsItalic As Integer '1 - курсив 0 - нет
        FontName As String 'название шрифта
        FontColor As Long 'цвет шрифта
        FontSize As Long 'размер шрифта
        Indent As Integer 'отступ
		IsUnderline As Integer 'подчеркнут
    End Type  
###DECLARE###      
    'Укажите после "To" количество уровней группировки,
    '3 (три) означет что у группы есть подгруппы и у подгруппы есть еще вложенные подгруппы
    Const lvlCount = 3
	
    Dim i, j, clmnPriceIndex, clmnOutIndex, rowFirstData As Integer
    
    'Укажите номер колонки, в которой указана цена товара
    clmnPriceIndex = 0
    
    'Укажите номер колонки, в которую будет помещен результат
    clmnOutIndex = 0
    
    'Укажите номер первой строки с данными
    rowFirstData = 0
    
    Dim GroupLevels(1 To lvlCount) As GroupLevel

    'Укажите настройки для каждого уровня, пример:
GroupLevels(1).ColIndex = 2
GroupLevels(1).InterColor = 11842740
GroupLevels(1).IsBold = 1
GroupLevels(1).IsItalic = 1
GroupLevels(1).IsUnderline = -4142
GroupLevels(1).FontName = "Arial"
GroupLevels(1).FontColor = 0
GroupLevels(1).FontSize = 9
GroupLevels(1).Indent = 0

GroupLevels(2).ColIndex = 2
GroupLevels(2).InterColor = 12829635
GroupLevels(2).IsBold = 1
GroupLevels(2).IsItalic = 1
GroupLevels(2).IsUnderline = -4142
GroupLevels(2).FontName = "Arial"
GroupLevels(2).FontColor = 0
GroupLevels(2).FontSize = 9
GroupLevels(2).Indent = 0
    

    
    For i = rowFirstData To ActiveSheet.UsedRange.Rows.Count
        If ActiveSheet.Cells(i, clmnPriceIndex).Value = "" Then
            For j = 1 To lvlCount
                Set cel = ActiveSheet.Cells(i, GroupLevels(j).ColIndex)
                
                If (cel.Interior.Color = GroupLevels(j).InterColor) And _
                    ((-1) * CInt(cel.Font.Bold) = GroupLevels(j).IsBold) And _
                    ((-1) * CInt(cel.Font.Italic) = GroupLevels(j).IsItalic) And _
                    (cel.Font.Name = GroupLevels(j).FontName) And _
                    (cel.Font.Color = GroupLevels(j).FontColor) And _
                    (cel.Font.Size = GroupLevels(j).FontSize) And _                    
                    (cel.IndentLevel = GroupLevels(j).Indent) Then
                        
                       ActiveSheet.Cells(i, clmnOutIndex).Value = j
                       Exit For
                End If
            Next j
        End If
    Next i

Вам так же понадобится этот файл, для определения форматирования в ячейках, скачайте его себе:

При открытии этого файла нужно разрешить ему работу с макросами

Что нужно делать:

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

Установите в нем синтаксис Visual Basic

В макросе укажите:

Количество уровней групп, которые собираетесь загружать из прайса

Номер колонки в которой цена товара

Номер колонки, в которую будет помещен код уровня группировки:

Здесь нужно указать какую-то пустую колонку, после всех колонок прайса. Указывайте с запасом, на случай, если поставщик добавит в прайс колонки, чтобы не переделывать макрос. Обычно указывается 20-ая колонка, если в прайсе колонок меньше.

Укажите номер строки, с которой начинается прайс (первая строка после шапки)

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

2. Откройте файл "Проверка формата ячейки"

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

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

после этого скопировать

и вставить в файл с макросом проверки, ввести номер уровня группы и нажать "Проверить"

в результате получим код, который нужно скопировать в макрос для прайса:

Копируем код вместо того, который был в макросе:

Аналогично делаем для второго и третьего уровней.

Для каждого уровня указываем номер колонки, в которой указано название группы этого уровня (в примере это колонка B, указывается номер 2)

Вот здесь должен быть указан уровень, разный в каждом блоке

Полученный результат копируется в настройку прайса

3. В настройке прайса указываются колонки "Название группы" и "Название выш. гр." - в моем примере одна и та же колонка B, в которой находятся группы в прайсе.

Указываем так же поле "Код вышестоящей группы" - это та колонка, в которую в макросе указали помещать номер уровня группы (20-ая колонка)

Устанавливаем птичку "Дерево"

Загружать группу по наименованию + птичка Группа в отдельной строке

Сохраняем настройки и пробуем загрузить. В моем примере получились такие настройки:

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

Удаление всех групп из прайса

Если что-то загрузилось с группами не так, можно удалить только группы прайса, при этом оставить товар, чтобы не пропали привязки. Для этого во вкладке Прайсы выберите слева нужный прайс и нажмите эту кнопку: