excel - VBA 滚动平均值

Question

我对 VBA 很陌生。我正在尝试计算一个范围的滚动平均值，我确定我的错误是非常愚蠢的

Function Rolling_Mean(Prices as Range)

Dim window as Long, i As Integer, temp_sum as Long
Dim means() as Long

window = 10
temp_sum = 0
ReDim means(1 to 253)

For i = 1 to 253
    temp_sum = temp_sum + Prices(i)

    If i Mod window = 0 Then
        means(i) = temp_sum / 10
        temp_sum = 0
    End If
Next

Rolling_Mean = means

End Function

我不断收到错误或 0 数组。我认为我的问题是我如何尝试访问 Prices .我也想知道如何实现语法 AVERAGE(Prices(1) to Prices(10))因为这也会有很大帮助。
这将在带有 =Rolling_Mean(B2:B253) 的工作表中运行

Answer 1

这似乎对我有用。添加窗口作为第二个参数。

Function Rolling_Mean(Prices As Range, window As Long)
    Dim i As Long, sum As Double
    Dim means(), data
    
    data = Prices.Value 'assuming a single column of cells....
    ReDim means(1 To UBound(data, 1), 1 To 1)
    
    For i = 1 To UBound(data, 1)
        sum = sum + data(i, 1)
        If i >= window Then
            means(i, 1) = sum / window
            sum = sum - data(i - window + 1, 1) 'subtract value from trailing end of window
        Else
            means(i, 1) = ""
        End If
    Next i
    Rolling_Mean = means
End Function

如果您的 Excel 版本没有“自动溢出”，那么您需要将其作为数组公式输入(Ctrl+Shift+Enter)