c_c_lai

¦^´_ 31# ÂŤÑÄR¦À
§A§â ThisWorkbook ¸Ìªº¨ç¼Æ¤º®eµy¥[²§°Ê
µM«á¤©¥HÀx¦s«á¡AÃö³¬­«¶} Excel¡G

1. Option Explicit
   
2. 
   
3. Private Sub Workbook_BeforeClose(Cancel As Boolean)
   
4.     Application.RTD.ThrottleInterval = 2000
   
5.     Application.Calculation = xlCalculationAutomatic
   
6. End Sub
   
7. 
   
8. Private Sub Workbook_Open()
   
9.     Application.RTD.ThrottleInterval = 0
   
10.     Application.Calculation = xlCalculationManual
    
11. End Sub

½Æ»s¥N½X

­×§ï¦¨¡G

1. Option Explicit
   
2. 
   
3. Private Sub Workbook_BeforeClose(Cancel As Boolean)
   
4.     Application.RTD.ThrottleInterval = 0
   
5.     Application.Calculation = xlCalculationManual
   
6. End Sub
   
7. 
   
8. Private Sub Workbook_Open()
   
9.     Application.RTD.ThrottleInterval = 2000
   
10.     Application.Calculation = xlCalculationAutomatic
    
11. End Sub

½Æ»s¥N½X

¦A­«·s¸Õ¸Õ¬Ý¡C
³o«K¬O­ã´£³¡ªLª©¤j°Ý§Aªº°ÝÃD¡C

c_c_lai

