☰

如何将两列以上的数据拆分成几列？

tag-icon • tag-icon microsoft-excel power-query
如何将两列以上的数据拆分成几列？

我有一个包含图 1 中显示的三列的表格，我想通过 power query 将表格布局更改为图 2 中显示的布局。通常，表格包含超过 500 个采购订单号，因此手动执行此操作会非常繁琐。

答案1

以下是一种方法：

  • 这有点像从期望结果到源数据的过程的逆转。

  • 需要从高级编辑器中完成的“棘手”部分是按采购订单号生成条目的运行计数，以便动态生成重复的列号（可能有更有效的方法来实现这一点）

M 代码

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"PO Number"}, "Attribute", "Value"),

       //Generate running count by PO Number to use for new column names
        group = Table.Group(#"Unpivoted Other Columns", {"PO Number"}, {"temp", each Table.AddIndexColumn(_, "Counter",1,1)}),
        expand = Table.ExpandTableColumn(group, "temp", {"Counter", "Attribute","Value"}),
        
       //Integer-Divide the running count for the column suffix 
        #"Inserted Integer-Division" = Table.AddColumn(expand, "Integer-Division", each Number.IntegerDivide([Counter]+1.5, 2), Int64.Type),
        
       //Merge with the base column name 
        #"Inserted Merged Column" = Table.AddColumn(#"Inserted Integer-Division", "colNames", each Text.Combine({[Attribute], Text.From([#"Integer-Division"], "en-US")}, " "), type text),

    #"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"Counter", "Attribute", "Integer-Division"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"PO Number", type text}, {"Value", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[colNames]), "colNames", "Value", List.Sum)
in
    #"Pivoted Column"

在此处输入图片描述

在此处输入图片描述

笔记：以上内容是根据您提供的数据编写的，其中所有值都是数字。但是，如果某些尺寸是字母数字（例如 40W），则尺寸列需要输入为文本，顺序列则需要输入为数字。如果是这种情况，下面的代码将考虑到这一点：

M 代码 修改为允许 Size 列为文本，OrderQty 列为数字

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"PO Number"}, "Attribute", "Value"),

       //Generate running count by PO Number to use for new column names
        group = Table.Group(#"Unpivoted Other Columns", {"PO Number"}, {"temp", each Table.AddIndexColumn(_, "Counter",1,1)}),
        expand = Table.ExpandTableColumn(group, "temp", {"Counter", "Attribute","Value"}),
        
       //Integer-Divide the running count for the column suffix 
        #"Inserted Integer-Division" = Table.AddColumn(expand, "Integer-Division", each Number.IntegerDivide([Counter]+1.5, 2), Int64.Type),
        
       //Merge with the base column name 
        #"Inserted Merged Column" = Table.AddColumn(#"Inserted Integer-Division", "colNames", each Text.Combine({[Attribute], Text.From([#"Integer-Division"], "en-US")}, " "), type text),

    #"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"Counter", "Attribute", "Integer-Division"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"PO Number", type text}, {"Value", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[colNames]), "colNames", "Value"),

        //Convert Orders to type whole number
        ordCols = List.Select(Table.ColumnNames(#"Pivoted Column"), each Text.StartsWith(_,"ordered")),
        xForm = List.Zip({ordCols, List.Repeat({Int64.Type},List.Count(ordCols))}),
        typed = Table.TransformColumnTypes(#"Pivoted Column",xForm)
in
    typed

答案2

我建议使用 Excel 公式驱动方法，需要一些辅助数据。

INDEX 和 MATCH 的组合与 IFERROR 结合，使用一些辅助数据，按所需顺序提取值。

在此处输入图片描述

怎么运行的：

  • 对于单元格 D2 中的辅助数据公式并填写：

    =COUNTIF($A$2:$A2,A2)

  • 输入辅助值，如 G1:N1 中所示。

  • 单元格 F3 中的数组（CSE）公式获取唯一的 PO 列表。

    {=IFERROR(INDEX($A$2:$A$11, MATCH(0,COUNTIF($F$2:F2, $A$2:$A$11), 0)),"")}

  • 在单元格 G3 中输入此数组（CSE）公式，并填充。

    {=IFERROR(IF(OR(G$2="Size1",G$2="Size2",G$2="Size3",G$2="Size4"),INDEX($B$2:$B$11,MATCH(1,($F3=$A$2:$A$11)*(G$1=$D$2:$D$11),0)),INDEX($C$2:$C$11,MATCH(1,($F3=$A$2:$A$11)*(G$1=$D$2:$D$11),0))),0)}

注意：

  • 使用以下方式完成数组公式Ctrl+Shift+Enter& 向下填充。

  • 在第二个数组公式中，您可以用 替换最后 ,0)}一个,"")}。

  • 为了整洁起见，您可以隐藏辅助数据。

  • 您可以根据需要调整公式中的单元格引用。

相关内容