Страницы: 1 2 След.
RSS
Преобразование многоуровневой таблицы из1С в плоскую
 
Добрый день!
Перерыл весь интернет, в том числе и ваш ресурс,пересмотрел кучу видео, но не нашел решения своей проблемы.
Суть в следующем:
Есть большое количество данных(помесячно за 5 лет) выгруженных с 1 С в форме оборотно-сальдовой ведомости по счету (пример в приложении).
Доступа к 1 с для выгрузки сразу в плоский вид нет.
Необходимо преобразовать все данные в плоский вид для дальнейшего анализа. Пробовал использовать PQ, PLEX, Multex и другие надстройки результат нулевой.
Неужели только VBA? Основная проблема в расположении данных количество/сумма по двум столбцам. Желаемый результат также в файле во вложении.
Посоветуйте пожалуйста как быть. Может быть есть готовое решение?

С Уважением, Андрей
 
может это видео поможет Как обработать в Power Query таблицу с многоуровневой шапкой.
 
Спасибо но не поможет,там структура таблицы другая, у меня основная проблема перенести сумму/количество из строк в два столбца
 
И где у Вас в исходном файле даты?
 
1. Период брать из названия листа?
2. В одном файле всегда будет только один лист с одним периодом, или их может быть несколько?
Вот горшок пустой, он предмет простой...
 
Цитата
Sertg написал:
И где у Вас в исходном файле даты?
Это оборотка за один месяц. Даты я при консолидации всех листов в PQ по названию листов получу
 
volandse, если ожидаете помощи в решении, почему не отвечаете на вопросы из #5 ?
И в примере зачем данные за февраль,  если этих данных в исходнике нет?
Заполните побольше ожидаемый результат. Добавьте для АТЦ, АТЦ Сидорович. Вам надо только по бензину?
 
Хорошо,детализирую вопрос. Исходник у меня состоит из 12 месяцев 2019г, разбитых по листам с названием месяцев, плоская таблица с желаемым результатом должна получится за весь год, откуда брать период я уже написал- из названия листа (с этим я разберусь). Основная проблема решение которой я спрашиваю это если возможность с помощью PQ или другой надстройки транспонировать таблицу, пусть даже за один месяц, в виде плоской указанной на листе результат в примере. Или только макросом получится.

Данные мне нужны по всей таблицы в исходнике в виде период-склад-номенклатура-счет учета(из столбцов)-количество-сумма, по всем складам и номенклатуре
 
Можно глянуть это видео Как подружить отчёты из 1С и Excel
 
Спасибо)
Полезно и интересно, но при моем объеме данных для преобразования слишком долго(((
 
volandse, На минуте 44:55 идет с Power Query.
 
volandse, может, и вправду, ссылками вам помогать :)
посмотрите здесь. Может, что полезное увидите
 
Усё))) Добил эту табличку с помощью PQ!
Всех благодарю за желание помочь!
Решение было в видео ссылку на которое дал Sertg хотя там структура исходника другая но  на 57 минуте все стало ясно, так что ему особенное спасибо)
Тему можно закрывать
 
Цитата
volandse написал:
Усё))) Добил эту табличку с помощью PQ!
Нам решение покажете?
 
А как показать?
Если только преобразование годовой исходной в плоскую скринами заснять.
А так основная проблема, которая у меня была, по переносу количества/сумм из строк в столбцы решилась операцией "столбец сведения" в PQ
В принципе это видно в видео сылку на которое дал Sertg  
 
volandse, если не секрет, по какому принципу вы разбираете структуру иерархии?
Вот горшок пустой, он предмет простой...
 
Вы имеете ввиду то что у меня номенклатура и склады с отступами? Если в этом вопрос то в PQ пока с этим не разобрался, в видео от  Sertg есть фрагмент об этом, а я надстройкой Ёxcell воспользовался.  
 
Цитата
PooHkrd написал:
по какому принципу вы разбираете структуру иерархии
По отступу в ячейке. Добавляем столбец с обращением к функции (или то же макросом):

Код
Function Indent(ByVal r As Range)
  Indent = r.IndentLevel
End Function
Изменено: sokol92 - 08.04.2020 15:30:26
Владимир
 
Цитата
volandse написал:
А как показать?
В шапке есть пример с желаемым результатом. От вас достаточно запроса, который исходную таблицу преобразует в желаемый результат

