Google Sheets 实现每月指定日期自动按固定金额调整单元格数值

方案一：纯公式法（无需宏，稳定可靠） #

这种方法利用Excel日期函数自动计算累计还款次数，打开文件即自动更新数值，可无限期生效。

操作步骤： #

1. 确认关键参数：
   • 初始房贷余额：-100000（放在单元格D8）
   • 首次还款基准日期：比如你第一次还款的日期2024-01-23（可存在辅助单元格如E8，方便后续修改）
   • 每月还款额：500（可存在辅助单元格如F8，方便调整）
2. 在D8单元格输入以下公式：

   =-100000 + 500 * (DATEDIF(E8, TODAY(), "m") + IF(DAY(TODAY()) >= 23, 1, 0))
   

   若不想用辅助单元格，直接将基准日期写入公式：

   =-100000 + 500 * (DATEDIF(DATE(2024,1,23), TODAY(), "m") + IF(DAY(TODAY()) >= 23, 1, 0))
   

公式说明： #

• DATEDIF(基准日期, TODAY(), "m")：计算基准日期到当前日期的整月数
• IF(DAY(TODAY()) >=23,1,0)：如果当前日期已过当月23日，额外加1次还款，确保当月还款生效
• 逻辑：初始余额加上累计还款总额（还款次数×每月还款额），因初始余额为负数，加正数相当于余额绝对值减少

方案二：VBA宏自动触发（每月23日自动更新） #

如果需要每月23日自动执行更新（若无需打开文件触发，需配合Windows任务计划定时打开工作簿）

操作步骤： #

1. 按Alt + F11打开VBA编辑器
2. 双击左侧面板的ThisWorkbook，粘贴以下代码：

   Sub UpdateMortgageBalance()
       Dim ws As Worksheet
       Set ws = ThisWorkbook.Worksheets("Sheet1") '替换为你的实际工作表名称
       Dim lastUpdate As Date
       
       'G8用作记录最后一次更新日期，避免同一天重复更新
       If IsEmpty(ws.Range("G8")) Then
           ws.Range("G8").Value = DateSerial(Year(Date), Month(Date), 22) '初始设为上月22日
       End If
       lastUpdate = ws.Range("G8").Value
       
       '检查是否到了新的还款日
       If (Day(Date) >= 23 And Month(Date) > Month(lastUpdate)) Or _
          (Day(Date) >= 23 And Year(Date) > Year(lastUpdate)) Then
           ws.Range("D8").Value = ws.Range("D8").Value + 500 '余额减少500
           ws.Range("G8").Value = Date '更新最后更新日期
       End If
       
       '设置下个月23日自动触发任务
       Dim nextRunDate As Date
       nextRunDate = DateSerial(Year(Date), Month(Date) + 1, 23)
       Application.OnTime nextRunDate, "UpdateMortgageBalance"
   End Sub
   
   '工作簿打开时启动定时任务
   Private Sub Workbook_Open()
       Dim nextRunDate As Date
       If Day(Date) < 23 Then
           nextRunDate = DateSerial(Year(Date), Month(Date), 23)
       Else
           nextRunDate = DateSerial(Year(Date), Month(Date) + 1, 23)
       End If
       Application.OnTime nextRunDate, "UpdateMortgageBalance"
   End Sub
   
   '关闭工作簿时取消定时任务，避免残留
   Private Sub Workbook_BeforeClose(Cancel As Boolean)
       On Error Resume Next
       Dim nextRunDate As Date
       If Day(Date) < 23 Then
           nextRunDate = DateSerial(Year(Date), Month(Date), 23)
       Else
           nextRunDate = DateSerial(Year(Date), Month(Date) + 1, 23)
       End If
       Application.OnTime nextRunDate, "UpdateMortgageBalance", Schedule:=False
   End Sub
   

3. 将文件保存为.xlsm格式（启用宏的工作簿）

注意事项： #

• 替换代码中的Sheet1为你的实际工作表名称
• 首次使用时，G8会自动生成初始日期，无需手动输入
• 若要实现无需打开文件自动更新，需配合Windows任务计划定时启动该工作簿

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