[算表] vba迴圈問題

看板Office作者nazomegami (深呼吸~)時間10年前 (2016/04/23 08:57)推噓0(0推 0噓 30→)

留言30則, 2人參與討論串1/1

軟體:excel vba 版本:2010 各位版大好，以下有一vba程式，目的是要抓取3萬筆左右的交易價格進行統計分析(交易價格可隨機設為1000~10000之間的值)。我的問題是，程式中有一段 For zzz = 0.95 To 1 Step k_gap，如果我將0.95改為0.9，則原本百分比介於0.95~1的資料就沒有跑出來，可是做迴圈時zzz卻會取到1，覺得很困惑，系望各位大大解惑，謝謝。 Sub slope_analysis() Dim k_gap, percentile_lbound_value, percentile_ubound_value Dim ary0(33000), ary1(33000), ary2(33000), ary3(3500), ary4(3500), _ ary5(30), ary6(30), ary7(500, 3) Dim i&, j&, k&, m&, n&, p& '60=3個月；120=6個月；180=9個月；250=1年；500=2年；750=3年；1000=4年；_ 1250=5年 '1500=6年；1750=7年；2000=8年；2250=9年；2500=10年 k_gap = 0.05 j = 1 For i = 1 To 33000 If Cells(6 + i, 5) <> "" Then ary0(j) = Cells(6 + i, 5) j = j + 1 End If Next 'zzz=百分比上限;xxx=歷史天數;yyy=未來天數 For zzz = 0.95 To 1 Step k_gap For xxx = 250 To 2500 Step 250 n = 0 Erase ary5, ary6 For yyy = 625 To 1250 Step 125 '若第i日前x天且後y天不超過資料範圍，則分別寫入第i日前x天與 _ 後y天的報酬率 k = 0 m = 0 Erase ary1, ary2, ary3, ary4 For i = 1 To j - 1 If i - xxx > 0 And i + yyy <= j - 1 Then ary1(i) = (ary0(i) - ary0(i - xxx)) / ary0(i - xxx) ary2(i) = (ary0(i + yyy) - ary0(i)) / ary0(i) k = k + 1 End If Next '求得歷史報酬率樣本中第i個百分比之下限值與第j個百分比之上限值 If zzz - k_gap = 0 Then percentile_lbound_value = WorksheetFunction.Min(ary1) Else percentile_lbound_value = _ WorksheetFunction.Percentile(ary1, zzz - k_gap) End If If zzz = 1 Then percentile_ubound_value = WorksheetFunction.Max(ary1) Else percentile_ubound_value = _ WorksheetFunction.Percentile(ary1, zzz) End If '將歷史報酬率在百分比上下限內之值寫入 For i = 1 + xxx To k + xxx If ary1(i) > percentile_lbound_value And ary1(i) <= _ percentile_ubound_value Then ary3(m) = ary1(i) ary4(m) = ary2(i) m = m + 1 End If Next ary5(n) = WorksheetFunction.Correl(ary3, ary4) ary6(n) = n + 1 n = n + 1 Next yyy ary7(p, 0) = zzz - k_gap ary7(p, 1) = zzz ary7(p, 2) = xxx ary7(p, 3) = WorksheetFunction.Slope(ary5, ary6) p = p + 1 Next xxx Next zzz For i = 0 To 500 Cells(6 + i, 9) = ary7(i, 0) Cells(6 + i, 10) = ary7(i, 1) Cells(6 + i, 11) = ary7(i, 2) Cells(6 + i, 12) = ary7(i, 3) Next End Sub -- ※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 36.239.180.109 ※ 文章網址: https://www.ptt.cc/bbs/Office/M.1461373035.A.436.html

→