Дело в том что название темы очень актуальное и, я уверен, будет популярно в поиске. И что найдут в теме? Проблему с примером и отсутствием решения.
Опять пустая тема!
Изменено: Михаил Л - 08.04.2020 16:23:19
 
Михаил Л, чуть погодя будет тут решение и с разбором иерархии и с разворотом строк по столбцам. Щас просто руки не доходят, но сегодня сделаю.
Вот горшок пустой, он предмет простой...
 
volandse,Можно выложить код из расширенного редактора.И файл Екселя в котором делали табличку.
 
Цитата
PooHkrd написал:
погодя будет тут решение
Это хорошо :)  
 
Народ, вы не обессудьте я не спец по Excell, я только учусь))) Я этой темой  только дня 4 как озадачился.
Просто у меня весь процесс преобразования исходника не полностью в PQ, а часть преобразований я делал через надстройки, в VBA я вообще ноль.
Для себя я часть проблемы решил с  вашей помощью, за что вам большой  респект.  Дальше буду углубляться в тему.
А если есть спецы которые опишут решение полностью буду только рад.
 
Цитата
volandse написал:
опишут решение полностью буду только рад
Цитата
Михаил Л написал:
И в примере зачем данные за февраль,  если этих данных в исходнике нет? Заполните побольше ожидаемый результат. Добавьте для АТЦ, АТЦ Сидорович. Вам надо только по бензину?
volandse, узнать бы точнее ожидаемый результат
 
Цитата
volandse написал:
буду только рад.
Ну, радуйтесь.
Только есть пара нюансов:
1. В разворачиваемом столбце не 2 уникальных значения, а 4, соответственно на выходе получается не 2, а 4 столбца
2. Есть в вашем примере номенклатура "Масло трансмиссионное", подозреваю что у вас в БД есть 2 таких номенклатуры, но с таким вот одинаковым наименованием ибо в сальдовке она делится на 2 строки для одного и того же склада АТЦ. В связи с этим предупреждаю что если последним шагом разворачивать столбец без агрегирования, то эти позиции выдадут ошибку, а если с агрегированием то ошибки не будет, но по сути данные по двум разным позициям соберутся в одну.
А так, принцип разбора вашего файла во вложении. Пути к папкам и конкретному файлу задается в параметрах запроса. Если путь к файлу оставить пустым, то в обработку попадут все файлы в папке.
З.Ы. Как всегда спасибо Максиму Зеленскому за предоставленную функцию разбора иерархии через группировки.
Вот горшок пустой, он предмет простой...
 
PooHkrd, спасибо за способ
 
