Excel使用Power Query按分组列拆分生成横向分组表的方法

前期准备 #

确保使用Excel 2016及以上版本（Power Query为内置功能，无需额外插件），准备好包含Var1、Group两列的原始数据区域。

分步操作 #

• 第一步：将原始数据导入Power Query编辑器
  选中完整的原始数据区域，点击顶部菜单栏「数据」选项卡 → 「从表格/区域」，在弹出的创建表窗口中确认勾选「我的表有标题」，点击确定进入Power Query编辑界面。
• 第二步：计算每个分组内的顺序索引（核心对齐逻辑）
  1. 保持界面中数据的原始行顺序不要调整，选中Group列，点击顶部「转换」选项卡 → 「分组依据」，选择高级模式：
     • 分组列设置为Group
     • 聚合操作新增一条：新列名设为「匹配行」，操作类型选「所有行」，点击确定后界面会收缩为2行（对应A、B两个分组），每行的匹配列里存储了对应分组的全部原始记录。
  2. 点击顶部「添加列」选项卡 → 「自定义列」，在公式框中输入= Table.AddIndexColumn([匹配行], "组内序号", 1, 1, Int64.Type)，点击确定，为每个分组内的记录按出现顺序添加从1开始的序号。
  3. 点击自定义列头部的展开箭头，选择展开Var1和组内序号两列，取消「使用原始列名作为前缀」的勾选，展开后即可得到三列结构：Group、Var1、组内序号，同组的记录会按出现顺序标记对应序号。
• 第三步：透视生成分组列结构
  选中Group列，点击顶部「转换」选项卡 → 「透视列」：
  • 值列选择Var1
  • 高级选项下的聚合值函数选择「不要聚合」
  • 点击确定后，删除用于对齐的组内序号辅助列，即可得到目标结构：A列依次为1、3、5，B列依次为2、4，最后一行B列留空。
• 第四步：导出结果
  点击编辑器左上角「关闭并上载」，转换完成的目标表就会自动同步到指定的Excel工作表中。

操作注意事项 #

• 导入数据后不要随意调整原始记录的排序，否则组内序号匹配的Var1值会发生错位，导致结果和原始记录顺序不符。
• 透视列环节必须选择「不要聚合」，如果选择求和、取最大值等聚合函数，会直接破坏原有数值的对应关系。

内容的提问来源于stack exchange，提问作者J. Doe