Sub aaaa() 'Dim d1 As New Dictionary d1 是多余的注释掉 Dim d2 As New Dictionary Dim d3 As New Dictionary Dim d4 As New Dictionary r = Sheet2.Range("a2").End(xlDown).Row - 1 arr = Sheet2.Range("a2").Resize(r, 3) For I = 1 To r For j = 1 To 3 arr(I, j) = Trim(arr(I, j)) '文字净化处理 去掉手误空格 Next ' s = d1(arr(I, 1)) d2(arr(I, 2)) = d2(arr(I, 2)) + 1 s = d3(arr(I, 3)) xhgz = arr(I, 2) & " " & arr(I, 3) ' 型号 故障 If d4.Exists(xhgz) = False Then Set d4(xhgz) = New Dictionary'字典套字典 d4(xhgz)(arr(I, 1)) = d4(xhgz)(arr(I, 1)) + 1 Next arr = d4.Items For I = 0 To UBound(arr) If arr(I).Count > X Then X = arr(I).Count: SS = d4.Keys(I) Next Erase arr ReDim AR(1 To (d2.Count + d4.Count), 1 To 11) I = 0 For Each K In d4 xh = Split(K)(0) gz = Split(K)(1) I = I + 1 AR(I, 1) = (I - 1) Mod (d3.Count + 1) + 1 AR(I, 2) = xh AR(I, 3) = gz For Each KK In d4(K) AR(I, 4) = AR(I, 4) + d4(K)(KK)
For Each KK In d4(K) AR(i, 4) = AR(i, 4) + d4(K)(KK) If d4(K)(KK) >= AR(i, 7) Then' 这里重新改过了, 取前三名,优化问题就不管了 AR(i, 10) = AR(i, 8): AR(i, 11) = AR(i, 9) AR(i, 8) = AR(i, 6): AR(i, 9) = AR(i, 7) AR(i, 6) = KK: AR(i, 7) = d4(K)(KK) ElseIf d4(K)(KK) >= AR(i, 9) Then AR(i, 10) = AR(i, 8): AR(i, 11) = AR(i, 9) AR(i, 8) = KK: AR(i, 9) = d4(K)(KK) ElseIf d4(K)(KK) > AR(i, 11) Then AR(i, 10) = KK: AR(i, 11) = d4(K)(KK) End If Next Next AR(I, 5) = AR(I, 4) / d2(xh) If AR(I, 1) = d3.Count Then I = I + 1 AR(I, 1) = (I - 1) Mod (d3.Count + 1) + 1 AR(I, 2) = xh AR(I, 3) = "合计" AR(I, 4) = d2(xh) AR(I, 5) = "100%" End If Next Range("a3").Resize(UBound(AR), 11) = AR End Sub
[此贴子已经被作者于2007-11-27 17:26:17编辑过] |