如何借助公式/脚本实现模板修订历史追踪及版本切换功能？

完全可以实现，下面分**公式方案（适合无编程基础场景）和脚本方案（适合自动化需求）**给你具体操作步骤：

一、公式方案（以Excel为例） #

1. 搭建版本存储区 #

新建一个名为版本历史的工作表，第一列（A列）存版本号（如Revision 1、Revision 2），后续列对应模板的所有输入字段（比如B列是姓名、C列是金额等）。

2. 手动触发版本保存 #

在模板工作表添加一个辅助单元格（比如Z1），输入保存版本触发保存逻辑：

• 用COUNTA(版本历史!A:A)统计已有版本数，确定新版本的行号（比如=COUNTA(版本历史!A:A)+1）
• 用INDEX+IF组合，将当前模板的数据同步到版本历史表的新行，示例公式（以B列数据为例）：

  =IF($Z$1="保存版本", 模板!B2, "")
  

  （注：设置完后，输入保存版本再删除，就能完成一次版本快照）

3. 下拉切换版本 #

• 在模板工作表添加数据验证下拉框，数据源选择版本历史!A:A的版本号
• 用XLOOKUP函数根据选中的版本号，拉取对应版本的数据填充模板，示例：

  =XLOOKUP($A$1, 版本历史!A:A, 版本历史!B:B)
  

  （$A$1是下拉框所在单元格，B:B对应模板的字段列）

4. 差异高亮 #

用条件格式实现差异对比：

• 选中模板所有数据单元格，设置条件格式公式：

  =A1<>XLOOKUP("Revision 1", 版本历史!A:A, 版本历史!B:B)
  

  （把与Revision 1不同的单元格标色，方便管理层查看差异）

二、脚本方案（以Excel VBA为例） #

1. 自动保存版本 #

打开VBA编辑器（Alt+F11），在ThisWorkbook中添加保存事件：

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim wsHistory As Worksheet
    Dim newRow As Long
    Set wsHistory = ThisWorkbook.Worksheets("版本历史")
    
    ' 获取新版本行号
    newRow = wsHistory.Cells(wsHistory.Rows.Count, "A").End(xlUp).Row + 1
    ' 写入版本号（自动生成）
    wsHistory.Cells(newRow, "A").Value = "Revision " & newRow - 1
    ' 复制当前模板数据到版本历史
    ThisWorkbook.Worksheets("模板").Range("B2:Z100").Copy wsHistory.Cells(newRow, "B")
End Sub

2. 下拉切换功能 #

在模板工作表插入ActiveX组合框，然后添加如下代码：

Private Sub ComboBox1_Change()
    Dim wsHistory As Worksheet
    Dim matchRow As Long
    Set wsHistory = ThisWorkbook.Worksheets("版本历史")
    
    ' 查找选中版本的行号
    matchRow = Application.Match(Me.ComboBox1.Value, wsHistory.Range("A:A"), 0)
    ' 填充模板数据
    wsHistory.Range("B" & matchRow & ":Z" & matchRow).Copy ThisWorkbook.Worksheets("模板").Range("B2")
End Sub

' 加载版本号到下拉框
Private Sub Worksheet_Activate()
    Dim wsHistory As Worksheet
    Set wsHistory = ThisWorkbook.Worksheets("版本历史")
    Me.ComboBox1.List = wsHistory.Range("A2:A" & wsHistory.Cells(wsHistory.Rows.Count, "A").End(xlUp).Row).Value
End Sub

3. 一键对比差异 #

添加一个按钮，绑定如下子过程，直接高亮两个版本的差异：

Sub CompareVersions()
    Dim wsHistory As Worksheet
    Dim rev1Row As Long, rev2Row As Long
    Dim rng As Range
    Set wsHistory = ThisWorkbook.Worksheets("版本历史")
    
    rev1Row = Application.Match("Revision 1", wsHistory.Range("A:A"), 0)
    rev2Row = Application.Match("Revision 2", wsHistory.Range("A:A"), 0)
    
    ' 对比并高亮差异单元格
    For Each rng In wsHistory.Range("B" & rev1Row & ":Z" & rev1Row)
        If rng.Value <> wsHistory.Cells(rev2Row, rng.Column).Value Then
            ThisWorkbook.Worksheets("模板").Cells(rng.Row - rev1Row + 2, rng.Column).Interior.ColorIndex = 6
        End If
    Next rng
End Sub

注意事项 #

• 公式方案操作简单，但版本保存需要手动触发，适合数据量小、版本少的场景
• 脚本方案支持自动保存和更灵活的差异对比，适合频繁修订的模板
• 记得保护版本历史工作表，避免误删历史数据

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