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

[µo°Ý] API³vµ§¹Bºâ­t¾á¤j¡A¥i§_ºë²

[µo°Ý] API³vµ§¹Bºâ­t¾á¤j¡A¥i§_ºë²

¦U¦ì¤j¤j¡A¤p§Ì³Ìªñªì¾ÇVBA¡A¸ÕµÛ¤gªk·Ò¿û§â·Q­nªº¤è¦¡§ï¥ÎVBA¾Þ§@¡A¥¨¶°Áö¥i¥H¨Ï¥Î¡A¦ýµ{¦¡ªº­t²ü«Ü­«¡A¬O§_¦³¿ìªkºë²©O
±Ä¥Îªº¬OAPI´Á³f³ø»ù¡A¶i¦æ³vµ§¹Bºâ(½d¨Òªºµ§¼Æ«Ü¤Ö¡A¹ê»Ú±¡ªp±NÀH®É¶¡Åܦ¨´X¸Uµ§¸ê®Æ)¡A¨Ã±NBÄ檺¦¨¥æ®É¶¡½Æ»s¨ìSht1.Range("C1")

±µµÛ¨Ì·ÓSht1.Range("C1")ªº®É¶¡¡A±N¹Bºâªºµ²ªG(Sht2.Cells(4, "J")»PSht2.Cells(5, "J"))   ¶K¦bSht3ªºB.CÄæ
¨C
  1. Sub ¦hªÅ¬ö¿ý()
  2. Call ¦@¥Î°Ñ·Ó '´ú¸Õ¥Î

  3. If Sht1.Range("C1") >= TimeValue("08:45:00") And Sht1.Range("C1") < TimeValue("08:50:00") Then
  4. Sht3.Cells(2, "B") = Sht2.Cells(4, "J")
  5. Sht3.Cells(2, "C") = Sht2.Cells(5, "J")
  6. End If

  7. If Sht1.Range("C1") >= TimeValue("08:50:00") And Sht1.Range("C1") < TimeValue("08:55:00") Then
  8. Sht3.Cells(3, "B") = Sht2.Cells(4, "J")
  9. Sht3.Cells(3, "C") = Sht2.Cells(5, "J")
  10. End If
  11. If Sht1.Range("C1") >= TimeValue("08:55:00") And Sht1.Range("C1") < TimeValue("09:00:00") Then
  12. Sht3.Cells(4, "B") = Sht2.Cells(4, "J")
  13. Sht3.Cells(4, "C") = Sht2.Cells(5, "J")
  14. End If
  15. If Sht1.Range("C1") >= TimeValue("09:00:00") And Sht1.Range("C1") < TimeValue("09:05:00") Then
  16. Sht3.Cells(5, "B") = Sht2.Cells(4, "J")
  17. Sht3.Cells(5, "C") = Sht2.Cells(5, "J")
½Æ»s¥N½X
¦p¥H¤Wµ{¦¡¨C5¤ÀÄÁ¤@­Ó°Ï¶¡¡A¤@ª½°O¿ý¨ì13:45¬°¤î¡A
¥Ñ©ó²{¦b¬O¨C¤@µ§¦¨¥æ´N¶i¦æ¤@¦¸¹Bºâ¡A1¬í¤º¥i¯à¦¨¥æ«Ü¦hµ§¥æ©ö¡A
¦³·Q¹L¬O§_¥i¥H§ï¬°1¬í¶i¦æ¤@¦¸¬ö¿ý´N¦n¡A
¦A³Â·Ð¦U¦ì°ª¤âÀ°¦£¡AÁÂÁ¡I

¨Ì®É¶¡¬ö¿ý.rar (16.85 KB)

¥»©«³Ì«á¥Ñ GBKEE ©ó 2018-10-13 09:01 ½s¿è

¦^´_ 1# dreamsway
°Ñ¦Ò¬Ý¬Ý
  1. Option Explicit
  2. Public uMode&, StartTime, EndTime
  3. Public MyBook As Workbook, Sht1 As Worksheet, Sht2 As Worksheet, Sht3 As Worksheet, xRow&
  4. Sub ¦@¥Î°Ñ·Ó()
  5.     Set MyBook = ThisWorkbook
  6.     Set Sht1 = MyBook.Sheets("¦hªÅÂŹÏ")
  7.     Set Sht2 = MyBook.Sheets("³ø»ù¼Æ¾Ú")
  8.     Set Sht3 = MyBook.Sheets("¦hªÅ¼Æ¾Ú")
  9.     StartTime = "08:44:50"  '¶}½L®É¶¡¡]´£¦­¤Q¬í¶}©l¡A¤~¥i°O¿ý¶}½L¶q»ù¡^
  10. End Sub
  11. Sub ³ø»ù¹Bºâ()
  12.     Dim xTime As Date
  13.     Call ¦@¥Î°Ñ·Ó '´ú¸Õ¥Î
  14.     If Sht2.Range("H2") <> 1 Then '¶}½L±ø¥ó
  15.         Sht2.Range("J2,J4,J5").ClearContents '²M°£¬ö¿ý¸ê®Æ
  16.         Sht2.Range("K2") = Sht2.Range("I2") '§PÂ_»ù§ï¬°¶}½L»ù
  17.     End If
  18.     i = 1
  19.     xTime = Time
  20.     Do
  21.                 If Time > xTime Then  'Time ¥H¬í­pºâªº ** Time > xTime = >¤U¤@¬í **
  22.             i = i + 1: xTime = Time
  23.             If Sht2.Cells(2, "J") = "¡ô" And Sht2.Cells(i, "H") <> 1 Then '¦h¤è¥[Á`
  24.                 Sht2.Range("J4") = Sht2.Range("J4") + Sht2.Range("D" & i)
  25.             End If
  26.             If Sht2.Cells(2, "J") = "¡õ" And Sht2.Cells(i, "H") <> 1 Then 'ªÅ¤è¥[Á`
  27.                 Sht2.Range("J5") = Sht2.Range("J5") + Sht2.Range("D" & i)
  28.             End If
  29.             If Sht2.Cells(i, "H") <> 1 Then '³ø»ù®É¶¡¶Ç°e¨ì¦hªÅÂŹÏ
  30.                 Sht1.Cells(1, "C") = Sht2.Range("B" & i)
  31.             End If
  32.             Call ¦hªÅ¬ö¿ý
  33.             Sht2.Cells(i, "H") = 1 '¹Bºâ¹Lªº¶i¦æ¼Ð°OÁקK­«½Æ¹Bºâ
  34.         End If
  35.     Loop Until Sht2.Range("C" & i + 1) = 0 '°j°é°±¤î±ø¥ó
  36. End Sub
  37. Sub ¦hªÅ¬ö¿ý()
  38.      Dim xMinute As Integer
  39.         Call ¦@¥Î°Ñ·Ó '´ú¸Õ¥Î
  40.         xMinute = Int(Application.Text(Time - #8:45:00 AM#, "[M]") / 5)
  41.         '*** xMinute ¥H Time ¶Z 8:45 ªº¤ÀÄÁ¼Æ / 5 ¶Ç¦^ªº¾ã¼Æ
  42.         '*** Time ¤p©ó 8:45 ±o¨ì­t¼Æ
  43.         If xMinute > -1 And Time <= #1:45:00 PM# Then
  44.             xMinute = xMinute + 2   '*** ±q²Ä2¦C¶}©l
  45.             Sht3.Cells(xMinute, "B") = Sht2.Cells(4, "J")
  46.             Sht3.Cells(xMinute, "C") = Sht2.Cells(5, "J")
  47.         End If
  48. End Sub
  49. Sub ²M°£¼Ð°O()
  50.     Call ¦@¥Î°Ñ·Ó '´ú¸Õ¥Î
  51.     Sht2.Columns("H").Clear
  52.     Sht2.Cells(1, "H").Value = "¼Ð°O"
  53. End Sub
  54. Sub ²M°£³ø»ù()
  55.     Dim qyt As QueryTable '§R°£¥~³¡³s½u
  56.     Call ¦@¥Î°Ñ·Ó '´ú¸Õ¥Î
  57.     With Sht2
  58.         .Columns("A:G").Clear '§R°£«á±N¼ÐÃD¦WºÙ¶ñ¤J
  59.         .Columns("A:G").Rows(1) = Array("¥N¸¹", "®É¶¡", "¦¨¥æ»ù", "³æ¶q", "Á`¶q", "³Ì°ª»ù", "³Ì°ª»ù", "³Ì§C»ù")
  60.         For Each qyt In .QueryTables
  61.             qyt.Delete
  62.         Next
  63.     End With
  64. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 2# GBKEE
ÁÂÁ¶Wª©G¤jªº¨ó§U
¸Õ¹B¦æ¤Fµ{¦¡¡Aµo²{·|³s³ø»ù¹Bºâªº³¡¤À³£Åܦ¨1¬í­pºâ¤@¦¸¡A
¦Ó¦hªÅ¬ö¿ý¦ü¥G¬O¥H¹q¸£®É¶¡¨Ó°µ¬ö¿ý¡A

¦]¬°¤p§Ìªì¾ÇVBA¡A¤£½T©w¬O§_¦³¥i¯à¹ê²{¡A
ÅýSub ³ø»ù¹Bºâ()ªº³¡¤À¥H¥¿±`³t«×¹B¦æ¡A¦ýSub ¦hªÅ¬ö¿ý()¥Hµ{¦¡ªº®É¶¡¶i¦æ1¬í1¦¸(©Î¬O¥[±ø¥ó·s®É¶¡>®ɶ¡)¦A§PÂ_®É¶¡±ø¥ó«á°O¿ý©O

TOP

¦^´_ 3# dreamsway
½Ð¦Û¦æ¸Õ§ï¬Ý¬Ý
Sub ¦hªÅ¬ö¿ý() ¤¤ Time §ï¦¨ Sht1.Range("C1")
  1. Sub ³ø»ù¹Bºâ()
  2.     Dim xTime As Date
  3.     Call ¦@¥Î°Ñ·Ó '´ú¸Õ¥Î
  4.     If Sht2.Range("H2") <> 1 Then '¶}½L±ø¥ó
  5.         Sht2.Range("J2,J4,J5").ClearContents '²M°£¬ö¿ý¸ê®Æ
  6.         Sht2.Range("K2") = Sht2.Range("I2") '§PÂ_»ù§ï¬°¶}½L»ù
  7.     End If
  8.     i = 1
  9.     xTime = Time
  10.     Do
  11.         i = i + 1
  12.         If Sht2.Cells(2, "J") = "¡ô" And Sht2.Cells(i, "H") <> 1 Then '¦h¤è¥[Á`
  13.             Sht2.Range("J4") = Sht2.Range("J4") + Sht2.Range("D" & i)
  14.         End If
  15.         If Sht2.Cells(2, "J") = "¡õ" And Sht2.Cells(i, "H") <> 1 Then 'ªÅ¤è¥[Á`
  16.             Sht2.Range("J5") = Sht2.Range("J5") + Sht2.Range("D" & i)
  17.         End If
  18.         If Sht2.Cells(i, "H") <> 1 Then '³ø»ù®É¶¡¶Ç°e¨ì¦hªÅÂŹÏ
  19.             Sht1.Cells(1, "C") = Sht2.Range("B" & i)
  20.         End If
  21.         If Time > xTime Then  '** ¤@¬í¹B¦æ¤@¦¸    **
  22.             xTime = Time
  23.             Call ¦hªÅ¬ö¿ý
  24.         End If
  25.         Sht2.Cells(i, "H") = 1 '¹Bºâ¹Lªº¶i¦æ¼Ð°OÁקK­«½Æ¹Bºâ
  26.     Loop Until Sht2.Range("C" & i + 1) = 0 '°j°é°±¤î±ø¥ó
  27. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 4# GBKEE
ÁÂÁÂG¤j ­ì¥»¹J¨ì¤@¨Ç°ÝÃD¡A
¬ã¨s¤F¤@¤U¡A¬ö¿ýªº³¡¤À³£¸Ñ¨M¤F¡I
ÁÂÁ¡I

TOP

¦^´_ 4# GBKEE
¤£¦n·N«ä¡A¦A¥t°µ¤@­Ó¬ö¿ý15¤ÀK¶}°ª§C¦¬ªº¥¨¶°¤W¹J¨ì¤F§xÃø(¥Ñ©ó³o¸ò­ì¼ÐÃD¤£§¹¥þ²Å¦X¡A­Y»Ý¥t¥DÃD¦A³Â·Ð§iª¾)
§Æ±æ¦b­ì¥»³vµ§­pºâªº¹Lµ{¤¤¡A±N¨C15¤ÀÄÁªº¶}°ª§C¦¬»ù®æ¬ö¿ý°_¨Ó¡A¹B¥Îo,h,l,c ¥|­ÓÅܼÆ
½Ð°Ý­n¦p¦ó¦b¨C15¤ÀÄÁ¬ö¿ý¨ºÃä¡A­n´«¦æ¬ö¿ý®É¤~±No,h,lÅܼÆÂk0¨Ã­«·s­pºâ©O
  1. Option Explicit
  2. Public uMode&, StartTime, EndTime
  3. Public MyBook As Workbook, Sht1 As Worksheet, Sht2 As Worksheet, Sht3 As Worksheet, xRow&
  4. Public o As Single, h As Single, l As Single c As Single

  5. Sub ¦@¥Î°Ñ·Ó()
  6.     Set MyBook = ThisWorkbook
  7.     Set Sht1 = MyBook.Sheets("¦hªÅÂŹÏ")
  8.     Set Sht2 = MyBook.Sheets("³ø»ù¼Æ¾Ú")
  9.     Set Sht3 = MyBook.Sheets("¦hªÅ¼Æ¾Ú")
  10.     StartTime = "08:44:50"  '¶}½L®É¶¡¡]´£¦­¤Q¬í¶}©l¡A¤~¥i°O¿ý¶}½L¶q»ù¡^
  11. End Sub
  12. Sub ³ø»ù¹Bºâ()
  13.     Dim xTime As Date
  14.     Dim i As Long
  15.    
  16.     Call ¦@¥Î°Ñ·Ó '´ú¸Õ¥Î
  17.     If Sht2.Range("H2") <> 1 Then '¶}½L±ø¥ó
  18.         Sht2.Range("J2,J4,J5").ClearContents '²M°£¬ö¿ý¸ê®Æ
  19.         Sht2.Range("K2") = Sht2.Range("I2") '§PÂ_»ù§ï¬°¶}½L»ù
  20.     End If
  21.     i = 1
  22.    
  23.     Do
  24.         i = i + 1
  25.         
  26.         If Sht2.Cells(2, "J") = "¡ô" And Sht2.Cells(i, "H") <> 1 Then '¦h¤è¥[Á`
  27.             Sht2.Range("J4") = Sht2.Range("J4") + Sht2.Range("D" & i)
  28.         End If
  29.         If Sht2.Cells(2, "J") = "¡õ" And Sht2.Cells(i, "H") <> 1 Then 'ªÅ¤è¥[Á`
  30.             Sht2.Range("J5") = Sht2.Range("J5") + Sht2.Range("D" & i)
  31.         End If
  32.         If Sht2.Cells(i, "H") <> 1 Then
  33.             Sht1.Cells(1, "C") = Sht2.Range("B" & i) '³ø»ù®É¶¡¶Ç°e¨ì¦hªÅÂŹÏ
  34.         End If

  35. '***¥H¤U·s¼W15¤ÀK¬ö¿ý
  36. c = Sht2.Range("C" & i)
  37.    If c > h Then h = c '§ó·s³Ì°ª»ù
  38.    If c < l Then l = c '§ó·s³Ì§C»ù
  39.    If o = 0 Then o = Sht2.Range("C" & i) Else o = o
  40.    If l = 0 Then l = Sht2.Range("C" & i) Else l = l
  41.    If h = 0 Then h = Sht2.Range("C" & i) Else h = h
  42. Sht2.Range("O2").Value = o '¶ñ¶}½L»ù
  43. Sht2.Range("P2").Value = h '¶ñ³Ì°ª»ù
  44. Sht2.Range("Q2").Value = l '¶ñ³Ì§C»ù
  45. Sht2.Range("R2").Value = c '¶ñ¦¬½L»ù
  46.         
  47.         If Sht1.Range("C1") > xTime Then  '** ¤@¬í¹B¦æ¤@¦¸    **
  48.             
  49.             Call ¦hªÅ¬ö¿ý
  50.             Call ¤ÀK¶}°ª§C¦¬
  51.         End If
  52.         

  53.         Sht2.Cells(i, "H") = 1 '¹Bºâ¹Lªº¶i¦æ¼Ð°OÁקK­«½Æ¹Bºâ
  54.     Loop Until Sht2.Range("C" & i + 1) = 0 '°j°é°±¤î±ø¥ó
  55. End Sub

  56. Sub ¤ÀK¶}°ª§C¦¬()
  57. Call ¦@¥Î°Ñ·Ó '´ú¸Õ¥Î

  58.   Dim xMinute As Integer
  59.    
  60.         
  61.         xMinute = Int(Application.Text(Sht1.Range("C1") - #8:45:00 AM#, "[M]") / 15)
  62.         '*** xMinute ¥H Time ¶Z 8:45 ªº¤ÀÄÁ¼Æ / 15 ¶Ç¦^ªº¾ã¼Æ
  63.         '*** Time ¤p©ó 8:45 ±o¨ì­t¼Æ
  64.         If xMinute > -1 And Sht1.Range("C1") <= #1:45:00 PM# Then
  65.             xMinute = xMinute + 3   '*** ±q²Ä2¦C¶}©l
  66.             Sht2.Cells(xMinute, "O") = Sht2.Cells(2, "O")
  67.             Sht2.Cells(xMinute, "P") = Sht2.Cells(2, "P")
  68.             Sht2.Cells(xMinute, "Q") = Sht2.Cells(2, "Q")
  69.             Sht2.Cells(xMinute, "R") = Sht2.Cells(2, "R")
  70.         End If
  71.       
  72. End Sub
½Æ»s¥N½X

15¤ÀK¬ö¿ý.rar (19.87 KB)

TOP

¦^´_ 6# dreamsway

©ú¤Ñ¶}½L ¥i´ú¬Ý¬Ý

    ¨Ì®É¶¡¬ö¿ý.zip (29.26 KB)
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 7# GBKEE
ÁÂÁ¶Wª©G¤j¡A©ú¤Ñ½L¤¤´ú¸Õ¬Ý¬Ý¡A
ÁöµM»¡³o¦¸ªºµ{¦¡¹ï§Ú¹ê¦b¤Ó²`¶ø¤F¡A
ÁÙ¦³¦nªøªº¤@±ø¸ô¡A§Ú·|§V¤O¾Ç²ßªº¡I

TOP

¦^´_ 7# GBKEE
¦­¦wG¤j¡A¦]¬°³o¦¸ªºµ{¦¡¡A¤p§ÌºCºC¬dMSDN¤@¨Çµ{¦¡ªº§t·N¤´µM¤£¬Æ¤F¸Ñ¡A§Ú¤j·§´y­z¤@¤U¨Ï¥Î¤Wªº°ÝÃD¡A¦A½Ð±z¦h¦h«ü¾É¡A
¤@¶}©l·ÓµÛ»¡©ú¦sÀÉ«á­«¶}(±z´£¨Ñªº­ìÀÉ)¡A¨S¦³¥ô¦ó¤ÏÀ³¡A¤]ı±o«Ü©_©Ç³o¼Ë¨S³ø»ùªº¼Æ¾Ú«ç»ò¹Bºâ©O¡A
±µµÛÆ[¹î¨ì¥H¤U¥N½X¡A
  1. Set Rng = Sheets("¦hªÅÂŹÏ").Range("A2") '** «ü¼Æ ¥N¸¹
½Æ»s¥N½X
¨Ã±NSheets("¦hªÅÂŹÏ").Range("A2")¶ñ¤W¥N½X«á¦sÀÉ­«¶}¡A§Y·s³Ð¤F¤@­Ó¸Õºâªí¡A¸Õºâªí¦WºÙ¬°«ü¼Æªº¥N¸¹¡A
¨ä¸Õºâªí¤]¶]¥X
  1. Names.Add "Ar", Array("1¤À", "5¤À", "10¤À", "15¤À", "20¤À", "30¤À", "60¤À")
½Æ»s¥N½X
µ¥¦U¶µÄæ¦ì¦WºÙ¡A¦ý¦]¬°¨S¦³³ø»ù¼Æ¾Ú¡A©Ò¥H¨Ã¨S¦³¦b¹Bºâ¡A
±µ¤U¨Ó§Ú§âµ{¦¡¶K¨ì§Ú­ì¥»ªºÀɮסA¨Ã±N
  1. Set Rng = Sheets("¦hªÅÂŹÏ").Range("A2") '** «ü¼Æ ¥N¸¹
½Æ»s¥N½X
§ï¦¨§Ú­ì¥»ªº³ø»ù°Ï°ì
  1. Set Rng = Sheets("³ø»ù¼Æ¾Ú").Range("A2") '** «ü¼Æ ¥N¸¹
½Æ»s¥N½X
­«±Ò«áEXCEL§e²{­t¸ü¨S¦^À³ªºª¬ºA¡A¹Á¸Õµ¥¤F5¤ÀÄÁ¥H¤W¡A¨ä·sªº¸Õºâªí(1-60¤ÀK¬ö¿ý)¤´µM¬°ªÅ¥Õ¡A±µµÛ´NEXCELµL¦^À³±Y¼ì¤F¡A
¤§«á¦bÀÉ®×­«±Ò«áÃö³¬¥¨¶°¹B¦æ¡A¶i¦æ¼Æ¦¸°»¿ù
³£Åã¥Ü¦b¥H¤Uµ{¦¡½X
  1. .Offset(1).Resize(, 3) = Array("®É¶¡", "¦h¤è¥[Á`", "ªÅ¤è¥[Á`")
½Æ»s¥N½X
¤£ª¾¬O¤p§Ì­þÃä¾Þ§@¤W¦³»~¶Ü!?

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2018-10-16 10:53 ½s¿è

¦^´_ 9# dreamsway

¥Ñ©ó²{¦b¬O¨C¤@µ§¦¨¥æ´N¶i¦æ¤@¦¸¹Bºâ¡A1¬í¤º¥i¯à¦¨¥æ«Ü¦hµ§¥æ©ö

³o¸ê®Æ¬O­þ¸Ì¨Óªº



¦hªÅÂÅ¹Ï ¤W¬O»Ý¶ñ¤WDee¤½¦¡ªº¦p§A¨S¦³
½Ðªþ¤W§Aªº­ì©lÀɮ׬ݬÝ
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

        ÀR«ä¦Û¦b : ¤Hªº²´·úªø¦b«e­±¡A¥u¬Ý¨ì§O¤Hªº¯ÊÂI¡Aµ·²@¬Ý¤£¨ì¦Û¤vªº¯ÊÂI¡C
ªð¦^¦Cªí ¤W¤@¥DÃD