|
楼主 |
发表于 2014-9-6 16:31
|
显示全部楼层
再举一个例,里面的有二个过程代码,都是对同一不规则列数据的提取、计算和输出。其中第一个是网友所写,用的是字典套字典的方法,后一个是我所写,就用一本词典,关键在于让词典关键词去对应输出数组行号。里面的数据量并不大,可是,就这么一点数据,二者的运算速度也有了明显差别。
我的代码如下:- Sub kphz()
- Dim dic, Arr, drr, jrr, i&, myR&, iRow&, t
- Application.ScreenUpdating = False
- t = Timer
- With Sheet1
- myR = .Range("L65536").End(xlUp).Row
- .Range("Y5:AM" & myR).ClearContents
- Arr = .Range("L5:L" & myR)
- Set dic = CreateObject("scripting.dictionary")
- For i = 1 To UBound(Arr, 1)
- If Not dic.exists(Arr(i, 1)) Then dic(Arr(i, 1)) = dic.Count + 1'让第N个出现的关键字对应第N行,dic.count随关键字增加而增加,拿来用刚好
- Next
- jrr = .Range("R5:X" & myR)'取得数据源
- ReDim drr(1 To dic.Count, 1 To 9)'定义输出数组
- k = dic.Count
- For i = 1 To myR - 4
- iRow = dic.Item(Arr(i, 1))
- drr(iRow, 1) = jrr(i, 1) * 1000 + drr(iRow, 1)
- drr(iRow, 2) = jrr(i, 4) + drr(iRow, 2)
- drr(iRow, 3) = jrr(i, 5) + drr(iRow, 3)
- drr(iRow, 4) = jrr(i, 2) * 1000 + drr(iRow, 4)
- drr(iRow, 5) = jrr(i, 6) + drr(iRow, 5)
- drr(iRow, 6) = jrr(i, 7) + drr(iRow, 6)
- drr(iRow, 7) = drr(iRow, 1) + drr(iRow, 4)
- drr(iRow, 8) = drr(iRow, 2) + drr(iRow, 5)
- drr(iRow, 9) = drr(iRow, 3) + drr(iRow, 6)
- Next
- .Range("AD5").Resize(dic.Count, 1) = Application.Transpose(dic.keys)
- .Range("AE5").Resize(dic.Count, 9) = drr
- End With
- MsgBox "整理完成,用时" & Format(Timer - t, "#0.#####") & "秒!"
- End Sub
复制代码 |
|