|
楼主 |
发表于 2013-6-3 08:42
|
显示全部楼层
大灰狼1976 发表于 2013-6-2 22:17
附件请测试,不出现次数显示在SHEET2里面
这是我改的,有的计算结果会正确,有的就是不正确,帮忙看看
Dim no(2): Dim ro()
With Sheets("统计")
Const SD = 490
rng1 = .Range("A2:E" & SD)
End With
With Sheets("sheet1")
i = 166
rng2 = .Range("b2:d" & i)
End With
ReDim arr(1 To (i - 1), 1 To 4)
For x = 1 To UBound(rng2)
k = 0
For y = 1 To SD - 1
no(0) = 0: no(1) = 0: no(2) = 0
For z = 1 To 5 - 1
If rng1(y, z) = rng2(x, 1) Then no(0) = no(0) + 1
If rng1(y, z) = rng2(x, 2) Then no(1) = no(1) + 1
If rng1(y, z) = rng2(x, 3) Then no(2) = no(2) + 1
Next
If no(0) = 0 And no(1) = 0 And no(2) = 0 Then
ReDim Preserve ro(k)
ro(k) = y
k = k + 1
End If
Next
ReDim Preserve ro(k)
ro(k) = SD
ReDim roh(k)
ma = 0
s = 0
m = 0
' Stop
For j = 0 To k
If j + 1 > k Then Exit For
roh(j) = ro(j + 1) - ro(j)
s = s + roh(j)
m = m + 1
If ma < roh(j) Then
ma = roh(j)
End If
Next
arr(x, 1) = roh(k - 1)
arr(x, 2) = ma
arr(x, 3) = s / m
arr(x, 4) = m
Next
With Sheets("sheet1")
.Range("e2:h" & i) = arr
End With
|
|