我是一名正在学习更高级 Excel 的学生，我很好奇 Excel 是否有一些内置功能来处理更复杂的数据透视。还是需要从头开始编写脚本？
假设我有这样的表格：
| ID | 名称_1 | 名称_2 | 名称_3 |
|---|---|---|---|
| 100 | 胜利者 | 妮娜 | 安吉拉 |
| 200 | 蒂姆 | 约翰 | 麦克风 |
使用一些 PowerQuary 很容易获得该表：Excel 中的动态重组（宽 -> 长）
| ID | 姓名 |
|---|---|
| 100 | 胜利者 |
| 100 | 妮娜 |
| 100 | 安吉拉 |
| 200 | 蒂姆 |
| 200 | 约翰 |
| 200 | 麦克风 |
我的问题
如果我有包含多个不同列的类似数据怎么办
| ID | 名称_1 | 性别_1 | 名称_2 | 性别_2 | 名称_3 | 性别_3 |
|---|---|---|---|---|---|---|
| 100 | 胜利者 | 男性 | 妮娜 | 女性 | 安吉拉 | 女性 |
| 200 | 蒂姆 | 男性 | 约翰 | 男性 | 麦克风 | 男性 |
我找不到进入这些表格的方法 ->
| ID | 姓名 | 性别 |
|---|---|---|
| 100 | 胜利者 | 男性 |
| 100 | 妮娜 | 女性 |
| 100 | 安吉拉 | 女性 |
| 200 | 蒂姆 | 男性 |
| 200 | 约翰 | 男性 |
| 200 | 麦克风 | 男性 |
我知道我可以使用 R 中的pivot_longer函数来执行此操作，但仍在熟悉 Excel 中执行类似这样的操作。
我之所以问这个问题，是因为我正在处理一些以 csv 形式输出的 Adobe Fillable 表单，这就是数据的结构。我希望得到一些解决问题的指导。谢谢！
答案1
Power Query您可以使用Windows Excel 2010+ 和 Office 365 Excel 中提供的获取所需的输出
- 选择原始表格中的某个单元格
Data => Get&Transform => From Table/Range或者From within sheet- 当 PQ UI 打开时，导航至
Home => Advanced Editor - 记下代码第 2 行的表名称。
- 用以下代码替换现有代码M 代码以下
- 将粘贴代码第 2 行的表名更改为您的“真实”表名
- 检查代码注释和
Applied Steps窗口，以更好地理解算法和步骤
M 代码
let
//Get data => change table name in next line to your actual table name
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
//Set the column data types=> ID=integer; other columns =>text
#"Changed Type" = Table.TransformColumnTypes(Source,
{"ID", Int64.Type} & List.Transform(List.RemoveFirstN(Table.ColumnNames(Source),1), each {_, type text})),
//Unpivot all except the ID column
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value"),
//Split the Attribute column on the underscore to have lists of identical attributes
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute",
Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute"}),
//Group by ID
// Then aggregate to create list of records where each record consists of each pair of values
// effectively Pivoting with no aggregation
#"Grouped Rows" = Table.Group(#"Split Column by Delimiter", {"ID"}, {
{"pivot", (t)=>let
recs = List.Generate(
()=> [r=Record.FromList({t[Value]{0},t[Value]{1}},{t[Attribute]{0},t[Attribute]{1}}),idx=0],
each [idx]<Table.RowCount(t),
each [r=Record.FromList({t[Value]{[idx]+2},t[Value]{[idx]+3}},{t[Attribute]{[idx]+2},t[Attribute]{[idx]+3}}),
idx=[idx]+2],
each [r])
in recs, type list}
}),
//expand the list of records to new rows
#"Expanded pivot" = Table.ExpandListColumn(#"Grouped Rows", "pivot"),
//expand the records to new columns
#"Expanded pivot1" = Table.ExpandRecordColumn(#"Expanded pivot", "pivot", {"Name", "Gender"}, {"Name", "Gender"}),
//set the data types
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded pivot1",{{"Name", type text}, {"Gender", type text}})
in
#"Changed Type1"
答案2
Excel 2019 及更高版本公式解决方案中的另一种选项。
在I2数组公式（“Ctrl+Shift+Enter”）中复制到K2并全部复制下来：
=IFERROR(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,IF(MOD(COLUMN($B$2:$G$3),2)=0,$A$2:$A$3&"</b><b>"&$B$2:$G$3,"")&IF(MOD(COLUMN($B$2:$G$3),2)=1,$B$2:$G$3,""))&"</b></a>","//b["&ROW($A1)*3+COLUMN(A$1)-3&"]"),"")
