ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ¤U©Ô¦¡²M³æ¸Ì¿ï¾Ü"¿z¿ï¤£­«½Æªº¸ê®Æ"

[µo°Ý] ¤U©Ô¦¡²M³æ¸Ì¿ï¾Ü"¿z¿ï¤£­«½Æªº¸ê®Æ"

½Ð°Ý§Ú¤âÃ䦳¤@µ§¸ê®Æ
sheet1:
¡@¡@A
¢°¡@¡´
¢±¡@¡´
¢²¡@¡³
¢³¡@¡³
¢´¡@¡³
¢µ¡@¡°
¢¶¡@¡°
¢·¡@¡°

¤µ¤Ñ§Ú·Q­n¥Î­Ó«ö¶s¦bsheet2¡A«ö¤U«ö¶s«á·|¥X²{µøµ¡¡A³oµøµ¡¦³¤U©Ô¦¡²M³æ¡A¸g¹L¿z¿ï«á¥i¥H¿ï¾Ü¢ÏÄæ¸Ì³o¤TºØ¤£¦P¸ê®Æ

½Ð°Ý¸Ó«ç»ò¤U¤â¤ñ¸û¦n¡H

¤p§Ì¥Ø«e¦b¡i«Ø¥ß«ö¶s¡j¡÷¡i«ö¤U«áshow¥Xµøµ¡¡j¡÷¡i¡H¡j

½Ð«üÂI¤èªk¡AÁÂÁ¡C

¦^´_ 1# lifedidi
ªí³æ¼Ò²Õ
  1. Private Sub UserForm_Initialize()
  2. Set d = CreateObject("Scripting.Dictionary")
  3. With Sheet1
  4.    For Each a In .Range(.[A1], .[A1].End(xlDown))
  5.    d(a.Value) = ""
  6.    Next
  7. End With
  8. ComboBox1.List = d.keys
  9. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

ÁÂÁ¶W¯Åª©¥DªºÀ°¦£

³oµ§¸ê®Æ¬°

sheet1:
¡@¡@A¡@ ¢Ð
¢°¡@¡´¡@3:05
¢±¡@¡´¡@1:51
¢²¡@¡³¡@0:40
¢³¡@¡³¡@3:01
¢´¡@¡³¡@5:11
¢µ¡@¡°¡@5:01
¢¶¡@¡°¡@3:36
¢·¡@¡°¡@2:11

(¡¯¢Ï¬°²£«~Ãþ§O¡@¢Ð¬°»Ý®É)

¤p§Ì»Ý­nªº¬yµ{¡G

¡i¦bSheet2«Ø¥ß«ö¶s¡j¡÷¡i«ö¤U«áshow¥XAµøµ¡¡j¡÷¡i¤U©Ô¦¡²M³æ¿ï¾Ü¡´¡³¡°¤§¤@¡j¡÷¡i¿ï¾Ü¡´¡j¡÷¡iÂI¿ï½T©w¡j¡÷¡i«ö¤U«áshow¥XBµøµ¡¡j¡÷¡i¢Ðµøµ¡¬°¨â­Ó«ö¶s 1.Á`®É¶¡ 2.¥­§¡®É¶¡¡j¡÷¡iÂI¿ï1.Á`®É¶¡¡j¡÷¡ishow¥XCµøµ¡¡j¡÷¡iCµøµ¡¦³­ÓListBOXÅã¥Ü¥Xµ²ªG¡j

·Ð½Ð¤j¤j«üÂI¡A·PÁ¡C

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2013-2-26 14:18 ½s¿è

¦^´_ 3# lifedidi

Sheet2¼Ò²Õ
  1. Private Sub CommandButton1_Click()
  2.    FormA.Show 0
  3. End Sub
½Æ»s¥N½X
FormA¼Ò²Õ
  1. Private Sub CommandButton1_Click()
  2. FormB.Show 0
  3. End Sub

  4. Private Sub UserForm_Initialize()
  5. Set d = CreateObject("Scripting.Dictionary")
  6. With Sheet1
  7.    For Each a In .Range(.[A2], .[A1].End(xlDown))
  8.    d(a.Value) = ""
  9.    Next
  10. End With
  11. ComboBox1.List = d.keys
  12. End Sub
½Æ»s¥N½X
FormB¼Ò²Õ
  1. Private Sub CommandButton1_Click()
  2. FormC.Show 0
  3. FormC.­pºâ 1
  4. End Sub

  5. Private Sub CommandButton2_Click()
  6. FormC.Show 0
  7. FormC.­pºâ 2
  8. End Sub
½Æ»s¥N½X
FormC¼Ò²Õ
  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 Format(Application.Sum(Ar), "hh:mm:ss")
  13. If work = 2 Then ListBox1.AddItem Format(Application.Average(Ar), "hh:mm:ss")
  14. End Sub
½Æ»s¥N½X
Form Test.zip (12.55 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

ÁÂÁ¶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

TOP

¦^´_ 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
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 6# Hsieh

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

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

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

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2013-2-27 19:01 ½s¿è

¦^´_ 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
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

«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...

TOP

¦^´_ 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

TOP

        ÀR«ä¦Û¦b : ¤H­nª¾ºÖ¡B±¤ºÖ¡B¦A³yºÖ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD