|
发表于 2015-6-1 20:16
|
显示全部楼层
本楼为最佳答案
两法供比较,新方法速度快一些。数据量大时更明显。- Sub 原方法()
- Dim trr
- Dim i&, w&
- Dim h5&, h10&, h20&
- t = Timer
- Application.ScreenUpdating = False '关闭屏幕刷新
- Application.DisplayAlerts = False '禁止弹出警告对话框
- trr = [a1].CurrentRegion
- ReDim brr(1 To UBound(trr), 1 To 3) As Long
- For i = 20 To UBound(trr)
- n = n + 1
- For w = i - 19 To i
- x = trr(w, 1)
- brr(n, 3) = brr(n, 3) + x
- If w >= i - 9 Then brr(n, 2) = brr(n, 2) + x
- If w >= i - 4 Then brr(n, 1) = brr(n, 1) + x
- Next
- '输出结果
- Next
- [c5].Resize(n, 3) = brr
- MsgBox Timer - t
- End Sub
- Sub 新方法()
- Dim trr
- Dim i&, w&
- t = Timer
- Application.ScreenUpdating = False '关闭屏幕刷新
- Application.DisplayAlerts = False '禁止弹出警告对话框
- trr = [a1].CurrentRegion
- ReDim brr(1 To UBound(trr), 1 To 3) As Long
- i = 20: n = 1
- For w = 1 To i
- x = trr(w, 1)
- brr(n, 3) = brr(n, 3) + x
- If w >= i - 9 Then brr(n, 2) = brr(n, 2) + x
- If w >= i - 4 Then brr(n, 1) = brr(n, 1) + x
- Next
-
- For i = 21 To UBound(trr)
- n = n + 1
- x = trr(i, 1)
- brr(n, 3) = brr(n - 1, 3) + x - trr(i - 20, 1)
- brr(n, 2) = brr(n - 1, 2) + x - trr(i - 10, 1)
- brr(n, 1) = brr(n - 1, 1) + x - trr(i - 5, 1)
- Next
- [g5].Resize(n, 3) = brr
- MsgBox Timer - t
- End Sub
复制代码 |
|