如何在Excel中自动记录数据变化并生成资产净值趋势图表？

可以实现无需手动保存历史数据的需求，以下是按维护工作量从低到高排序的方案：

1. Power Query 自动快照记录（维护量最小） #

这是最省心的方案，一次设置后，每周更新数据时只需点一次刷新就能自动追加历史记录：

• 操作步骤：
  1. 新建空白工作表命名为「历史记录」。
  2. 选中要追踪的单元格（如资产净值、特定资产单元格），点击「数据」→「从表格/区域」，导入Power Query编辑器。
  3. 在编辑器中添加自定义列「记录日期」，公式用 =Date.From(DateTime.LocalNow()) 生成当前日期。
  4. 点击「主页」→「关闭并上载至」，选择「仅创建连接」并勾选「添加到数据模型」。
  5. 回到「历史记录」工作表，点击「数据」→「现有连接」，找到对应连接后右键选「属性」，勾选「刷新时启用背景刷新」和「打开文件时刷新数据」。
  6. 打开Power Query编辑器的「高级编辑器」，将最后一行代码修改为追加模式（把当前数据追加到历史记录，而非覆盖）：

     Table.Append(Table.LoadFromSheet("历史记录"), 你的查询表名称)
     

• 后续维护：每周更新完财务数据后，点击「数据」→「刷新全部」，历史记录自动更新，直接用「历史记录」表制作折线图即可。

2. VBA 触发式记录（维护量较低） #

用简单的VBA代码实现手动或自动触发记录，适合习惯用宏的用户：

• 操作步骤：
  1. 新建「历史记录」工作表，第一行设置表头：记录日期、资产净值、[需追踪的资产名称]。
  2. 按 Alt + F11 打开VBA编辑器，插入模块并粘贴以下代码（替换注释里的工作表和单元格引用）：

     Sub 记录财务数据()
         Dim wsSource As Worksheet, wsHistory As Worksheet
         Set wsSource = ThisWorkbook.Worksheets("你的财务表名称") '替换为实际财务工作表名
         Set wsHistory = ThisWorkbook.Worksheets("历史记录")
         
         Dim lastRow As Long
         lastRow = wsHistory.Cells(wsHistory.Rows.Count, "A").End(xlUp).Row + 1
         
         '写入日期和追踪数据
         wsHistory.Cells(lastRow, "A").Value = Date
         wsHistory.Cells(lastRow, "B").Value = wsSource.Range("B2").Value '替换为资产净值单元格
         wsHistory.Cells(lastRow, "C").Value = wsSource.Range("C2").Value '替换为其他资产单元格
     End Sub
     

  3. 回到Excel界面，插入一个形状（如矩形），右键选择「指定宏」关联上述代码，点击即可记录数据。
  4. 进阶设置：在ThisWorkbook对象中添加保存触发事件，实现保存文件时自动记录：

     Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
         Call 记录财务数据
     End Sub
     

• 后续维护：更新数据后点击按钮或保存文件即可自动记录，仅当新增追踪项目时需修改代码中的单元格引用。

3. Excel 365 动态数组公式记录（维护量中等） #

利用Excel 365的动态数组功能，通过公式自动保留历史数据，无需宏或Power Query：

• 操作步骤：
  1. 在「历史记录」工作表A列输入公式并下拉至足够行数（如50行，对应一年的周度记录）：

     =IF(ROW(A1)=1,TODAY(),IF(B1<>"",TODAY(),""))
     

  2. B列（资产净值）输入公式并下拉：

     =IF(A1<>"",IF(ROW(B1)=1,财务表!B2,IF(B1<>财务表!B2,财务表!B2,B1)),"")
     

  3. 其他追踪资产列参照B列公式修改单元格引用即可。
• 后续维护：需确保预留的行数足够，仅当数据发生变化时才会新增记录，适合固定周度更新的场景。

结论：无需手动保存历史数据即可实现需求，其中Power Query自动快照方案的维护工作量最小。

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