­ã´£³¡ªL

¦^´_ 35# PJChen

[¦Û°Ê­pºâ]³QÃö³¬¤F(«D¥²­n¤£¥i¦p¦¹³]©w, ¥i¯à·|µo¥Í¤@°ï°ÝÃD, ¨Ò¦p¤½¦¡µLªk§ó·s­pºâ..§ì¨ì¤@°ï¿ù»~¼Æ¾Ú..):
With Range(xH, xR(0, 0)):  .Value = .Value: End With
§ï¦¨:
With Range(xH, xR(0, 0)): .Calculate: .Value = .Value: End With

­ã´£³¡ªL

¦^´_ 37# PJChen

Sub TEST_1()
Dim R&, Arr, Brr, i&, S&(1 To 3), V1, V2, C%
R = Cells(Rows.Count, "K").End(xlUp).Row
If R <= 2 Then Exit Sub
Arr = Range("K2:Q" & R)
Brr = Range("M2:N" & R)
For i = 1 To UBound(Arr)
    If Arr(i, 1) = "«~¦W" Then Erase S: C = 1: GoTo 101
    If Arr(i, 1) = "¦X­p" Then
       Brr(i, 1) = S(1) '½c¼Æ¦X­p
       Brr(i, 2) = S(2) '²~¼Æ¦X­p
       Cells(i + 1, "Q") = S(3) '­qÁʼƦX­p
       Erase S: C = 0: GoTo 101
    End If
    If C = 1 Then
       Brr(i, 1) = "":    Brr(i, 2) = ""
       V1 = Val(Arr(i, 6)) '¥]¸Ë¼Æ
       V2 = Val(Arr(i, 7)) '­qÁʼÆ
       If Arr(i, 2) = "" Or V1 = 0 Then GoTo 101
       Brr(i, 1) = Int(V2 / V1) '½c¼Æ
       S(1) = S(1) + Brr(i, 1)  '½c¼Æ²Ö­p
       Brr(i, 2) = V2 Mod V1  '²~¼Æ
       S(2) = S(2) + Brr(i, 2) '²~¼Æ²Ö­p
       S(3) = S(3) + V2 '­qÁʼƲ֭p
    End If
101: Next i
Range("M2:N" & R) = Brr
End Sub


'========================

­ã´£³¡ªL

¦^´_ 41# PJChen


Sub ¹º³æ_¤½¦¡()
Dim Rw&, xR As Range, xH As Range, C%, Fx$
Rw = Cells(Rows.Count, "K").End(xlUp).Row
If Rw <= 2 Then Exit Sub
[R2] = "=-SUMPRODUCT(([³Ì·s®w¦s.xlsx]­¸¤ñ!$F$4:$F$70=$L2)*([³Ì·s®w¦s.xlsx]­¸¤ñ!$CD$3:$CV$3=$B2)*([³Ì·s®w¦s.xlsx]­¸¤ñ!$CD$4:$CV$70))"
For Each xR In Range("K2:K" & Rw)
    If xR = "«~¦W" Then Set xH = xR(2, 8): C = 1: GoTo 101
    If xR = "¦X­p" Then
       If C = 0 Then GoTo 101
       With Range(xH, xR(0, 8)) 'RÄæ¶ñ¤J¤½¦¡
            .FormulaR1C1 = [R2].FormulaR1C1
            .Value = .Value
            .Replace 0, "", 1  '*****(1,§¹¥þ²Å¦X)
       End With
       C = 0
    End If
101: Next
[R2] = ""
End Sub


==============================

­ã´£³¡ªL

¦^´_ 43# PJChen

If Rw <= 2 Then Exit Sub
__¸ê®Æ¦æ¼Æ¤p¤_µ¥2, ªí¥Üªí®æ¤¤¨S¦³¸ê®Æ

    If xR = "«~¦W" Then Set xH = xR(2, 8): C = 1: GoTo 101
    If xR = "¦X­p" Then
       If C = 0 Then GoTo 101
__§ä¨ì"«~¦W", ¼ÐµùC=1, ©¹¤U§ä¨ì"¦X­p", ¤~ºâ°t¹ï¦¨¥\, ¥ç§Y"«~¦W"¨ì"¦X­p"¤§¶¡ªº½d³ò

With Range(xH, xR(0, 8))
.Replace 0, "", 1....³o¸Ì¤w¸g§â0¨ú¥N¬°ªÅ¥Õ,¬°¤°»òÁٻݭn[R2] = ""
__³o¸Ì¥u¨ú¥N"«~¦W(¤U¤@®æ)"¨ì"¦X­p(¤W¤@®æ)"¤§¶¡ªº½d³ò

­ã´£³¡ªL

¦^´_ 45# PJChen

³Ì«á¤@¦æ
[F2]="¹ê¥X®Ä´Á"

­ã´£³¡ªL

¦^´_ 45# PJChen

