[µo°Ý] for next °j°é³t«×ºC¡A¦p¦ó§ï³t«×¯à¥[§Ö

chiang0320

test.zip (93.13 KB)

¤U¸ü¦¸¼Æ: 11

2016-12-2 16:35

   

¤U¸ü (7.99 KB)

2016-12-2 16:36

MÄ欰ÅܼơA¦³¥i¯à¼Æ¶q·|¨ì¤Q¸U©Î¤G¤Q¸U...
§Q¥Î°j°é³t«×«ÜºC¡A¦p¦ó¥i¥H¥[§Ö³t«×

Public Sub WWW()

Start = Timer

Range("K3") = "=COUNTIF(M:M,""*RBW*"")"
    a = Range("K3")
        For i = 4 To a + 3

            Cells(i, "N") = "=IFERROR(AVERAGEIF($D:$H,M" & i & ",E:H),""0"")"
            Cells(i, "O") = "=IFERROR(COUNTIF(D:D,M" & i & "),""0"")"

Next i

Range("M:O").Value = Range("M:O").Value
      
Finish = Timer
TotalTime = Finish - Start
MsgBox "¤w§¹¦¨!  Á`¦@¡G" & TotalTime & "¬í !"

End Sub

chiang0320

¦^´_ 2# ­ã´£³¡ªL


Dear ­ã´£³¡ªL :

ÁÂÁÂÀ°¦£¡A¶]§¹¤~ªá¶O¤£¨ì¤@¬íÄÁ

¹ï©óªì¾Ç«ùªÌ¦ý¤Ó²`¶ø¤F!«Ü·Q¾Ç°_¨Ó¦ý¤£ª¾¦p¦ó°Ý°_ =_=

­ã´£³¡ª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