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

¥Îvba»yªk¤ñ¹ï¤é´Á

¥Îvba»yªk¤ñ¹ï¤é´Á

¥»©«³Ì«á¥Ñ imzues ©ó 2011-4-18 15:37 ½s¿è

­Y¬O§Úªº­ì©l¼Æ¾Ú¸Ì¦³ ¦~ ¤ë ¤é ®É¶¡¡A¦ý§Ú¥u­nmatch¤é´Á©M®É¶¡¡AµM«á«ömatchªº¤é´Á¶×¤J¸ê®Æ
¹³³oºØ­nmatch¨âµ§¥H¤Wªº±ø¥ó¡A
¨º§Ú­n«ç»ò¥Îvba¼g¨º¦æªº»yªk£«???

³o¼Ë»¡µLªkÅý¤H©ú¥Õ§Aªº¶·¨D
¥i´£¨Ñ¤å¥ó»¡©ú

TOP

¤£¦n·N«ä....Á¿ªº¤Ó¼Ò½k¤F
§Ú¤w¸gÀ°Àɪþ¤W¨Ó¤F¡A¦b³Â·ÐÁ¿¸Ñ¤@¤U~·PÁÂ

Book1v1.zip (1.98 KB)

TOP

  1. Sub test()
  2.     Dim c%, r%, arr(1 To 6, 1 To 3), d As Range, a#, b#
  3.     [i4:k9] = ""
  4.     For c = 1 To 3
  5.         For r = 1 To 6
  6.             For Each d In [e4:e11]
  7.                 a = Cells(3, c + 8) + Left(Cells(r + 3, 8), 2) / 24
  8.                 b = a + 0.166667
  9.                 If d >= a And d < b Then
  10.                     arr(r, c) = arr(r, c) + d(1, 2)
  11.                 End If
  12.             Next
  13.         Next
  14.     Next
  15.     [i4].Resize(6, 3) = arr
  16. End Sub
½Æ»s¥N½X

TOP

·PÁ¤j¤j¼ö¤ßªº¸Ñµª¡A¤p§ÌÁÙ¦³­Ó°ÝÃD
­Y¬O§Ú[E4:E11]³o8µ§ªº¸ê®Æ¬O¨C¤Ñ³£·|°µÅÜ°Ê¡A­Y¦³©M¥ý«e¶×¹Lªº¤é´Á¬Û¦P®É¡A
¦³¿ìªk©M¶×¹Lªº¨º¨Ç¼Æ¦r°µ¥[Á`¶Ü¡H

TOP

¦^´_ 5# imzues

    ³o¬O¤p§Ì~ ¦Û¤v¼gªºÂ²³æ³B²z¤è¦¡
   ¦]¬°¤£¤Ó¼ô½m«ç»ò§â¤é´Á­È§ì¥X¨Ó«áÁÙ«O¯d¬O¤é´Áªº®æ¦¡~ ©Ò¥H¦h§Q¥Î¤@¨Ç¨BÆJ¨Ó³B²z~
    ¤p§Ì¤]¤£¤Ó·|¥Îarray(°}¦C)ªº¤è¦¡¨Ó¼g~ ´N¥Î©Ò¼gªº¤è¦¡¨Ó³B²z~
    ¶Â¿ß~ ¶À¿ß~ §ì±o¨ì¦Ñ¹«ªº´N¬O¦n¿ß~  ¶È¨Ñ±z°Ñ¦Ò~ À³¸Ó«Ü®e©ö¤F¸Ñ§Ú¦b¼g¨Ç¤°»ò~
    1.³B²z"¤é´Á®æ¦¡" (¤è«Kfind¦b§ä¬Û¹ïÀ³ªº¤é´Á¦ì¸m)
    2.³B²z®É¶¡ªº¦ì¸m  (¦]¬°¬O©T©wªº¦ì¸m,©Ò¥H¥Îif§PÂ_¸Óµ§¸ê®Æ¬O¸¨¦b­þ¤@¦Cªº¦ì¸m)
    3.´M§ä¥¿½Tªº¦ì¸m,±a¤J­È  (¨Ìk¹ïÀ³¨ìªº¦ì¸m«á,¦V¤U(¦C)²¾°Êd-3,±a¤J­È,¥~¥[¤@­Óif¥ý¦æ§P¬q¬O¤£¬O¦³­È,­Y¦³­È´N²Ö¥[)

Sub xx()

Do Until Sheet1.Cells(4 + N, 5) = ""
'======³B²z¤é´Á======
Sheet1.Range("A1") = Year(Sheet1.Cells(4 + N, 5)) & "/" & Month(Sheet1.Cells(4 + N, 5)) & "/" & Day(Sheet1.Cells(4 + N, 5))
A = Sheet1.Range("A1")             'A: ³B²z¸ê®Æªº¤é´Á
Sheet1.Range("A1").ClearContents
'======³B²z¤é´Á======

'======³B²z®É¶¡ªº¦ì¸m======
b = Hour(Sheet1.Cells(4 + N, 5))
c = Minute(Sheet1.Cells(4 + N, 5))
If b < 3 And c <= 59 Or (b = 4 And c = 0) Then
   d = 4
   ElseIf b < 7 And c <= 59 Or (b = 8 And c = 0) Then
   d = 5
   ElseIf b < 11 And c <= 59 Or (b = 12 And c = 0) Then
   d = 6
   ElseIf b < 15 And c <= 59 Or (b = 16 And c = 0) Then
   d = 7
   ElseIf b < 19 And c <= 59 Or (b = 20 And c = 0) Then
   d = 8
   ElseIf b < 23 And c <= 59 Or (b = 24 And c = 0) Then
   d = 9
End If
'======³B²z®É¶¡ªº¦ì¸m======

Set k = Sheet1.Range("I3:K3").Find(A, LookIn:=xlFormulas)
    If k.Offset(d - 3, 0) = "" Then
       k.Offset(d - 3, 0) = Sheet1.Cells(4 + N, 5).Offset(0, 1)
    Else
       k.Offset(d - 3, 0) = k.Offset(d - 3, 0) + Sheet1.Cells(4 + N, 5).Offset(0, 1)
    End If

N = N + 1    'N¬°³B²z¤U¤@µ§¸ê®Æ¤§°Ñ¼Æ
Loop


End Sub
¾Ç²ß¤~¯à´£¤É¦Û¤v

TOP

¤£¦n·N«ä
§Ú·Q½Ð°Ý¤@¤UCells(4 + N, 5) = ""
=""  <===³o¼gªkªº·N«ä¬O¤°»ò?

TOP

¦^´_ 7# imzues


    ¦]¬°§AªºE4:411¬O¤@­Ó³sÄòªº¸ê®Æ~
    ©Ò¥H§Ú¨Ï¥Îdo loopªº¤è¦¡¨Ó°õ¦æ~
    ·íCells(4 + N, 5) = ""´N°±¤î°õ¦æ
    ="" <---¦bvba©Îexcel¤½¦¡¤¤ªí¥Üªº³£¬OªÅ®æ
¾Ç²ß¤~¯à´£¤É¦Û¤v

TOP

¤£¦n·N«ä§A¼gªº¨º¬qµ{¦¡¦n¹³¨S¿ìªk°õ¦æ

Set k = Sheet1.Range("I3:K3").Find(A, LookIn:=xlFormulas)
    If k.Offset(d - 3, 0) = "" Then
       k.Offset(d - 3, 0) = Sheet1.Cells(4 + N, 5).Offset(0, 1)
    Else
       k.Offset(d - 3, 0) = k.Offset(d - 3, 0) + Sheet1.Cells(4 + N, 5).Offset(0, 1)
    End If

N = N + 1    'N¬°³B²z¤U¤@µ§¸ê®Æ¤§°Ñ¼Æ
Loop


End Sub

TOP

¥»©«³Ì«á¥Ñ hugh0620 ©ó 2011-4-21 11:13 ½s¿è

¦^´_ 9# imzues


    ¦]¬°~ §Ú¤£ª¾¹D§Aªº¸ê®Æ½d³ò¬O¤£¬O¸ò§Úªº¤@¼Ë~
    ªþ¤W§Ú§¹¦¨ªºÀÉ®×µ¹±z,¥ç§Y§A¤@¶}©lªºªþ¥ó¶i¦æ»s§@~
    ¥Îvba»yªk¤ñ¹ï¤é´Á_§¹¦¨ÀÉ.rar (10.79 KB)
    ­Y¦³°ÝÃD¥i¥H§â§A°µªº¤W¶Ç¤W¨Ó¬Ý¤@¤U~
    ·Q¨ì¤F~ §AªºI3¬O¤é´Á®æ¦¡ J3»PK3¬O¦r¦ê®æ¦¡~ ½Ð±N¥þ³¡§ï¬°¤é´Á®æ¦¡
   ®æ¦¡³Ì¦n²Î¤@~ ¤£µM¥Îfindªº¤è¦¡~ ·|¦]¬°®æ¦¡ªº°ÝÃD§ì¤£¨ì¸ê®Æ~
¾Ç²ß¤~¯à´£¤É¦Û¤v

TOP

        ÀR«ä¦Û¦b : ¤£­nÀH¤ß©Ò±ý¡A­nÀH¤ß±Ð¨|¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD