在 Excel 中计算移动平均线

Question

我想计算一列最后（例如 20）个数字的移动平均值。问题是该列的某些单元格可能为空，应忽略它们。示例：

 A
175
154

188
145
155

167
201

最后三个的移动平均值为 (155+167+201)/3。我尝试使用平均值、偏移量、索引来实现此目的，但我根本不知道如何实现。我对宏有点熟悉，所以这样的解决方案可以很好地工作：=MovingAverage(A1;3)

感谢您提供任何提示或解决方案！

Answer 1

{=SUM(($A$1:A9)*(ROW($A$1:A9)>LARGE((ROW($A$1:A9))*(NOT(ISBLANK($A$1:A9))),3+1)))/3}

使用 control+shift+enter 输入使其成为数组公式。这将找到最新的三个值。如果您想要更多或更少，请将公式中“3”的两个实例更改为您想要的任何值。

LARGE((ROW($A$1:A9))*(NOT(ISBLANK($A$1:A9))),3+1)

此部分返回具有值的所有单元格中第四高的行号，或者在示例中返回 5，因为第 6、8 和 9 行是具有值的第一到第三高的行。

(ROW($A$1:A9)>LARGE((ROW($A$1:A9))*(NOT(ISBLANK($A$1:A9))),3+1))

该部分根据行号是否大于第四大行号返回 9 个 TRUE 或 FALSE。

($A$1:A9)*(ROW($A$1:A9)>LARGE((ROW($A$1:A9))*(NOT(ISBLANK($A$1:A9))),3+1))

这会将 A1:A9 中的值乘以这 9 个 TRUE 或 FALSE。 TRUE 转换为 1，FALSE 转换为 0。这留下了一个像这样的 SUM 函数

=SUM({0;0;0;0;0;155;0;167;201})/3

因为所有高于 155 的值都不满足行数标准，所以 get 乘以零。

Answer 2

如果您要使用 UDF，则只有在参数包含您要处理的所有数据范围时，它才会在您更改数据时正确重新计算。

这是一个处理整个列并包含一些错误处理的移动平均 UDF。
您可以通过在单元格中输入公式 =MovingAverage(A:A,3) 来调用它。

Function MovingAverage(theRange As Range, LastN As Long) As Variant
    Dim vArr As Variant

    Dim j As Long
    Dim nFound As Long
    Dim dSum As Double

    On Error GoTo Fail
    MovingAverage = CVErr(xlErrNA)
    '
    ' handle entire column reference
    '
    vArr = Intersect(Application.Caller.Parent.UsedRange, theRange).Value2

    If IsArray(vArr) And LastN > 0 Then
        For j = UBound(vArr) To 1 Step -1
            ' skip empty/uncalculated
            If Not IsEmpty(vArr(j, 1)) Then
                ' look for valid numbers
                If IsNumeric(vArr(j, 1)) Then
                    If Len(Trim(CStr(vArr(j, 1)))) > 0 Then
                        nFound = nFound + 1
                        If nFound <= LastN Then
                            dSum = dSum + CDbl(vArr(j, 1))
                        Else
                            Exit For
                        End If
                    End If
                End If
            End If
        Next j

        If nFound >= LastN Then MovingAverage = dSum / LastN

    End If
    Exit Function
Fail:
    MovingAverage = CVErr(xlErrNA)
End Function

Answer 3

只是一个快速解决方案：
假设您的数字位于单元格 A2:A10 上，请在 B10 中输入以下公式：

=IF(COUNT(A8:A10)=3,AVERAGE(A8:A10),IF(COUNT(A7:A10)=3,AVERAGE(A7:A10),"too many blanks"))

向上拖动公式即可得到移动平均值

如果存在两个连续空白的可能性，您可以嵌套另一个 if，超过这个值，此解决方案就变得太复杂的

Answer 4

我用 VBA 写了一个简短的脚本。希望它能达到你想要的效果。在这里：

Function MovingAverage(ByVal r As String, ByVal i As Integer) As Double
Dim rng As Range, counter As Long, j As Integer, tmp As Double
    Set rng = Range(r)
    counter = 360
    j = 0
    tmp = 0
    While j < i + 1 And counter > 0
        If Len(rng.Offset(j, 0)) > 0 Then
            tmp = tmp + rng.Offset(j, 0).Value
        End If
        j = j + 1
        counter = counter - 1
    Wend
    MovingAverage = CDbl(tmp / i)
End Function

1) 我已将限制设置为 360 个单元格。这意味着该脚本不会查找超过 360 个单元格。如果您想更改它，请更改计数器的初始值。

2) 脚本返回未四舍五入的平均值。将最后一行更改为
MovingAverage = Round(CDbl(tmp / i),2)

3) 使用方法如你所愿，只需输入 =MovingAverage("a1";3) 即可细胞。

欢迎任何评论。