|
- Sub Macro1()
- Dim arr, brr, d, d2, i&, j%
- Set d = CreateObject("scripting.dictionary")
- Set d2 = CreateObject("scripting.dictionary")
- arr = Sheet1.Range("a1").CurrentRegion
- ReDim brr(1 To 60000, 1 To 200)
- n = -3
- For i = 2 To UBound(arr)
- nj = Left(arr(i, 2), 3) '年级
- If Not d2.exists(nj) Then n = n + 5: d2(nj) = n
- l = d2(nj) '列
- If Not d.exists(nj) Then
- d(nj) = arr(i, 3) + 1
- brr(1, l - 1) = "学校名称"
- brr(1, l) = nj
- brr(1, l + 1) = "姓名"
- brr(1, l + 2) = "语文"
- brr(1, l + 3) = "数学"
- For j = 1 To arr(i, 3)
- brr(1 + j, l - 1) = arr(i, 1)
- brr(1 + j, l) = arr(i, 2)
- Next
- Else
- h = d(nj) '行
- For j = 1 To arr(i, 3)
- brr(h + j, l - 1) = arr(i, 1)
- brr(h + j, l) = arr(i, 2)
- Next
- d(nj) = d(nj) + arr(i, 3)
- End If
- Next
- zdh = Application.Max(d.items) '最大行
- zdl = Application.Max(d2.items) + 3 '最大列
- Sheet2.Range("a1").Resize(zdh, zdl) = brr
- End Sub
复制代码 |
|