Excel中基于ID将行转置为多列的技术方案问询

针对你350+个唯一ID的多行违规记录转置需求，以下提供两种高效实现方式，均能保留记录原始顺序：

VBA 实现方案（最优选择） #

这段代码可自动识别唯一ID，将每个ID对应的多行记录转置为单行多列，结果可输出到新工作表：

Sub TransposeIDRecords()
    Dim wsSource As Worksheet, wsTarget As Worksheet
    Dim lastRow As Long, i As Long, j As Long, targetRow As Long
    Dim idDict As Object
    Dim currentID As String
    
    ' 定义源工作表（替换为你的实际工作表名称）
    Set wsSource = ThisWorkbook.Worksheets("Sheet1")
    ' 创建新工作表存储结果
    Set wsTarget = ThisWorkbook.Worksheets.Add
    wsTarget.Name = "Transposed_Result"
    
    ' 获取源数据最后一行
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    
    ' 用字典存储每个ID的所有记录
    Set idDict = CreateObject("Scripting.Dictionary")
    
    ' 遍历源数据填充字典
    For i = 2 To lastRow ' 假设第一行为表头
        currentID = wsSource.Cells(i, "A").Value ' ID所在列（可修改为你的列号）
        If Not idDict.Exists(currentID) Then
            idDict(currentID) = Array(wsSource.Cells(i, "B").Value) ' 违规记录所在列（可修改）
        Else
            ' 追加新记录到数组
            idDict(currentID) = Split(Join(idDict(currentID), "|") & "|" & wsSource.Cells(i, "B").Value, "|")
        End If
    Next i
    
    ' 写入结果表头
    wsTarget.Cells(1, 1).Value = "唯一ID"
    ' 生成违规记录列标题
    Dim maxCols As Integer
    maxCols = 0
    For Each key In idDict.Keys
        If UBound(idDict(key)) + 1 > maxCols Then maxCols = UBound(idDict(key)) + 1
    Next key
    For j = 1 To maxCols
        wsTarget.Cells(1, j + 1).Value = "违规记录" & j
    Next j
    
    ' 写入转置后的数据
    targetRow = 2
    For Each key In idDict.Keys
        wsTarget.Cells(targetRow, 1).Value = key
        For j = 0 To UBound(idDict(key))
            wsTarget.Cells(targetRow, j + 2).Value = idDict(key)(j)
        Next j
        targetRow = targetRow + 1
    Next key
    
    ' 自动调整列宽
    wsTarget.Columns.AutoFit
    MsgBox "转置完成，结果已保存至工作表：" & wsTarget.Name, vbInformation
End Sub

使用步骤 #

1. 打开Excel文件，按下Alt + F11打开VBA编辑器
2. 右键点击左侧工程窗口的工作簿名称 → 插入 → 模块
3. 粘贴上述代码，修改wsSource的工作表名称、ID列（A列）、违规记录列（B列）为你的实际数据列
4. 按下F5运行代码，或回到Excel界面，通过「开发工具」→「宏」执行TransposeIDRecords

无代码可视化操作方案（Power Query） #

无需编程，通过点击操作即可完成转置，支持后续数据刷新：

1. 选中包含表头的数据区域，点击「数据」选项卡 →「从表格/区域」（确认勾选「我的表格有标题」）
2. 在Power Query编辑器中，选中ID列，点击「转换」→「分组依据」：
   • 分组依据：选择ID列
   • 新列名：输入「违规记录」
   • 操作：选择「所有行」
3. 点击「违规记录」列右侧的展开箭头 → 选择「到列」，并选中你的违规记录列名，自动拆分出多列
4. 点击「关闭并上载」，选择上载到新工作表，即可得到转置结果

优势 #

• 自动保留记录原始顺序
• 后续数据更新时，右键点击结果表格 →「刷新」即可重新生成转置数据

转换效果示例 #

转换前（单列多行）：

唯一ID	违规记录
ID001	违规A
ID001	违规B
ID002	违规C

转换后（单行多列）：

唯一ID	违规记录1	违规记录2
ID001	违规A	违规B
ID002	违规C

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