­ã´£³¡ªL

1. Sub WWW()
   
2. Dim Arr, Brr, xD, i&, j%, N&, SS, TM
   
3. TM = Timer
   
4. Set xD = CreateObject("Scripting.Dictionary")
   
5. Arr = Range([M4], Cells(Rows.Count, "M").End(xlUp))
   
6. ReDim Brr(1 To UBound(Arr), 1 To 2)
   
7. For i = 1 To UBound(Arr)
   
8.     If Arr(i, 1) <> "" Then xD(Arr(i, 1)) = i
   
9. Next i
   
10. 
    
11. Arr = Range([D4], Cells(Rows.Count, "D").End(xlUp)(1, 5))
    
12. For i = 1 To UBound(Arr)
    
13.     N = xD(Arr(i, 1)): If N = 0 Then GoTo 101
    
14.     SS = Split(Mid(Brr(N, 1), 2) & "/", "/")
    
15.     For j = 2 To 5
    
16.         If IsNumeric(Arr(i, j)) Then
    
17.            SS(0) = Val(SS(0)) + Arr(i, j)
    
18.            SS(1) = Val(SS(1)) + 1
    
19.         End If
    
20.     Next j
    
21.     Brr(N, 1) = "=" & SS(0) & "/" & SS(1) '=Á`¦X­p/­Ó¼Æ
    
22.     Brr(N, 2) = Brr(N, 2) + 1
    
23. 101: Next i
    
24. 
    
25. [N4].Resize(UBound(Brr), 2) = Brr
    
26. MsgBox "¤w§¹¦¨!  Á`¦@¡G" & Timer - TM & "¬í !"
    
27. End Sub

½Æ»s¥N½X

Xl0000001.rar (91.71 KB)

¤U¸ü¦¸¼Æ: 18

2016-12-2 22:02