Excel VBA根据单元格值动态调整公式计算用户自定义简单移动平均

以下是修改后的可动态适配周期的完整代码：

Sub 自定义周期SMA计算()
    Dim period As Integer
    Dim startRow As Integer
    Dim lastRow As Integer
    
    ' 读取D1单元格的周期值
    On Error Resume Next
    period = Range("D1").Value
    On Error GoTo 0
    
    ' 校验输入合法性
    If period < 1 Or Not IsNumeric(Range("D1").Value) Then
        MsgBox "请在D1单元格输入大于0的正整数作为周期", vbExclamation
        Exit Sub
    End If
    
    ' 计算SMA起始行：周期为n时，第一个可计算的行是n+1（前n行历史数据不足）
    startRow = period + 1
    ' 动态获取B列最后一个有数据的行，适配不同长度的数据源
    lastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    ' 避免起始行超过数据最大行
    If startRow > lastRow Then
        MsgBox "周期大于现有数据量，请调小周期或补充数据", vbExclamation
        Exit Sub
    End If
    
    ' 写入第一个SMA公式，偏移量随周期自动调整
    Cells(startRow, "C").FormulaR1C1 = "=AVERAGE(R[-" & (period - 1) & "]C[-1]:RC[-1])"
    ' 自动填充到数据最后一行
    Cells(startRow, "C").AutoFill Destination:=Range(Cells(startRow, "C"), Cells(lastRow, "C"))
    
    ' 选中生成的SMA区域，匹配原操作习惯
    Range(Cells(startRow, "C"), Cells(lastRow, "C")).Select
End Sub

功能说明 #

• 动态读取D1单元格的周期值，支持任意正整数周期，无需修改代码硬编码参数
• 自带输入合法性校验，避免非数字、周期过小/过大导致的公式报错
• 自动适配数据源长度，不需要手动修改填充终止行，B列新增数据后重新运行即可自动扩展计算范围
• 公式逻辑自动适配周期：比如输入周期50时，公式自动变为=AVERAGE(R[-49]C[-1]:RC[-1])，起始计算单元格自动定位到C51，完全匹配需求

进阶优化 #

如果需要实现修改D1数值后自动计算、无需手动运行宏，可以在当前工作表的代码模块中加入如下事件触发代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 只有修改D1单元格时触发计算
    If Not Intersect(Target, Range("D1")) Is Nothing Then
        Call 自定义周期SMA计算
    End If
End Sub

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