![在 LibreOffice Calc 中,如何将包含调查回复的工作表转换为二维网格?](https://linux22.com/image/1604018/%E5%9C%A8%20LibreOffice%20Calc%20%E4%B8%AD%EF%BC%8C%E5%A6%82%E4%BD%95%E5%B0%86%E5%8C%85%E5%90%AB%E8%B0%83%E6%9F%A5%E5%9B%9E%E5%A4%8D%E7%9A%84%E5%B7%A5%E4%BD%9C%E8%A1%A8%E8%BD%AC%E6%8D%A2%E4%B8%BA%E4%BA%8C%E7%BB%B4%E7%BD%91%E6%A0%BC%EF%BC%9F.png)
例如,如果我有以下数据:
Name Question Answer
Alice What is your favorite color? blue
Alice What is your favorite video game? Minecraft
Alice What is your favorite number? 42
Bob What is your favorite color? red
Bob What is your favorite video game? Team Fortress 2
Bob What is your favorite number? 7
Charlie What is your favorite color? green
Charlie What is your favorite video game? Candy Crush
Charlie What is your favorite number? -1
我希望它的格式如下:
Name What is your favorite color? What is your favorite video game? What is your favorite number?
Alice blue Minecraft 42
Bob red Team Fortress 2 7
Charlie green Candy Crush -1
有没有办法在 LibreOffice Calc 中做到这一点?(或者在 SQL Server 中;那是我从那里获取数据的地方。)
答案1
笔记: 在最初的问题中,发帖者提到使用 Excel 作为一种可能的解决方案。他后来修改了他的问题,澄清他只想要 LibreOffice 或 SQL 解决方案。但我将保留这个答案,以防 Excel 用户将来偶然遇到这个问题
您可以在 Excel 中使用Power Query
(在 Excel 2010 及更高版本中可用)。
Question
以柱为轴- 选择
Answers
列Values
- 在
Advanced Options
选择下Don't aggregate
M 代码
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Question", type text}, {"Answer", type any}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Question]), "Question", "Answer")
in
#"Pivoted Column"
源数据
结果