|
参与一下。。。
- Sub ykcbf() '//2024.11.10
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Set d = CreateObject("Scripting.Dictionary")
- ReDim brr(1 To 10000, 1 To 100)
- m = 1: n = 1
- arr = Sheets("汇总").UsedRange
- brr(1, 1) = arr(1, 2)
- For i = 2 To UBound(arr)
- If arr(i, 2) <> Empty Then
- 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))
- s = arr(i, 11)
- If Not d.exists(s) Then
- n = n + 1
- d(s) = n
- brr(1, n) = s
- End If
- c = d(arr(i, 11))
- brr(r, c) = arr(i, 7)
- End If
- Next
- n = n + 1
- brr(1, n) = "合计"
- m = m + 1
- brr(m, 1) = "合计"
- With Sheets("结果")
- .UsedRange.Clear
- .Cells.Interior.ColorIndex = 0
- .[a1].Resize(1, n).Interior.Color = 49407
- .[a2].Resize(m - 1, 1).Interior.Color = 5296274
- With .[a1].Resize(m, n)
- .Value = brr
- .Borders.LineStyle = 1
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlCenter
- With .Font
- .Name = "微软雅黑"
- .Size = 11
- End With
- End With
- For i = 2 To m - 1
- .Cells(i, n) = Round(Application.Sum(.Cells(i, 2).Resize(, n - 2)), 2)
- Next
- For j = 2 To n
- .Cells(m, j) = Round(Application.Sum(.Cells(3, j).Resize(m - 2)), 2)
- Next
- End With
- Application.ScreenUpdating = True
- MsgBox "OK!"
- End Sub
复制代码
|
评分
-
1
查看全部评分
-
|