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

¡y¤w¸Ñ¨M¡zdde tick¸ê®ÆÂà´«¤ÀÄÁ¸ê®Æ

¡y¤w¸Ñ¨M¡zdde tick¸ê®ÆÂà´«¤ÀÄÁ¸ê®Æ

¥»©«³Ì«á¥Ñ ko310kmo ©ó 2011-5-10 23:06 ½s¿è

§Ú¬O¨Ï¥Î¥ÃÂת÷ªº(e-leader)ªºDDE
§Ú¨C¤é³£·|¿z¿ï¥X§Ú¤µ¤Ñ·QÆ[¹îªº¼i¬ù»ù¡A¦C¦bSheet1
¨Ã¥H¦U§O¼i¬ù»ù©R¦W·s¼W¤u§@ªí¡A°O¿ý¤U¥¦ªº§Y®É¸ê®Æ

§Ú²{¦bªº°ÝÃD¦b©óe-leader¬O¢ì¢ì¢í¬O¨S¦³¤é´ÁÄæ¦ì
§Ú·Q¦b¨C­Ó¤u§@ªí¤¤¢ØÄæ¤À§O¬ö¿ý¤U¥¦­Ìªº§Y®É¸ê®Æ
¦A¢ØÄ檺¹j¾À·Q°O¤U¥¦­Ì¨C¤ÀÄÁªº¶}¡B°ª¡B¦¬¡B§C¡B¦¨¥æ¶q
¦³·Qªk¡A¦ý«o¤£ª¾¸Ó±q¦ó°µ°_¡I¡I
½Ð¦U¦ì¤j¤jÀ°¦£¡I¡I¡@ÁÂÁÂ
50 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

¦^´_ 1# ko310kmo

­º¥ý­n§ì¨ú§Y®É³ø»ù³nÅ餺ªº¸ê®Æ,
³Ì²³æªº¤è¦¡´N¬O±Ä¥Î¸Ó³nÅé¦Û±aªº¶×¥X¨ì(©Î¬O©ì¦²¨ì) Excel ªí®æ¤¤ªº¥\¯à,
¶×¥X«á¦bExcelªí¤Wªº´N¬O§Y®Éªº³ø»ù¸ê®Æ, (°O±o¶}³ø»ù³nÅé)
¦p¦¹¨C¹j¤@¬q®É¶¡ Copy ¤@¦¸¨ì¨ä¥Lªí®æ¤ºÀ³¸Ó´N¬O§A­nªºªF¦è¤F.

¦Ó¤é´Á¥i¥H¥ÎMyDate = Date §ì,
®É¶¡´N§ì¥Ø«eªº®É¶¡ : MyTime = Time)

¦Ü©ó¨C¹j¤@¬q®É¶¡´N­n°µ¬Y¥ó¨Æªº¤è¦¡´N½Ðª¦ª¦¤åÅo,
¦¹¯¸¤W¤§«e¤w¤£¤Ö¤H°Ý¹L,
¥i¨Ñ°Ñ¦Òªº¨Ò¤lÀ³¸Óº¡¦hªº.

TOP

¦^´_ 1# ko310kmo

®É¶¡¤W»yªk¥i¥H°Ñ¦Ò§ÚPoªº¤å (­n·PÁªO¥Dªº¸Ñµª)
¤]¦³Ãþ¦üªº°ÝÃD

TOP

¥»©«³Ì«á¥Ñ ko310kmo ©ó 2011-5-6 10:34 ½s¿è

¦^´_ 2# luhpro


¡y­º¥ý­n§ì¨ú§Y®É³ø»ù³nÅ餺ªº¸ê®Æ,
³Ì²³æªº¤è¦¡´N¬O±Ä¥Î¸Ó³nÅé¦Û±aªº¶×¥X¨ì(©Î¬O©ì¦²¨ì) Excel ªí®æ¤¤ªº¥\¯à,
¶×¥X«á¦bExcelªí¤Wªº´N¬O§Y®Éªº³ø»ù¸ê®Æ, (°O±o¶}³ø»ù³nÅé)
¦p¦¹¨C¹j¤@¬q®É¶¡ Copy ¤@¦¸¨ì¨ä¥Lªí®æ¤ºÀ³¸Ó´N¬O§A­nªºªF¦è¤F.¡z

³o¸Ì§Ú¥»¨Ó´N¤w¸g°µ¥X¨Ó¤F¡Aµ´¹ï¤£¬O°½Ãi¨S·Q¹L¡A´N¶K¤W¨Ó°Ý¤j®a

