|
发表于 2016-1-20 14:47
|
显示全部楼层
本楼为最佳答案
速度秒杀SQL- Sub 汇总()
- Dim sht As Worksheet
- Set d = CreateObject("scripting.dictionary")
- rq1 = [c3]: rq2 = [e3] '起止日期
- Dim brr(1 To 10000, 1 To 5)
- For Each sht In Worksheets
- If sht.Name Like "数据*" Then
- arr = sht.Range("a1:k" & sht.[d65536].End(3).Row)
- For i = 10 To UBound(arr)
- If Len(arr(i, 4)) > 0 Then
- rq = arr(i, 4)
- x = arr(i, 6)
- If Not d.Exists(x) Then
- n = n + 1: d(x) = n
- brr(n, 1) = x
- End If
- p = d(x)
- If rq < rq1 Then '日期在指定日期之前,期初值
- brr(p, 2) = brr(p, 2) + arr(i, 10)
- brr(p, 3) = brr(p, 3) + arr(i, 11)
- ElseIf rq <= rq2 Then '日期在指定日期之间,本期值
- brr(p, 4) = brr(p, 4) + arr(i, 10)
- brr(p, 5) = brr(p, 5) + arr(i, 11)
- End If
- End If
- Next
- End If
- Next
- [b6:f10000].ClearContents
- If n > 0 Then [b6].Resize(n, 5) = brr
- End Sub
复制代码 |
评分
-
查看全部评分
|