如何在Excel中为重复ID生成新列并聚合相关数据？

一、核心结论 #

完全可以在Excel中实现你的需求，不需要回溯源数据，现有表格数据即可完成。核心逻辑是：先按「ID+产品」组合对数值求和去重，再将产品和对应数值转换为独立列（如Product 1、Value 1）。

二、无代码实现：Power Query（推荐） #

这是Excel原生工具，无需编程，步骤如下：

1. 选中你的数据区域，点击「数据」选项卡 → 「从表格/区域」（勾选「我的表格有标题」），进入Power Query编辑器。
2. 分组求和：点击「开始」→ 「分组依据」，分组列选择ID和Product，新列名设为「合计值」，操作选「求和」，目标列选你的数值列，点击确定。此时已完成同一ID+产品的数值求和。
3. 转成宽表：选中ID列（若有姓名列也一起选中），点击「转换」→ 「透视列」，值列选择「合计值」，高级选项选「不要聚合」，点击确定。系统会自动生成Product 1、Value 1这类对应列。
4. 点击「关闭并上载」，结果将导出到新工作表。

三、VBA代码实现 #

如果需要自动化处理，可使用以下VBA代码：

Sub AggregateAndPivot()
    Dim wsSource As Worksheet, wsDest As Worksheet
    Dim lastRow As Long, destRow As Long, colNum As Long, i As Long
    Dim dictIDProd As Object
    Dim key As String, idVal As String, prodVal As String
    
    ' 定义源表和目标表
    Set wsSource = ActiveSheet
    Set wsDest = ThisWorkbook.Sheets.Add(After:=wsSource)
    Set dictIDProd = CreateObject("Scripting.Dictionary")
    
    ' 写入目标表表头
    wsDest.Range("A1:B1") = Array("ID", "Name")
    colNum = 3
    
    ' 遍历源数据，聚合ID+Product的数值
    lastRow = wsSource.Cells(Rows.Count, "A").End(xlUp).Row
    For i = 2 To lastRow
        key = wsSource.Cells(i, "A").Value & "|" & wsSource.Cells(i, "C").Value
        ' 累加数值或新增条目
        If dictIDProd.Exists(key) Then
            dictIDProd(key) = dictIDProd(key) + wsSource.Cells(i, "D").Value
        Else
            dictIDProd(key) = wsSource.Cells(i, "D").Value
            ' 新增Product和Value列（避免重复）
            If wsDest.Rows(1).Find(wsSource.Cells(i, "C").Value, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
                wsDest.Cells(1, colNum) = "Product " & (colNum \ 2)
                wsDest.Cells(1, colNum + 1) = "Value " & (colNum \ 2)
                colNum = colNum + 2
            End If
        End If
    Next i
    
    ' 写入聚合后的数据到目标表
    destRow = 2
    For Each key In dictIDProd.Keys
        idVal = Split(key, "|")(0)
        prodVal = Split(key, "|")(1)
        ' 写入ID和姓名（同一ID只写一次）
        If wsDest.Columns(1).Find(idVal, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
            wsDest.Cells(destRow, "A") = idVal
            wsDest.Cells(destRow, "B") = wsSource.Cells(wsSource.Columns(1).Find(idVal).Row, "B").Value
            destRow = destRow + 1
        End If
        ' 找到对应列写入产品和数值
        Dim prodCol As Range
        Set prodCol = wsDest.Rows(1).Find("Product *", LookIn:=xlValues, LookAt:=xlPart)
        Do While Not prodCol Is Nothing
            If wsDest.Cells(1, prodCol.Column + 1).Value = "Value " & (prodCol.Column \ 2) Then
                wsDest.Cells(wsDest.Columns(1).Find(idVal).Row, prodCol.Column) = prodVal
                wsDest.Cells(wsDest.Columns(1).Find(idVal).Row, prodCol.Column + 1) = dictIDProd(key)
            End If
            Set prodCol = wsDest.Rows(1).FindNext(prodCol)
        Loop
    Next key
    
    ' 自动调整列宽
    wsDest.Columns.AutoFit
    Set dictIDProd = Nothing
End Sub

使用说明： #

1. 确保源数据表头为ID（A列）、Name（B列）、Product（C列）、Value（D列）。
2. 按Alt+F11打开VBA编辑器，插入模块，粘贴代码。
3. 回到源数据工作表，运行宏即可生成结果表。

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