Excel自动逐日记录外部工作簿数据并冻结历史值的实现方法

方法1：VBA工作簿打开事件（自动触发，推荐） #

这是最贴合需求的方案，打开目标工作簿时自动读取源数据、写入新行并冻结为静态值，同时计算差值。

1. 打开目标工作簿，按Alt + F11打开VBA编辑器
2. 在左侧项目栏找到你的工作簿，双击ThisWorkbook对象
3. 在右侧代码窗口的下拉菜单中，依次选择Workbook和Open，粘贴以下代码：

Private Sub Workbook_Open()
    Dim sourceWB As Workbook
    Dim sourceWS As Worksheet
    Dim targetWS As Worksheet
    Dim lastRow As Long
    Dim currentDate As String
    
    ' 按需修改以下参数
    Const sourcePath As String = "C:\你的路径\exampleworkbook.xlsx" ' 源工作簿完整路径
    Const sourceSheetName As String = "Sheet1" ' 源工作表名
    Const sourceCell As String = "A1" ' 要引用的源单元格地址
    Const targetSheetName As String = "记录" ' 存放历史数据的目标工作表名
    
    Set targetWS = ThisWorkbook.Worksheets(targetSheetName)
    
    ' 找到A列最后一条记录的行号
    lastRow = targetWS.Cells(targetWS.Rows.Count, "A").End(xlUp).Row
    
    ' 获取当前日期的英文星期格式（如MONDAY）
    currentDate = UCase(Format(Date, "DDDD"))
    
    ' 检查当天是否已记录，避免重复写入
    If Not IsError(Application.Match(currentDate, targetWS.Range("A:A"), 0)) Then
        Exit Sub
    End If
    
    ' 后台打开源工作簿（只读模式）
    Set sourceWB = Workbooks.Open(Filename:=sourcePath, ReadOnly:=True)
    Set sourceWS = sourceWB.Worksheets(sourceSheetName)
    
    ' 写入日期和静态数据（不再链接源工作簿）
    targetWS.Cells(lastRow + 1, "A").Value = currentDate
    targetWS.Cells(lastRow + 1, "B").Value = sourceWS.Range(sourceCell).Value
    
    ' 关闭源工作簿，不保存
    sourceWB.Close SaveChanges:=False
    
    ' 自动计算当日与前日的差值（从第二条记录开始）
    If lastRow + 1 > 2 Then
        targetWS.Cells(lastRow + 1, "C").Value = targetWS.Cells(lastRow + 1, "B").Value - targetWS.Cells(lastRow, "B").Value
    End If
End Sub

4. 替换代码中的路径、工作表名、单元格地址为你的实际信息
5. 将工作簿保存为**启用宏的工作簿（.xlsm）**格式，否则宏无法生效

核心逻辑说明 #

• 打开工作簿自动触发执行
• 校验当日是否已记录，防止重复写入
• 读取源数据后直接写入静态值，历史记录不会随源数据变化而覆盖
• 自动计算当日与前日的差值

方法2：Power Query + 手动粘贴值（无宏场景） #

如果你的环境禁用宏，可以用这个方案手动完成数据冻结：

1. 点击「数据」选项卡 → 「获取数据」→ 「从文件」→ 「从工作簿」，选择源工作簿
2. 选择要引用的工作表和单元格，将数据加载到目标工作表的临时区域
3. 每次打开工作簿后，刷新Power Query获取最新数据，选中数据后右键→粘贴为值到记录行的末尾
4. 差值计算：在C3单元格输入=IF(B3<>"",B3-B2,"")，下拉填充即可自动计算当日与前日的差值

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