c_c_lai

¦^´_ 1# ÂŤÑÄR¦À
¦b shtRTD (RTD) ªí³æ¤º RecordPrice()
¥[¤J STSumifs WR, WR¡A ¦p¤U¡G

1. Option Explicit
   
2. 
   
3. Sub RecordPrice()
   
4.     Dim WR As Long
   
5.     Dim I As Byte
   
6. 
   
7.     If Range("P2") < 1 Then Exit Sub
   
8. 
   
9.     WR = Range("A1").End(xlDown).Row + 1
   
10.     '  ActiveWindow.ScrollRow = WR - 5     '  ¥uÅã¥Ü³Ì·s´Xµ§¸ê®Æ
    
11.     If (WR = 3) Or _
    
12.             (Range("F" & WR - 1) <> Range("F2")) Then   '  Á`¶q¦³²§°Ê®É¤~°O¿ý
    
13.         For I = 1 To 6
    
14.             Cells(WR, I) = Cells(2, I)
    
15.         Next 'I
    
16.         
    
17.         STSumifs WR, WR                         '  ¸ê®Æ¦P¨B±N¼Æ­È¼g¤J¨ì R¡AS¡AT ¤TÄ椺
    
18.     End If
    
19.     '  With ActiveWindow
    
20.         '  If Intersect(Cells(WR, "B"), .VisibleRange) Is Nothing Then .SmallScroll 5
    
21.     '  End With
    
22. End Sub
    
23. 
    
24. Private Function STSumifs(ByVal endST As Long, Optional startST As Long = 3)
    
25.     Dim cts As Long
    
26.     Dim btm As Long
    
27.    
    
28.     btm = Range("A1").End(xlDown).Row
    
29.    
    
30.     For cts = startST To endST
    
31.         Cells(cts, "R") = IIf(cts = 2, "", IIf(cts = 3, "=MAX(B3:B" & btm & ")", "=R" & (cts - 1) & "-1"))
    
32.         Cells(cts, "R") = Cells(cts, "R").Value
    
33.         Cells(cts, "S") = "=IF(SUMIFS($E3:$E" & btm & ",$D3:$D" & btm & ",S$1,$B3:$B" & btm & ",$R" & (cts - 1) & ")=0," & Chr(34) & Chr(34) & ",SUMIFS($E3:$E" & btm & ",$D3:$D" & btm & ",S$1,$B3:$B" & btm & ",$R" & (cts - 1) & "))"
    
34.         Cells(cts, "S") = Cells(cts, "S").Value
    
35.         Cells(cts, "T") = "=IF(SUMIFS($E3:$E" & btm & ",$D3:$D" & btm & ",T$1,$B3:$B" & btm & ",$R" & (cts - 1) & ")=0," & Chr(34) & Chr(34) & ",SUMIFS($E3:$E" & btm & ",$D3:$D" & btm & ",T$1,$B3:$B" & btm & ",$R" & (cts - 1) & "))"
    
36.         Cells(cts, "T") = Cells(cts, "T").Value
    
37.     Next cts
    
38. End Function
    
39. 
    
40. Sub Test()
    
41.     Dim WR As Long
    
42.    
    
43.     WR = Range("A1").End(xlDown).Row
    
44.     STSumifs WR
    
45. End Sub

½Æ»s¥N½X

³Ì«á­±¤§ Test() ¬O¥iÅý§A¤@¦¸¦Û°Ê°õ¦æ§ó´« ²Ä 3 ¦æ ~ ²Ä 4903 ¦æ
R¡AS¡AT ¤TÄ檺¸ê®Æ­È¡C¦pªG¤£²§°Êªº¸Ü¡A´N¤£»Ý°õ¦æ¥¦¡C
¨ä¥¦µ{¦¡³¡¤À§¡«O¯d­ì»ª¡C¦]§ÚµL¨é°Ó³s½uµLªk´úª¾ª¬ªp¡C

c_c_lai

¦^´_ 1# ÂŤÑÄR¦À
½Ð°Ñ¦Ò¡G
¦p¦ó§â­ÓªÑ¨C 5 ¤ÀÄÁªº¦¨¥æ»ù®æ°O¿ý¤U¨Ó¡H

c_c_lai

¦^´_ 6# ÂŤÑÄR¦À
»¡¯uªº¡A§Ú¤£¤ÓÀ´ SUMIFS ªº¥Î·N¡A¥u¬O¨Ì¼Ëµe¸¬¡C
¦p§A¦³®É¶¡ªº¸Ü¡Aµyµy»¡©ú¤@¤U¥¦ªº¨Ï¥Î¤è¦¡»P§Aªº·Qªk¡A
¦p¦¹·|§ó©úÁA¨ä§@¥Î¡AÁÂÁ¡C

c_c_lai

¦^´_ 12# ÂŤÑÄR¦À
³o¬O§A­ì¥»ªº©w¸q¡G

1. S3
   
2. =IF(SUMIFS($E:$E,$D:$D,S$1,$B:$B,$R2)=0,"",SUMIFS($E:$E,$D:$D,S$1,$B:$B,$R2))
   
3. T3
   
4. =IF(SUMIFS($E:$E,$D:$D,T$1,$B:$B,$R2)=0,"",SUMIFS($E:$E,$D:$D,T$1,$B:$B,$R2))

½Æ»s¥N½X

³o¬Oµ{¦¡½X¸ÑªRªºµ²ªG¡G

1. S3
   
2. =IF(SUMIFS($E3:$E4903,$D3:$D4903,S$1,$B3:$B4903,$R2)=0,"",SUMIFS($E3:$E4903,$D3:$4903,S$1,$B3:$B4903,$R2))
   
3. T3
   
4. =IF(SUMIFS($E3:$E4903,$D3:$D4903,T$1,$B3:$B4903,$R2)=0,"",SUMIFS($E3:$E4903,$D3:$D4903,T$1,$B3:$B4903,$R2))

½Æ»s¥N½X

µM«á¦AÂର¼Æ­Èªí¥Ü¡C

c_c_lai

¦^´_ 14# ÂŤÑÄR¦À
Function STSumifs(ByVal endST As Long, Optional startST As Long = 3)
1.  Optional startST As Long = 3 ªº¥Î·N¡A¨Æ¥ý½á¤©¹w³]­È¡F
    ¨Ò¦p:
    Sub Test()
        Dim WR As Long
   
        WR = Range("A1").End(xlDown).Row   '  ³Ì«á¤@µ§¸ê®Æ¦C
        STSumifs WR
    End Sub
    ¦b STSumifs ªº¨ç¦¡¤¤¡G
        For cts = startST To endST
    startST µ¥©ó 3¡A endST  µ¥©ó WR (4903)
    ¦¹®É STSumifs WR = STSumifs WR, 3 ¤§·N¡A
    Optional  ªºÅܼƫŧi¡A¦p¥¼±a¤J­È¡A«h¥H¨ä
   ³]©wªº¹w³]­È (3) ¬°°Ñ¼Æ­È¡C
    ***  ³o¬O¤@¦¸´N³B²z 3 ~ 4903 §¹²¦¡C
     
