For x = 1 To FN
ReDim Crr(x)
Crr(x) = Arr(x) - Brr(x)
Next作者: ML089 時間: 2013-11-29 17:51
3#不對 REDIM 不能在迴圈裡
FN = Arr.Count
ReDim Crr(x)
For x = 1 To FN
Crr(x) = Arr(x) - Brr(x)
Next作者: ML089 時間: 2013-11-29 17:55
本帖最後由 ML089 於 2013-11-29 17:58 編輯
Function TrkErr(Arr As Range, Brr As Range, FN As Integer) As Double
Dim avg As Single, SumSq As Single ', Crr As Range
Dim i As Range
'FN 是loop的次數 也是整個資料的rows -1 ;
'因為我沒有更好的方法可以直接在function裡求 所以改為input
'如果有更好的方法也請告知 謝謝唷
FN = Arr.Count
ReDim Crr(FN)
For x = 1 To Arr.Count
Crr(x) = Arr(x) - Brr(x)
Next
' Brr是固定的 Arr是不同的公司
avg = WorksheetFunction.Average(Crr)
For Each i In Arr
SumSq = SumSq + (i - avg) ^ 2
Next i
FN = Arr.Count
TrkErr = Sqr(SumSq / (FN - 1))
End Function作者: ML089 時間: 2013-11-29 18:05
這是另一種練習寫法，請參考
Function TrkErr(Arr As Range, Brr As Range, FN As Integer) As Double
Dim avg As Single, SumSq As Single
FN = Arr.Count
Crr = Application.Evaluate(Arr.Address & "-" & Brr.Address)
avg = WorksheetFunction.Average(Crr)
For Each i In Arr
SumSq = SumSq + (i - avg) ^ 2
Next i
TrkErr = Sqr(SumSq / (FN - 1))
End Function作者: ldneye 時間: 2013-12-2 04:41
