如何实现跨工作表区域求和，让年假余额公式无需手动调整

我有一个考勤工作簿，每个双周工资周期对应独立工作表。员工每周期录入工作/休假时长，年假余额会自动结转，每年最多结转240小时，超出部分年度初作废。

需要在每个工作表中计算**“用则留不用则废”年假余额**，原公式为：

=SUM('PP03:PP02'I24)+I26-240

该公式对当前工作表右侧所有表的I24（当期赚取年假）求和，加当前余额后减240。但复制工作表后需要手动修改公式范围，目标是创建一个模板表后复制25次，无需手动更新任何内容。

尝试过编写UDF，但切换工作表时函数不会自动执行，且存在其他问题。

方法1：修正自定义函数（UDF）实现自动更新 #

原UDF依赖ActiveSheet且未标记易失性，导致切换工作表不更新。修正后的函数会自动识别公式所在工作表，并触发Excel自动计算：

Function calcUseOrLose() As Variant
    ' 标记为易失性，强制Excel在工作表切换/数据变更时重新计算
    Application.Volatile True
    
    Dim currentWs As Worksheet
    Set currentWs = Application.Caller.Parent ' 获取公式所在的工作表
    Dim lastWsName As String
    lastWsName = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name
    
    ' 构建正确的求和公式字符串
    Dim formulaText As String
    formulaText = "=SUM('" & currentWs.Name & ":" & lastWsName & "'!I24) + I26 - 240"
    
    ' 返回计算结果
    calcUseOrLose = Application.Evaluate(formulaText)
End Function

用法：在目标单元格输入=calcUseOrLose()，复制工作表后，函数会自动适配当前工作表的名称，无需手动修改。

方法2：利用工作表激活事件自动生成公式 #

如果不想使用UDF，可以通过工作表事件，在切换到工作表时自动填充正确的公式：

1. 按Alt+F11打开VBA编辑器
2. 在左侧工程窗口，选中模板工作表，右键选择「查看代码」
3. 粘贴以下代码：

Private Sub Worksheet_Activate()
    ' 替换为你要显示余额的单元格地址，比如I27
    Dim targetRange As Range
    Set targetRange = Me.Range("I27")
    
    Dim lastSheetName As String
    lastSheetName = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name
    
    ' 写入动态适配的公式
    targetRange.Formula = "=SUM('" & Me.Name & ":" & lastSheetName & "'!I24) + I26 - 240"
End Sub

说明：复制模板工作表时，事件代码会随工作表一起复制，每次切换到该工作表时，目标单元格会自动生成正确的公式。

方法3：动态数组公式（适用于Excel 365/2021及以上） #

如果你的Excel版本支持动态数组，可直接使用公式实现，无需VBA（需启用宏，工作簿保存为.xlsm格式）：

=SUM(INDIRECT("'"&MID(CELL("filename"),FIND("]",CELL("filename"))+1,99)&"'!I24:"&INDEX(GET.WORKBOOK(1),SHEETS())&"'!I24")) + I26 - 240

说明：

• CELL("filename")获取当前工作表名称
• GET.WORKBOOK(1)获取所有工作表名称，INDEX(...,SHEETS())定位到最后一个工作表
• 公式会自动适配当前工作表，复制后无需修改

• 复制工作表时，确保I24（当期赚取年假）、I26（当前余额）的单元格引用为相对引用（或按需设置绝对引用）
• 年度重置时，需手动调整最后一个工作表的范围，或在VBA/公式中加入年度判断逻辑
• 若工作表名称包含空格、特殊字符，公式中的单引号必须保留，避免引用错误

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