lifedidi

¦^´_ 13# GBKEE

©êºp¡A³Ìªñ¤u§@¤ñ¸û¦£¡A·PÁ¨â¦ì¤j¤jªºÀ°¦£¡A²M°£ªº«ü¥OOK¤F:) ¡A¦ý¬O¨â±ø¥óªº¿z¿ï§Ú±Nµ{¦¡½X¶K¤W¥hÁÙ¬O¶]¤£¥X¨Ó¡A

²¤Æ¤F¤@¤Uªí³æ

¨â­Ó±ø¥ó¿z¿ï.rar (42.34 KB)

¤U¸ü¦¸¼Æ: 10

2013-3-2 17:58

§Ú§â°ÝÃD¥Î¹Ï§Îªí¹F¡AÁÂÁ¡C

GBKEE

¦^´_ 12# lifedidi
1¦³¿ìªk·Ó¶¶§Ç

1. Private Sub UserForm_Initialize()  'ªí³æªì©l¤Æªºµ{§Ç
   
2.     Dim D As Object
   
3.     Set D = CreateObject("Scripting.Dictionary")    '¦r¨åª«¥ó
   
4.     With Sheet1
   
5.         For Each a In .Range(.[D7], .[D7].End(xlDown))
   
6.             D(a.Value) = ""
   
7.         Next
   
8.     End With
   
9.     ComboBox1.List = D.keys       '±M®×¿ï¶µ¤º®e
   
10. End Sub
    
11. Private Sub ComboBox1_Change()      '±M®×¿ï¶µ¤º®e: ¦³§ïÅÜ
    
12.     If ComboBox1.ListIndex > -1 Then
    
13.         ComboBox2¸ê®Æ
    
14.     Else                            '§ïÅܪº¤º®e¤£¦bList¤¤
    
15.         ComboBox2.Clear
    
16.     End If
    
17. End Sub
    
18. Private Sub ComboBox2¸ê®Æ()
    
19.     Dim D As Object
    
20.     Set D = CreateObject("Scripting.Dictionary")
    
21.     With Sheet1
    
22.         For Each a In .Range(.[D7], .[D7].End(xlDown))
    
23.             If a.Value = ComboBox1 Then D(a.Offset(, 3).Value) = ""    '
    
24.         Next
    
25.         With .Columns(.Columns.Count).EntireColumn           'Sheet1ªº³Ì«á¤@Äæ
    
26.             .Clear
    
27.             .Cells(1).Resize(D.Count, 1) = Application.WorksheetFunction.Transpose(D.keys)
    
28.             '*** ±Æ§Ç
    
29.             .Cells(1).Resize(D.Count, 1).Sort Key1:=.Cells(1), Order1:=xlAscending, Header:= _
    
30.                     xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    
31.                     SortMethod:=xlStroke, DataOption1:=xlSortNormal
    
32.             '*******
    
33.             ComboBox2.List = .Cells(1).Resize(D.Count, 1).Value  '¤uºØ¿ï¶µ¤º®e
    
34.             ComboBox2.Value = ComboBox2.List(0)                  '¤uºØ¿ï¶µªº­È
    
35.             .Clear
    
36.         End With
    
37.     End With
    
38. End Sub

½Æ»s¥N½X

2¹q¸£¤£¦Y¤O

1. Public Sub ²M°£()
   
2.     With Sheet2
   
3.         '.Cells(7, 3).Resize(999 - 7 + 1, 25 - 3 + 1) = ""
   
4.         .Cells(7, 3).Resize(993, 23) = ""
   
5.         'For i = 7 To 999
   
6.         '    For j = 3 To 25
   
7.         '   Cells(i, j) = ""
   
8.         '   Next
   
9.         ' Next
   
10.     End With
    
11. End Sub

½Æ»s¥N½X

lifedidi

¦^´_ 11# Hsieh


    ·PÁ¤j¤jªº¤£Ã㨯³Ò¡A¬Ý¤F§Aªº¬yµ{·Pı§ó¶¶ºZ¡A§Ú­×§ï¤@¤U§Úªº°ÝÃD¨Ã§â°ÝÃD¼g¦bexcel¸Ì¡AÁÂÁ¡C

¤u®É¨t²Îexcelª©¥»)20130228.rar (80.41 KB)

