|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
- Sub qs()
- Dim arr, i, dic, sht As Worksheet
- Set dic = CreateObject("scripting.dictionary")
- ReDim brr(1 To 1000, 1 To 100): brr(1, 1) = "NO"
- m = 1: c = 1
- For Each sht In Sheets
- If sht.Name <> "汇总" Then
- rw = sht.Cells(Rows.Count, "j").End(3).Row
- arr = sht.Range("g3:j" & rw).Value
- For i = 1 To UBound(arr)
- If arr(i, 3) <> Empty And arr(i, 4) <> Empty Then
- xm = arr(i, 4)
- dt = CDate(arr(i, 3))
- dt = "'" & Year(dt) & "年" & Month(dt) & "月"
- If Not dic.exists(dt) Then
- m = m + 1
- dic(dt) = m
- brr(m, 1) = dt
- End If
-
- If Not dic.exists(xm) Then
- c = c + 1
- dic(xm) = c
- brr(1, c) = xm
- End If
- rw = dic(dt): cl = dic(xm)
- brr(rw, cl) = brr(rw, cl) + Val(arr(i, 1))
-
- End If
- Next i
- End If
- Next
- ReDim crr(1 To 1, 1 To c + 1)
- crr(1, 1) = "合计"
- For j = 2 To c
- crr(1, j) = Application.Sum(Application.Index(brr, 0, j))
- Next
- ReDim drr(1 To m, 1 To 1): drr(1, 1) = "小计"
- sm = 0
- For i = 2 To m
- drr(i, 1) = Application.Sum(Application.Index(brr, i, 0))
- sm = sm + drr(i, 1)
- Next
- crr(1, c + 1) = sm
- With Sheet1
- .[a3].Resize(m, c) = brr
- .[a3].Offset(m, 0).Resize(1, c + 1) = crr
- .[a3].Offset(0, c).Resize(m, 1) = drr
- End With
- End Sub
复制代码 |
|