|
只统计了“ETC专用道”,如需统计全部可删除相应的IF语句。
- Private Sub CommandButton1_Click()
- Dim arr, crr, brr(), d As Object, i&
- Set d = CreateObject("scripting.dictionary")
- crr = [b5:b16]: crr(1, 1) = crr(2, 1)
- ReDim brr(1 To UBound(crr), 1 To 2)
- With Workbooks.Open(ThisWorkbook.Path & "" & "Sheet2.xls")
- arr = .Sheets(1).Range("a1").CurrentRegion
- .Close False
- End With
- For i = 2 To UBound(arr)
- If arr(i, 3) = "ETC专用道" Then '统计ETC专用道
- If d.exists(arr(i, 6)) Then
- d(arr(i, 6)) = Array(d(arr(i, 6))(0) + 1, d(arr(i, 6))(1) + Val(arr(i, 10)))
- Else
- d(arr(i, 6)) = Array(1, Val(arr(i, 10)))
- End If
- End If
- Next
- For i = 1 To UBound(crr)
- If d.exists(crr(i, 1)) Then
- brr(i, 1) = d(crr(i, 1))(0)
- brr(i, 2) = d(crr(i, 1))(1)
- End If
- Next
- [e5:f16] = ""
- [e5:f16] = brr
- End Sub
复制代码
求助.rar
(21.48 KB, 下载次数: 3)
|
|