|
- Option Explicit
- Option Base 1
- Sub 一键汇总()
- Dim Sh As Worksheet, arr, i%
- Dim a1(10000, 4), m1%, n1%
- Dim a2(10000, 4), m2%, n2%
- Dim d1 As Object, d2 As Object
- Set d1 = CreateObject("scripting.dictionary")
- Set d2 = CreateObject("scripting.dictionary")
- For Each Sh In ThisWorkbook.Worksheets
- If InStr(Sh.Name, "汇总") = 0 Then
- Debug.Print Sh.Name
- arr = Sh.Range("A1").CurrentRegion.Value
- For i = 4 To UBound(arr)
- If InStr(arr(i, 1), "合计") = 0 Then
- Rem 按线路汇总
- If Not d1.Exists(arr(i, 1)) Then
- n1 = n1 + 1: m1 = n1
- d1(arr(i, 1)) = n1
- Else
- m1 = d1.Item(arr(i, 1))
- End If
- a1(m1, 1) = a1(m1, 1) + arr(i, 3) '总笔数
- a1(m1, 2) = a1(m1, 2) + arr(i, 4) '应收总金额
- a1(m1, 3) = a1(m1, 3) + arr(i, 5) '实收总金额
- a1(m1, 4) = a1(m1, 4) + arr(i, 6) '优惠总金额
-
- Rem 按车号汇总
- If Not d2.Exists(arr(i, 2)) Then
- n2 = n2 + 1: m2 = n2
- d2(arr(i, 2)) = n2
- Else
- m2 = d2.Item(arr(i, 2))
- End If
- a2(m2, 1) = a2(m2, 1) + arr(i, 3) '总笔数
- a2(m2, 2) = a2(m2, 2) + arr(i, 4) '应收总金额
- a2(m2, 3) = a2(m2, 3) + arr(i, 5) '实收总金额
- a2(m2, 4) = a2(m2, 4) + arr(i, 6) '优惠总金额
- End If
- Next
- End If
- Next
- Sheets("线路汇总").Select
- Range("A3:E1000").ClearContents
- Range("A3:E1000").Borders.LineStyle = xlNone
- Range("A3").Resize(d1.Count, 1) = WorksheetFunction.Transpose(d1.keys)
- Range("B3").Resize(d1.Count, 4) = a1
- Range("A3").Resize(d1.Count, 5).Borders.LineStyle = xlContinuous
- Columns("B:B").NumberFormatLocal = "#,##0_);[红色](#,##0)"
- Columns("C:E").NumberFormatLocal = "#,##0.00_);[红色](#,##0.00)"
- Cells.Columns.AutoFit
-
- Sheets("车牌号汇总").Select
- Range("A3:E1000").ClearContents
- Range("A3:E1000").Borders.LineStyle = xlNone
- Range("A3").Resize(d2.Count, 1) = WorksheetFunction.Transpose(d2.keys)
- Range("B3").Resize(d2.Count, 4) = a2
- Range("A3").Resize(d2.Count, 5).Borders.LineStyle = xlContinuous
- Columns("B:B").NumberFormatLocal = "#,##0_);[红色](#,##0)"
- Columns("C:E").NumberFormatLocal = "#,##0.00_);[红色](#,##0.00)"
- Cells.Columns.AutoFit
- End Sub
复制代码 |
|