Excel VBA AVERAGE（可变范围）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45410281/
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
Excel VBA AVERAGE(variable range)
提问by CCM
I'm trying to use an Excel formula that averages the last 12 month values and compares it to the current month value (in this case RC[-2]-RC[-1]and a 30% variance).
我正在尝试使用一个 Excel 公式，该公式对过去 12 个月的值求平均值并将其与当前月份的值进行比较（在本例中RC[-2]-RC[-1]为 30% 的方差）。
The issue is that the row changes, it is nested in a loop.
问题是行发生了变化，它嵌套在一个循环中。
Below the code:
代码下方：
Dim i As Long
Dim o As Double
Dim p As Double
'some code here
For i = 1 To n
Selection.Value = "=RC[-2]-RC[-1]"
o = ActiveCell.Value
p = Application.Formula.Average(Range("RC[-13]", "RC[-2]"))
If (o >= (p + p * 3 / 10)) Or (o < (p + p * 3 / 10)) Then
ActiveCell.Font.Color = vbRed
End If
ActiveCell.Offset(1, 0).Select
Next i
Any ideas on how to define that average without a separate average function ?
关于如何在没有单独平均函数的情况下定义该平均值的任何想法？
采纳答案by CCM
Found a workaround with ActiveCell.Offset(0, 1).Value = "=AVERAGE(RC[-13]:RC[-2])".
找到了一个解决方法ActiveCell.Offset(0, 1).Value = "=AVERAGE(RC[-13]:RC[-2])"。
Thanks for your time guys!
感谢您的时间！
回答by Micha? Turczyn
Change Selection.Value = "=RC[-2]-RC[-1]"to Selection.Value = Offset(0, -2).Value - Offset(0, -1).Value.
更改Selection.Value = "=RC[-2]-RC[-1]"为Selection.Value = Offset(0, -2).Value - Offset(0, -1).Value。
Then, declare some range variable: Dim rng As Range, then set it to range with data from last 12 months and calculate average:
然后，声明一些范围变量：Dim rng As Range，然后将其设置为包含过去 12 个月数据的范围并计算平均值：
Set rng = Range(Selection.Offset(0, -13), Selection.Offset(0, -2))
p = Application.Average(rng)