§Ú¦³·j´M¹L¸ê®Æ¡A¦ý¦h¥b¬O¤@¹ï¤@ªº¡]°w¹ï¤@ÀɪѲ¼or¤@ÀÉ´Á³f¡^
¹³§Ú³oºØ¤@¹ï¦h­Óªº¦n¹³¤ñ¸û¤Ö¡A¦Ó¥B§Ú©È§ÚªºCalculate¨Æ¥ó·|­t¾á¹L¤jorµo¥Í¿ù¶Ã
ÁÙ¦³¤]¤ñ¸û¤Ö¬Ý¨ì¦P®É¦h­Óªí®æ³£¶i¦æ§Y®É»ù®æ«ùÄò°O¿ý¨Ã§ì¥X¨C¤@¤ÀÄÁªº¶}¡B°ª¡B¦¬¡B§C¡B¦¨¥æ¶qªº¼gªk¡A
¦Ó¥B§ÚÁÙ¬O¤p¾Ç¥Í¶¥¯Å¡AµLªk¶}ªþ¥ó¨Ó¬Ý
©Ò¥H¤~·|¶K¥X¨Ó½Ð±Ð¤j®aªº¡AÁÂÁ¡C
50 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¦^´_ 4# ko310kmo
§Ú·Q¦b¨C­Ó¤u§@ªí¤¤¢ØÄæ¤À§O¬ö¿ý¤U¥¦­Ìªº§Y®É¸ê®Æ,¦A¢ØÄ檺¹j¾À·Q°O¤U¥¦­Ì¨C¤ÀÄÁªº¶}¡B°ª¡B¦¬¡B§C¡B¦¨¥æ¶q.
³o¬OÃþ¦ü´Á³f§a ,§Ú¤£¤F¸Ñ´Á³f½Ð°Ý¤@¤U:
§AªºªþÀÉSheet1  ¥u¦³   ¼i¬ù»ù , §Y®É¦¨¥æ»ù, ³æ¶q.  ¦ý¦U¤u§@ªí¥u¦³ ¼i¬ù»ù ,§Y®É¦¨¥æ»ù,¥v¦¨¥æ»ù,³æ¶q
¶}¡B°ª¡B¦¬¡B§C¡B¦¨¥æ¶q ªº¸ê®Æ ¬O­n±qsheet2 ¨úªº¶Ü? ¦p¬O Sheet2ªº¸ê®Æ·|¸òµÛ SHEET1§Y®É¦¨¥æ»ù ¨ÓÅܰʶÜ?

TOP

¥»©«³Ì«á¥Ñ ko310kmo ©ó 2011-5-6 10:36 ½s¿è

¦^´_ 5# GBKEE


