作者raymond5566 (雷門56)
看板Office
標題[算表] VBA bug問題
時間Tue Dec 18 18:17:52 2018
軟體:excel
版本:office365
小弟想要弄一個能一樣本t檢定和投報率的vba程式
有時候可以用有時候又不能用
但不曉得為什麼會一直有bug
每次說錯的地方都不同
小弟改過一些地方 t=av/(std/a^0.5) 那行會出現型態不符合
Private Sub CommandButton1_Click()
Set r1 = Range(Me.RefEdit1)
Set out_put = Range(Me.RefEdit2)
r = out_put.Row
c = out_put.Column
av = Application.Average(r1)
Sum = Application.Sum(r1)
std = Application.StDev([r1])
roi = (Sum) / 10.1
a = WorksheetFunction.Count(r1)
t = av / (std / a ^ 0.5)
p = 1 - WorksheetFunction.T_Dist(t, a - 1, 1)
Min = WorksheetFunction.Min(r1)
Max = WorksheetFunction.Max(r1)
Cells(r, c) = "Average"
Cells(r, c + 1) = av
Cells(r + 1, c) = "ROI"
Cells(r + 1, c + 1) = roi
Cells(r + 2, c + 1) = std
Cells(r + 2, c) = "s"
Cells(r + 3, c + 1) = t
Cells(r + 3, c) = "t"
Cells(r + 4, c + 1) = p
Cells(r + 4, c) = "p-value"
Cells(r + 5, c + 1) = Min
Cells(r + 5, c) = "Min"
Cells(r + 6, c + 1) = Max
Cells(r + 6, c) = "Max"
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 123.205.64.45
※ 文章網址: https://webptt.com/m.aspx?n=bbs/Office/M.1545128276.A.4DE.html
※ 編輯: raymond5566 (123.205.64.45), 12/18/2018 22:04:49
1F：→ soyoso: 是否是資料有錯誤值呢？測試如範圍內有錯誤值時會出現如原 12/18 23:36
3F：→ soyoso: 或是範圍內的數字為文字型態 12/18 23:41