如何在Excel自定义audit trail中同步显示单元格原始与当前地址？

可以通过VBA实现这个需求，公式方案因为无法持久化记录单元格的原始地址，局限性极大，所以优先推荐VBA方案。

核心思路 #

给每个被编辑的单元格绑定一个持久化的原始地址标识（比如用单元格注释），不管后续插入/删除行/列导致单元格地址变化，这个标识会跟着单元格移动。每次操作时，从标识中读取原始地址，同时获取单元格当前的实时地址，一起写入审计日志。

具体实现代码 #

在需要监控的工作表模块中插入以下代码（右键工作表标签→查看代码，粘贴进去）：

' 处理单元格值修改事件
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim auditSheet As Worksheet
    Dim lastRow As Long
    Dim originalAddr As String
    Dim cell As Range
    
    ' 指定审计日志工作表，改成你实际的日志表名称
    Set auditSheet = ThisWorkbook.Worksheets("AuditLog")
    Application.EnableEvents = False
    On Error GoTo ResetEvents
    
    For Each cell In Target
        ' 为首次编辑的单元格添加原始地址注释
        If cell.Comment Is Nothing Then
            cell.AddComment cell.Address(False, False)
            originalAddr = cell.Address(False, False)
        Else
            ' 从注释中读取已有的原始地址
            originalAddr = Trim(cell.Comment.Text)
        End If
        
        ' 写入审计记录
        lastRow = auditSheet.Cells(auditSheet.Rows.Count, 1).End(xlUp).Row + 1
        With auditSheet
            .Cells(lastRow, 1) = Now()               ' 操作时间
            .Cells(lastRow, 2) = Environ("Username") ' 操作人
            .Cells(lastRow, 3) = originalAddr        ' 原始地址
            .Cells(lastRow, 4) = cell.Address(False, False) ' 当前地址
            .Cells(lastRow, 5) = "修改值"            ' 操作类型
            .Cells(lastRow, 6) = cell.Value          ' 新值
        End With
    Next cell

ResetEvents:
    Application.EnableEvents = True
End Sub

' 处理行/列插入事件
Private Sub Worksheet_Insert(ByVal Sh As Object, ByVal Target As Range)
    Dim auditSheet As Worksheet
    Dim lastRow As Long
    Dim insertType As String
    
    Set auditSheet = ThisWorkbook.Worksheets("AuditLog")
    Application.EnableEvents = False
    On Error GoTo ResetEvents
    
    ' 判断插入类型（行/列）
    If Target.Columns.Count = Me.Columns.Count Then
        insertType = "插入行：" & Target.Row & "-" & Target.Row + Target.Rows.Count - 1
    Else
        insertType = "插入列：" & Split(Target.Address, "$")(1) & "-" & Split(Target.Offset(0, Target.Columns.Count - 1).Address, "$")(1)
    End If
    
    ' 写入插入操作日志
    lastRow = auditSheet.Cells(auditSheet.Rows.Count, 1).End(xlUp).Row + 1
    With auditSheet
        .Cells(lastRow, 1) = Now()
        .Cells(lastRow, 2) = Environ("Username")
        .Cells(lastRow, 3) = "-"
        .Cells(lastRow, 4) = Target.Address(False, False)
        .Cells(lastRow, 5) = insertType
        .Cells(lastRow, 6) = "-"
    End With

ResetEvents:
    Application.EnableEvents = True
End Sub

前置准备与注意事项 #

1. 提前创建名为AuditLog的工作表，建议添加表头：时间、操作人、原始地址、当前地址、操作类型、值
2. 若要防止用户误删单元格注释（原始地址标识），可以设置工作表保护：允许编辑单元格，但禁止编辑注释
3. 如果需要记录修改前的旧值，可以在Worksheet_SelectionChange事件中缓存选中单元格的旧值，再在Worksheet_Change事件中写入日志

公式方案的局限性 #

公式无法实现这个需求，因为Excel公式会自动跟随单元格地址变化更新引用。比如用=CELL("address",A1)记录地址，插入一行后公式会自动变成=CELL("address",A2)，无法保留最初的原始地址。

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