¨C¤@­Ó¤u§@ªíªº¾ú¥v¦¨¥æ»ù¬O§Q¥Îsheet1ªºÅܰʨӬö¿ýªº¡A¥u­n¤@ÅÜ°Ê´N·|©¹JÄæ¤@¤U¦V¤U¬ö¿ý¡A
§Ú·Q±qJÄ檺¬ö¿ý¤¤§ì¥X¥¦¨C¤@¤ÀÄÁªº¶}¡B°ª¡B¦¬¡B§C¡BÁ`¶q
(JÄæ¬O³Q°Ê±µ¨üDDE¶Ç¨Óªº­È¡A¨C¬í¤£°±ªº±µ¦¬¡A§Ú·Q¨C¨ì¤@¤ÀÄÁ´N²Î­p¥X¥¦ªº¶}¡B°ª¡B¦¬¡B§C¡B¦¨¥æ¶q)
sheet2¨ä¹ê¥u¬O§Ú¿z¿ï¥X¼i¬ù»ùªº¤u¨ã¡A¸ò¨ä¥Lsheet¨S¦³¤Ó¤jªºÃö«Y
§Ú¬O·Q¦bsheet1ªºCalculate¨Æ¥ó¼g¤Jµ{¦¡¡A¥H§¹¦¨
¦P®É¦h­Óªí®æ³£¶i¦æ¦U¦Ûªº¾ú¥v¦¨¥æ»ù«ùÄò°O¿ý¨Ã§ì¥X¨C¤@¤ÀÄÁªº¶}¡B°ª¡B¦¬¡B§C¡B¦¨¥æ¶qªº¼gªk¡A
¦ý©È·|­t¾á¹L¤jorµo¥Í¿ù¶Ã§ì¤£¨ì¥¿¯uÅܰʪºÀx¦s®æ
ÁÂÁÂ
50 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¦^´_ 6# ko310kmo
¸Õ¸Õ¬Ý ¤£²z·Q¦A»¡
¨C¤ÀÄÁªº¦¨¥æ¶q  ¨S¦³¦¨¥æ®É¶¡ªºÅÜ°Ê°µ¤£¥X¨Ó
  1. Sub Ex()      Dim t As Date, i%, R%, P%   
  2.    With Sheet1
  3.         R = .[B1].End(xlDown).Row
  4.         Do
  5.             ReDim AR(1 To 6, 2 To R)
  6.             t = Time
  7.             For i = 2 To R
  8.                 AR(1, i) = TimeSerial(Hour(Time), Minute(Time), 0)   '¨C¤ÀÄÁ®É¶¡
  9.                 AR(2, i) = .Cells(i, 3)   '¨C¤ÀÄÁªº¶}½L»ù
  10.                 AR(3, i) = .Cells(i, 3)   '¨C¤ÀÄÁªº³Ì°ª»ù
  11.                 AR(5, i) = .Cells(i, 3)   '¨C¤ÀÄÁªº³Ì§C»ù
  12.                 'AR(6, i) = .Cells(i, 4)   '¨C¤ÀÄÁªº¦¨¥æ¶q
  13.             Next
  14.             Do While Minute(Time) = Minute(t)
  15.                 For i = 2 To R
  16.                     DoEvents
  17.                     AR(3, i) = IIf(.Cells(i, 3) > AR(3, i), .Cells(i, 3), AR(3, i)) '¨C¤ÀÄÁªº³Ì°ª»ù
  18.                     AR(5, i) = IIf(.Cells(i, 3) < AR(5, i), .Cells(i, 3), AR(5, i)) '¨C¤ÀÄÁªº³Ì§C»ù
  19.                     '  AR(6, i) = AR(6, i) + .Cells(i, 4)                             '¨C¤ÀÄÁªº¦¨¥æ¶q
  20.                 Next
  21.             Loop
  22.             For i = 2 To R
  23.                 AR(4, i) = .Cells(i, 3)                '¨C¤ÀÄÁªº¦¬½L»ù
  24.                 P = Sheets(.Cells(i, 2).Text).Cells(Row.Count, "J").End(xlUp).Row + 1
  25.                 Sheets(.Cells(i, 2).Text).Range("J" & P).Resize(1, 6) = Application.Index(Application.Transpose(AR), i - 1)
  26.             Next
  27.         Loop Until Time <= #1:30:00 PM#
  28.     End With
  29. End Sub
½Æ»s¥N½X

TOP

¦^´_ 7# GBKEE

¤j¤j¤£¦n·N«ä
¦]¬°§Ú¬Oªì¾ÇªÌ¡A°}¦CÁÙ¤£¬O¾Çªº«Ü¦n¡A©Ò¥H¦³´X­Ó°ÝÃD·Q½Ð±Ð¡A
1.
¡yAR(1 To 6, 2 To R)¡z¬O(¦C,Äæ)ªº·N«ä¹ï§a!!
¦ý¤£À´ªº¬O¡A¬°¤°»òÄæ­n¡y2to R¡z
R = .[B1].End(xlDown).Row
¬Oºâ·s¼W¤F´X­Ó¤u§@ªí¶Ü?

2.
Cells(i, 3)¬O§Y®É»ù®æ
¬°¤°»ò¡yCells(i, 3) > AR(3, i)¡z´N¥i¥Hª¾¹D¥¦¬O³o¤@¤ÀÄÁ¤ºªº³Ì°ª»ù??

3.¡yApplication.Index(Application.Transpose(AR), i - 1)¡z
½Ð°Ý³o¬qªº·N«ä¬O¤°»ò¡A¦³google¹L¡A¦ýÁÙ¬O¤£¤ÓÀ´¥¦­Ìªº¸ÑÄÀªº·N¸q.


¤j¤j¼gªº¶}¡B°ª¡B§C¡Aµ¥¤T»ùªºµ{¦¡¡A¨Ã¨S¦³¬Ý¨ì»¡­n§â¥¦¼g¤J­þ­Ó¤u§@ªí¡A
³o¼Ë·|¨C­Ósheet¦U§O­pºâ¶Ü?(¦]¬°ÁÙ¨S¶}½L¡AÁÙ¨S´ú¸Õ¹L)
¹³¦¬½L»ù´N¦³¼g
P = Sheets(.Cells(i, 2).Text).Cells(Row.Count, "J").End(xlUp).Row + 1
Sheets(.Cells(i, 2).Text).Range("J" & P).Resize(1, 6) =Application.Index(Application.Transpose(AR), i - 1)
¨º¥¦¥i¥Hª¾¹D¸ê®Æ¬O­n¼g¥h­þ¤@­Ó¤u§@ªí¡A¨ä¥L¤T»ùªº¨S¼g¡A·|¦s¹ï¦a¤è¶Ü??


ªþ¤WÀÉ®×Åý¤j¤j§ó¤F¸Ñ§Úªº¬[ºc»P·Qªk!! Calculate OVER.rar (162.25 KB)
50 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¦^´_ 8# ko310kmo
1¬°¤°»òÄæ­n¡y2 to R¡z  
²Ä¤@ºûªº¤¸¯À ->¨C¤ÀÄÁ®É¶¡,¶}¡B°ª¡B¦¬¡B§C¡B¦¨¥æ¶q
²Ä¤Gºûªº¤¸¯À -> ¼i¬ù»ùªº­Ó¼Æ

AR( ²Ä¤@ºûªº¤¸¯À±q  1 To 6,²Ä¤Gºûªº¤¸¯À±q  2 To R)  ²Ä¤Gºûªº¤¸¯À->  2 =[B1]ªº¦C¸¹ , R = .[B1].End(xlDown).Row ( ¼i¬ù»ù³Ì«áªº½d³ò)ªº¦C¸¹
°t¦X Sheets(.Cells(i, 2).Text).Range("J" & P).Resize(1, 6) =Application.Index(Application.Transpose(AR), i - 1)
Application.Index ¨Ï¥Î¬°¤u§@ªí¨ç¼Æ ->¶Ç¦^«ü©wÀx¦s®æªº­È©Î°}¦C¤¤ªºÀx¦s®æ°}¦C¡C

2. ¬°¤°»ò¡yCells(i, 3) > AR(3, i)¡z´N¥i¥Hª¾¹D¥¦¬O³o¤@¤ÀÄÁ¤ºªº³Ì°ª»ù??
¤@¶}©l³]AR(3, i)¬°³Ì°ª»ù     Do While Minute(Time) = Minute(t)  «á
  AR(3, i) = IIf(.Cells(i, 3) > AR(3, i)  , ±ø¥ó¦¨¥ß-> .Cells(i, 3),±ø¥ó¤£¦¨¥ß-> AR(3, i)) 'Âà´«¬°¤jªÌ¬O¨C¤ÀÄÁªº³Ì°ª»ù
3.¨Ã¨S¦³¬Ý¨ì»¡­n§â¥¦¼g¤J­þ­Ó¤u§@ªí
¼g¤J¦bSheets(.Cells(i, 2).Text)
Sheets(.Cells(i, 2).Text).Range("J" & P).Resize(1, 6) =Application.Index(Application.Transpose(AR), i - 1)

ªþÀÉ©ú¤Ñ¦A¬ã¨s

TOP

¥»©«³Ì«á¥Ñ ko310kmo ©ó 2011-5-9 00:35 ½s¿è

¦^´_ 9# GBKEE

§Ú§â³o¤j¤jªºµ{¦¡¥[¤J§Ú­ì¥»ªºCalculate¨Æ¥ó¤¤«á¡A
¤Ï¦Ó·|³y¦¨§Úsheet1ªº¢Ñ¡B¢ÒÄ檺­È¥X¤£¨Ó
¡]³o¨âÄæ¬O¼g¢Ò¢Ò¢Ó¤½¦¡¡AÅý¥¦±µ¨ü¶Ç­Èªº¡^
¦U¤u§@ªíªºJÄ檺¾ú¥v¦¨¥æ»ù¡A¤]³£µLªk©¹¤U°O¿ý

¦Ó¥B¤]·|¤@ª½¦b
¡yP = Sheets(.Cells(i, 2).Text).Cells(Row.Count, "J").End(xlUp).Row + 1¡z
¥X²{¡@¶À©³¦â¡B¡y¦¹³B»Ý­nª«¥ó¡zªº¿ù»~
   
½Ð°Ý¬O§Ú­þ¸Ì§Ë¿ù¤F¶Ü¡H ¦³¥[.rar (22.85 KB) ¨S¥[.rar (21.57 KB)

§â¨â­Ó¦³¥[¡B¨S¥[¡A°õ¦æ«áªºµ{¦¡ªþµ¹¤j¤j¬Ý¬Ý
50 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

        ÀR«ä¦Û¦b : ¤@¥y·Å·xªº¸Ü¡A´N¹³©¹§O¤H¨­¤WÅx­»¤ô¡A¦Û¤v·|ªg¨ì¨â¤Tºw¡C
ªð¦^¦Cªí ¤W¤@¥DÃD