如何在Excel中自动记录J、L列的历史值到K、M列？

核心思路 #

因为J、L列是公式引用的动态计算值，需要借助VBA的Worksheet_Calculate事件监控数值变化，同时用模块变量存储上一次的数值，变化时写入对应K、M列。

操作步骤 #

1. 打开你的周报工作表，右键点击工作表标签（如“周报”），选择「查看代码」
2. 在弹出的VBA编辑器中，粘贴以下代码
3. 保存工作簿为**启用宏的工作簿（*.xlsm）**格式

' 模块级变量，存储J、L列上一次的数值
Private prevJ As Variant
Private prevL As Variant

' 激活工作表时初始化旧值
Private Sub Worksheet_Activate()
    prevJ = Range("J:J").Value
    prevL = Range("L:L").Value
End Sub

' 工作表计算完成后触发，检查数值变化并记录旧值
Private Sub Worksheet_Calculate()
    Dim cell As Range
    
    ' 处理J列变化：旧值写入K列
    For Each cell In Range("J:J")
        If cell.Value <> prevJ(cell.Row, 1) And Not IsEmpty(cell.Value) Then
            Range("K" & cell.Row).Value = prevJ(cell.Row, 1)
        End If
    Next cell
    
    ' 处理L列变化：旧值写入M列
    For Each cell In Range("L:L")
        If cell.Value <> prevL(cell.Row, 1) And Not IsEmpty(cell.Value) Then
            Range("M" & cell.Row).Value = prevL(cell.Row, 1)
        End If
    Next cell
    
    ' 更新旧值为当前值，准备下一次检测
    prevJ = Range("J:J").Value
    prevL = Range("L:L").Value
End Sub

优化建议 #

• 如果仅需监控特定数据行（比如第2行到第100行），可将代码中的Range("J:J")改为Range("J2:J100")，Range("L:L")改为Range("L2:L100")，减少不必要的计算，提升效率
• 若需保留历史所有旧值而非仅最新一次，可修改代码为在K/M列末尾追加记录（需调整写入逻辑）

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