如何在Excel中为按客户分组的批次添加相对引用列？

需求说明 #

• 按B列（客户ID）分组，为每个客户的C列（批次）生成相对引用编号并写入E列
• 每个客户的批次为递增序列，起始值不固定（最小可到0），相对引用需从0开始计数（比如批次2、3、4对应的相对值为0、1、2）

现有代码问题 #

当前宏仅能选中指定客户（ID=10000201）的行，无法遍历所有客户并自动计算写入相对引用值：

Sub select_relative_column()

    Dim ref As Range
    Dim ref2 As Range

    For i = 1 To 100
        If Cells(i, 2) = 10000201 Then
            Set ref = Range(Cells(i, 1), Cells(i, 5))
            If ref2 Is Nothing Then
                Set ref2 = ref
            Else
                Set ref2 = Union(ref2, ref)
            End If
        End If
    Next i
    ref2.Select
End Sub

改进后的解决方案 #

以下宏会自动遍历所有客户，计算每个批次的相对引用值并写入E列：

Sub GenerateRelativeBatchNumbers()
    Dim lastRow As Long
    Dim customerDict As Object
    Dim currentCustomer As Variant
    Dim minBatch As Long
    Dim i As Long
    
    ' 获取数据最后一行
    lastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    ' 创建字典存储每个客户的最小批次
    Set customerDict = CreateObject("Scripting.Dictionary")
    
    ' 第一遍遍历：记录每个客户的最小批次
    For i = 2 To lastRow ' 假设第1行是表头
        currentCustomer = Cells(i, "B").Value
        If Not customerDict.Exists(currentCustomer) Then
            customerDict(currentCustomer) = Cells(i, "C").Value
        Else
            ' 更新最小批次
            If Cells(i, "C").Value < customerDict(currentCustomer) Then
                customerDict(currentCustomer) = Cells(i, "C").Value
            End If
        End If
    Next i
    
    ' 第二遍遍历：计算并写入相对引用值
    For i = 2 To lastRow
        currentCustomer = Cells(i, "B").Value
        minBatch = customerDict(currentCustomer)
        ' 相对值 = 当前批次 - 该客户最小批次
        Cells(i, "E").Value = Cells(i, "C").Value - minBatch
    Next i
    
    MsgBox "相对引用编号已生成完成！"
End Sub

代码说明 #

• 使用Scripting.Dictionary存储每个客户的最小批次，保证分组处理的效率
• 分两次遍历：第一次收集每个客户的最小批次，第二次计算相对值并写入E列
• 自动识别数据最后一行，打破原代码固定行数的限制
• 假设第1行为表头，若数据从第1行开始，将代码中i = 2改为i = 1即可

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