Power Query中多列表格行合并实现行列转换方法咨询

针对你遇到的Pivot无法处理多列合并的问题，这里提供三种可行的解决方法：

方法1：Excel公式实现 #

假设原数据放在A1:D11区域（表头A1:D1，数据A2:D11）：

1. 添加分组辅助列：在E2输入公式 =INT((ROW()-2)/2)，下拉填充到E11。这个公式会给每两行分配同一个分组编号（0、0、1、1...）。
2. 新建目标表头：在G1:N1依次输入 ID, rating, cntry, Index, ID 2, rating 2, cntry 2, Index 2。
3. 填充数据公式：
   • G2: =INDEX(A:A, 2 + E2*2)
   • H2: =INDEX(B:B, 2 + E2*2)
   • I2: =INDEX(C:C, 2 + E2*2)
   • J2: =INDEX(D:D, 2 + E2*2)
   • K2: =INDEX(A:A, 3 + E2*2)
   • L2: =INDEX(B:B, 3 + E2*2)
   • M2: =INDEX(C:C, 3 + E2*2)
   • N2: =INDEX(D:D, 3 + E2*2)
4. 选中G2:N2，下拉填充到对应行，即可得到目标表格。

方法2：Power Query（批量高效处理） #

适合数据量较大的场景，步骤如下：

1. 选中原数据区域，点击「数据」选项卡 → 「从表格/范围」，导入Power Query编辑器（勾选「我的表格有标题」）。
2. 添加索引列：点击「添加列」→「索引列」→「从0开始」。
3. 添加分组列：点击「添加列」→「自定义列」，输入公式 =Number.IntegerDivide([Index],2)，命名为GroupID。
4. 添加行内序号：再次添加自定义列，公式 =Number.Mod([Index],2)+1，命名为RowNum。
5. 逆透视数据：选中GroupID和RowNum列，右键 → 「逆透视其他列」，此时会生成Attribute（原列名）和Value（对应值）两列。
6. 合并列名与序号：添加自定义列，公式 = [Attribute] & " " & Text.From([RowNum])，命名为NewColumn。
7. 透视生成目标表：选中GroupID列，点击「转换」→「透视列」，值列选择Value，列名选择NewColumn。
8. 调整列顺序后，点击「关闭并上载」，即可将结果导入Excel。

方法3：Python pandas代码处理 #

如果熟悉代码，用pandas可以快速完成：

import pandas as pd

# 加载原数据（这里直接构造示例，实际可通过pd.read_excel读取）
df = pd.DataFrame({
    'ID': ['Albert', 'Peter', 'Peter', 'Albert', 'Franz', 'Peter', 'Eddie', 'Peter', 'Peter', 'Joe'],
    'rating': [603, 912, 907, 608, 833, 894, 753, 884, 905, 787],
    'cntry': ['pl', 'at', 'at', 'pl', 'pl', 'at', 'it', 'at', 'at', 'de'],
    'Index': [0, 1, 2, 3, 4, 5, 6, 7, 8, 9]
})

# 拆分奇偶行并重命名列
df_odd = df.iloc[::2].reset_index(drop=True)  # 取第0、2、4...行
df_even = df.iloc[1::2].reset_index(drop=True)  # 取第1、3、5...行
df_even.columns = [f"{col} 2" for col in df_even.columns]

# 合并两表
result = pd.concat([df_odd, df_even], axis=1)
print(result)

运行后输出的结果即为目标格式，可通过result.to_excel()导出到Excel。

内容的提问来源于stack exchange，提问作者Gecko