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

[µo°Ý] ¤jµ§¸ê®Æ¿z¿ï¡A³t«×«ÜºC(¿z¿ï¤Î±Æ¦C)

[µo°Ý] ¤jµ§¸ê®Æ¿z¿ï¡A³t«×«ÜºC(¿z¿ï¤Î±Æ¦C)

¥»©«³Ì«á¥Ñ lifedidi ©ó 2013-4-30 15:42 ½s¿è

¤j¤j¦n¡G

¤p§Ì¦³¤@­Ó¤u®ÉªºEXCELÀÉ¡A¥æµ¹¦P¨Æ¨Ï¥Î¡A

°_ªì100µ§or500µ§¸ê®Æ³£¥i¥H«Ü¶¶¡A¥\¯à¤]³£ok¡I

¦ý¬O·í¸ê®Æ¼Æ¨Ó¨ì10000µ§¥H¤W®É¡A¶}©lÅܺC¤F¡I

¬d¸ß¤@µ§¸ê®Æ­nµ¥«Ü¤[¡AÁöµMÁÙ¬O¶]±o¥X¨Ó..

½Ð±Ð¤j¤j¦³¨S¦³§ó¦nªº¼gªk©O¡H

¦³´X­Ó­nÂI¡G
1.¸ê®Æ­n¿z¿ï¥X¨Ã¶K¦bSheet2¤W
2.¶K©óSheet2ªº¸ê®Æ»Ý­n·Ó¤é´Á±Æ§Ç
3.²Î­p¿z¿ï«á¸ê®Æ¤¤ RÄæ ®É¶¡ªºÁ`©M

´ú¸ÕÀɸê®Æ¼Æ1.6Wµ§¡A¤½¥q¹q¸£­n¶]2~3¤ÀÄÁ¡A
¦³ÂI¤[...½Ð¤j¤j¤p¤ß:L
´ú¸Õ¥Î.rar (480.01 KB)

¥»©«³Ì«á¥Ñ GBKEE ©ó 2013-4-30 17:11 ½s¿è

¦^´_ 1# lifedidi
¸Õ¸Õ¬Ý
  1. Private Sub UserForm_Initialize()   '°ò¥»³]©w
  2.   '  Set «¬¸¹ = CreateObject("Scripting.Dictionary")  '¯Ó®É:¶·¶]§¹©Ò¦³¸ê®Æ¦C
  3.     Dim X As Integer
  4.    ' Sheets("¿z¿ï¥Î").[A2:A2].ClearContents
  5.     With Sheets("SHEET1")
  6.         'er = .[A65536].End(3).Row
  7.         'myrng = .Range("A7:R" & er)        '¸ê®Æ¼Æ¨Ó¨ì10000µ§¥H¤W®É ***³o·|¦û¥Î°O¾ÐÅé***
  8.          .Cells(1, .Columns.Count) = ""
  9.         .Range("D6", .[D6].End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Cells(1, .Columns.Count), Unique:=True
  10.         '¶i¶¥¿z¿ï DÄæ ¤£­«½Æ¸ê®Æ Unique:=True ¨ì³Ì«á¤@Äæ
  11.         X = 2
  12.         Do While .Cells(X, .Columns.Count) <> ""
  13.             ComboBox1.AddItem .Cells(X, .Columns.Count)
  14.             X = X + 1
  15.         Loop
  16.         .Columns(.Columns.Count).EntireColumn.Clear   '²M°£ ³Ì«á¤@Äæ¸ê®Æ
  17.     End With
  18. End Sub
  19. Private Sub ComboBox1_Change()  '¿ï¾Ü ¤U©Ô¦¡¿ï³æ1 ¥ß§YÅã¥ÜÁ`Á`®É¶¡;¥i¤£¥Î¬d¸ß¶s
  20.     If ComboBox1.ListIndex = -1 Then   '¤£¦b¤U©Ô¦¡¿ï³æªº²M³æ¤º
  21.         MsgBox "±M®×½s¸¹ ½s¸¹ " & ComboBox1 & " ¤£¥¿½T"
  22.     Else
  23.         Application.ScreenUpdating = False
  24.         With Sheets("SHEET1")
  25.             .Range("a6").AutoFilter Field:=4, Criteria1:=ComboBox1            'AutoFilter:  ­ì¸ê®Æ®w¤W¦Û°Ê¿z¿ï.
  26.             With .Range("r:r").SpecialCells(xlCellTypeVisible)
  27.                 TextBox1.Value = Application.Text(Application.Sum(.Cells), "[hh]:mm")
  28.             End With
  29.             .AutoFilterMode = False
  30.         End With
  31.         Application.ScreenUpdating = True
  32.     End If
  33. End Sub
  34. Private Sub CommandButton3_Click()  'Â÷¶}¶s
  35.     Unload UserForm1
  36. End Sub
  37. Private Sub UserForm_Terminate()    '­«·s±Æ¦C
  38.     'Sheets("SHEET1").Range("A7:R" & er) = myrng   '­«·s¶ñ¤W¸ê®Æ¯Ó®É
  39. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 2# GBKEE


    GBKEE¤j¤j¦n¡G

´ú¸Õ¹LOK¡A¦ý¬O¨S¦³±N¿z¿ïªº¸ê®Æ¶K©óSHEET2¡A¬O§_½Ð¤j¤j¦b½s¤@¤U¡A

¤p§Ì¬ã¨s¤@¤U¤j¤jªºµù¸Ñ;P ·PÁ¡C

TOP

¦^´_ 3# lifedidi
¥Î§A­ì¨Óªº¶i¶¥¿z¿ï
²Î­p³¡¤À§ï¥Î¨ç¼Æ­pºâ§Y¥i
  1. Private Sub CommandButton1_Click()  '¬d¸ß¶s
  2.     Dim d1 As Date, d2 As Date
  3.     Dim Srng As Range, Crng As Range, Orng As Range
  4.     Set Srng = Sheets("SHEET1").[A6].CurrentRegion
  5.     Set Crng = Sheets("¿z¿ï¥Î").[A1:A2]
  6.     With Sheets("SHEET2")
  7.         Set Orng = .[A6]
  8.         .[A1:W65536].ClearContents
  9.         Srng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Crng, CopyToRange:=Orng
  10.         If .[A7].Value = "" Then
  11.             MsgBox "¨S¸ê®Æ", vbCritical + vbOKOnly, "ª`·N"
  12.             Exit Sub
  13.         End If
  14.     TextBox1.Value = Application.Text(Application.Sum(.[R:R]), "[hh]:mm")
  15.     End With
  16. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ lifedidi ©ó 2013-5-2 13:47 ½s¿è

