­ã´£³¡ª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

Joforn

¦^´_ 4# popomilk

1. Sub Test()
   
2.   Dim I           As Long
   
3.   Dim R As Long, EndRow As Long
   
4.   Dim strValue    As String
   
5.   Dim Ranges      As Range
   
6.   Dim Range1      As Range
   
7.   Dim strFormula  As String
   
8.   
   
9.   EndRow = Range("A" & Rows.Count).End(xlUp).Row
   
10.   For I = 1 To EndRow
    
11.     With Range("A" & I)
    
12.       strValue = Trim$(.Value)
    
13.       Select Case strValue
    
14.         Case "¤p­p"
    
15.           .Offset(0, 1).FormulaR1C1 = "=SUM(R[" & R - I & "]C:R[-1]C)"
    
16.           R = 0
    
17.           If Ranges Is Nothing Then
    
18.             Set Ranges = .Offset(0, 1)
    
19.           Else
    
20.             Set Ranges = Union(Ranges, .Offset(0, 1))
    
21.           End If
    
22.         Case "Á`­p"
    
23.           If Not Ranges Is Nothing Then
    
24.             strFormula = vbNullString
    
25.             For Each Range1 In Ranges
    
26.               With Range1
    
27.                 If Len(strFormula) Then
    
28.                   strFormula = strFormula & "," & .Address
    
29.                 Else
    
30.                   strFormula = .Address
    
31.                 End If
    
32.               End With
    
33.             Next Range1
    
34.             Set Ranges = Nothing
    
35.             .Offset(0, 1).Formula = "=SUM(" & strFormula & ")"
    
36.           End If
    
37.         Case Else
    
38.           If R = 0 Then
    
39.             If strValue Like "###########" Then R = I
    
40.           End If
    
41.       End Select
    
42.     End With
    
43.   Next
    
44. End Sub

½Æ»s¥N½X

popomilk

¦^´_ 12# ­ã´£³¡ªL

«D±`ÁÂÁ§Aªá®É¶¡¦^ÂÐ
¸g¹L§Aªº±Ð¾Ç¡A§Ú¯uªºÀ´¤F«Ü¦h!! (¼g¤F«Ü¦hµ§°O)Macro1 ¬O¥Î
¦bAÄæ¥Îfor eachªº¥Îªk¡A§ì¥X¤p­pªº¦æ¼Æ
¨Ã¥B¦bÁÙ¨S§ì¨ì¤p­p¤§«e ¤@ª½ÅýNÀHµÛ¦æ¼ÆN+1
¸I¨ì¤p­p®É ±Ò°Êsum ¥[Á`½d³ò¥Î1-Nªº¤è¦¡¸Ñ¨M  (²×©óµy·LÀ´sum¦bVBªº¥Îªk¤F¡A·P°Ê)
¥[Á`«á¡A¦A¥tN=0 ­«·s¶}©l




Macro3 ¬Û¸û©ómacro1ªº¤£¦P
À³¸Ó´N¦bµo²{AÄ檺½s¸¹³£¬O11¦r¤¸¡A
©Ò¥H±NxH = xR(1, 2)
¦b²Ä¤@¦¸¸I¨ìAÄ榳11¦r¤¸®É¥BN=0®É¡A³]©w¤@­ÓÅܼưO¿ý¸Ó¦æªºÀx¦s®æ¦ì¸m¡A¨ÃÅýN¬°1
³Ì«áªºN=1¬OÁקK¦A­«Âг]©wXH³o­ÓÅܼơAÅý¥L´N©T©w¦b²Ä¤@¦¸¸I¨ì11­Ó¦r¤¸®Éªº¨º¦æªºÀx¦s®æ
¦bsum¥[Á`«á¡A­«·sÅýN¬°0
¤§«á¸I¨ì11­Ó¦r¤¸®É¡A¦A­«·s³]©wXHÅܼÆ


ªá¤F¦n¦h®É¶¡¤F¸Ñ³o¨âªk¼gªºÅÞ¿è¤F
«D±`¨ØªA§AªºÁo©ú
µS¦p¤W¤F¤@°ó½Ò

³Ì«á·Q¸ò¸ò§A½Ð±Ð¼Ð¬õ¦rªº³¡¤À

If Trim(xR) = "Á`­p" Then xR(1, 2) = "=SUMIF(R1C[-1]:R[-1]C[-1],""*¤p­p*"",R1C:R[-1]C)"

¤@¯ëexcel
sumif³o­Ó¨ç¼Æ§Ú¬Oª¾¹Dªº
sumif(­n³Q§PÂ_ªº½d³ò°Ï¶¡¡A§PÂ_±ø¥ó,¥[Á`½d³ò)
§Ú¬Ý¤£¤ÓÀ´R1C[-1]
§Ú¬dºô¸ô¤W»¡¬O¬Û¹ï¦ì¸m
¦ý¬O¦pªG¦bexcelÀ³¸Ó¬O­n³]¸m¦¨ =SUMIF(A:A,"*¤p­p*",B:B)
¹ê¦b¬O¬Ý¤£À´ÂনVB»y¨¥«á¬O¥´¦¨³o¼Ë

³Â·Ð¦A½Ð¤j®v¼·¤¾¸Ñµª¤@¤U
«D±`·PÁÂ

popomilk

¦^´_ 13# Joforn
·PÁÂJofornªºªá®É¶¡À°§Ú


·Q¦A½Ð±Ð±z¬õ¦â³¡¤À
For I = 1 To EndRow   
½Ð°Ý³oÃä­n«ç»ò¼g¦¨For each
§Ú´«¦¨³o¼Ë¤@ª½Åã¥Ü¿ù»~
For Each I In Range([A1], EndRow)
³o¨âºØ§PÂ_¦¡¬Oºâ¬Û¦P¥\¯àªº¶Ü??

·PÁÂ

­ã´£³¡ª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

popomilk

¦^´_ 16# ­ã´£³¡ªL

«D±`ÁÂÁÂ
§Ú³£§Ñ°O¦³³o¤è¦¡¤F!
·PÁ±zªº¤À¨É

Joforn

¦^´_ 14# popomilk
Sub Test()
  Dim xR As Range
  
  Set xR = Range("A7")
  With xR
    If Trim$(.Value) = "Á`­p" Then
      .Offset(0, 1).Formula = "=SUMIF(A:A,""*¤p­p*"",B:B)"
      '.Offset(0, 1).FormulaR1C1 = "=SUMIF(C[-1],""*¤p­p*"",C)" '³o±ø»y¥y»P¤W¤@±ø»y¥y¦b³o¸Ì¬O¬Û¦Pªº®ÄªG¡A¦ý¤£¦Pªº¦ì¸m¤½¦¡¤£¤@¼Ë¡A©Ò¥H¤W­±¨º±ø¤ñ¸û¦n¥Î¡C
    End If
  End With
End Sub