¦^´_ 34# ÂŤÑÄR¦À
§Ú«e«e«á«á¬d¬Ý¤F§A»P­ã´£³¡ªLª©¤j¤W¶Ç¤À¨ÉªºÀɮסA
µo²{°ÝÃD¤´µM¥X¦Û©ó§Aªº¦Û¦æ¦A¾ã¦X¡A­ã´£³¡ªLª©¤j
¤W¶Çªº¤º®e³£¨S¥[¤J Workbook_Open() ªº°õ¦æ¤º®e¡A
¬Æ¦Ü¦b²Ä¤@¦¸µ¹ªº Xl0000328.rar ¤]¥ç±N¥¦ Marked ±¼¡A
§A¥u±N¥¦¤À¨É¼Ò²Õ¤ºªº Sub ²Î­p() ª½±µ¶K¤J¨ì§A­ì¥»ªºÀɮפº
©Ò¥H¤~·|³y¦¨¥¼¯à¥¿±`°õ¦æªº­ì¦]¡C¬°À°§U§A§ó¯à©úÁAª©¤j
¼Ò²Õªº¹B§@¡A¯S¤©¦b¨ä¼Ò²Õ¤º¨C¤@´`Àô²Ó³¡¤ÀªR¸Ñ»¡¡A§Æ±æ§A
¯à¶i¤@¨B¾Ç²ß¨ì¦p¦ó¹B¥Î¡A¦P®É¯à¼W¶i§A¥»¨­ªº¦Û§ÚÆ[¹î¤O¡C
§Ú±N¤T¤èªºµ{¦¡¼Ò²Õ¤©¥H²Õ¦X¤W¶Ç¤W¨Ó¡A§A´ú¸Õ¬Ý¬Ýµ²ªG¦p¦ó¡C
¸ÑÀ£«á«Kª½±µ¥Î¥¦¨Ó°õ¦æ´ú¸Õ¡A¦p¦¹¤~±o¥HÆ[´ú¨ä´ú¸Õµ²½×¡C
©ú²ÓÅÜ°Ê°O¿ý.rar (178.89 KB)

¤U¸ü¦¸¼Æ: 10

2016-1-29 07:28

c_c_lai

¦^´_ 36# ÂŤÑÄR¦À
³Ìªñ¦³ÂI¨Æ¯ÔÀÁ¤F¡C
§A¦b #10 ¸Ìªº»¡©ú¡A­nªº¬O¡H

¤U¸ü (32.95 KB)

2016-2-1 08:29

c_c_lai

¦^´_ 38# ÂŤÑÄR¦À
¨º§A¥Î§Ú¥Ø«e¤W¶ÇªºÀɮרӰµ´ú¸Õ¬Ý¬Ý¡C
´ú¸Õ§¹«á§i¶D§Ú¤@Ánµ²ªG¡C
§Ú¥ý§â­ã´£³¡ªLª©¤j¤À¨Éªº¥\¯à§ï¬° ²Î­pA()¡A
¥ý¤£¤©°õ¦æ¡A¦Ó¥h°õ¦æ§Ú¼W¥[¤§´ú¸Õ¼Ò²Õ
²Î­p() ->dicStatics §AÆ[¹î¬Ý¬Ý¶i¦æ¶¶ºZ§_¡H
©ú²ÓÅÜ°Ê°O¿ý.rar (192.18 KB)

¤U¸ü¦¸¼Æ: 5

2016-2-1 11:16

c_c_lai

¦^´_ 38# ÂŤÑÄR¦À

1. Sub ²Î­p()        '  L¡BM¡BN¡BO Äæ¦ì²Î­p
   
2.     Dim DD As Date
   
3.    
   
4.     dicStatics
   
5.     DD = Format(Now, "yyyy/mm/dd hh:mm")    '  DD = 2016/1/28 ¤W¤È 12:41:00 : Date
   
6.     TimeTxt = DD + 1 / 1440                 '  TimeTxt = 2016/1/28 ¤W¤È 12:42:00 : Variant/Date
   
7.     Application.OnTime TimeTxt, "²Î­p"      '  ¨C¤@¤ÀÄÁ¦Û°Ê¦A¦¸°õ¦æ¤@¦¸¡C
   
8. End Sub
   
9. 
   
10. Sub dicStatics()
    
11.     Dim txt As String, dic As Object, dic2 As Object, A As Range, sp As Variant
    
12. 
    
13.     ' txt = [B2] & Left(CStr(Format([A2], "HH:MM:SS")), 5)
    
14.     ' txt = [B2] & Left(CStr([A2]), 5)
    
15.     '  MsgBox txt
    
16. 
    
17.     Set dic = CreateObject("Scripting.Dictionary")
    
18.     Set dic2 = CreateObject("Scripting.Dictionary")
    
19. 
    
20.     For Each A In Range([A3], [A3].End(xlDown))
    
21.         txt = A.Offset(, 1) & "," & Left(Format(A, "HH:MM:SS"), 5)
    
22.         '  dic(txt) = IIf(IsEmpty(dic(txt)), A.Offset(, 4).Value + 1, dic(txt)) + A.Offset(, 4).Value
    
23.         '  ¦b IsEmpty(dic(txt)) §PÂ_®É¡A dic(txt) ·|¦Û°Ê¥ý½á¤©¤@¦¸¤§ A.Offset(, 4).Value ­È¡AµM«á¦A¦¸
    
24.         '  Assign ¤@¦¸ªº A.Offset(, 4).Value ­È¡A ¦p A.Offset(, 4).Value = -1¡A«hµ²ªG·|Åܦ¨ -2¡C
    
25.         '  ¬O¬G§ï¦¨¦p¤U¤è¦¡¡Aª½±µ½á¤©¤@¦¸¤§ A.Offset(, 4).Value ­È¡A«hµ²ªG«K·|Åܦ¨ -1 (ªì©l­È³]©w)¡C
    
26.         dic(txt) = dic(txt) + A.Offset(, 4).Value       '  ¦¸
    
27.         dic2(txt) = dic2(txt) + A.Offset(, 2).Value     '  ¶q
    
28.     Next
    
29.    
    
30.     [M3].Resize(UBound(dic.Keys) + 1) = Application.Transpose(dic.Keys)                '  ¯Á¤Þ­È´N¬O Keys
    
31.     [N3].Resize(UBound(dic.Keys) + 1) = Application.Transpose(dic.Items)               '  ¸ê®Æ¤º®e´N¬O Items
    
32.     [O3].Resize(UBound(dic2.Keys) + 1) = Application.Transpose(dic2.Items)               '  ¸ê®Æ¤º®e´N¬O Items
    
33.    
    
34.     With [M3].Resize(UBound(dic.Keys) + 1, 3)        '  Range("M3:M" & [M3].End(xlDown).Row)
    
35.         .Cells.Sort Key1:=.Cells(1), Order1:=xlDescending, Header:=xlNo    '  xlAscending
    
36.     End With
    
37.    
    
38.     For Each A In Range([M3], [M3].End(xlDown))
    
39.         sp = Split(A, ",")
    
40.         A.Offset(, -1) = sp(0)
    
41.         A = sp(1)
    
42.     Next
    
43. End Sub

½Æ»s¥N½X