|
发表于 2012-8-16 23:49
|
显示全部楼层
本楼为最佳答案
qdzbk 发表于 2012-8-16 23:28
只有二个文件,但二个文件中的记录有很多。
- Sub 统计()
- Dim arr, arr1(), d, i&, j%, k%, mypath$, b As Boolean
- Set d = CreateObject("scripting.dictionary")
- mypath = ThisWorkbook.Path & ""
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- On Error Resume Next
- Workbooks("表1.xls").Activate
- Workbooks.Open mypath & "表1.xls"
- arr = Range("a2:f" & Range("a65536").End(3).Row)
- If Err <> 0 Then
- ActiveWorkbook.Close
- Err.Clear
- End If
- For i = 1 To UBound(arr)
- If Not d.exists(Left(arr(i, 1), 2)) Then
- k = k + 1
- d(Left(arr(i, 1), 2)) = k
- ReDim Preserve arr1(1 To 7, 1 To k)
- arr1(1, k) = Left(arr(i, 1), 2)
- End If
- If Left(arr(i, 2), 2) = Left(arr(i, 1), 2) Then
- For j = 3 To UBound(arr, 2)
- arr1(2, d(Left(arr(i, 1), 2))) = arr1(2, d(Left(arr(i, 1), 2))) + arr(i, j)
- Next
- Else
- For j = 3 To UBound(arr, 2)
- arr1(j, d(Left(arr(i, 1), 2))) = arr1(j, d(Left(arr(i, 1), 2))) + arr(i, j)
- Next
- End If
- Next
- Workbooks("表2.xls").Activate
- Workbooks.Open mypath & "表2.xls"
- arr = Range("a2:f" & Range("a65536").End(3).Row)
- If Err <> 0 Then ActiveWorkbook.Close
- For i = 1 To UBound(arr)
- arr1(7, d(Left(arr(i, 1), 2))) = arr1(7, d(Left(arr(i, 1), 2))) + arr(i, 3)
- Next
- ThisWorkbook.Activate
- Range("a:a").NumberFormat = "@"
- With Range("a3").Resize(k, 7)
- .ClearContents
- .Value = Application.Transpose(arr1)
- End With
- Application.DisplayAlerts = True
- Application.ScreenUpdating = True
- End Sub
复制代码 点击"统计"按钮就可以了。
|
|