excel - 用于从宽格式转换为长格式的 Excel 脚本或公式，但需要注意的是

Question

为简单起见，我会说我有这张表（原始表扩展得更广，比方说，最多 30 个项目、价格和数量）：

 Date| Requester|  Item1|  Amount1|  Price1|  Item2|  Amount2|  Price2|
12-10|         A|   Shoe|      200|      30|  Shirt|       40|      10|
12-10|         B|  Socks|       20|      10|       |         |        |
13-10|         A|       |         |        | Gloves|        5|       3|

我需要这个输出：

 Date| Requester|   Item|   Amount|   Price|  
12-10|         A|   Shoe|      200|      30|  
12-10|         A|  Shirt|       40|      10| 
12-10|         B|  Socks|       20|      10|
13-10|         A| Gloves|        5|       3|

我已经遇到了一些非透视公式，它们在一个名为“变量”的列中显示 3 个变量，但这不适合我，我想减少列数，是的，但也要保留 3 个关键变量分成 3 列。此外，由于原始表有数千条记录，我希望输出不要为空列创建不必要的行，否则它可能会崩溃或变得无法管理。我发现的所有脚本都会为空数据创建不必要的行。请注意，在我发布的示例中，如果项目、金额和价格数据为空，则输出表不会打印不必要的行。

谢谢。

标签： excelexcel-formula

Answer 1

您可以Power Query使用 Excel 2010+ 中的来执行此操作

逐步浏览 PQ 编辑器的“应用步骤”窗口，以更好地了解正在发生的事情

• 将任何空白替换为null（因此它们将在下一步中被排除）
• UNPIVOT 除日期和请求者之外的列
• 我们现在将要分组为三组：项目、数量、价格
  • 添加索引列
  • 根据 Index 列添加一个整数/除法列，这将产生一个类似的系列{0,0,0,1,1,1,2,2,2...}
• GroupBy 整数/除法列
• 从 Group 表中提取所需数据
• 重命名新列并删除不需要的列。

M-代码

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    //Replace blanks with null
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,Table.ColumnNames(Source)),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Date", "Requester"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1, Int64.Type),
    #"Inserted Integer-Division" = Table.AddColumn(#"Added Index", "Integer-Division", each Number.IntegerDivide([Index], 3), Int64.Type),
    #"Removed Columns1" = Table.RemoveColumns(#"Inserted Integer-Division",{"Index"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns1", {"Integer-Division"}, {{"Group", each _, type table [Date=nullable date, Requester=nullable text, Value=any, #"Integer-Division"=number]}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Grouped Rows",{"Integer-Division"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns2", "Date", each Table.Column([Group],"Date"){0}),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Requester", each Table.Column([Group],"Requester"){0}),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each Table.Column([Group],"Value")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom2", {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Date", type date}, {"Custom.1", type text}, {"Custom.2", Int64.Type}, {"Custom.3", Currency.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Custom.1", "Item"}, {"Custom.2", "Amount"}, {"Custom.3", "Price"}}),
    #"Removed Columns3" = Table.RemoveColumns(#"Renamed Columns",{"Group"})
in
    #"Removed Columns3"

您应该能够向任何行添加更多“三元组”数据；或更多行；无需更改代码。