Excel VBA 如何从数组动态提取指定范围的值实现滚动计算？

原来的testArr过程报错的核心问题是：你将拼接好的索引字符串RowsToExtract直接传入Array()函数，不会自动转换为数值型索引数组，Application.Index无法识别字符串格式的索引集合，因此触发类型不匹配错误。

方案1：通用滚动窗口数组方案（支持求和外的任意聚合运算） #

该方案全程在内存中操作数组，无需和单元格交互，适合需要对滚动窗口执行求和、平均、中位数等多种计算的场景，比Range操作效率提升10~100倍。

Sub testArrOptimized(Lookback As Long)
    Dim MyArr As Variant, OutputArr As Variant
    Dim i As Long, k As Long, n As Long
    Dim windowSum As Double ' 其他计算可替换对应变量
    
    ' 一次性读取所有数据源到数组
    With Range("A2")
        MyArr = Range(.Offset(0, 0), .End(xlDown)).Value
    End With
    n = UBound(MyArr, 1)
    
    ' 初始化输出数组，前lookback-1位可以留空或者填0，根据需求调整
    ReDim OutputArr(1 To n, 1 To 1)
    
    For i = Lookback To n
        ' 直接遍历当前窗口的数组元素计算，无需构造索引
        windowSum = 0
        For k = i - Lookback + 1 To i
            windowSum = windowSum + MyArr(k, 1)
            ' 如果是其他计算，比如取最大/最小、计算方差等，这里改逻辑即可
        Next k
        OutputArr(i, 1) = windowSum
    Next i
    
    ' 一次性把结果写入单元格
    Range("B2").Resize(n, 1).Value = OutputArr
End Sub

方案2：优化版滚动求和（仅求和场景，效率更高） #

如果只需要做滚动求和，可以用递推累加的方式，避免每次循环整个窗口，时间复杂度从O(n*lookback)降到O(n)，超大数据量下优势更明显：

Sub testRollingSumFast(Lookback As Long)
    Dim MyArr As Variant, OutputArr As Variant
    Dim i As Long, n As Long
    Dim runningSum As Double
    
    With Range("A2")
        MyArr = Range(.Offset(0, 0), .End(xlDown)).Value
    End With
    n = UBound(MyArr, 1)
    ReDim OutputArr(1 To n, 1 To 1)
    
    ' 先计算第一个窗口的和
    For i = 1 To Lookback
        runningSum = runningSum + MyArr(i, 1)
    Next i
    OutputArr(Lookback, 1) = runningSum
    
    ' 后续窗口递推：减去移出窗口的元素，加新进入窗口的元素
    For i = Lookback + 1 To n
        runningSum = runningSum - MyArr(i - Lookback, 1) + MyArr(i, 1)
        OutputArr(i, 1) = runningSum
    Next i
    
    Range("B2").Resize(n, 1).Value = OutputArr
End Sub

• 输入列有空值的话可以提前加判断过滤，避免求和结果异常
• 如果需要支持二维数组的滚动窗口，只要修改数组的下标访问逻辑即可

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