|
发表于 2017-4-1 20:29
|
显示全部楼层
本楼为最佳答案
代码确实有bug,计算结果是错的。
更正如下:
- Sub test()
- Dim ar, i&, i1&, m&, m1&, m2&, n&, n1&, r#, s$, t&, t1&, tms#
- tms = Timer
-
- m = 10000 '设置数据总行数
- ar = [a1].Resize(m, 2)
-
- m1 = m / 10 * 3 '取值个数下限
- m2 = m / 10 * 4 '取值个数上限
-
- For n = m1 To m2 '在取值范围内设置取值个数n
- t = 0
- For i = 1 To n '遍历1-n得到起始n个数据的概率统计值
- t = t + ar(i, 2)
- Next
- t1 = t: If t / n > r Then r = t / n: i1 = 1: n1 = n
- For i = 1 To m - n '继续按取值n个进行位移遍历
- t = t - ar(i, 2) + ar(i + n, 2) '位移时去掉第一个、加上最后1个,变成新的概率统计值
- If t > t1 Then t1 = t: If t / n > r Then r = t / n: i1 = i + 1: n1 = n
- Next
- DoEvents
- Application.StatusBar = Format(Timer - tms, "0.0s ") & Format((n - m1 + 1) / (m2 - m1 + 1), "0.0%")
- Next
- s = "i = " & i1 & " : n = " & n1 & " : r = " & Format(r, "0.00%")
- [d1] = s: [d2] = i1: [d3] = n1
- [d4] = "=SUM(B" & i1 & ":B" & i1 + n1 - 1 & ")": [d5] = "=D3/D2"
- MsgBox Format(Timer - tms, "0.00s") & vbCr & s
- End Sub
复制代码
注意:如果设置m=10万,那么耗时将指数级增加,大约需要运行1个小时。
|
|