2.  °²³]±a¤J­È¬°¡G
    WR = Range("A1").End(xlDown).Row + 1 '  ³Ì«á¤@µ§¸ê®Æ¦C + 1
    STSumifs WR, WR
    startST µ¥©ó WR¡A endST  µ¥©ó WR (4094)
    ***  ³o¬O±N¸ê®Æ¼g¤J¨ì¸ê®Æ¿ýªº³Ì«á¦C¡C

c_c_lai

¦^´_ 20# ÂŤÑÄR¦À
§A»¡ªº "¬Q¤Ñ7¼Óªºªþ¥ó¸g´ú¸Õ«áµLªk°O¿ý»ù®æ"¡A
7¼Ó ¨º¨Óªºªþ¥ó¡H
§A«üªº¬O¡H

1. Sub RecordPrice()
   
2.     Dim WR As Long
   
3.    Dim I As Byte
   
4. 
   
5.    If Range("P2") < 1 Then Exit Sub
   
6.     WR = Range("A1").End(xlDown).Row + 1
   
7.    '  ActiveWindow.ScrollRow = WR - 5     '  ¥uÅã¥Ü³Ì·s´Xµ§¸ê®Æ
   
8.     If (WR = 3) Or _
   
9.            (Range("F" & WR - 1) <> Range("F2")) Then   '  Á`¶q¦³²§°Ê®É¤~°O¿ý
   
