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

¬°¤°»ò§ÚªºExcel ¤£·|°Ê ??

¬°¤°»ò§ÚªºExcel ¤£·|°Ê ??

¥»©«³Ì«á¥Ñ color790 ©ó 2011-11-11 17:29 ½s¿è

°Ý¤@­Ó«Ü²Ê²Lªº°ÝÃD.....(¤£¹L§Ú§Ö³Q·dºÆ¤F)

¥D­n¬O·Q¥Îª©¥DGBKEE ³o¤@¬qµ{¦¡ http://forum.twbts.com/thread-4188-1-1.html
¥u¬O³£µLªk°Ê,©Ò¥H§ä¤F¥H¤U³Ì²³æªºµ{¦¡¨Ó¸Õ¸Õµ²ªG¤]¤£¦æ,
Private Sub Worksheet_Calculate()
Range("b1").End(xlDown).Offset(1) = Range("a1")
End Sub
·N«äÀ³¸Ó¬O[A1]¦pªGÅÜ°Ê,¦bBÄæ·|°O¿ý¤U¨Ó, ¥i¬O¬°¤°»ò§ÚªºExcel ¤£·|°Ê ??
µ{¦¡¼g¦b¤u§@ªí1¤]¤£·|°Ê, ThisWorkbook¤]¤£·|°Ê
¬¡­¶Ã¯2.rar (10.9 KB)
ªì¾ÇªÌcolor

¦^´_ 1# color790


    Worksheet_Calculate¬O¤u§@ªí­«ºâ¨Æ¥ó
¥²¶·¬O¤u§@ªí¤º¦³¤½¦¡¦s¦b
·í¤½¦¡Àx¦s®æ¦³­«ºâ°Ê§@¤~·|IJµo
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

­è¦³­«¸Õ¤@¤U, §Ú¥ÎDDE, ¼Æ¾Ú½T©w·|°Ê, ¦ýBÄæ«o³£¤£·|°Ê
ÁÙ¬O»¡­n³]©w­þÃä, ¯u©_©Ç
ªì¾ÇªÌcolor

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2011-11-11 18:36 ½s¿è

¦^´_ 1# color790
Range("b1").End(xlDown).Offset(1) = Range("a1").Address
³o»yªk°ò¥»¤W¨S¦³¿ù»~ªº   ¦ý¦pªG BÄæ¬O§¹¥þ¨S¦³¸ê®Æ,©Î¬Oª½¨ì³Ì«á¤@¦C¤~¦³¸ê®Æ
Range("b1").End(xlDown).Offset(1) ->·|¬O¤u§@ªí½d³ò¤§¥~ ,§AªºªþÀÉ´N¦³¿ù»~ªº

¨Ò¦b b1 ¥H¤U¥ô¤@¦C¿é¤J ¸ê®Æ ´N¨S¿ù»~¤F

Range("b1").End(xlDown).Offset(1) = "=" & Range("a1").Address  
¤½¦¡¤~·|¤Þµo Worksheet_Calculate ³o¨Æ¥ó

TOP

