|
发表于 2015-1-18 17:06
|
显示全部楼层
本楼为最佳答案
- Sub total()
- Dim fn$, pth$, data, i&, j%, re(), n&
- Dim wb As Workbook
- Dim dx As Object, dy As Object
- pth = ThisWorkbook.Path & ""
- fn = Dir(pth & "*.xls")
- ReDim re(1 To 20000, 1 To 3)
- Set dx = CreateObject("scripting.dictionary")
- Set dy = CreateObject("scripting.dictionary")
- Do Until fn = ""
- If fn <> ThisWorkbook.Name Then
- Set wb = GetObject(pth & fn)
- With wb.Sheets(1)
- If Application.CountA(.UsedRange) > 0 Then
- data = .UsedRange.Value
- For j = 4 To UBound(data, 2)
- If Not dy.exists(data(1, j)) Then dy(data(1, j)) = dy.Count + 1: ReDim Preserve re(1 To UBound(re), 1 To dy.Count + 2)
- For i = 2 To UBound(data)
- If Not dx.exists(data(i, 2) & data(i, 3)) Then
- n = dx.Count + 1
- dx(data(i, 2) & data(i, 3)) = n
- re(n + 1, 1) = data(i, 2)
- re(n + 1, 2) = data(i, 3)
- End If
- re(dx(data(i, 2) & data(i, 3)) + 1, dy(data(1, j)) + 2) = data(i, j)
- Next
- Next
- End If
- End With
- wb.Close 0
- Set wb = Nothing
- End If
- fn = Dir
- Loop
- data = dy.keys
- re(1, 1) = "姓名": re(1, 2) = "班级"
- For i = 0 To UBound(data)
- re(1, i + 3) = data(i)
- Next
- Range("A1").Resize(dx.Count + 1, UBound(re, 2)) = re
- End Sub
复制代码 |
|