如何在Excel中将同一ID的多行数据转换为单行多列格式

原始数据 #

目标格式 #

方法1：带辅助列的透视表法（快速直观） #

1. 在原始数据新增一列（表头设为「序号」），输入公式 =COUNTIF($A$2:A2,A2)，下拉填充所有行。该公式会为每个ID的重复行生成1、2、3...的递增序号。
2. 选中包含辅助列的完整数据区域（含表头），点击「插入」选项卡 → 「数据透视表」，选择结果放置位置。
3. 在透视表字段面板中配置：
   • 将ID拖至「行」区域
   • 将「序号」拖至「列」区域
   • 将VALUE拖至「值」区域，点击值区域的VALUE → 「值字段设置」，选择「最大值」（文本类型数据用最大值不会改变内容）
4. 透视表生成后，列标题自动为1、2、3、4，对应位置自动填充VALUE，空值保留空白，直接调整表头即可匹配目标格式。

方法2：公式法（无需额外操作，适合轻量数据） #

假设原始数据在A1:B10区域：

1. 在新表格的A列提取唯一ID，可使用公式 =UNIQUE(A2:A10)（Excel 365及以上版本支持），或手动输入去重后的ID。
2. 在新表格B2单元格输入公式：

   =XLOOKUP($A2&COLUMN(A1),$A$2:$A$10&COUNTIFS($A$2:A10,$A$2:A10),$B$2:$B$10,"")
   

   按回车后，向右拖动公式至E列（对应4个目标列），再向下拖动覆盖所有ID行。
   • 注：旧版Excel可改用数组公式 =INDEX($B$2:$B$10,MATCH($A2&COLUMN(A1),$A$2:$A$10&COUNTIFS($A$2:A10,$A$2:A10),0))，输入后需按Ctrl+Shift+Enter触发。

方法3：Power Query法（适合批量/复杂数据） #

1. 选中原始数据区域 → 「数据」选项卡 → 「从表格/区域」，确认表头存在并进入Power Query编辑器。
2. 选中ID列 → 「转换」选项卡 → 「分组依据」，设置：
   • 分组依据：ID
   • 新列名：Values
   • 操作：「所有行」
3. 选中Values列 → 「添加列」→ 「自定义列」，输入公式 =Table.Column([Values], "VALUE")，生成包含对应VALUE的列表。
4. 删除Values列，选中新生成的自定义列，点击列右侧的展开按钮 → 选择「将值拆分为列」，设置列数为4，空值自动留空。
5. 点击「关闭并上载」，将转换结果导出至Excel表格。

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