office交流网--QQ交流群号

Access培训群:792054000         Excel免费交流群群:686050929          Outlook交流群:221378704    

Word交流群:218156588             PPT交流群:324131555

Access vba自定义求标准差函数MyStdevp(同样适用于Excel VBA, VB6)

2020-04-16 08:00:00
zstmtony
原创
1052

Access自带了DStdevp 聚合函数求标准差,但只能对表中字段求标准差,无法对提供的多个数字参数求标准差

所以自己做了一个求标准差的自定义函数,同样适用于Access VBA, Excel VBA, VB6


'MyStdevp 求标准差

Public Function MyStdevp(Optional dblNum1 As Variant, Optional dblNum2 As Variant, Optional dblNum3 As Variant, Optional dblNum4 As Variant, Optional dblNum5 As Variant)

Dim rs As Object
Dim dblAvg As Double
Dim dblSum As Double
Dim i As Integer
dblAvg = MyAvg(dblNum1, dblNum2, dblNum3, dblNum4, dblNum5)
If Not IsMissing(dblNum1) Then
i = i + 1
dblSum = dblSum + (dblNum1 - dblAvg) ^ 2

End If

If Not IsMissing(dblNum2) Then
i = i + 1
dblSum = dblSum + (dblNum2 - dblAvg) ^ 2

End If

If Not IsMissing(dblNum3) Then
i = i + 1
dblSum = dblSum + (dblNum3 - dblAvg) ^ 2

End If


If Not IsMissing(dblNum4) Then
i = i + 1
dblSum = dblSum + (dblNum4 - dblAvg) ^ 2

End If


If Not IsMissing(dblNum5) Then
i = i + 1
dblSum = dblSum + (dblNum5 - dblAvg) * (dblNum5 - dblAvg)

End If

If i > 0 Then
MyStdevp = Sqr(dblSum / i)
Else
MyStdevp = 0
End If


End Function


标准差的算法


    分享