|
更新一下。。。
- Sub ykcbf() '//2024.5.8
- Dim arr, brr, d
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Set d = CreateObject("scripting.dictionary")
- With Sheets("Sheet1")
- r = .Cells(Rows.Count, 2).End(3).Row
- arr = .Range("a1").Resize(r, 8)
- End With
- ReDim brr(1 To UBound(arr), 1 To 100)
- brr(1, 1) = arr(1, 2)
- m = 1: n = 1
- On Error Resume Next
- For i = 2 To UBound(arr)
- s = Month(arr(i, 1))
- If Not d.exists(s) Then
- n = n + 1
- d(s) = n
- brr(1, n) = s & "月"
- End If
- s = arr(i, 2)
- If Not d.exists(s) Then
- m = m + 1
- d(s) = m
- brr(m, 1) = s
- End If
- r = d(arr(i, 2)): c = d(Month(arr(i, 1)))
- brr(r, c) = brr(r, c) + arr(i, 8)
- Next
- With Sheets("Sheet2")
- .UsedRange.Clear
- .[a1].Resize(1, n + 1).Interior.Color = 49407
- With .[a1].Resize(m + 1, n + 1)
- .Value = brr
- .Borders.LineStyle = 1
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlCenter
- With .Font
- .Name = "微软雅黑"
- .Size = 11
- End With
- End With
- .Cells(1, n + 1) = "合计"
- For i = 2 To m + 1
- .Cells(i, n + 1) = Application.Sum(.Range(.Cells(i, "b"), .Cells(i, n)))
- Next
- m = m + 1
- .Cells(m, "a") = "合计"
- .Cells(m, "b").Resize(1, n).FormulaR1C1 = "=SUM(R2C:R" & "[-1]C)"
- End With
- Set d = Nothing
- MsgBox "OK!"
- End Sub
复制代码
|
|