PJChen

¦^´_ 32# ­ã´£³¡ªL
­ã¤j,
´ú¸Õ¦^³ø~~§Ú¬O¥Î¦P¤@­ÓÀÉ´ú¸Õªº,¦A³Â·Ð±zÀ°¦£­×§ï¤@¤U~~·PÁÂ
1) ¤½¦¡º|±¼VÄ檺¤¹¦¬¡ÓX....³o­Ó·|¦b¯S®í®É¶¡¥Î¨ì,¨Ï¥Î¨ì®É,·|Key¤J¼Æ­È,ªíÀY¥u¥´¦bV2Äæ,¦ý¨C­ÓHÄ檺¤½¦¡³£­n¥[¤JH3=$B$1+T3-(V3+U3)-1
2) §Ú·Q§â¤¹¦¬ªºH,JÄæ¦ì,key¤J¸ê®Æ«á¤]Åܦ¨­È(µL¤½¦¡),³o¼Ë¥i¥H¨ÏÀÉ®×run§Ö¨Ç
3) Sub ¤¹¦¬¤é_¤½¦¡()....´ú¸ÕÀɪº³Ì¥½2­Óªí(ªíÀY¼g"¥_")µLªk¶ñ¤J¤¹¦¬¤é_¤½¦¡,¦Ó¥B­ì¨ÓH,JÄæ¦ì¤¤¶¡ªº~,ÁÙ·|³Q²M°£
½Ð¥Î§Úªþ¤Wªº³o­ÓÀÉ,­ì¥ýªºÀÉ,§Ñ¤F¦b³Ì¥½2­Óªí¥´¤W¤é´Á
²z³f³æII.rar (146.03 KB)

¤U¸ü¦¸¼Æ: 3

2020-2-18 21:16

­ã´£³¡ªL

¦^´_ 31# PJChen


Sub ­qÁʼÆ_²M°£()
Dim R&, xR As Range, xH As Range, C%
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: GoTo 101
    If xR = "¦X­p" Then
       If C = 0 Then GoTo 101
       Range(xH(1, 7), xR(0, 7)).ClearContents
       C = 0
    End If
101: Next
End Sub

