vba VBA中的均方根（rms）函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18212876/
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
Root Mean Square (rms) function in VBA?
提问by WATERflowTech
So I'm calculating basic statistics in my worksheet and it includes code such as:
所以我在我的工作表中计算基本统计数据，它包括如下代码：
xxx = Application.worksheetfunction.average(etc etc etc
xxx = Application.worksheetfunction。平均（等等等等等等
yyy = Application.worksheetfunction.min(etc etc etc
yyy = Application.worksheetfunction。分钟（等等等等等等
zzz = Application.worksheetfunction.max(etc etc etc
zzz = Application.worksheetfunction。最大（等等等等等等
My question: Is there an RMS equivalent function where I can simply plug it in place of where I have 'average, min, max' functions in that code? And if there isn't then what would be the most efficient means to code in to find RMS solutions?
我的问题：是否有一个 RMS 等效函数，我可以简单地将其插入该代码中具有“平均值、最小值、最大值”函数的位置？如果没有，那么编码以找到 RMS 解决方案的最有效方法是什么？
I hope I've stated the goal clearly enough. I'm curious as to whether or not there is a predefined RMS function for VBA or whether or not I've got to create some sort of user defined function? ~ That of which I'm fairly new to as well so if there isn't a simple line of code to write for this, I'll have to do more reading on UDF's.
我希望我已经足够清楚地说明了目标。我很好奇是否有 VBA 的预定义 RMS 函数，或者我是否必须创建某种用户定义的函数？~我对它也很陌生，所以如果没有为此编写简单的代码行，我将不得不更多地阅读UDF。
EDIT:
编辑：
I've got around 30,000 rows, and for simplicity's sake: imagine two columns. Column A has the year i.e. 1941 or anything else through 2008. Column B is a numeric value. I'm just trying to put code together that gives decade summaries of Average, Min, Max, and the RMS values.
我有大约 30,000 行，为简单起见：想象一下两列。A 列包含年份，即 1941 年或其他任何到 2008 年的时间。B 列是一个数值。我只是想把代码放在一起，给出平均值、最小值、最大值和 RMS 值的十年摘要。
采纳答案by Lance Roberts
You can do the average with
你可以做平均
=SQRT(SUMSQ(A:A)/COUNTA(range))
or in VBA:
或在 VBA 中：
r = (Application.WorksheetFunction.SumSq(Range("A:A")) / Range("A:A").Count) ^ (1 / 2)
回答by Cor_Blimey
A VBA function that accepts arrays (any rank) and ranges with multiple areas (a discontinuous range like A4:B6,C11:D15), or even a union of ranges in a formula. It skips non number datatypes (including dates, boolean, blanks etc).
一个 VBA 函数，它接受数组（任何等级）和具有多个区域的范围（不连续范围，如 A4:B6,C11:D15），甚至是公式中范围的联合。它跳过非数字数据类型（包括日期、布尔值、空白等）。
You can use it in VBA code, or as a UDF in a worksheet formula such as:
您可以在 VBA 代码中使用它，也可以在工作表公式中将其用作 UDF，例如：
"=RMS(A1:A10)" (basic usage)
“=RMS(A1:A10)”（基本用法）
"=RMS(A1:A10,C1:C10)" (multiple ranges (or arrays for that matter))
“=RMS(A1:A10,C1:C10)”（多个范围（或数组））
"{=RMS({1,2,3,4})}" (array formula entered with Ctrl+shift+enter)
"{=RMS({1,2,3,4})}"（使用 Ctrl+shift+enter 输入的数组公式）
Function RMS(ParamArray args()) As Double
Dim arg, arr, area As Range, ss As Double, n As Long
For Each arg In args
If TypeOf arg Is Range Then
For Each area In arg.Areas
arr = area.value
If VarType(arr) < vbArray Then
queryRmsElements Array(arr), ss, n
Else
queryRmsElements arr, ss, n
End If
Next area
ElseIf VarType(arg) > vbArray Then
queryRmsElements arg, ss, n
Else
Err.Raise 1, "RMS", "Invalid Argument"
End If
Next arg
RMS = (ss / n) ^ 0.5
End Function
Private Sub queryRmsElements(ByRef elements, ByRef ss As Double, ByRef n As Long)
Static element As Variant
'Enumerate to cover rank > 1 (vs. Iterate)
For Each element In elements
Select Case VarType(element)
Case VbVarType.vbByte, _
VbVarType.vbCurrency, _
VbVarType.vbDecimal, _
VbVarType.vbDouble, _
VbVarType.vbInteger, _
VbVarType.vbLong, _
VbVarType.vbSingle
ss = element ^ 2 + ss
n = n + 1
Case Else
End Select
Next element
End Sub
回答by Anderson Wanselowski
This one worked for me:
这个对我有用：
Function RMS(Intervalo As Range)
Dim SomaQ As Double
Dim Tamanho As Integer
SomaQ = 0
Tamanho = Intervalo.Count
SomaQ = Application.WorksheetFunction.SumSq(Intervalo)
RMS = Sqr(SomaQ / Tamanho)
End Function
