Страницы: 1
RSS
Слияние групп столбцов, Слияние нескольких "групп" столбцов с однородными столбцами, макросом или Power Query
 
Добрый день!

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

Нужно:
а) Слить каждую таблицу с листов типа "Регион N" в единую (столбцы везде одинаковые).

б) Преобразовать данные по шести столбцам  (которые содержат однородные данные, просто разделенные по типу  конкурента, т.е. всего 5 групп по 6 столбцов) с дублированием полей  "Регион","Код товара", "Конкурент" в вид как на листе "Шаблон".
*Поле "Комментарий+ссылка" содержит текстовые значения, рассчитанные формулой.

Листы для преобразования: все, кроме листа "Шаблон"
Итоговый вид: лист "Шаблон"

Суть  в том, что, например, поля "Цена 1", "Цена 2" и т.д. содержат соответствующую информацию по 5 конкурентам. Каждую "группу" нужно развернуть с  дублированием региона, кода товара и конкурента.

Пробовал через Power Query,  дальше пункта а) не получается, т.к. только начал работу с данной  надстройкой и не до конца понимаю функционал.
Если через Power Query нельзя это сделать, то помогите с макросом.

Заранее спасибо!

P,S, Если криво объяснил, то в приложении рисунок с порядком преобразования столбцов.
Изменено: Ragnar64 - 08.02.2019 17:28:41 (Ошибка в файле примере)
 
Цитата
Ragnar64 написал:уже всю голову сломал
Покажите ваши наработки, что не получается? Если макрос нужен с нуля, то вам в ветку Работа.
"Все гениальное просто, а все простое гениально!!!"
 
Nordheim,
Приложил тот же файл с последним листом. Пока что максимум, к чему я пришел.
1) Объединяю все нужные листы.
2) Отменяю свертывание для столбцов Конкурент1-5.
3) Отменяю свертывание всех столбцов, кроме "Код товара", "Регион" и "Конкурент"
4) В столбце Атрибут убираю все цифры и привожу значения к одному виду. Удаляю дубликаты.
5) Провожу сведение столбца "Атрибут" с выводом столбца "Значение" без агрегирования (чтобы текст с ссылкой вывел).

В итоге, во-первых, в столбцах "Комментарий+ссылка" и "Отсутствует" вылезают ERROR, а во-вторых, количество строчек меньше, чем должно быть (может быть, я ошибаюсь).
Изменено: Ragnar64 - 08.02.2019 15:37:55
 
Ragnar64, сведение столбцов сработало бы (и имело бы смысл), только если для каждой связки Код товара + Регион было бы только одно уникальное значение Конкурента, цены и т.д. Очевидно, это не Ваш случай. Т.е. нужно понять, как именно Вы хотите агрегировать данные. Например, неясно, из какой логики на листе Шаблон отсутствует Цена = 1899 по Коду товара 1001117174 и Региону 1.
 
Aleksei_Zhigulin,  про отсутствие цены: видимо, я ошибся когда вручную копировал в шаблон с листов. Файл в первом письме перезалил с исправлением.

Я хотел агрегировать сначала отдельные столбцы, которых 5*6 таким образом, чтобы остался только один блок из 6 столбцов с шапкой как на листе "Шаблон" с повторением Кодов товара, Региона. То есть для каждой связки Регион-Код товара будет существовать по единственной связвке из столбцов Конкурент, Цена, Отсутствует, Аналог, Акция, Комментарий+Ссылка.

Это равносильно, если вручную копировать N раз (в данном случае 5, так как всего 5 блоков по 6 столбцов) каждого конкурента ("Конкурент(N)"->"Комментарий+Ссылка(N)), затем так же вручную дублировать Регион и Код товара N раз (я попытался изобразить это на рисунке).
Изменено: Ragnar64 - 08.02.2019 19:12:13
 
Понял, как решить задачу "в лоб", так как задача, по моему мнению, сводится к объединению таблиц внутри листа: дописать перед каждой группой столбцов коды товаров и регион, сформировать по 5 таблиц на каждом листе (умные таблицы) и "слить" в Power Query. В моем случае максимум 7 листов (35 мини-таблиц), поэтому один раз можно проделать и потом объединить запросы по каждому региону.

Если подскажете, как выполнить "изящнее", то буду крайне благодарен!
 
Ragnar64, примерно так:
Код
let
    Source = Excel.CurrentWorkbook(),
    filter = Table.SelectRows(Source, each List.Contains({"Рег1","Рег2"},[Name])),
    expand = Table.ExpandTableColumn(filter, "Content", Table.ColumnNames(filter{0}[Content])),
    unpivot = Table.UnpivotOtherColumns(expand, {"Код товара", "Name"}, "Поле", "Значение"),
    num = Table.AddColumn(unpivot, "num", each Text.End([Поле], 1)),
    transform = Table.TransformColumns(num, {"Поле", each Text.Trim(Text.Select(_,{"a".."я"," ", "+"}))}),
    pivot = Table.Pivot(transform, List.Distinct(transform[Поле]), "Поле", "Значение"),
    rename = Table.RenameColumns(pivot,{{"Name", "Регион"}}),
    remove = Table.RemoveColumns(rename,{"num", "Ссылка", "Комментарий"}),
    reorder = Table.ReorderColumns(remove,{"Регион", "Код товара", "Конкурент", "Цена", "Отсутствует", "Аналог", "Комментарий + Ссылка"})
in
    reorder

В исходных данных столбец "Акция" у Вас не заполнен, поэтому его нет в финальной таблице. Т.е. когда данные появятся, его просто нужно будет добавить на последнем шаге в список столбцов.
Страницы: 1
Наверх