10. 
    
11. .        For I = 1 To 6
    
12.             Cells(WR, I) = Cells(2, I)
    
13.        Next 'I
    
14.         STSumifs WR, WR                         '  ¸ê®Æ¦P¨B±N¼Æ­È¼g¤J¨ì R¡AS¡AT ¤TÄ椺
    
15.   End If
    
16.    '  With ActiveWindow
    
17.         '  If Intersect(Cells(WR, "B"), .VisibleRange) Is Nothing Then .SmallScroll 5
    
18.     '  End With
    
19. End Sub

½Æ»s¥N½X

§Aªºµ{¦¡½X¤¤¦³¥[¤J³o¤@¦æ¶Ü¡H

1. STSumifs WR, WR                         '  ¸ê®Æ¦P¨B±N¼Æ­È¼g¤J¨ì R¡AS¡AT ¤TÄ椺

½Æ»s¥N½X

§Aªº­ì©l½X¬O¦b Workbook_Calculate() ¸Ì°õ¦æªº¡A
½ÐÀˬd¤@¤U¡C
­ã´£³¡ªLª©¤jªº¤À¨É¥ç­È±o§A¬ã¨s°Ñ¦Ò¡A§A·Q­nªºµª®×
¬O¤£¬O¨º¼Ë¡H
§Úªºµ{¦¡½X STSumifs() ¥u¬O±N§A¥»¨Ó¤§¤½¦¡ (Formula) ¥Hµ{¦¡¼Ò¦¡»P¸ê®Æ¦P¨B¼g¤J
¦Ó¤w¡A¨S°µ¥ô¦ó¤§©µ¦ù³Ð·N¡C¦]¬°§Ú¤£À´§A¤½¦¡ªº§@¥Î¡C¬Ý¤F­ã´£³¡ªLª©¤jªº¤À¨É
¤~µyµy©úÁA§A­nªºµ²ªG¥i¯à·|¬O¦p¦¹¡AÁÙ¬O¥t¦³·Qªk¡H

c_c_lai

¦^´_ 22# ÂŤÑÄR¦À

¤U¸ü (51.86 KB)

2016-1-28 12:56

c_c_lai

¦^´_ 24# ÂŤÑÄR¦À
§A§â§A¥Ø«eªºExcelÀÉ®×À£ÁY¤W¶Ç¡A
²´¨£¬°¾Ì¡I

c_c_lai

¦^´_ 26# ÂŤÑÄR¦À
§A»¡ "¥i¥H°O¿ý¡A¦ý«o¤£¬O§Ú­ì¥»³]©wªºÅܰʤ~¬ö¿ý"
¦¹¸Ü«ç»¡¡H
§A¶}±ÒÀɮ׫á¡A¥¦·|±q DDE ¶×¤J§Y®É¼Æ¾Ú¡A±µµÛ¥¦«K¦Û°Ê§PÂ_
Á`¶q¦³²§°Ê®É¤~°O¿ý¡A³o­Ó¹Lµ{¤£¹ï¶Ü¡H"¤£¬O§A­ì¥»³]©wªºÅܰʤ~¬ö¿ý"
¬O¬Æ»ò±¡§Î¡A¦]§Ú¨S¨é°Óªº³nÅé©Ò¥HµL±q±oª¾®t²§¦b¨ºùØ¡C

c_c_lai

¦^´_ 28# ÂŤÑÄR¦À
¨º§A¦AÆ[¹î¤@¤U F2 Ä檺¼Æ¾Ú¦³¨S¦³¤@ª½¦bÅÜ°Ê¡H
¦p¨S¡A«h§A¥²¶·­«·s¦A¦¸±Ò°Ê¨é°Óªº³nÅé¡C