怎么都要借助外力呢才能达到高速呢!我这个自定义数据类型,不用排序,所有数据统计完毕才0.5秒,最后显示估计不会超过1秒!!欢迎大家来完善,因为这更具有通用性.(好象论坛之中,讲到自定义数据类型(也就是大家所熟悉的结构)的很少啊) Option Explicit '----------------------- Type sf name As String sum As Integer End Type '----------------------- Type gz name As String pro(1 To 50) As sf sum As Long sfsum As Byte End Type '----------------------- Type jx name As String sum As Long bad(1 To 20) As gz gzsum As Byte End Type '---------------------- Dim data(1 To 20) As jx, p As Long, p1 As Integer, p2 As Integer Dim arr Dim j As Integer, jj As Integer, jjj As Integer Dim over As Boolean, over1 As Boolean, over2 As Boolean Dim str As String, str1 As String, str2 As String
Sub erw() Dim a Dim i As Long, m As Long, jxsum As Byte a = Timer With Worksheets("sheet2") p = .Range("a65536").End(xlUp).Row arr = .Range("a2:c" & p) End With m = UBound(arr, 1) p = 0 p1 = 0 p2 = 0 jxsum = 0 For i = 1 To m over = False str = Trim(arr(i, 1)) str1 = Trim(arr(i, 2)) str2 = Trim(arr(i, 3)) For j = 1 To jxsum If data(j).name = str1 Then over = True data(j).sum = data(j).sum + 1 checkgz j Exit For End If Next If over = False Then jxsum = jxsum + 1 data(jxsum).name = str1 data(jxsum).sum = data(jxsum).sum + 1 data(jxsum).name = str1 data(jxsum).gzsum = data(jxsum).gzsum + 1 data(jxsum).bad(data(jxsum).gzsum).sfsum = data(jxsum).bad(data(jxsum).gzsum).sfsum + 1 data(jxsum).bad(data(jxsum).gzsum).pro(data(jxsum).bad(data(jxsum).gzsum).sfsum).name = str2 data(jxsum).bad(data(jxsum).gzsum).pro(data(jxsum).bad(data(jxsum).gzsum).sfsum).sum = data(jxsum).bad(data(jxsum).gzsum).pro(data(jxsum).bad(data(jxsum).gzsum).sfsum).sum + 1 End If Next '------------------------------------------------------------------ For i = 1 To jxsum Cells(i + 1, 4) = data(i).name Cells(i + 1, 5) = data(i).sum Next MsgBox Format(Timer - a, "0.00") End Sub Sub checkgz(j As Integer) over1 = False For jj = 1 To data(j).gzsum If data(j).bad(jj).name = str2 Then over1 = True data(j).bad(jj).sum = data(j).bad(jj).sum + 1 checksf j, jj Exit For End If Next If over1 = False Then data(j).gzsum = data(j).gzsum + 1 data(j).bad(data(j).gzsum).name = str2 data(j).bad(data(j).gzsum).sum = data(j).bad(data(j).gzsum).sum + 1 data(j).bad(data(j).gzsum).sfsum = data(j).bad(data(j).gzsum).sfsum + 1 data(j).bad(data(j).gzsum).pro(data(j).bad(data(j).gzsum).sfsum).name = str data(j).bad(data(j).gzsum).pro(data(j).bad(data(j).gzsum).sfsum).sum = data(j).bad(data(j).gzsum).pro(data(j).bad(data(j).gzsum).sfsum).sum + 1 End If End Sub Sub checksf(j As Integer, jj As Integer) over2 = False For jjj = 1 To data(j).bad(jj).sfsum If data(j).bad(jj).pro(jjj).name = str Then over2 = True data(j).bad(jj).pro(jjj).sum = data(j).bad(jj).pro(jjj).sum + 1 Exit For End If Next If over2 = False Then data(j).bad(jj).sfsum = data(j).bad(jj).sfsum + 1 data(j).bad(jj).pro(data(j).bad(jj).sfsum).name = str data(j).bad(jj).pro(data(j).bad(jj).sfsum).sum = data(j).bad(jj).pro(data(j).bad(jj).sfsum).sum + 1 End If End Sub
|