¦³Ãösumif¨ç¼Æªº°ÝÃD·Q½Ð±Ð¡H

skyutm

¦U¦ì¥ý¶i¤j®a¦n¡I¦]¬°¤§«e¹ï©óVBA»yªkªº¾Ç²ß¿³½ì¡A©Ò¥H·Q»¡À³¥Î¦b¥Í¬¡¤W¡C©ó¬O¤p§Ì§Ú¥Îsumif¨ç¼Æ¼g¤F¤@­Ó¥[Á`ÂI¼Æªº»yªk¡C»yªk¦p¤U
Sub Worksheet_Deactivate()
Dim a1, a2, a3, i As Integer
For i = 1 To 40
  With Sheets("¤À°tªí")
    a1 = Application.SumIf(Range("D3:D52"), i, Range("C3:C21"))
        a2 = Application.SumIf(Range("h3:h52"), i, Range("g3:g52"))
            a3 = Application.SumIf(Range("l3:l52"), i, Range("k3:k52"))
                Sheets("²Î­pªí").Range("b" & i + 1).Value = a1 + a2 + a3
End With
Next i
End Sub
³o»yªk¬O¹B¦æµL»~ªº¡A¦ý¤µ¤Ñ¤ß¦å¨Ó¼é¡A·Q§â¥¦°µ­Ó¤jÂX¥R¡A©ó¬O¤S¼W¥[¤F´X­Ó¤u§@ªí¡A¤S§ï¤F»yªk¦p¤U¡G
Sub Worksheet_Activate()
Dim a1, a2, a3, a4, a5, a6, a7, a8, a9, a10, a11, a12, i
For i = 1 To 22
With Sheets("¯Z¯Å·F³¡")
    a1 = Application.SumIf(Range("D2:D23"), i, Range("C2:C23"))
    a2 = Application.SumIf(Range("h2:h23"), i, Range("g2:g23"))
    a3 = Application.SumIf(Range("l2:l23"), i, Range("k2:k23"))
End With
With Sheets("±½¦a¤u§@")
    a4 = Application.SumIf(Range("D2:D23"), i, Range("C2:C23"))
    a5 = Application.SumIf(Range("h2:h23"), i, Range("g2:g23"))
    a6 = Application.SumIf(Range("l2:l23"), i, Range("k2:k23"))
End With
With Sheets("¨C¶g¤u§@")
    a7 = Application.SumIf(Range("D2:D23"), i, Range("C2:C23"))
    a8 = Application.SumIf(Range("h2:h23"), i, Range("g2:g23"))
    a9 = Application.SumIf(Range("l2:l23"), i, Range("k2:k23"))
End With
With Sheets("­Ó¤Hªí²{")
    a10 = Application.SumIf(Range("D2:D23"), i, Range("C2:C23"))
    a11 = Application.SumIf(Range("h2:h23"), i, Range("g2:g23"))
    a12 = Application.SumIf(Range("l2:l23"), i, Range("k2:k23"))
End With
Sheets("²Î­pµ²ªG").Range("b" & i + 1).Value = a1 + a2 + a3 + a4 + a5 + a6 + a7 + a8 + a9 + a10 + a11 + a12
Next i
End Sub
µ²ªG«o¤@°Ê¤]¤£°Ê¡A¯u¬O¶Ë¸£µ¬¡C§Úªá¤F¤T¡B¥|­Ó¤p®É¡A§Y¨Ï¸£µ¬¥´µ²¡A«o¤]ºN¤£µÛÀYºü¡C¦³³Ò¦U¦ì¤F¡A¥i§_§iª¾¤p§Ì³o»yªkªº°ÝÃD¦b­þ¸Ì¡Hªþ¤WÀɮסI

skyutm

­ü§r¡I¯u¬O¸£³U¬ðµM¥d¦í¤F¡C§Ú«ç»ò¨Sª`·N¨ì¡A­ì¨Ó¤@¶}©lªº»yªk´N¿ù¤F¡A¥u¬O³±¿ù¶§®t¡C©Ò¥H«á¨Ó¤S¼W¥[¤u§@ªí¤§«á´N·|¥X°ÝÃD¡C¯u¬O·PÁ¡C

kimbal

¥u®t¤@ÂIÂI, ´N¬O¦b¨C­ÓRANGE «e¥[¤@ÂI, ¤£µMRANGE(XX)¬O·í«e­¶ªºRANGE, .RANGE ¬OWITH­¶¤UªºRANGE

1. Sub Worksheet_Activate()
   
2. Dim a1, a2, a3, a4, a5, a6, a7, a8, a9, a10, a11, a12, i
   
3. For i = 1 To 22
   
4. With Sheets("¯Z¯Å·F³¡")
   
5.     a1 = Application.SumIf(.Range("D2:D23"), i, .Range("C2:C23"))
   
6.     a2 = Application.SumIf(.Range("h2:h23"), i, .Range("g2:g23"))
   
7.     a3 = Application.SumIf(.Range("l2:l23"), i, .Range("k2:k23"))
   
8. End With
   
9. With Sheets("±½¦a¤u§@")
   
10.     a4 = Application.SumIf(.Range("D2:D23"), i, .Range("C2:C23"))
    
11.     a5 = Application.SumIf(.Range("h2:h23"), i, .Range("g2:g23"))
    
12.     a6 = Application.SumIf(.Range("l2:l23"), i, .Range("k2:k23"))
    
13. End With
    
14. With Sheets("¨C¶g¤u§@")
    
15.     a7 = Application.SumIf(.Range("D2:D23"), i, .Range("C2:C23"))
    
16.     a8 = Application.SumIf(.Range("h2:h23"), i, .Range("g2:g23"))
    
17.     a9 = Application.SumIf(.Range("l2:l23"), i, .Range("k2:k23"))
    
18. End With
    
19. With Sheets("­Ó¤Hªí²{")
    
20.     a10 = Application.SumIf(.Range("D2:D23"), i, .Range("C2:C23"))
    
21.     a11 = Application.SumIf(.Range("h2:h23"), i, .Range("g2:g23"))
    
22.     a12 = Application.SumIf(.Range("l2:l23"), i, .Range("k2:k23"))
    
23. End With
    
24. Sheets("²Î­pµ²ªG").Range("b" & i + 1).Value = a1 + a2 + a3 + a4 + a5 + a6 + a7 + a8 + a9 + a10 + a11 + a12
    
25. Next i
    
26. End Sub

½Æ»s¥N½X