samwang

¦^´_ 19# PJChen

¥t¥~¤@­ÓUÄæµ{¦¡,¬õ¦r³o¤@¬q,¤é´Á+1ªº¼gªk¤£¯à¥Î,½Ð°Ý¦p¦óÅý¤é´Á+1
        If Arr(i, 2) >= d And Arr(i, 4) <> "¤¤©M" Or Arr(i, 4) <> "¤º´ò" And Arr(i, 4) <> "¦Á¤î" Then 'i=¦C
            .Cells(i, "u").Formula = "=B" & i & " +1 "
         ElseIf Arr(i, 2) >= d And Arr(i, 4) = "¤¤©M" Or Arr(i, 4) = "¤º´ò" And Arr(i, 4) = "¦Á¤î" Then
             .Cells(i, "u").Formula = "=B" & i & ""
         End If

«á¾Ç¤]¬O²Ä¤@¦¸¥Î³oºØ¼gªk(VBA¦bexcel¼g¤½¦¡)¡A
¼g¤F³o´X¦¸¤U¨Ó¦n¹³¦³ÂI³W«h¡A¥u­n¹J¨ìÅܼÆi    " &   i   & " µM«á & ¨ä¥¦

PJChen

¦^´_ 21# samwang

·PÁ±z,
U & EÄ檺°ÝÃD³£¸Ñ¨M¤F,
¦ýµo²{¥t¤@­Ó°ÝÃD,¦]¬°¨C¤@­ÓÄæ³£¦³¤@­Ó§Oªºµ{¦¡,
¦ý¥u­n°õ¦æ¤F .Value = .Value
´N·|¤zÂZ¨ì¨ä¥LÄæ¦ì,¤]³£Åܦ¨­È,
½Ð°Ý¦p¦ó¨Ïµ{¦¡¤§¶¡¤£­n¤¬¬Û¤zÂZ,
³o¼Ë»Ý­n«O¯d¤½¦¡ªº,´N¤£·|Åܦ¨­È¡I
~~~~~~~~~
¥t¥~,­ì¨Ó¬O¥Î¨ç¼Æ­pºâ ¬£ªO-¥æªO®t²§,½Ð°Ý³o­Ó¦³辧ªk¶}¬°µ{¦¡¶Ü? VÄ檺µ{¦¡¤]¬O­n³æ¿W¤@­Ó®@...
V3=IF(COUNTIFS(¥_°Ï!$B$2:$B3,¥_°Ï!$B3,¥_°Ï!$D$2:$D3,¥_°Ï!$D3)=1,SUMIFS(¥_°Ï!$W:$W,¥_°Ï!$B:$B,¥_°Ï!$B3,¥_°Ï!$D:$D,¥_°Ï!$D3)-SUMIFS(¥_°Ï!$F:$F,¥_°Ï!$B:$B,¥_°Ï!$U3,¥_°Ï!$D:$D,¥_°Ï!$D3),0)

samwang

¦^´_ 22# PJChen

¦ý¥u­n°õ¦æ¤F .Value = .Value
´N·|¤zÂZ¨ì¨ä¥LÄæ¦ì,¤]³£Åܦ¨­È,
>> §ï¬°³æ¿WÄæÂର­È
Range("K3:K" & R).Value = Range("K3:K" & R).Value    'KÄæÂର­È

samwang

¦^´_ 22# PJChen

