- 帖子
- 6
- 主題
- 3
- 精華
- 0
- 積分
- 10
- 點名
- 0
- 作業系統
- Windows 7
- 軟體版本
- 1
- 閱讀權限
- 10
- 註冊時間
- 2013-5-2
- 最後登錄
- 2019-11-21
|
hi~各位高手們，小弟潛水已久，今日遇到vba標準差的問題想請教各位
以下是我的程式碼!!
問題在於我的rawdata是一排都是同樣整數時，標準差計算結果會正確並顯示為0
但一排為同樣小數時，則標準差會計算出E-17次方，請問該如何解決
附上檔案!!
Sub toolmeanstd()
Dim shTable As Worksheet
Set shTable = Sheets("Finaltable")
Set shraw = Sheets("data-tool")
TableEndR = shTable.Range("A65536").End(xlUp).Row
dataEndR = shraw.Range("H65536").End(xlUp).Row
dataEndC = shraw.Range("XDF1").End(xlToRight).Column
For R = 3 To TableEndR
Set mappRng = shraw.Range("H1:XFD1").Find(Trim(shTable.Cells(R, 1)), LookAt:=xlWhole)
If Not mappRng Is Nothing Then '比對到資料
mappC = mappRng.Column '(在mappRng範圍裡有幾個column)
rowCnt = 0: dataSum = 0
'shTable.Cells(R, 6) = shraw.Cells(1, mappC)
For R1 = 2 To dataEndR
rowCnt = rowCnt + 1
dataSum = dataSum + shraw.Cells(R1, mappC)
shTable.Cells(R, 6) = dataSum / rowCnt
Next
'歸零的目的在於怕之前有用到此變數，會造成程式異常
sigma = 0
For R2 = 2 To dataEndR
sigma = sigma + ((shraw.Cells(R2, mappC).Value - (dataSum / rowCnt)) ^ 2)
Next
stdValue = (sigma / (rowCnt - 1)) ^ 0.5
shTable.Cells(R, 7) = stdValue
End If
Next
End Sub |
-
-
2.rar
(187.23 KB)
標準差
|
發表於 2015-4-22 11:19
| 
