VBA 分割求平均值

最新推荐文章于 2024-10-12 09:59:10 发布

转载最新推荐文章于 2024-10-12 09:59:10 发布 · 1.4k 阅读

文章标签：

#vba

VBA 专栏收录该内容

2 篇文章

订阅专栏

Function StrToArr(str As String, delimiter As String) As Variant

Dim ar, nar()
If (Trim(str) = "") Then
    ReDim nar(0)
    StrToArr = nar
    GoTo EndR
End If

ar = Split(str, delimiter)

ReDim nar(UBound(ar))
For i = LBound(ar) To UBound(ar)
    nar(i) = CDbl(ar(i))

Next
EndR:
StrToArr = nar ' Application.Average(nar) ' nar

End Function
Function ArrAVG(arr1 As Variant, arr2 As Variant, arr3 As Variant, arr4 As Variant) As Double

Dim Narr, AArr(3)
Dim index, count As Integer
count = UBound(arr1) + UBound(arr2) + UBound(arr3) + UBound(arr4) + 4

ReDim Narr(count)
AArr(0) = arr1
AArr(1) = arr2
AArr(2) = arr3
AArr(3) = arr4
index = 0

For i = 0 To 3
    For j = LBound(AArr(i)) To UBound(AArr(i))
        If (CDbl(AArr(i)(j)) <> 0) Then
            Narr(index) = CDbl(AArr(i)(j))
            index = index + 1
        End If
    Next

Next


ArrAVG = Application.Average(Narr)
End Function
Function Get4AVG(s1 As String, s2 As String, s3 As String, s4 As String) As Double
Debug.Print s1


Dim d As String
d = "/"
Get4AVG = ArrAVG(StrToArr(s1, d), StrToArr(s2, d), StrToArr(s3, d), StrToArr(s4, d))


End Function


Sub test()
c = StrToArr("4.10/3.87/3.74", "/")
'Debug.Print c
d = Get4AVG("4.10/3.87/3.74", "4.08/3.88/3.78", "", "4.09/3.90/3.79")
Debug.Print d

End Sub