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

¥[¯Z¶O­pºâ

¥[¯Z¶O­pºâ

¦U¦ì:  
¥»¤H·Q½Ð±Ð¦³Ãö¥[¯Z¶O¤ëµ²©M¦~µ²ªº­pºâ.(°õ¦æ¥¨¶°)
  ¤ëµ²§ó§ï¦~©M¤ë´N¥iÂ^¨úSheet"Main"¸ê®Æ. OT.rar (20.15 KB)

¦^´_ 1# mmggmm
  1. Private Sub Worksheet_Change(ByVal Target As Range) '¤ëµ²
  2. If Intersect(Target, Union([A1], [C1])) Is Nothing Then Exit Sub
  3. Set d = CreateObject("Scripting.Dictionary")
  4. MyDay = [A1] & [C1]
  5. With Sheets("Main")
  6. For Each a In .Range(.[A2], .[A2].End(xlDown))
  7.    If Format(a, "yyyym") = MyDay Then
  8.      If IsEmpty(d(a.Offset(, 1).Value)) Then
  9.         d(a.Offset(, 1).Value) = Array(a.Offset(, 1).Value, a.Offset(, 2).Value, a.Offset(, 3).Value, a.Offset(, 4).Value)
  10.         Else
  11.         ar = d(a.Offset(, 1).Value)
  12.         ar(2) = ar(2) + a.Offset(, 3): ar(3) = ar(3) + a.Offset(, 4)
  13.         d(a.Offset(, 1).Value) = ar
  14.         Erase ar
  15.      End If
  16.     End If
  17. Next
  18. Me.Range("A1").CurrentRegion.Offset(3).ClearContents
  19. If d.Count > 0 Then Me.[A3].Resize(d.Count, 4) = Application.Transpose(Application.Transpose(d.items))
  20. End With
  21. End Sub
½Æ»s¥N½X
  1. Private Sub Worksheet_Change(ByVal Target As Range) '¦~µ²
  2. If Intersect(Target, [A1]) Is Nothing Then Exit Sub
  3. Set d = CreateObject("Scripting.Dictionary")
  4. MyYear = [A1]
  5. With Sheets("Main")
  6. For Each a In .Range(.[A2], .[A2].End(xlDown))
  7.    If Year(a) = MyYear Then
  8.      If IsEmpty(d(a.Offset(, 1).Value)) Then
  9.         d(a.Offset(, 1).Value) = Array(a.Offset(, 1).Value, a.Offset(, 2).Value, a.Offset(, 3).Value, a.Offset(, 4).Value)
  10.         Else
  11.         ar = d(a.Offset(, 1).Value)
  12.         ar(2) = ar(2) + a.Offset(, 3): ar(3) = ar(3) + a.Offset(, 4)
  13.         d(a.Offset(, 1).Value) = ar
  14.         Erase ar
  15.      End If
  16.     End If
  17. Next
  18. Me.Range("A1").CurrentRegion.Offset(3).ClearContents
  19. If d.Count > 0 Then Me.[A3].Resize(d.Count, 4) = Application.Transpose(Application.Transpose(d.items))
  20. End With
  21. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

H¤j¤j,¦n¤F¤£°_,³o´N¬O§Ú­nªº,¤£¹L§Ú­nºCºC®ø¤Æ,ÁÂÁÂ.

TOP

¦^´_ 2# Hsieh


    ¦n²¼äªº¤èªk
   ¦pªG¬O§ÚÀ³¸Ó·|¥Î
   1.¶i¶¥¿z¿ïªº¤è¦¡§ä¥XNO ¸ò©m¦W
   2.µM«á¦b¥Î¨ç¼Æ¥h¥[Á`¥[¯Z®É¼Æ¸òª÷ÃB

   ³t«×¤W¥i¯à´N¤ñª©¥Dªº³t«×¦AºC¤@ÂI
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

H¤j¤j:
  µ{¦¡¸Õ¥Î«áµo²{¤@­Ó°ÝÃD,¨Ò¦p"¤ëµ²"Â^¨ú¸ê®Æ«á,¦A´z¿ï叧¤@­Ó¤ë¦Ó¸Ó¤ë¥÷¨ä¹ê¨S¦³¸ê®Æ¥i¨ú,¦ýµ{¦¡¨Ã¥¼±N©Ò¦³¸ê®Æ§R°£ÁÙ«O¯d²Ä¤@µ§¸ê®Æ."¦~µ²"¦P¦³³o°ÝÃD.¦ó¸Ñ,½ÐÀ°§U¸Ñ¨M,ÁÂÁÂ.

TOP

¦^´_ 5# mmggmm
³o¦æOffset(3)¿ù»~
§ï¦¨
Me.Range("A1").CurrentRegion.Offset(2).ClearContents
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦hÁ½ç±Ð,¦hÁÂ.

TOP

        ÀR«ä¦Û¦b : ¦³´¼¼z¤~¯à¤À¿ëµ½´c¨¸¥¿¡F¦³Á¾µê¤~¯à«Ø¥ß¬üº¡¤H¥Í¡C
ªð¦^¦Cªí ¤W¤@¥DÃD