¤U¸ü¦¸¼Æ: 8

2013-2-28 13:00

Hsieh

¦^´_ 10# lifedidi
¤£¬O«Ü²M·¡§A­n¬Æ»ò¡A¸Õ¸Õ¬Ýªþ¥ó¬yµ{¬Ý¬O§_²Å¦X»Ý¨D

¤u®É¨t²Îexcelª©¥»).rar (51.31 KB)

¤U¸ü¦¸¼Æ: 13

2013-2-27 23:24

lifedidi

¦^´_ 8# Hsieh


¤j¤j§A¦n¡G

¥H¤U¬°§Úµ{¦¡½X¡A¤p§Ì·M¬N¡A½ÐÀ°¦£­×§ï¡C

·Qªk¡G
¡i¥ý¿z¿ïD7¥H¤U¸ê®Æ¡j¡÷¡i¦A¿z¿ïG7¥H¤U¸ê®Æ¡j¡÷¡i¨â¦¸¿z¿ï«áªº¸ê®ÆÁ`¤u®É¬Û¥[(¨Ã§â¸ê®Æ¶K¦bC7¤§«á)¡j

¾Þ§@¡G
¡i²Ä¤@µøµ¡¡G¿ï¾Ü¸ê®Æ(D7¿z¿ï)«ö½T©w¡j¡÷¡i²Ä¤Gµøµ¡¡G¿ï¾Ü¸ê®Æ(G7¿z¿ï)®×½T©w¡j¡÷¡i²Ä¤Tµøµ¡¡GListBox¨q¥XÁ`¤u®É¡j *©Ò¿z¿ï¥Xªº¸ê®ÆPO¦bC7¤§«á

Sub ­pºâ(work As Integer)
Dim Ar(), Ay()
With Sheet1
   For Each a In .Range(.[D7], .[D7].End(xlDown))
      If a.Value = ¤uºØ¬d¸ßx±M®×½s¸¹.ComboBox1.Value Then
         ReDim Preserve Ar(s)
         ReDim Preserve Ay(s)
         Ar(s) = a.Offset(, 14).Value
         Ay(s) = a.Offset(, -3).Resize(, 23).Value
         s = s + 1
      End If
   Next
   For Each a In .Range(.[G7], .[G7].End(xlDown))
      If a.Value = ¤uºØ¬d¸ßx±M®×x¿ï¾Ü¤uºØ.ComboBox1.Value Then
         ReDim Preserve Ar(x)
         ReDim Preserve Ay(x)
         Ar(x) = a.Offset(, 11).Value
         Ay(x) = a.Offset(, -6).Resize(, 23).Value
         x = x + 1
      End If
   Next
        
End With
If s > 0 Then
Sheet2.Range("C5").CurrentRegion.Offset(2) = ""
Sheet2.[C7].Resize(x, 23) = Application.Transpose(Application.Transpose(Ay))
If work = 1 Then ListBox1.AddItem Application.Text(Application.Sum(Ar), "[hh]:mm:ss")
End If
End Sub

lifedidi

«D±`ÁÂÁÂHsieh¤j¤j¡A·Ó§Aªº°µªk¦bshow«á­±¥[0 ´N¥i¥H¶¶§Q§¹¦¨¡C

¤p§Ì·Q¥H¦¹Ãþ±À¨ä¥L³¡¤À¡A¥i¥H¸ÑÄÀÅܼƪº³¡¤À¶Ü¡HÁÂÁ¡I

¤p§ÌÄ~Äò¬ã¨s...

Hsieh

¦^´_ 7# lifedidi
­º¥ý¥ý§â©Ò¦³Form.Showªº°Ñ¼Æ¥[¤W0
Form.Show 0
Åý¶}±Òªºªí³æ³£¬°«D±j¨î¦^À³

1. Sub ­pºâ(work As Integer)
   
2. Dim Ar(), Ay()
   
3. With Sheet1
   
4.    For Each a In .Range(.[D7], .[D7].End(xlDown))  '¦bDÄ檺¸ê®Æ´`Àô
   
5.       If a.Value = ±M®×½s¸¹.ComboBox1.Value Then   '¦pªGDÄ檺­Èµ¥©ó¤U©Ô¿ï³æªº­È
   
6.          ReDim Preserve Ar(s)  '«O¯d°}¦C¤¸¯À¨Ã­«³]°}¦C¤W­­
   
7.          ReDim Preserve Ay(s)
   
8.          Ar(s) = a.Offset(, 17).Value  '±NDÄæ¦V¥k17Ä檺­È¼g¤J°}¦C
   
9.          Ay(s) = a.Offset(, -3).Resize(, 26).Value  '±NA:ZÄ檺­È¼g¤J°}¦C
   
10.          s = s + 1  '¹w³Æ¤U¦¸°}¦CÂX®iªº¤W­­
    
11.       End If
    
12.    Next
    
13. End With
    
14. If s > 0 Then  '¦pªG¦³²Å¦Xªº¸ê®Æ
    
15. Sheet2.Range("C5").CurrentRegion.Offset(2) = ""   '¥ý²MªÅ¤W¦¸ªº¬d¸ß¤º®e
    
16. Sheet2.[C7].Resize(s, 26) = Application.Transpose(Application.Transpose(Ay))   '¼g¤J¤u§@ªí
    
17. If work = 1 Then ListBox1.AddItem Application.Text(Application.Sum(Ar), "[hh]:mm:ss")  '¤å¦r¤è¶ôÅã¥Ü¥[Á`µ²ªG
    
18. If work = 2 Then ListBox1.AddItem Application.Text(Application.Average(Ar), "[hh]:mm:ss")  '¤å¦r¤è¶ôÅã¥Ü¥­§¡µ²ªG
    
19. End If
    
20. End Sub

½Æ»s¥N½X

lifedidi

¦^´_ 6# Hsieh

¤j¤j¡A½Ð³Â·ÐÀ°¦£¬Ý¤@¤U­þ¸Ì¦³°ÝÃD¡A

¤u®É¨t²Îexcelª©¥»).rar (69.61 KB)