¤]¥i¥H³o¼Ë:
Sub ¼t¯Ê¸ü¤J()
Dim Rw&, xR As Range, xH As Range, C%, Fx$, LT$
Workbooks("²z³f³æII.xlsx").Sheets("BF²z³f").Activate
Rw = Cells(Rows.Count, "K").End(xlUp).Row
If Rw <= 2 Then Exit Sub
LT = [F2].Value
[F2] = "=IF(SUMPRODUCT(([³Ì·s®w¦s.xlsx]­¸¤ñ!$BJ$3:$CB$3=$B2)*([³Ì·s®w¦s.xlsx]­¸¤ñ!$F$4:$F$64=$L2)*([³Ì·s®w¦s.xlsx]­¸¤ñ!$BJ$4:$CB$64))=0,"""",IF(SUMPRODUCT(([³Ì·s®w¦s.xlsx]­¸¤ñ!$BJ$3:$CB$3=$B2)*([³Ì·s®w¦s.xlsx]­¸¤ñ!$F$4:$F$64=$L2)*([³Ì·s®w¦s.xlsx]­¸¤ñ!$BJ$4:$CB$64))=$Q2,""¼t¯Ê"",""¯Ê""&SUMPRODUCT(([³Ì·s®w¦s.xlsx]­¸¤ñ!$BJ$3:$CB$3=$B2)*([³Ì·s®w¦s.xlsx]­¸¤ñ!$F$4:$F$64=$L2)*([³Ì·s®w¦s.xlsx]­¸¤ñ!$BJ$4:$CB$64))))"
For Each xR In Range("K2:K" & Rw)
    If xR = "«~¦W" Then Set xH = xR(2, -4): C = 1: GoTo 101
    If xR = "¦X­p" Then
       If C = 0 Then GoTo 101
       With Range(xH, xR(0, -4)) 'FÄæ¶ñ¤J¤½¦¡
            .FormulaR1C1 = [F2].FormulaR1C1
            .Value = .Value
            .Replace 0, "", 1  '*****(1,§¹¥þ²Å¦X)
       End With
       C = 0
    End If
101: Next
[F2].Value = LT
End Sub


'===========================

­ã´£³¡ªL

µ{¦¡½X­nªá¨Ç®É¶¡¥h²z¸Ñ(¦³ªÅ´N¥h¬ã¨s¬ã¨s),
¤£µM³s³o¤p¤pªº°ÝÃD³£­n¦A°Ý¤@¦¸~~

­ã´£³¡ªL

¦^´_ 59# PJChen

Sub ²z³f±Æ§Ç()
Dim R&, xR As Range, xH As Range, C%, V&
R = Cells(Rows.Count, "K").End(xlUp).Row
If R <= 2 Then Exit Sub
For Each xR In Range("K2:K" & R)
    If xR = "«~¦W" Then Set xH = xR(2): C = 1: V = 0: GoTo 101
    V = V + Val(xR(1, -4))
    If xR = "¦X­p" Then
       If C = 0 Or V = 0 Then GoTo 101
       With Range(xH(1, -4), xR(0, 7))
           .Sort Key1:=.Cells(1, 1), Order1:=xlAscending, _
                 Key2:=.Cells(1, 7), Order2:=xlAscending, Header:=xlNo
       End With
       C = 0: V = 0
    End If
101: Next
End Sub

­ã´£³¡ªL

¦^´_ 61# PJChen


Sub ²z³f±Æ§Ç()
Dim R&, xR As Range, xH As Range, C%, V&
R = Cells(Rows.Count, "K").End(xlUp).Row
If R <= 2 Then Exit Sub
Application.ScreenUpdating = False
For Each xR In Range("K2:K" & R)
    If xR = "«~¦W" Then Set xH = xR(2): C = 1: V = 0: GoTo 101
    V = V + Val(xR(1, -4))
    If xR = "¦X­p" Then
       If C = 0 Or V = 0 Then GoTo 101
       Range(xH(1, -4), xR(0, -4)).Replace "", "ZZ", Lookat:=xlWhole '±NªÅ¦r²Å¨ú¥N¬°"ZZ"
       With Range(Rows(xH.Row), Rows(xR(0).Row))
           .Sort Key1:=.Cells(1, "F"), Order1:=xlAscending, _
                 Key2:=.Cells(1, "L"), Order2:=xlAscending, Header:=xlNo
       End With
       Range(xH(1, -4), xR(0, -4)).Replace "ZZ", "", Lookat:=xlWhole  '±N"ZZ¨ú¥N¬°ªÅ
       C = 0: V = 0
    End If
101: Next
End Sub

FÄ榳ªÅ¦r²Å""¦b°µ©Ç, ¾É­P±Æ§Ç°ÝÃD, ¥Î"¨ú¥N"¸Ñ¨M, ­YFÄ欰¤½¦¡, «Ü³Â·Ð~~

­ã´£³¡ªL

¦^´_ 65# PJChen


Xl0000190(½c²~¤¹¦¬¤é)v01.rar (27.21 KB)

¤U¸ü¦¸¼Æ: 2

2020-5-30 13:17

1) ¤¹¦¬¤é«ç»òºâªº, ¦Û¦æ­×§ï
2) ²z³f¤½¦¡, ª©¥»¤£¦X, °µ¤£¤F