а я тут все рекурсиями балуюсь
Функция Максима Зеленского у меня лежит в J:\PQ\ExcelWorksheetsRowOutlines.pq
Код
let
    RowsOutline     = Expression.Evaluate(Text.FromBinary(File.Contents("J:\PQ\ExcelWorksheetsRowOutlines.pq")),#shared),
    FullPath        = Excel.CurrentWorkbook(){[Name="filename"]}[Content]{0}[Column1],
    DataWithOutline = RowsOutline(FullPath,"Январ2019",true)[DataWithOutline]{0},
    Headers         = List.Buffer(List.FirstN(DataWithOutline[Column1],3)),
    Promoted        = Table.PromoteHeaders(DataWithOutline, [PromoteAllScalars=true]),
    SkipEmpty       = Table.Skip(Promoted,(_)=>[Показатели]=null),
    ReplaceNull     = Table.ReplaceValue(SkipEmpty,null,0,Replacer.ReplaceValue,{"Column1"}),
    FillDown        = Table.FillDown(ReplaceNull,{"Счет"}),
    Col             = List.Buffer(List.Skip(Table.ColumnNames(ReplaceNull),4)),
    Pos             = List.Buffer(List.PositionOf(Col,{"сальдо","Оборот"},3,(a,b)=>List.AnyTrue(List.Transform(b,each Text.Contains(a,_))))),
    Col2            = List.Buffer(List.Transform(Pos,each Col{_})),
    Col3            = List.Buffer(List.Transform(List.Zip({Pos,List.Skip(Pos)&{List.Count(Col)}}),each if _{1}-_{0}>1 then List.Transform({_{0}+1.._{1}-1},each Col{_}) else Col{_{1}-1})),
    Structure       = List.Zip({Col2,Col3}),
    AddRec          = Table.RemoveColumns(Table.AddColumn(FillDown, "rec", each let rec =_ in Record.FromList(List.Transform(Col3,each if Value.Is(_, type list) then Record.SelectFields(rec,_) else Record.Field(rec,_)),Col2)),Col),
    Group           = Table.Group(Table.AddIndexColumn(AddRec, "i", 0, 1), {"Column1", "Счет","i"}, {{"_", each _}},0,(a,b)=>if a[Column1]=b[Column1] and a[Счет]=b[Счет] then 0 else 1),
    Indexed         = Table.Group(Table.ExpandTableColumn(Table.RemoveColumns(Table.AddIndexColumn(Table.Sort(Group,"i"),"Индекс"),"i"), "_", {"Показатели","rec"}), {"Column1", "Счет", "Показатели", "Индекс"}, {{"rec", each [rec]}}),
    Pivot           = Table.Pivot(Table.Sort(Indexed,{{"Индекс", Order.Ascending}}), List.Distinct(Indexed[Показатели]), "Показатели", "rec"),
    Sorted          = Table.Sort(Pivot,{{"Индекс", Order.Ascending}}),
    PivotedCol      = List.Skip(Table.ColumnNames(Sorted),3),
    GroupRecursive  = (optional t,optional it)=> 
        let 
            t       = if t = null then Sorted else t,
            it      = if it = null then 0 else it, 
            header  = Headers{it},
            Grouped = Table.Group(t, {"Column1"}, 
                {
                    {header, each Record.ToList(_{0}){1}},
                    {"_",    each let 
                                tbl=Table.Skip(Table.Sort(_,"Индекс"),each [Column1]<=it) 
                            in if it<List.Count(Headers)-2 
                               then @GroupRecursive(tbl,it+1) 
                               else Table.RenameColumns(tbl,{Headers{0},List.Last(Headers)})}
                },
                0,(a,b)=>if b[Column1]>a[Column1] then 0 else 1
            )
        in
            Table.ExpandTableColumn(Grouped,"_",List.Skip(Headers,it+1)&PivotedCol),
    Merge          = Table.CombineColumns(GroupRecursive(),{"Сумма", "Кол-во", "БУ", "Кол."},(a)=>Table.SelectRows(#table({"a","b"},List.Zip({PivotedCol,a})),each [b]<>null),"a"),
    ExpandRec      = Table.ExpandTableColumn(Table.ExpandTableColumn(Merge, "a", {"a", "b"}, {"Показатели", "b"}), "b", Col2),
    FullExpad      = List.Accumulate(List.Select(Structure,each Value.Is(_{1},type list)),ExpandRec,(a,b)=>Table.ExpandRecordColumn(a,b{0},b{1},List.Transform(b{1},each b{0}&"."&Text.Split(_,"_"){0}))),
    Unpivot        = Table.UnpivotOtherColumns(Table.RemoveColumns(FullExpad,"Column1"), {"Показатели"}&Headers, "Атрибут", "Значение"),
    Result         = Table.SplitColumn(Unpivot, "Атрибут", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Атрибут.1", "Атрибут.2"})
in
    Result
Изменено: Андрей Лящук - 09.04.2020 05:20:59
 
Цитата
PooHkrd написал:
В связи с этим предупреждаю что если последним шагом разворачивать столбец без агрегирования, то эти позиции выдадут ошибку, а если с агрегированием то ошибки не будет, но по сути данные по двум разным позициям соберутся в одну.
вот так будет без ошибки, если считаем, что на одну номенклатуру всегда 2 строки (после шага RemovedEmptyColumns):
Код
    ToPairs = Table.Split(RemovedEmptyColumns,2),
    TransformPairs = List.Transform(ToPairs, each let a = Table.UnpivotOtherColumns(_, {"Период", "Счет", "Склады", "Номенклатура", "Показатели"}, "Атрибут", "Значение") in Table.Pivot(a, List.Distinct(a[Показатели]), "Показатели", "Значение", List.Sum)),
    Combine = Table.Combine(TransformPairs)
in
    Combine
F1 творит чудеса
 
Всем привет, очень нужна помощь не могу сообразить, как в PQ из Оборотки сделать плоскую таблицу, чтоб количество и стоимость стояли в отдельных колонках.  :cry:  Очень прошу с пояснениями.
 
Евгения мосеенкова,  
Страницы: 1 2 След.
Наверх