excel - 旋转时有没有办法在值字段中加入多个字符串？

Question

我试图通过制作报告来使表格更具可读性。该表有 3 列；

1. 职员
2. 任务（每个员工可以有 0 个任务）
3. 状态（计划、开始、完成）

该报告将工作人员作为最左边的列，3 个状态作为列标题。这些值应该是任务值，如果有很多任务，它应该连接起来，比如说，用回车。

我尝试了旋转，但由于任务值是文本，所以它不起作用。我尝试了 Power Query，它为每个有超过 1 个任务的单元格显示错误。

有没有办法做到这一点？...请不要使用 VBA。谢谢

标签： excelexcel-formulapivotpowerquery

Answer 1

我假设您知道如何操作 Power Query 编辑器，因此我将跳过如何将数据添加到编辑器的部分。

在我的解决方案中，我使用了存储在Table3中的以下示例数据。

添加数据后，编辑器将自动将所有数据识别为文本。

我的方法是首先添加一个自定义列来组合人员和状态，如下所示：

然后我使用一些高级编码按自定义列（Staff+Status ）对数据进行分组。您可以先进行分组，然后转到高级编辑器更改公式如下：

= Table.Group(#"Added Custom", {"Staff+Status"}, {{"Task", each Text.Combine([Task],"#(lf)"), type text}})

这将为您提供以下外观：

然后，您可以将自定义列分别拆分回 Staff 和 Status：

然后您可以旋转 Status 列，将Task设置为Values Column，并在Advanced Options中将Don't Aggregate设置为Aggregate Value Function。

然后，您在这里几乎完成了，您可以将查询加载到工作表中，该工作表可能如下所示，其中回车似乎不起作用。

为了“激活”回车（实际上是换行），您需要选择一个要查看回车的单元格，单击编辑栏中的某处，您会注意到回车已“激活”。

复制该单元格的格式并使用格式刷粘贴到表格的其余部分以获得以下内容：

如果您对上述步骤不清楚，请阅读本文：如何使用换行或回车显示 Power Query 结果

全部完成。干杯:)

顺便说一下幕后代码，仅供参考：

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Staff", type text}, {"Task", type text}, {"Status", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Staff+Status", each [Staff]&"+"&[Status]),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Staff+Status"}, {{"Task", each Text.Combine([Task],"#(lf)"), type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Staff+Status", Splitter.SplitTextByDelimiter("+", QuoteStyle.Csv), {"Staff+Status.1", "Staff+Status.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Staff+Status.1", type text}, {"Staff+Status.2", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[#"Staff+Status.2"]), "Staff+Status.2", "Task"),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Staff+Status.1", "Staff"}})
in
    #"Renamed Columns"