vba 在 Excel 中计算移动平均线
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5283466/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calculate Moving Average in Excel
提问by Daniel Lidstr?m
I want to calculate a moving average of the last, say 20, numbers of a column. A problem is that some of the cells of the column may be empty, they should be ignored. Example:
我想计算一列的最后一个（比如 20 个）的移动平均值。一个问题是该列的某些单元格可能是空的，它们应该被忽略。例子：
A
175
154
188
145
155
167
201
A moving average of the last three would be (155+167+201)/3. I've tried to implement this using average, offset, index, but I simply don't know how. I'm a little bit familiar with macros, so such a solution would work fine: =MovingAverage(A1;3)
最后三个的移动平均线将是 (155+167+201)/3。我尝试使用平均值、偏移量、索引来实现这一点，但我根本不知道如何实现。我对宏有点熟悉，所以这样的解决方案可以正常工作：=MovingAverage(A1;3)
Thanks for any tips or solutions!
感谢您提供任何提示或解决方案！
回答by Dick Kusleika
{=SUM(($A:A9)*(ROW($A:A9)>LARGE((ROW($A:A9))*(NOT(ISBLANK($A:A9))),3+1)))/3}
Enter this with control+shift+enter to make it an array formula. This will find the latest three values. If you want more or less, change the two instances of '3' in the formula to whatever you want.
使用 control+shift+enter 输入它以使其成为数组公式。这将找到最新的三个值。如果您想要更多或更少，请将公式中“3”的两个实例更改为您想要的任何内容。
LARGE((ROW($A:A9))*(NOT(ISBLANK($A:A9))),3+1)
This part returns the 4th highest row number of all the cells that have a value, or 5 in your example because rows 6, 8, and 9 are the 1st through 3rd highest rows with a value.
这部分返回具有值的所有单元格的第 4 大行号，或在您的示例中为 5，因为第 6、8 和 9 行是具有值的第 1 至第 3 行。
(ROW($A:A9)>LARGE((ROW($A:A9))*(NOT(ISBLANK($A:A9))),3+1))
This part returns 9 TRUEs or FALSEs based on whether the row number is larger than the 4th largest.
这部分根据行号是否大于第 4 大值返回 9 个 TRUE 或 FALSE。
($A:A9)*(ROW($A:A9)>LARGE((ROW($A:A9))*(NOT(ISBLANK($A:A9))),3+1))
This multiplies the values in A1:A9 by those 9 TRUEs or FALSEs. TRUEs are converted to 1 and FALSEs to zero. This leaves a SUM function like this
这将 A1:A9 中的值乘以那 9 个 TRUE 或 FALSE。TRUE 转换为 1，FALSE 转换为零。这留下了这样的 SUM 函数
=SUM({0;0;0;0;0;155;0;167;201})/3
Because all the values above 155 don't satisfy the row number criterion, the get multiplied by zero.
因为 155 以上的所有值都不满足行号标准，所以 get 乘以零。
回答by Charles Williams
If you are going to use a UDF it will only recalculate correctly when you change the data if the parameters include all the range of data you want to handle.
如果您打算使用 UDF，如果参数包括您要处理的所有数据范围，它只会在您更改数据时正确重新计算。
Here is a moving average UDF that handles entire columns and contains some error handling.
You can call it using by entering the formula =MovingAverage(A:A,3)into a cell.
这是一个处理整列并包含一些错误处理的移动平均 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
回答by momobo
Just a quick solution: Supposing your numbers are on the cells A2:A10, put in B10 the following formula:
只是一个快速的解决方案：假设您的数字在单元格 A2:A10 上，在 B10 中输入以下公式：
=IF(COUNT(A8:A10)=3,AVERAGE(A8:A10),IF(COUNT(A7:A10)=3,AVERAGE(A7:A10),"too many blanks"))
Dragging up the formula you get the moving average
向上拖动公式，您将获得移动平均线
If there is the possibility of two consecutive blank you could nest another if, more than that and this solution became too complicated
如果有两个连续空白的可能性，你可以嵌套另一个如果，不止于此，这个解决方案变得太复杂了
回答by MP?kalski
I have written a short script in VBA. Hopefull it does what you want. Here you are:
我用 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) I have set limit to 360 cells. It means that the script will not look for more than 360 cells. If you want to change it then change the initial value of counter.
1）我已将限制设置为 360 个单元格。这意味着脚本不会查找超过 360 个单元格。如果要更改它，请更改counter的初始值。
2) The script returns not rounded average. Change the last row to MovingAverage = Round(CDbl(tmp / i),2)
2) 脚本返回的不是四舍五入的平均值。将最后一行更改为 MovingAverage = Round(CDbl(tmp / i),2)
3) The use is just like you wanted, so just type =MovingAverage("a1";3)into the cell.
3) 用法如您所愿，因此只需在单元格中输入=MovingAverage("a1";3) 即可。
Any comments are welcome.
欢迎提出任何意见。
