![我有一列带有答案的测试(考试)问题。如何将它们转换为多行?](https://linux22.com/image/1616590/%E6%88%91%E6%9C%89%E4%B8%80%E5%88%97%E5%B8%A6%E6%9C%89%E7%AD%94%E6%A1%88%E7%9A%84%E6%B5%8B%E8%AF%95%EF%BC%88%E8%80%83%E8%AF%95%EF%BC%89%E9%97%AE%E9%A2%98%E3%80%82%E5%A6%82%E4%BD%95%E5%B0%86%E5%AE%83%E4%BB%AC%E8%BD%AC%E6%8D%A2%E4%B8%BA%E5%A4%9A%E8%A1%8C%EF%BC%9F.png)
我在 Excel 文件中有一列单元格(每个单元格 1 行)包含以下数据:
1. Question1:
A. Answer1;
B. Answer2;
C. Answer3;
D. Answer4.
2. Question2:
A. Answer1;
B. Answer2;
C. Answer3;
D. Answer4.
3. Question3:
A. Answer1;
B. Answer2;
C. Answer3;
D. Answer4.
... 等等
我正在尝试将其转换为以下格式 - 在单独的 Excel 行和单元格中:
1. Question1: | A. Answer1; | B. Answer2; | C. Answer3; | D. Answer4.
2. Question1: | A. Answer1; | B. Answer2; | C. Answer3; | D. Answer4.
3. Question1: | A. Answer1; | B. Answer2; | C. Answer3; | D. Answer4.
你能告诉我如何得到这个吗?这个列很大。我无法编写在自动扩展单元格时以 5 为增量的公式。
答案1
如果您的结果表不正确,并且您确实希望在每一行上有不同的问题,如下所示:
1. Question1: | A. Answer1; | B. Answer2; | C. Answer3; | D. Answer4.
2. Question2: | A. Answer1; | B. Answer2; | C. Answer3; | D. Answer4.
3. Question3: | A. Answer1; | B. Answer2; | C. Answer3; | D. Answer4.
那么你可以使用 Power Query
添加自定义列
- 测试字符串中的第一个字符
- 如果是数字,则这是问题,并从列中复制,否则留空无效的
- 测试字符串中的第一个字符
添加第二个自定义列
- 如果第一个字符不是数字,那么这就是答案,所以将其复制过来
- 选择问题列并
Fill Down
- 按问题分组
- 将结果表列转换为答案列表
- 添加列公式:
=Table.Column([Grouped],"Custom.1")
- 添加列公式:
tab
单击列顶部的双头箭头以使用分隔符展开列表。- 然后将列表列拆分为
tab
新列
除了输入各种添加的列公式外,所有这些都可以从 UI 中完成,但这里是
M 代码
let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if (try Text.InferNumberType(Text.Start([Column1],1)) otherwise false) = false then null else [Column1]),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
#"Added Custom1" = Table.AddColumn(#"Filled Down", "Custom.1", each if (try Text.InferNumberType(Text.Start([Column1],1)) otherwise false) = false then [Column1] else null),
#"Grouped Rows" = Table.Group(#"Added Custom1", {"Custom"}, {{"Grouped", each _, type table [Column1=text, Index=number, Custom=text, Custom.1=text]}}),
#"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Table.Column([Grouped],"Custom.1")),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Grouped"}),
#"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Custom.1", each Text.Combine(List.Transform(_, Text.From), "#(tab)"), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom.1", Splitter.SplitTextByDelimiter("#(tab)", QuoteStyle.Csv), {"Custom.1.1", "Custom.1.2", "Custom.1.3", "Custom.1.4"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1.1", type text}, {"Custom.1.2", type text}, {"Custom.1.3", type text}, {"Custom.1.4", type text}})
in
#"Changed Type1"