¤U¸ü¦¸¼Æ: 11

2013-2-26 17:21

ÁÂÁ¡A¨¯­W¤F¡C

Hsieh

¦^´_ 5# lifedidi
§A¬O­n¦bLISTBOX¤ºÅã¥Ü©Î¬OÀx¦s®æ¤ºÅã¥Ü?
LISTBOX¤º­nÅã¥Ü¶W¹L24¤p®É¥[Á`®É¶¡

1. Sub ­pºâ(work As Integer)
   
2. Dim Ar()
   
3. With Sheet1
   
4.    For Each a In .Range(.[A2], .[A1].End(xlDown))
   
5.       If a.Value = FormA.ComboBox1.Value Then
   
6.          ReDim Preserve Ar(s)
   
7.          Ar(s) = a.Offset(, 1).Value
   
8.          s = s + 1
   
9.       End If
   
10.    Next
    
11. End With
    
12. If work = 1 Then ListBox1.AddItem Application.Text(Application.Sum(Ar), "[hh]:mm:ss")
    
13. If work = 2 Then ListBox1.AddItem Application.Text(Application.Average(Ar), "[hh]:mm:ss")
    
14. End Sub

½Æ»s¥N½X

­YÀx¦s®æ®æ¦¡«h¦Û­q¬°[hh]:mm

lifedidi

ÁÂÁ¶W¯Åª©¥DªºÀ°¦£¡I¤p§Ì¤½¦¡ÁÙ¦b§l¦¬¤¤¡C

½Ð±ÐÀx¦s®æªº°ÝÃD¡G

BÄ欰¤p®É:¤À (ex: 03:50 ¬° 3¤p®É50¤ÀÄÁ)

§ÚªºÀx¦s®æ¦¡¸Ó¥Î­þ¤@ºØ«¬¦¡¡H¤p§Ì¥Ø«e¥Î"¦Û­q"mm:hh

run°_¨Ó©Ç©Çªº¡AÅã¥Ü¤£¥X¨Ó¡A

¤p§Ì¤â¤Wªº¸ê®Æ¤j¬ù¦ô­p¥[Á`«á¬°´X¦Ê­Ó¤p®É¡A

¹ïÀ³ªº®æ¦¡¸Ó«ç»ò³]©w©O¡H³Â·Ð¤F¡C