Sub ¤¹¦¬¤é_¤½¦¡()
Dim R&, xR As Range, xH As Range, C%, Fx$(1 To 3), j%
R = Cells(Rows.Count, "K").End(xlUp).Row
If R <= 2 Then Exit Sub
Fx(1) = "=IF(J3="""","""",J3-U3)"
Fx(2) = "=IF(J3="""","""",""~"")"
Fx(3) = "=IF(N(B$_X)*LEN(K3)*N(T3)*N(U3)=0,"""",B$_X+T3-1)"
For Each xR In Range("K2:K" & R)
    If xR = "«~¦W" Then Set xH = xR(2, -2): C = 1: GoTo 101
    If xR = "¦X­p" Then
       If C = 0 Then GoTo 101
       For j = 1 To 3
           Range(xH(1, j), xR(0, -3 + j)) = Replace(Replace(Fx(j), 3, xH.Row), "_X", xH.Row - 2)
       Next j
       C = 0
    End If
101: Next
End Sub

Sub ¤¹¦¬¤é_²M°£()
Dim R&, xR As Range, xH As Range, C%
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, -2): C = 1: GoTo 101
    If xR = "¦X­p" Then
       If C = 0 Then GoTo 101
       Range(xH, xR(0, 0)).ClearContents
       C = 0
    End If
101: Next
End Sub


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

PJChen

¦^´_ 30# ­ã´£³¡ªL
ÁÂÁ­ã¤j,
³o­Óµ{¦¡¤]¥i¥H«Ü·Ç½Tªº°õ¦æ¤F^^

¤@¼Ë¥H¤W¤äµ{¦¡¬°¼Òª©,¥H¤U3¤äµ{¦¡ªº¨Ï¥Î®É¾÷¤£¦P,»Ý­n³æ¿Wµ{¦¡¨Ï¥Î
1        ¥HKÄæ«~¦W¦C¼Æ¬°·Ç,ClearContents(¥u²M°£¸ê®Æ,¤£²M°£®æ¦¡) ­qÁʼƪº¼Æ­È
        ­n¯à®M¥Î¨ì³o­ÓBF²z³f¤u§@ªíªº¨C­Ó­qÁʼÆÄæ¦ì
        ¨ä¥LÀx¦s®æ¤£­n²M°£
       
2        ¥HKÄæ«~¦W¦C¼Æ¬°·Ç
        ¤é´Á¥H«~¦WªíÀYªº¤W¤@¦CBÄ欰·Ç
        ­n¯à®M¥Î¨ì³o­ÓBF²z³f¤u§@ªíªº¨C­ÓH,JÄæ¦ì
        HÄæ H3=$B1+T3-(V3+U3)-1
        JÄæ J3=$B1+T3-1
       
3        ¥HKÄæ«~¦W¦C¼Æ¬°·Ç,ClearContents H,JÄæ¦ì(¥u²M°£¸ê®Æ,¤£²M°£®æ¦¡)
        ­n¯à®M¥Î¨ì³o­ÓBF²z³f¤u§@ªíªº¨C­ÓH,JÄæ¦ì
        ¨ä¥LÀx¦s®æ¤£­n²M°£  
²z³f³æ.rar (145.21 KB)

¤U¸ü¦¸¼Æ: 3

2020-2-18 13:42

­ã´£³¡ªL

¦^´_ 28# PJChen


Sub TEST_2()
Dim R&, xR As Range, xH As Range, C%
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, 3): C = 1: GoTo 101
    If xR = "¦X­p" Then
       If C = 0 Then GoTo 101
       With Range(xH, xR(0, 4))
            .Columns(1).FormulaR1C1 = "=IF(OR(RC[-1]="""",N(RC[3])=0),"""",INT(RC[4]/RC[3]))"
            .Columns(2).FormulaR1C1 = "=IF(OR(RC[-2]="""",N(RC[2])=0),"""",MOD(RC[3],RC[2]))"
            .Value = .Value
       End With
       xR(1, 3) = "=SUM(" & Range(xH(1, 1), xR(0, 3)).Address & ")" '½c¼Æ¦X­p¤½¦¡
       xR(1, 4) = "=SUM(" & Range(xH(1, 2), xR(0, 4)).Address & ")" '²~¼Æ¦X­p¤½¦¡
       xR(1, 7) = "=SUM(" & Range(xH(1, 5), xR(0, 7)).Address & ")" '­qÁÊ¼Æ ¦X­p¤½¦¡
       C = 0
    End If
101: Next
End Sub


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

­ã´£³¡ªL

¦^´_ 28# PJChen

Sub TEST_1()
Dim R&, Arr, Brr, i&, S&(1 To 2), 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
       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
    End If
101: Next i
Range("M2:N" & R) = Brr
End Sub

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

PJChen

¦^´_ 25# ­ã´£³¡ªL
­ã¤j,

¤µ¤Ñ¦b§@·~¤¤µo²{,¦Û°Ê­pºâ½c²~«á,¥¦·|§âªí®æ¥~ªº¬Y¨Ç¤å¦r²M°£±¼,
§Ú§Æ±æªí®æ¥~ªº¥ô¦óÀx¦s®æ,³£¥i¥Hºû«ù­ì¨Óªº¼Ë¤l,³o­Ó³¡¥÷¯à§_§JªA?

¤U¸ü (93.9 KB)

2020-2-17 23:56

²z³f³æ.rar (86.32 KB)

¤U¸ü¦¸¼Æ: 3

2020-2-18 00:02

PJChen

¦^´_ 25# ­ã´£³¡ªL
Dear ­ã¤j,
´ú¸Õ¤F¾ã±ß,µo²{°ÝÃDÂI¨Ã¥B¸Ñ¨M¤F,¥i¥H©¿²¤§Úªº¤W­Ó¦^ÂÐ~~
§Úªºªí³æ·|¦]¬°«È¤á¦]¯À¦Ó¦³ÅܤÆ,²Ä¤@­Óµ{¦¡¥i¥H¤ñ¸û¬¡¥Î,§Ú¤ñ¸û³ßÅw
±zªº¥\¤O¯u¬OµL¼Ä¡I¡I
TEST_2ÁöµM«Üºë²,¦ý¦b´ú¸Õ®Éµo²{,ªí³æ¦³ÅÜ,¶K¤Wªº¸ê®Æ·|¥X¿ù

PJChen

¦^´_ 25# ­ã´£³¡ªL
½Ð°Ý­ã¤j,

§Ú¦³¥t¤@®æ¦¡ªºªí®æ,¦P¼Ë°µªk,¥»·Q®M¥Î¦P¤@µ{¦¡,¤p¤p­×§ïÄæ¦ì§Y¥i,¦ý«oµLªk¨Ï¥Î,
½ÐÀ°¦£¬Ý¤U,¬O§_ÁÙ¦³»Ý­×§ïªº¦a¤è?

1. Sub EX()
   
2. Dim R&, Arr, Brr, i&, S&(1 To 2), V1, V2
   
3. R = Cells(Rows.Count, "J").End(xlUp).Row
   
4. If R <= 2 Then Exit Sub
   
5. Arr = Range("J2:P" & R)
   
6. Brr = Range("L2:M" & R)
   
7. For i = 1 To UBound(Arr)
   
8.     If Arr(i, 1) = "«~¦W" Then Erase S: GoTo 101
   
9.     If Arr(i, 1) = "¦X­p" Then
   
10.        Brr(i, 1) = S(1) '½c¼Æ¦X­p
    
11.        Brr(i, 2) = S(2) '²~¼Æ¦X­p
    
12.        Erase S: GoTo 101
    
13.     End If
    
14.     Brr(i, 1) = "":    Brr(i, 2) = ""
    
15.     V1 = Val(Arr(i, 6)) '¥]¸Ë¼Æ
    
16.     V2 = Val(Arr(i, 7)) '­qÁʼÆ
    
17.     If Arr(i, 2) = "" Or V1 = 0 Then GoTo 101
    
18.     Brr(i, 1) = Int(V2 / V1) '½c¼Æ
    
19.     S(1) = S(1) + Brr(i, 1)  '½c¼Æ²Ö­p
    
20.     Brr(i, 2) = V2 Mod V1  '²~¼Æ
    
21.     S(2) = S(2) + Brr(i, 2) '²~¼Æ²Ö­p
    
22. 101: Next i
    
23. Range("L2:M" & R) = Brr
    
24. End Sub

½Æ»s¥N½X

²z³f³æ_¥t¤@®æ¦¡.rar (91.42 KB)

¤U¸ü¦¸¼Æ: 3

2020-2-16 18:43

­ã´£³¡ªL

¨âºØ¤è®×, ¦Û¦æ¿ï¥Î:
Sub TEST_1()
Dim R&, Arr, Brr, i&, S&(1 To 2), V1, V2
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: GoTo 101
    If Arr(i, 1) = "¦X­p" Then
       Brr(i, 1) = S(1) '½c¼Æ¦X­p
       Brr(i, 2) = S(2) '²~¼Æ¦X­p
       Erase S: GoTo 101
    End If
    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
101: Next i
Range("M2:N" & R) = Brr
End Sub

'============================================
Sub TEST_2()
Dim R&
R = Cells(Rows.Count, "K").End(xlUp).Row
If R <= 2 Then Exit Sub
With Range("M3:N" & R)
     .Formula = "=IF($K3=$K$2,M$2,IF($K3=""¦X­p"",SUM(M$1:M2)-SUMIF($K$1:$K2,""¦X­p"",M$1:M2)*2," & _
            "IF(($L3="""")+($P3=0),"""",IF(M$2=""½c¼Æ"",INT($Q3/$P3),MOD($Q3,$P3)))))"
     .Value = .Value
End With


'=============================================
End Sub

PJChen

¦^´_ 23# ­ã´£³¡ªL
ªí³æ¬O¹ê»Úªº¨Ï¥Î®æ¦¡
~~~·PÁ­ã¤j~~~

²z³f³æ.rar (109.92 KB)

¤U¸ü¦¸¼Æ: 10

2020-2-15 21:45