¦^´_ 4# Hsieh


¤j¤j¦n¡G

§ï¥Î¤j¤jªº»yªk«á¡Arun°_¨Ó¦³§Ö«Ü¦h¡A¦ýÁÙ¬O¦³­Ó¦a¤è¦³°ÝÃD
½Ð°Ý¤j¤j¤@­Ó°ÝÃD¡A
§Ú¤@ª½¥æ¤e´ú¸Õµo²{¨ì¡A
¿z¿ï¨Ã¶K¤Wªº»yªk¡A«Ü¶O®É¶¡¡A
µ{¦¡½X¦p¤U¡G
  1. Dim HH As Range, KK As Range, PP As Range
  2. Set PP = .[A6]
  3.        .[A1:W65536].ClearContents
  4. HH.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=KK,
½Æ»s¥N½X
¡i°²³]¤­¤dµ§¸ê®Æ¡j
run°_¨Ó¡A¸ê®Æ¤@µ§¤@µ§¶K¤W¡A¶K¨ì¤­¤dµ§»Ý­n¤£¤Ö®É¶¡¡A
(§Ú±NÀx¦s®æ©Ô¦Ü5000¦C³B¡A«ö°õ¦æ¡Aµo²{­n¤@¬q®É¶¡¤~·|run¨ì¤­¤dµ§¡A¦]¦¹§PÂ_¬O³o°ÝÃD)
¥i¬O§Ú§â¤@¼Ëªº¸ê®Æ­ì«Ê¤£°Êªº¶K¨ì·ssheetÀÉ(new excel file)¡A¦brun¡A¤£¨ì¤@¬í´N¶]§¹¤F¡A(........)

(PS)¾ã­ÓexcelÀɮצ³¬ù20­Óuserform¨C­Ó³£¿z¿ï¤£¦P±ø¥ó¡A¤p§Ì¦b²q·Q¡A³o¼Ë·|¤£·|³y¦¨excel°õ¦æ®Éªº½wºC¡H
½Ð¯q¤j¤j·N¨£¡A¤p§Ì²{¦bÀY¦n·w=.= ·PÁÂ

TOP

¦^´_ 5# lifedidi
run°_¨Ó¡A¸ê®Æ¤@µ§¤@µ§¶K¤W¡A¶K¨ì¤­¤dµ§»Ý­n¤£¤Ö®É¶¡¡A
¬°¦ó­n ¸ê®Æ¤@µ§¤@µ§¶K¤W???
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 4# Hsieh

¤j¤j¦n¡G

¦]¬°¦P¨Æ»Ý­n¡i¤u®É¥[Á`(³æ¡B¦h±ø¥ó¿z¿ï)¡j¡i¸ê®ÆÂsÄý¡jªº¥\¯à¡A©Ò¥H¥Ø«e¤p§Ì¥ý¥H³o¥\¯à¥h¥[±j¡C

½Ð°Ñ¦Òªþ¥ó¡C

¡i«Ø¥ß¦b¬Û¦Pª¬ªp¤U¡j
50,000µ§¸ê®Æ ³B²z¼Æ«×§Ö
3,000µ§¸ê®Æ ³B¸Ì¼Æ«×«o¸ûºC

½Ð¤j¤j©âªÅ®É´ú¸Õ¬Ý¬Ý¡AÁÂÁ¡C

¤p§ÌÄ~Äò¬ã¨sXD

    qq.rar (953.9 KB)

TOP

¦^´_ 6# GBKEE


¤j¤j¦n¡G

¤£ª¾¹D¬O¦]¬°¤½¥q¹q¸£Ãö«YÁÙ¬O¨ä¥L¤¸¯À¡A
¦p¦P¤W­±ªº¦^ÂСA

30000µ§¸ê®Æ¬O§Ö³tªºcopy¤Wsheet (¤W¤èªº¦^ÂФº®e¥´¿ù50000¡A§ó¥¿¬°30000)

3000µ§¸ê®Æ«o¬O¤@µ§¤@µ§ªºcopy¤Wsheet

(¤@µ§¤@µ§¶K¤W¬O¸Ø¹¢ªk..½Ð¨£½Ì)

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2013-5-2 16:54 ½s¿è

¦^´_ 8# lifedidi
¤£·|½}!!  
CPU Âù³B²z¾¹ 3.40 GHz  1GBªºRAM
´ú¸Õ 3000µ§¸ê®Æ ¶O®É1¬í , 30000µ§¸ê®Æ ¶O®É4¬í.
  1. Private Sub CommandButton1_Click()  '¬d¸ß¶s
  2.     Dim d1 As Date, d2 As Date, T As Date
  3.     Dim Srng As Range, Crng As Range, Orng As Range
  4.     T = Time
  5. ' µ{¦¡½X.... ¬°¦ó¤£¥Î4# Hsieh ¶Wª©ªºµ{¦¡½X
  6. 'µ{¦¡½X....
  7. 'µ{¦¡½X....
  8.     TextBox1.Value = Format(hh, "00") & ":" & Format(mm, "00")
  9.     MsgBox Application.Text(Time - T, "[SS]¬í")  'Åã¥Ü°õ¦æ¹Lµ{ªº®É¶¡
  10. End Sub
½Æ»s¥N½X
¦p¦b30000µ§¸ê®Æªº¤u§@ªí¤W¥Î¦Û°Ê¿z¿ï¨úªº¸ê®Æ·|§ó§Öªº
  1. Private Sub ComboBox1_Change()  '¿ï¾Ü ¤U©Ô¦¡¿ï³æ1 ¥ß§YÅã¥ÜÁ`Á`®É¶¡;¥i¤£¥Î¬d¸ß¶s
  2.     Dim T As Date
  3.     T = Time
  4.     If ComboBox1.ListIndex = -1 Then   '¤£¦b¤U©Ô¦¡¿ï³æªº²M³æ¤º
  5.         TextBox1 = ""
  6.         MsgBox "±M®×½s¸¹ ½s¸¹ " & ComboBox1 & " ¤£¥¿½T"
  7.     Else
  8.         Application.ScreenUpdating = False
  9.         With Sheets("¤u®É¸ê®Æ®w")
  10.             .Range("a6").AutoFilter Field:=4, Criteria1:=ComboBox1            'AutoFilter:  ­ì¸ê®Æ®w¤W¦Û°Ê¿z¿ï.
  11.             With .Range("r:r").SpecialCells(xlCellTypeVisible)
  12.                 TextBox1.Value = Application.Text(Application.Sum(.Cells), "[hh]:mm")
  13.             End With
  14.             .AutoFilterMode = False
  15.         End With
  16.         Application.ScreenUpdating = True
  17.         MsgBox Format(Time - T, " SS ¬í")
  18.     End If
  19. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ lifedidi ©ó 2013-5-2 19:25 ½s¿è

¦^´_ 9# GBKEE


    to GBKEE¤j¤j

¤p§Ìªº»yªk¡G

¡iA.©ósheet1¿z¿ï¸ê®Æ¡j¡÷¡iB.©ósheet2¶K¤W¸ê®Æ¡j¡÷¡iC.sheet2°µ®É¶¡¥[Á`¡j

¡°¤p§Ì¦b©óB.·|¶]«Ü¤[¡A¤U¯Z¦^¨ì®a¤]¬O¦P¼Ë±¡§Î¡A±¡§Î¤j·§¦pªþÀÉ(¼v¤ù)

