excel - 需要具有逗号分隔值的列的数据透视表

Question

因此，对于我遇到的类似问题，我找到了其他几个解决方案，但不是很准确。我在 Excel 中解释调查结果，其中调查工具 (Qualtrics) 将多个选择问题(“选择所有适用项”)的回答放在一个单元格中，以逗号分隔。我需要在数据透视表中计算个人回复的数量，我还将从一些我要添加到回复电子表格中的受访者人口统计数据中获取总数。我正在尝试做的与此非常相似:

Split comma separated entries to new rows

但是我的工作表将有多个列，其中包含以逗号分隔的响应，如下所示:

....我需要它的列格式，以便我可以旋转和计数。正如我提到的，我将添加一些属性数据(HR 数据，这就是为什么我无法将其导入 Qualtrics - 无法发送到公司外部)，因此我仍然需要将此人与回复配对。但是，如果有一个脚本或命令可以为我运行并拆分它，我不确定它如何处理列与列之间不同数量的响应以创建所需的行(如示例中的 Bill 和 Karen) .我是否需要首先拥有最长的 csv 单元格的列等等？但是，它看起来像这样:

我可以做些什么来实现这个目标吗？

Answer 1

您想要的布局实际上也不适合适当的数据透视表。您需要一个真正平坦的表结构，这样您就可以过滤喜欢的内容，而不会同时隐藏不喜欢的内容。

您可以使用 Power Query 轻松转换数据。将数据加载到 Power Query 编辑器中，然后用分隔符“,”(逗号后跟空格)拆分每个问题列。这会将每个答案拆分到自己的列中，标题中的问题会附加 .1、.2 等。

然后选择名称列并单击“Unpivot other columns”。问题标题现在将位于属性列中。用分隔符“.”拆分该属性列。 (点)并删除带有拆分数字的列。

最后，将列重命名为问题和答案。

这是执行此操作时生成的 M 代码。

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"like", type text}, {"dislike", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "dislike", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), {"dislike.1", "dislike.2", "dislike.3", "dislike.4", "dislike.5"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"dislike.1", type text}, {"dislike.2", type text}, {"dislike.3", type text}, {"dislike.4", type text}, {"dislike.5", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "like", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), {"like.1", "like.2", "like.3", "like.4"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"like.1", type text}, {"like.2", type text}, {"like.3", type text}, {"like.4", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type2", {"Name"}, "Attribute", "Value"),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type3",{"Attribute.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute.1", "question"}, {"Value", "Answer"}})
in
    #"Renamed Columns"

生成的表格如下所示:

如果您的原始数据接收到更多行，只需刷新查询即可。

现在您有一个可以在不影响任何数据的情况下用于数据透视表的表。