Автоматическая реорганизация большой таблицы Excel для разделения каждого столбца на несколько столбцов на основе переменных группировки

Чтобы создать диаграмму Box & Whiskers, похожую на то, что вы показываете,

просто используйте уровень экспозиции для оси x и столбец Score 1 для оси y

Чтобы создать таблицу, похожую на показанные вами результаты, вы можете использовать Power Query.

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

MCode прокомментирован, поэтому, читая комментарии, а также глядя на окно Applied Steps, я надеюсь, что понял, что происходит.

Большая часть MCode генерируется из пользовательского интерфейса, но особенно шаги colNames и ExpandTableColumns ближе к концу вводятся вручную. В противном случае количество столбцов в расширении не будет гибким.

MCode

let
    Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],

    //Won't need ID column so get rid of it
    #"Removed Columns2" = Table.RemoveColumns(Source,{"Sample ID"}),

    //Unpivot the Score columns to put them in a single column
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns2", {"Exposure Level", "Drug"}, "Attribute", "Value"),

   //sort by Score, Attribute, Drug so the results will be properly ordered 
    #"Sorted Rows" = Table.Sort(#"Unpivoted Columns",{{"Attribute", Order.Ascending}, {"Exposure Level", Order.Ascending}, {"Drug", Order.Ascending}}),

    //Create what will become a two line header column
    // and remove the originals
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Headers", each "Exposure " & Text.From([Exposure Level]) & "#(lf)" & [Attribute]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Exposure Level", "Attribute"}),

    //Move headers to first column
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Headers", "Drug", "Value"}),

    //Group by Drug
    #"Grouped Rows" = Table.Group(#"Reordered Columns", {"Drug"}, {{"Grouped", each _, type table [Headers=text, Drug=text, Value=number]}}),

    //Add an Index column
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1, Int64.Type),

    /*From each grouped table, remove Drug Column 
        and remove Header column EXCEPT fromk the first table
        then Transpose each grouped table*/
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Custom", each 
            Table.Transpose(
                if [Index] = 0 then 
                    Table.RemoveColumns([Grouped],"Drug") 
                else 
                    Table.RemoveColumns([Grouped],{"Headers","Drug"}))),

    //Remove no longer needed Grouped and Index columns
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Grouped", "Index"}),

    //Expand the table columns, promote headers, and rename the drug column to get final results
    colNames = Table.ColumnNames(#"Removed Columns1"[Custom]{0}),

    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", colNames),
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Custom", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"A", type text}, {"Exposure 1#(lf)Score 1", type number}, {"Exposure 2#(lf)Score 1", type number}, {"Exposure 3#(lf)Score 1", type number}, {"Exposure 1#(lf)Score 22", type number}, {"Exposure 2#(lf)Score 22", type number}, {"Exposure 3#(lf)Score 22", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"A", "Drug"}})
in
    #"Renamed Columns"

РЕДАКТИРОВАТЬ

@ user3316549 прокомментировал ниже, что у него может быть несколько записей для одного и того же препарата для одного и того же Score/Exposure, и хотел, чтобы результаты для каждого были показаны отдельно.

Здесь была бы полезна сводная таблица, за исключением того, что классическая сводная таблица будет иметь только одну запись для каждого пересечения Drug с Score/Exposure.

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

Пользовательская функция добавляется как blank query. Вы можете назвать его так, как вы выберете, и назвать его таким образом в своем основном коде.

М-код

Основной запрос

let
    Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],

    //Unpivot the Score columns to put them in a single column
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Sample ID","Exposure Level", "Drug"}, "Attribute", "Value"),

   //sort by multiple columns so the results will be properly ordered to our liking
    #"Sorted Rows" = Table.Sort(#"Unpivoted Columns",{{"Attribute", Order.Ascending}, {"Exposure Level", Order.Ascending}, {"Drug", Order.Ascending},{"Sample ID", Order.Ascending}}),

    //Create what will become a two line header column
    // and remove the originals
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Headers", each [Attribute] & "#(lf)" & "Exposure " & Text.From([Exposure Level])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Sample ID","Exposure Level", "Attribute"}),

    //custom pivot function for non-aggregation
    pivotAll = fnPivotAll(#"Removed Columns","Headers","Value")
in
    pivotAll

М-код

Пользовательская функция с именем fnPivotAll

//credit: Cam Wallace  https://www.dingbatdata.com/2018/03/08/non-aggregate-pivot-with-multiple-rows-in-powerquery/

(Source as table,
    ColToPivot as text,
    ColForValues as text)=> 

let
     PivotColNames = List.Buffer(List.Distinct(Table.Column(Source,ColToPivot))),
     #"Pivoted Column" = Table.Pivot(Source, PivotColNames, ColToPivot, ColForValues, each _),
 
    TableFromRecordOfLists = (rec as record, fieldnames as list) =>
    
    let
        PartialRecord = Record.SelectFields(rec,fieldnames),
        RecordToList = Record.ToList(PartialRecord),
        Table = Table.FromColumns(RecordToList,fieldnames)
    in
        Table,
 
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Values", each TableFromRecordOfLists(_,PivotColNames)),
    #"Removed Other Columns" = Table.RemoveColumns(#"Added Custom",PivotColNames),
    #"Expanded Values" = Table.ExpandTableColumn(#"Removed Other Columns", "Values", PivotColNames)
in
    #"Expanded Values"