☰

Excel 表格数据转置

tag-icon • tag-icon microsoft-excel worksheet-function
Excel 表格数据转置

我有以下格式的 Excel 表，其中包含流程的数据

样本数据

我希望将数据从输入模型转换为输出模型。我的实际数据包含 22 个流程和 6 个子流程，具有 16,000 个唯一 ID。因此无法进行手动转换。请建议一种我可以大规模复制的算​​法。

答案1

对于随着输入数据增长而可重复的大型重塑操作，我建议使用 PowerQuery 而不是公式。

选择输入范围内的一个单元格。

使用数据>获取和转换数据>从表/范围在输入范围上创建强力查询。

在此处输入图片描述

选择“唯一 ID”和“属性”列，然后使用“转换”>“取消透视列”>“取消透视其他列”来获得以下内容：

在此处输入图片描述

选择“Attribute.1”，右键单击并使用拆分列>按非数字转为数字，得到如下结果：

在此处输入图片描述

选择“Attribute.1.2”并使用“Transform”>“Pivot Column”。使用 Attribute.1.2 作为要进行透视的列，使用 Value 作为值列。执行此操作时，表格的单元格将包含数字，而不是文本值。

要纠正这个问题，请转到“主页”>“高级编辑器”，并将其中的内容替换List.Count为List.First：

在此处输入图片描述

完成更改后，您将获得所需格式的数据。您可以通过将列拖到所需位置来重新排序，并根据需要重命名它们，然后使用主页 > 关闭并加载将数据放回电子表格中。

在此处输入图片描述

以下是我的高级编辑的全文，供参考：

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unique ID", type text}, {"Attribute", type text}, {"A1", type text}, {"A2", type text}, {"A3", type text}, {"B1", type text}, {"B2", type text}, {"B3", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Unique ID", "Attribute"}, "Attribute.1", "Value"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute.1", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Attribute.1.1", "Attribute.1.2"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Character Transition", List.Distinct(#"Split Column by Character Transition"[Attribute.1.2]), "Attribute.1.2", "Value", List.First),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Attribute.1.1", "Unique ID", "Attribute", "1", "2", "3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Attribute.1.1", "Step"}})
in
    #"Renamed Columns"

相关内容