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

[µo°Ý] ¦p¦ó§Q¥Î®Ö¨ú¤è¶ô°µ¼Æ¶q¥[Á`­pºâ

[µo°Ý] ¦p¦ó§Q¥Î®Ö¨ú¤è¶ô°µ¼Æ¶q¥[Á`­pºâ

¥»©«³Ì«á¥Ñ luke ©ó 2012-4-21 09:08 ½s¿è

¦U¦ì¤j¤j

¤p²Ä·Q§Q¥Îsheet1ªí4­Ó®Ö¨ú¤è¶ô§@¿ï³æ¿ï¾Ü, ·í¥N½XA01-D01¤p¤è¶ô¦³³Q¤Ä¿ï,

¸Ó¥N½X¥þ³¡©Ò¹ïÀ³°Ï°ìªº®Æ¸¹Åã¥Ü¼Æ¶q­n»Psheet2ªí¬Û¦P®Æ¸¹ªº¼Æ¶q°µ¥[Á`­pºâ.

·Ð½Ð¥ý¶i ¤j¤j«ü¾É
TEST13.rar (33.86 KB)

¥»©«³Ì«á¥Ñ GBKEE ©ó 2012-4-21 06:33 ½s¿è

¦^´_ 1# luke
±N©Ò¦³ªº®Ö¨ú¤è¶ô «ü©w¥¨¶° ¬°¦¹µ{§Ç
  1. Option Explicit
  2. Sub Ex()
  3.     Dim D As Object, °Ï°ì As String, E, Check As Object
  4.     Set D = CreateObject("Scripting.Dictionary")
  5.     With sheet1
  6.         Set Check = ActiveSheet.Shapes(Application.Caller).OLEFormat.Object '¿ï¨úªº ®Ö¨ú¤è¶ô
  7.         °Ï°ì = Check.Caption                                                '®Ö¨ú¤è¶ôªº¼ÐÃD¤å¦r
  8.         For Each E In .Range("A9").CurrentRegion.Columns(1).Cells
  9.                 If E = °Ï°ì Then D(E.Cells(1, 2) & E.Cells(1, 3)) = E.Cells(1, 4)
  10.         Next
  11.     End With
  12.     With sheet2
  13.         For Each E In .Range("A1").CurrentRegion.Columns(1).Cells
  14.             If Check.Value=1 Then
  15.                 If D(E & E.Cells(1, 2)) <> "" Then E.Cells(1, 4) = D(E & E.Cells(1, 2)) + E.Cells(1, 3)
  16.             Else
  17.                 If D(E & E.Cells(1, 2)) <> "" Then E.Cells(1, 4) = E.Cells(1, 3)
  18.             End If
  19.         Next
  20.     End With
  21. End Sub
½Æ»s¥N½X

TOP

¦^´_ 1# luke
«ö¶s°õ¦æ¦¹µ{¦¡
  1. Sub ex()
  2. Dim sp As Shape, A As Range
  3. Set d = CreateObject("Scripting.Dictionary")
  4. With sheet1
  5. For Each sp In .Shapes
  6. If sp.Name Like "Check*" Then
  7.   If sp.OLEFormat.Object.Value = 1 Then d(sp.OLEFormat.Object.Caption) = 0
  8. End If
  9. Next
  10. For Each A In .Range(.[A10], .[A10].End(xlDown))
  11. If d.exists(A.Value) = True Then d(A.Offset(, 1) & A.Offset(, 2)) = A.Offset(, 3)
  12. Next
  13. End With
  14. With sheet2
  15. For Each A In .Range(.[A2], .[A2].End(xlDown))
  16. A.Offset(, 3) = A.Offset(, 2) + d(A & A.Offset(, 1))
  17. Next
  18. End With
  19. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 2# GBKEE


    ÁÂÁÂGBKEE ª©¤j

     °õ¦æ¦Ü²Ä7¦C
     °Ï°ì = Check.Caption

     Åã¥Ü"«¬ºA¤£²Å¦X"¿ù»~

TOP

¦^´_ 3# Hsieh

    ÁÂÁÂH¤j

    ­YA01-D01¥N½XÅã¥Ü°Ï°ì¦³¬Û¦Pªº®Æ¸¹®É,
    À³¦p¦óÁקK¬Û¥[ªº¿ù»~¦p¶À¦â¸ê®Æ¦C©Ò¥Ü

     ·Ð½Ð¥ý¶i ¤j¤j«ü¾É
    TEST13A.rar (24.48 KB)

TOP

¦^´_ 5# luke

µL¸Ñ¡Asheet2¨Ã¤£¤À°Ï°ì¡A°£«D¦bsheet2¼W¥[°Ï°ìÄæ¦ì
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 4# luke
°õ¦æ5# ªºÀÉ®× ¨Ã¨S¦³¿ù»~µo¥Í

TOP

¦^´_ 6# Hsieh


    ÁÂÁÂH¤j

    ¦psheet1ªíEÄæµ¹¤F§PÂ_, ¦A±N¤Ä¿ï«áªºµ²ªG
    ¾ã²z¦Üsheet1ªíG:IÄæ¦p­×¥¿¼Æ¶q©Ò¥Ü
   
     ½Ð°Ý¦p¦ó»Psheet2ªí¬Û¹ïÀ³ªº®Æ¸¹°µ¥[Á`

      ·Ð½Ð¥ý¶i ¤j¤j«ü¾É
       TEST13B.rar (24.53 KB)

TOP

¦^´_ 7# GBKEE


    ÁÂÁÂGBKEE ¦^ÂÐ

     F8³v¨B°õ¦æ«áÅã¥Ü"«¬ºA¤£²Å¦X"¿ù»~
     

TOP

¦^´_ 8# luke
  1. Sub ex()
  2. Dim sp As Shape, A As Range
  3. Set d = CreateObject("Scripting.Dictionary")
  4. With sheet1
  5. For Each sp In .Shapes
  6. If sp.Name Like "Check*" Then
  7.   If sp.OLEFormat.Object.Value = 1 Then d(sp.OLEFormat.Object.Caption) = 0
  8. End If
  9. Next
  10. For Each A In .Range(.[A10], .[A10].End(xlDown))
  11. If d.exists(A.Value) = True Then d(A.Offset(, 1) & A.Offset(, 2)) = d(A.Offset(, 1) & A.Offset(, 2)) + A.Offset(, 3)
  12. Next
  13. End With
  14. With sheet2
  15. For Each A In .Range(.[A2], .[A2].End(xlDown))
  16. A.Offset(, 4) = A.Offset(, 2) + d(A & A.Offset(, 1))
  17. Next
  18. End With
  19. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : ¥Í®ð¡A´N¬O®³§O¤Hªº¹L¿ù¨ÓÃg»@¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD