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

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

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

¦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

ÂI¼Æ»È¦æ.rar (45.91 KB)

¥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
À´±oµo°Ý,µª®×´N·|¦b¨ä¤¤

¤µ¤éの¤@¬íは  ©ú¤éにない
http://kimbalko-chi.blogspot.com
http://kimbalko.blogspot.com

TOP

­ü§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

TOP

        ÀR«ä¦Û¦b : ¨ü¤HÂI¤ô¤§®¦¡A¶··í´é¬u¥H³ø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD