¨Ì©Ò¹º¤Àªº®É¶¡§ì¸ê®Æ¥[Á`

imzues

½Ð°Ý¤@¤U¦³¿ìªk¥Î¥¨¶°
§â¤£¦P®É¶¡ªº¸ê®Æ¡A¦ý¨Ì®É¶¡ªº°Ï®æ§â¥L¤À§O¶×¥X¥[Á`¶Ü??
³Â·Ð¸Ñµª ÁÂÁÂ

oobird

1. Sub yy()
   
2. Dim arr(1 To 6, 0), rng, i%, h%
   
3. rng = [c5:d12]
   
4. For i = 1 To UBound(rng)
   
5. h = Application.Match(Hour(rng(i, 1)), Array(0, 4, 8, 12, 16, 20))
   
6. If h > 0 Then
   
7. arr(h, 0) = arr(h, 0) + rng(i, 2)
   
8. End If
   
9. Next
   
10. [g5].Resize(6, 1) = arr
    
11. End Sub

½Æ»s¥N½X

imzues

½Ð°Ý¤@¤U­Y¬O§Ú¶×¥XªºÄæ¦ì¤£¬O³sÄòªº¡A¥i¯à¬Og5 0000-0400
                                                               g7 0400-0800
¹³³o¼Ë§Ú­n¦b­þÃä§ó§ï¶×¥Xªº¦ì¸m
¦b³Â·Ð¸Ñµª ÁÂÁÂ

GBKEE

¦^´_ 3# imzues

1. Sub Ex()
   
2.     Dim E As Range, R As Range, i As Integer, A As Variant, Timer As Date
   
3.     For Each E In [F5:F10]
   
4.         A = Split(E, "-")
   
5.         i = 0
   
6.         For Each R In [C5:C12]
   
7.             Timer = TimeSerial(Hour(R), Minute(R), 0)
   
8.             If Timer >= TimeValue(Format(A(0), "00:00")) Then
   
9.                 If Timer <= TimeValue(Format(A(1), "00:00")) Then
   
10.                     i = i + R.Cells(1, 2)
    
11.                 End If
    
12.             End If
    
13.          Next
    
14.          E.Cells(1, 2) = i
    
15.     Next
    
16. End Sub

½Æ»s¥N½X

imzues

¤£¦n·N«ä§Ú¥i¯àÁ¿±o¤£¤Ó²M·¡¡A­Y¬O§Ú­ì¥»¬OG5¨º®æ¬O0000-0400
                                                            G6¨º­Ó¬O0400-0800
¦ý§Ú²{¦b·Q­n¨CªÅ¤@®æ¶K¸ê®Æªº¸Ü¡A¨Ò¦p¡GG5¨º®æ¬O0000-0400
                                                         G7¨º­Ó¬O0400-0800
¦³¿ìªk°µ¨ì¶Ü¡H¦]¬°§Ú¤£¤Ó·|§ï
¦A³Â·Ð¸Ñµª·PÁÂ

GBKEE

¦^´_ 5# imzues
ªþ¤WÀÉ®× »¡©ú·|²M·¡¨Ç

Hsieh

¦^´_ 5# imzues

1. Sub nn()
   
2. Dim A As Range, t#
   
3. Set d = CreateObject("Scripting.Dictionary")
   
4. For i = 0 To 20 Step 4
   
5.    mystr = Format(i / 24, "hhmm") & "-" & IIf(i = 20, 2400, Format((i + 4) / 24, "hhmm"))
   
6.    d(mystr) = i / 24
   
7. Next
   
8. kys = d.keys: ar = d.items
   
9. d.RemoveAll
   
10. For Each A In Range([C5], [C65536].End(xlUp))
    
11.    t = TimeValue(Format(A, "hh:mm"))
    
12.    ky = kys(Application.Match(t, ar) - 1)
    
13.    d(ky) = d(ky) + A.Offset(, 1)
    
14. Next
    
15. For Each A In Range([F5], [F65536].End(xlUp)).SpecialCells(xlCellTypeConstants)
    
16.    A.Offset(, 1) = d(A.Value)
    
17. Next
    
18. End Sub

½Æ»s¥N½X

imzues

´N¹³Àɮתº¨ººØ®æ¦¡ ¦bÀ°¦£¸Ñµª ÁÂÁÂ

oobird

Dim arr(1 To 11, 0), rng, i%, h%

rng = [c5:d12]

For i = 1 To UBound(rng)

h = Application.Match(Hour(rng(i, 1)), Array(0, 4, 8, 12, 16, 20))

If h > 0 Then

arr(h * 2 - 1, 0) = arr(h * 2 - 1, 0) + rng(i, 2)

End If

Next

[g5].Resize(11, 1) = arr

imzues

·PÁ¦U¦ì¤j¤jªº¸Ñµª!!!