¥i¥H¤F.... µ²ªG¯uªº«Ü©_©Ç
¦n¹³­n¦b¥¨¶°³]©w¸Ì, ÂI¿ï "±Ò¥Î©Ò¦³¥¨¶°(......) "
¥H«e³£ÂI¿ï "°£¤F¸g¼Æ¦ìñ³¹ªº¥¨¶°¥~,°±¥Î©Ò¦³¥¨¶°" , ¶}Àɮ׫ö±Ò¥Î¥¨¶°´N¥i¨Ï¥Î¥¨¶°
³o­Ó©~µM¤£¦æ.....
¤£¹LÁ`ºâ¥i¥Î¤F, ±ß¤@ÂI¦A¨Ó¸Õ¸ÕGBKEEªºµ{¦¡
ÁÂÁ¤j®aªºÀ°¦£, ¦pªG¦³°ÝÃD,¦A³Â·Ð¦U¦ìÀ°¦£¸Ñµª...... ^^
ªì¾ÇªÌcolor

TOP

HI~GBKEEª©¥D
­è¥Î§A¼gªºµ{¦¡, ¤S¤£·|°Ê¤F
¥i§_³Â·Ð§AÀ°§Ú¬Ý¤@¤U(§Ú¦³­×§ï®É¶¡¤Î¤@ÂIÂI¨ä¥Lªº)
¬¡­¶Ã¯2.rar (16.12 KB)
ªì¾ÇªÌcolor

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2011-11-11 21:00 ½s¿è

¦^´_ 6# color790
¦³¤ÏÀ³ªº
startTime = Range("b1") '¶}½L®É¶¡, ¨Ò¦p: "09:00:00 AM"
stopTime = Range("d1")  '¦¬½L®É¶¡, ¨Ò¦p: "01:30:00 PM"
§A´ú¸Õ®É¶¡»Ý¦bstartTime - stopTime ®É¶¡¤º ,¥i½Õ¾ã B1,D1
¦p¹Ï³]¥ß¤¤Â_ÂI  , ¤u§@ªí¤¤¿ï¾Ü¦³¤½¦¡ªºÀx¦s®æ  ¥ý«ö F2  ¦A«ö Enter «K¥i¨£°Ê§@¤F
¤§«á¦bVBA µøµ¡ «ö F8 ³v¨B°õ¦æ


TOP

ª©¥D,§Ú¤£¤ÓÀ´
§ÚD1²{¦b¬O³] 10:00:00 PM ,  B1¬O09:00:00 AM
¥Ñ©ó¬O§ì¥~°êªºDDE©Ò¥H¥Ø«e³£·|°Ê, ¤]³]¥ß¤¤Â_ÂI, ¤]«ö F2  ¦A«ö Enter
¦ý¦bVBAµøµ¡«öF8³£¬O¨ì©|¥¼¶}½L¤U¤@¦æ´N¤S°j°é¤F, ³o¬O¬°¤°»ò?
ªì¾ÇªÌcolor

TOP

¦^´_ 8# color790
ºF·\ ¬O§Ú¨º¤@½g¼g¿ù¤F ,¤w§ó¥¿¤F.
  1. Private Sub Worksheet_Calculate()
  2.     Dim startTime, stopTime
  3.     Dim Tr As String, Time_Step As Date
  4.     Time_Step = #12:05:00 AM#     '³]©w¶¡¹j®É¶¡: 5¤ÀÄÁ->300¬í
  5.     'Time_Step = #12:00:01 AM#     '³]©w¶¡¹j®É¶¡: 1¬í
  6.     startTime = Range("b1") '¶}½L®É¶¡, ¨Ò¦p: "09:00:00 AM"
  7.     stopTime = Range("d1")  '¦¬½L®É¶¡, ¨Ò¦p: "01:30:00 PM"
  8.     If startTime > Time Then  '<- ­×§ï³o¸Ì  ©|¥¼¶}½L      
  9.         Exit Sub
  10.     ElseIf stopTime < Time Then '<- ­×§ï³o¸Ì   ¤w¸g¦¬½L
  11.         Exit Sub
  12.     Else
  13.         If [C2] <> "-" And [C2] <> "###" Then   '²M½Lªºª¬ºA, ¤£¨ú¨ä¸ê®Æ
  14.             'Tr = Int((nowTime - startTime) * 1440) + 30  '¨C®t 300 ¬í´N´«¤@¦C
  15.             Tr = Int((Time - startTime) / Time_Step) + 30 ' ¨C5¤ÀÄÁ¤@¦C ±q²Ä30¦C¶}©l
  16.             If Range("D" & Tr) = "" Then Range("D" & Tr) = Range("C2")  '¶}©l»ù
  17.             If Range("E" & Tr) = "" Or Range("C2") > Range("E" & Tr) _
  18.                 Then Range("E" & Tr) = Range("C2")                      '³Ì°ª»ù
  19.             If Range("F" & Tr) = "" Or Range("C2") < Range("F" & Tr) _
  20.                 Then Range("F" & Tr) = Range("C2")                      '³Ì§C»ù
  21.             Range("G" & Tr) = Range("C2")                               'µ²§ô»ù
  22.         End If
  23.     End If
  24. End Sub
½Æ»s¥N½X

TOP

ª©¥D¤£­n³o»ò»¡, §AªÖÀ°¦£¤w¸g«Ü¦n¤F....
¥Ñ©ó²{¦b¬O°²¤é¨S¦³DDE¨Ó·½¥i¥Î, §«ô¤@¦A¨Ó§¹¾ãªº¨Ï¥Î¬Ý¬Ý,
¦pªG¥Îª©¥Dªº¤èªk(³]¤¤Â_ÂI,«öF8´ú¸Õ), ·|¦b³o¤@¦æ°±¤î
  Tr = Int((Time - startTime) / Time_Step) + 30
´«¦¨¥t¤@¦æOK
Tr = Int((Time - startTime) * 1440) +30
ªì¾ÇªÌcolor

TOP

        ÀR«ä¦Û¦b : ¤@­Ó¯Ê¤fªºªM¤l¡A¦pªG´«¤@­Ó¨¤«×¬Ý¥¦¡A¥¦¤´µM¬O¶êªº¡C
ªð¦^¦Cªí ¤W¤@¥DÃD