3000µ§¸ê®Æ.rar (886.57 KB) 30000µ§¸ê®Æ.rar (416.4 KB)

¯u¨¸ªùXD

GBKEE¤j¤jªº»yªk¡G

¡i©ósheet1¿z¿ï¸ê®Æ¡j¡÷¡i©ósheet1®É¶¡¥[Á`¡j

¡°½Ð°Ý¤j¤j¿z¿ï¸ê®Æ¥i¥H¦h±ø¥ó¿z¿ï¶Ü¡H°²³]¦P®É­n¿z¿ï¡G±M®×½s¸¹¡B¾¤u½s¸¹¡B¤é´Á¡E¡E¡Eªº±ø¥ó¡C(½Ð°Ñ¦ÒEXCELÀɮײĤGºØ¬d¸ß)

¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w
Hsieh¤j¤jªºµ{¦¡½X¤]·|¦³¦P¼Ëªº°ÝÃD(½Æ»s¶K¤W»Ý­n®É¶¡)¡A

¦ý¬O§Ú±N®É¶¡¥[Á`ªº»yªk§ï¦¨Hsieh¤j¤jªº»yªk¡A¤ñ¸û²¼ä¡A«¢XD

¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w¢w
GBKEE¤j¤j¤Ó¯«¤F§a¡A¹Bºâ®É¶¡³ºµM¤]¥i¥Hºâªº¥X¨Ó...

TOP

        ÀR«ä¦Û¦b : ¡i®É¤é²öªÅ¹L¡j¤@­Ó¤H¦b¥@¶¡°µ¤F¦h¤Ö¨Æ¡A´Nµ¥©ó¹Ø©R¦³¦hªø¡C¦]¦¹¥²¶·»P®É¶¡Ävª§¡A¤Á²ö¨Ï®É¤éªÅ¹L¡C
ªð¦^¦Cªí ¤W¤@¥DÃD