Excel按名称列匹配自动递增值的公式或宏实现方法咨询

方案选择建议 #

优先使用公式实现，无需启用宏、兼容性更强，数据更新时可以自动重算，适配绝大多数使用场景。仅当数据量超过10万行、公式计算出现明显卡顿时，再考虑使用宏生成固定结果。

公式实现方法 #

假设你的数据从第2行开始，A列为Name、B列为Reference、C列为输出列，对应以下不同Excel版本选择公式输入到C2单元格，下拉填充即可：

Excel 365/2021及以上版本 #

=IF(COUNTIF($A$2:A2,A2)=1,"N/A",XLOOKUP(A2,$A$2:A2,$B$2:B2,,0,1) & "." & COUNTIF($A$2:A2,A2)-1)

如果需要输出为数值格式而非文本，可以改为：

=IF(COUNTIF($A$2:A2,A2)=1,"N/A",XLOOKUP(A2,$A$2:A2,$B$2:B2,,0,1) + (COUNTIF($A$2:A2,A2)-1)/10)

低版本Excel（无XLOOKUP函数） #

=IF(COUNTIF($A$2:A2,A2)=1,"N/A",INDEX($B$2:B2,MATCH(A2,$A$2:A2,0)) & "." & COUNTIF($A$2:A2,A2)-1)

注意：公式中的$A$2、$B$2为数据起始行的绝对引用，不要修改，后续的相对引用A2、B2下拉时会自动适配对应行。

宏实现方法 #

如果数据量极大需要减少计算开销，可以使用VBA宏，运行后生成固定结果，源数据更新时需要重新运行宏才能同步输出：

1. 按Alt+F11打开VBA编辑器，插入新模块
2. 粘贴以下代码：

Sub GenerateOutput()
    Dim lastRow As Long, i As Long, dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 2 To lastRow
        If Not dict.exists(Cells(i, "A").Value) Then
            dict.Add Cells(i, "A").Value, Array(Cells(i, "B").Value, 0)
            Cells(i, "C").Value = "N/A"
        Else
            Dim arr As Variant
            arr = dict(Cells(i, "A").Value)
            arr(1) = arr(1) + 1
            dict(Cells(i, "A").Value) = arr
            Cells(i, "C").Value = arr(0) & "." & arr(1)
        End If
    Next i
    Set dict = Nothing
End Sub

3. 按F5运行即可自动填充所有输出结果。

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