ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] «ü©w¤é´Á­pºâµ²¾l¼Æ

¦^´_ 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

TOP

¦^´_ 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

TOP

¦^´_ 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

TOP

¥»©«³Ì«á¥Ñ PJChen ©ó 2021-9-28 15:27 ½s¿è

¦^´_ 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)

TOP

¦^´_ 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¤°»ò?  

TOP

¦^´_ 22# PJChen

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

TOP

¦^´_ 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)

TOP

¥»©«³Ì«á¥Ñ samwang ©ó 2021-9-27 08:07 ½s¿è

¦^´_ 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«á & ¨ä¥¦

TOP

¦^´_ 18# PJChen

EÄæ­n¶ñ¤J³æ¸¹(³sµ²)T&S&R
¦ýRÄæ=""®É,«hEÄæ="µL¥æ³f"
½Ð°Ý­n¦p¦ó§ï³o¬qµ{¦¡?
>> ¨Ì¾Ú±zªºµ{¦¡°µ­×§ï¦p¤U
        If Arr(i, 2) >= d And Arr(i, 18) = "" Then 'Arr(i, 2),²Ä2Äæ¬O¤é´Á,RÄæµL³æ¸¹
            .Cells(i, "e") = "µL¥æ³f"
        Else
             .Cells(i, "e").Formula = "=T" & i & " & S" & i & "& R" & i & ""
        End If


¦ý¬O¨Ì¾Ú±zªº´y­z¡A«á¾Çı±o±ø¥ó¥i¥H©î¦¨2¬qif ¤ñ¸û¦n¡AÁÂÁÂ
  If Arr(i, 2) >= d And Arr(i, 18) = "" Then 'Arr(i, 2),²Ä2Äæ¬O¤é´Á,RÄæµL³æ¸¹
          .Cells(i, "e") = "µL¥æ³f"
end if
If Arr(i, 2) >= d And Arr(i, 18) <> "" Then
        .Cells(i, "e").Formula = "=T" & i & " & S" & i & "& R" & i & ""
end if

TOP

¦^´_ 17# samwang

¥t¥~¤@­ÓUÄæµ{¦¡,¬õ¦r³o¤@¬q,¤é´Á+1ªº¼gªk¤£¯à¥Î,½Ð°Ý¦p¦óÅý¤é´Á+1
¨Ò¦p:u3=8/3,«h+1=8/4
    For i = 3 To UBound(Arr)
        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 = "=Arr(i, 2)" + 1

        ElseIf Arr(i, 2) >= d And Arr(i, 4) = "¤¤©M" Or Arr(i, 4) = "¤º´ò" And Arr(i, 4) = "¦Á¤î" Then
            .Cells(i, "u").Formula = "Arr(i, 2)"
        End If
    Next

TOP

        ÀR«ä¦Û¦b : ¤@­Ó¯Ê¤fªºªM¤l¡A¦pªG´«¤@­Ó¨¤«×¬Ý¥¦¡A¥¦¤´µM¬O¶êªº¡C
ªð¦^¦Cªí ¤W¤@¥DÃD