¥t¥~,­ì¨Ó¬O¥Î¨ç¼Æ­pºâ ¬£ªO-¥æªO®t²§,½Ð°Ý³o­Ó¦³辧ªk¶}¬°µ{¦¡¶Ü? VÄ檺µ{¦¡¤]¬O­n³æ¿W¤@­Ó®@...
V3=IF(COUNTIFS(¥_°Ï!$B$2:$B3,¥_°Ï!$B3,¥_°Ï!$D$2:$D3,¥_°Ï!$D3)=1,SUMIFS(¥_°Ï!$W:$W,¥_°Ï!$B:$B,¥_°Ï!$B3,¥_°Ï!$D:$D,¥_°Ï!$D3)-SUMIFS(¥_°Ï!$F:$F,¥_°Ï!$B:$B,¥_°Ï!$U3,¥_°Ï!$D:$D,¥_°Ï!$D3),0)
>> ³o¬O­n°µ¤°»ò??¤£ª¾¹D±z¹ê»Ú»Ý¨D¬O¤°»ò?  

PJChen

¦^´_ 23# samwang

±z¦n,
Âà´«­Èªº³¡¥÷¨S°ÝÃD¤F¡I
¥t¤@­Ó¦P¤@¨t¦CÀÉ®×,¤@¼Ë«ü©wd = xS.[Af2]

¤¤°Ï¸ê®Æ¬O¤@­Ó¥þ¦~«×©Êªº,·|¤@ª½¼W¥[¦Ü¦~©³,
HÄæ­n²Î­pDÄæ¨C­Ó¤ë³Ì«á¤@­Ó¤é´Áªº¤ë²Î­p¼Æ¶q
¤]´N¬O¦P¤@¦~¦P¤@¤ëªº¥X³f¼Æ,²Ä¤@µ§¤£¤@©w1¤é¶}©l,³Ì«á¤@µ§¤]¤£¤@©w¬O¤ë©³¤é
,½Ð°Ý³oµ{¦¡­n«ç»ò¼g¡H
¤¤°Ï.rar (35.56 KB)

¤U¸ü¦¸¼Æ: 2

2021-9-28 15:17

samwang

¦^´_ 25# PJChen

HÄæ­n²Î­pDÄæ¨C­Ó¤ë³Ì«á¤@­Ó¤é´Áªº¤ë²Î­p¼Æ¶q
>> ¦p¤U¡A½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
Sub test()
Dim Arr, Brr, xD, i&, T$, T1$
Arr = Sheets("¤¤°Ï").Range("a3:k" & [¤¤°Ï!a65536].End(3).Row + 1)
ReDim Brr(1 To UBound(Arr), 1 To 1)
Set xD = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(Arr)
    If Not IsDate(Arr(i, 1)) Then GoTo 98
    T = Month(Arr(i, 1)): T1 = Month(Arr(i + 1, 1))
    If xD.Exists(T) Then
        If T <> T1 Then
            xD(Arr(i, 1) & "_" & Arr(i, 3)) = Val(xD(T)) + Val(Arr(i, 4))
        Else
            xD(T) = Val(xD(T)) + Val(Arr(i, 4))
        End If
    Else
        xD(T) = Val(Arr(i, 4))
    End If
98: Next
For Each ky In xD.keys
    For i = 1 To UBound(Arr)
        Brr(i, 1) = xD(Arr(i, 1) & "_" & Arr(i, 3))
    Next
Next
Sheets("¤¤°Ï").[h3].Resize(UBound(Brr)) = Brr
End Sub

PJChen

¦^´_ 26# samwang

±z¦n,
°õ¦æ«Ü§Ö³t,¤£¹Lµ{¦¡¹ï§Ú¨Ó»¡,½ÆÂø«Ü¦h¡I¯à§_À°§Úµù¸Ñ¤@¤U¬õ¦r¥Îªk?
¥t¥~³o¬qµ{¦¡"_",¥¦¥Nªí¤°»ò?
xD(Arr(i, 1) & "_" & Arr(i, 3)) = Val(xD(T)) + Val(Arr(i, 4))
Sub test()
Dim Arr, Brr, xD, i&, T$, T1$
Arr = Sheets("¤¤°Ï").Range("a3:k" & [¤¤°Ï!a65536].End(3).Row + 1)
ReDim Brr(1 To UBound(Arr), 1 To 1)
Set xD = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(Arr)
    If Not IsDate(Arr(i, 1)) Then GoTo 98
    T = Month(Arr(i, 1)): T1 = Month(Arr(i + 1, 1))
    If xD.Exists(T) Then
        If T <> T1 Then
            xD(Arr(i, 1) & "_" & Arr(i, 3)) = Val(xD(T)) + Val(Arr(i, 4))
        Else
            xD(T) = Val(xD(T)) + Val(Arr(i, 4))
        End If
    Else
        xD(T) = Val(Arr(i, 4))
    End If
98: Next
For Each ky In xD.keys
    For i = 1 To UBound(Arr)
        Brr(i, 1) = xD(Arr(i, 1) & "_" & Arr(i, 3))
    Next
Next
Sheets("¤¤°Ï").[h3].Resize(UBound(Brr)) = Brr
End Sub

samwang

¦^´_ 27# PJChen

¯à§_À°§Úµù¸Ñ¤@¤U¬õ¦r¥Îªk? >>µù¸Ñ¼g±o¤£¦n¡Bµü¤£¹F·N¡A½Ð¨£½Ì¡AÁÂÁÂ

Sub test()
Dim Arr, Brr, xD, i&, T$, T1$
Arr = Sheets("¤¤°Ï").Range("a3:k" & [¤¤°Ï!a65536].End(3).Row + 1) '¸ê®Æ¸Ë¤J°}¦C
ReDim Brr(1 To UBound(Arr), 1 To 1) '³ÐªÅªº°}¦C¥Î¨Ó¸Ëµª®×
Set xD = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(Arr)
    If Not IsDate(Arr(i, 1)) Then GoTo 98   '¤£¬O¤é´ÁÂ÷¶}
    T = Month(Arr(i, 1)): T1 = Month(Arr(i + 1, 1))
    If xD.Exists(T) Then    '¬O§_AÄæ¤ë¥÷¦³¦b¦r¨å
        If T <> T1 Then '¦Û¤v»P¤U¤@¦C¤é´Áªº¤ë¥÷¤ñ¸û
            '¬ö¿ý·í¤ë³Ì«á¤@µ§ªº¥æªO¼Æ¶q²Ö¥[¡Akey:³Ì«á¤é´Á_±ÄÁʳ渹¡AÁקK³Ì«á¤@¤Ñ¦³¦hµ§
            xD(Arr(i, 1) & "_" & Arr(i, 3)) = Val(xD(T)) + Val(Arr(i, 4))
        Else
            xD(T) = Val(xD(T)) + Val(Arr(i, 4)) '¦P¤ë¥÷¥æªO¼Æ¶q²Ö¥[
        End If
    Else
        xD(T) = Val(Arr(i, 4)) '¥æªO¼Æ¶q¸Ë¤J¦r¨å
    End If
98: Next
For Each ky In xD.keys  '¦r¨åkeys´`Àô
    For i = 1 To UBound(Arr)
        Brr(i, 1) = xD(Arr(i, 1) & "_" & Arr(i, 3)) '¦r¨åkey:³Ì«á¤é´Á_±ÄÁʳ渹¡A¸Ë¤Jµª®×°}¦C
    Next
Next
Sheets("¤¤°Ï").[h3].Resize(UBound(Brr)) = Brr  'Åã¥Üµª®×
End Sub

PJChen

¦^´_ 28# samwang

±z¦n,
½ÐÀ°§Ú¬Ý¤Uµ{¦¡
¸ê®Æ¦³¦h¦~«×®É,¥u¦³²Ä¤@¦~­pºâ¥¿½T,¨ä¾l¦X­p·|¥X¿ù...
¤¤°Ï_¦h¦~«×.rar (64.59 KB)

¤U¸ü¦¸¼Æ: 1

2021-10-4 21:37

samwang

¦^´_ 29# PJChen


¸ê®Æ¦³¦h¦~«×®É,¥u¦³²Ä¤@¦~­pºâ¥¿½T,¨ä¾l¦X­p·|¥X¿ù...   
>> §ó§ï¦p¤U¡A½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁ¡C
T = Year(Arr(i, 1)) & "|" & Month(Arr(i, 1)): T1 = Year(Arr(i, 1)) & "|" & Month(Arr(i + 1, 1))