![如何将两列以上的数据拆分成几列?](https://linux22.com/image/1630367/%E5%A6%82%E4%BD%95%E5%B0%86%E4%B8%A4%E5%88%97%E4%BB%A5%E4%B8%8A%E7%9A%84%E6%95%B0%E6%8D%AE%E6%8B%86%E5%88%86%E6%88%90%E5%87%A0%E5%88%97%EF%BC%9F.png)
答案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)}
一个,"")}
。为了整洁起见,您可以隐藏辅助数据。
您可以根据需要调整公式中的单元格引用。