­ã´£³¡ªL

1. Sub Macro1()
   
2. Dim xR As Range, N&
   
3. For Each xR In Range([A2], [A65536].End(3))
   
4.     If Trim(xR) = "¤p­p" Then
   
5.        If N > 0 Then xR(1, 2) = "=SUM(" & Range(xR(0, 2), xR(1 - N, 2)).Address & ")": N = 0
   
6.        If N = 0 Then GoTo 101
   
7.     End If
   
8.     N = N + 1
   
9.     If Trim(xR) = "Á`­p" Then xR(1, 2) = "=SUMIF(R1C[-1]:R[-1]C[-1],""*¤p­p*"",R1C:R[-1]C)"
   
10. 101: Next
    
11. End Sub

½Æ»s¥N½X

1. Sub Macro2()
   
2. Dim xR As Range, N&
   
3. For Each xR In Range([A2], [A65536].End(3))
   
4.     If Trim(xR) = "¤p­p" Then xR(1, 2) = _
   
5.         "=SUM(R1C:R[-1]C)-SUMIF(R1C[-1]:R[-1]C[-1],""*¤p­p*"",R1C:R[-1]C)*2"
   
6.     If Trim(xR) = "Á`­p" Then xR(1, 2) = "=SUMIF(R1C[-1]:R[-1]C[-1],""*¤p­p*"",R1C:R[-1]C)"
   
7. Next
   
8. End Sub

½Æ»s¥N½X

¨âºØ¤èªk!!!

­ã´£³¡ªL

¤èªk¤Gªº¤½¦¡:
¤p­p:=SUM(B$1:B4)-SUMIF(A$1:A4,"*¤p­p*",B$1:B4)*2
Á`­p:=SUMIF(A$1:A21,"*¤p­p*",B$1:B21)

­ã´£³¡ªL

¦^´_ 8# popomilk


   
1.
For each xR In Range([A2], [A65536].End(3))
½Ð°Ý¨º­ÓRange([A2]], [A65536].End(3))¬O¤°»ò·N«ä©O??
§Úª¾¹D[A65536].End(3)¬OAÄæ±q³Ì¤U­±¤W¨Ó²Ä¤@­Ó«DªÅ¥ÕªºÀx¦s®æ
¡Ä¢Ï¢±¨ì¢ÏÄæ³Ì«á¤@­Ó«DªÅ®æªº½d³ò¡@

2.
   If Trim(xR) = "¤p­p" Then
   If N > 0 Then xR(1, 2) = "=SUM(" & Range(xR(0, 2), xR(1 - N, 2)).Address & ")": N = 0
½Ð°Ýxr(1,2)¬O¤°»ò·N«ä??
¡ÄxR(1, 2) ¡× xR.Cells(1, 2)¡A¬Û¦P·N«ä¡@

­ã´£³¡ªL

¦^´_ 10# popomilk


   
If N > 0 Then xR(1, 2) = "=SUM(" & Range(xR(0, 2), xR(1 - N, 2)).Address & ")": xR(0, 3) = N: N = 0

xR(0, 3) = N  ·í¹J"¤p­p"®É, °O¿ýN­È, ³o¼ËÀ³¸û¥i²M·¡¨äµ{§Ç
¸I¨ì"¤p­p"®É, ¶×¾ã©Ò»Ý¤u§@, ¨Ã±NN­ÈÂk0, Ä~Äò¤U¤@°Ï¬q


"=SUM(" & Range(xR(0, 2), xR(1 - N, 2)).Address & ")"
&¥Î¨Ó³s±µ¤å¦r, ¥[¤W¦ì§}§Y¦¨¤@­Ó¤½¦¡ªº¤å¦r¦ê¡G=SUM($B$2:$B$4)

­ã´£³¡ªL

Sub Macro3()
Dim xR As Range, N&, xH As Range
For Each xR In Range([A2], [A65536].End(3))
¡@¡@If xR Like "###########" And N = 0 Then Set xH = xR(1, 2): N = 1
¡@¡@If Trim(xR) = "¤p­p" Then
¡@¡@¡@¡@If N = 1 Then xR(1, 2) = "=SUM(" & Range(xH, xR(0, 2)).Address & ")":  N = 0
¡@¡@End If
¡@¡@If Trim(xR) = "Á`­p" Then xR(1, 2) = "=SUMIF(R1C[-1]:R[-1]C[-1],""*¤p­p*"",R1C:R[-1]C)"
Next
End Sub

³o¬O¥t¤@¤èªk¡A¥i¥H§óºë·Ç§ì¤p­p½d³ò¡A
·í¢ÏÄ榳¡e¸ê²£½s¸¹¡]¢°¢°½X¼Æ¦r¡^¡f®É¡A°O¿ý¬°¡e­º®æ¡f¦ì¸m(xH)¡A
·í¹J"¤p­p"®É¡A¡e­º®æ¡f¦Ü¡e¤W¥k¤@®æ¡f§Y¬°¤p­p½d³ò¡I¡I¡I

­ã´£³¡ªL

¦^´_ 14# popomilk


   
xR(1, 2) = "=SUMIF(R1C[-1]:R[-1]C[-1],""*¤p­p*"",R1C:R[-1]C)"¡@
³o­Ó¬O¥ý¼g¤½¦¡¡D¢Ð¢±¢±¡G=SUMIF(A$1:A21,"*¤p­p*",B$1:B21)
¦A¥H¡e¿ý»s¡f¨ú±oµ{¦¡½Xªº¡I