Andy2483

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ßVBA°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð

°õ¦æµ²ªG:

¤U¸ü (130.08 KB)

2023-6-28 08:32

Sub TEST()
Dim Brr, V4&, V5&, Z, i&, T$, A&, Af&, B$, Bn&, D&, Df, Ra As Range
Set Z = CreateObject("Scripting.Dictionary")
Set Ra = Range([¤u§@ªí1!H3], [¤u§@ªí1!A65536].End(3)): Brr = Ra
For i = 1 To UBound(Brr)
   T = Brr(i, 3): V4 = Val(Brr(i, 4)): V5 = Val(Brr(i, 5))
   If V4 > 0 Then
      A = Z(T & "|¶i"): A = A + V4: Z(T & "|¶i") = A
      Af = Z(T & "|¶iÃB"): Af = Af + V4 * Val(Brr(i, 6))
      Z(T & "|¶iÃB") = Af
   End If
   Bn = Val(Brr(i, 8))
   If Bn > 0 Then
      B = Z(T & "|ÃرÔ")
      Z(T & "|ÃؼÆ") = Z(T & "|ÃؼÆ") + Bn
      If B = "" Then
         B = Brr(i, 1) & "¶µ_" & Brr(i, 2) & "_ÃØ_" & Bn
         Else
         B = B & " ¡¹" & Brr(i, 1) & "¶µ_" & Brr(i, 2) & "_ÃØ_" & Bn
      End If
      Z(T & "|ÃرÔ") = B: B = ""
   End If
   If V5 > 0 Then
      D = Z(T & "|¾P"): D = D + V5: Z(T & "|¾P") = D
      Df = Z(T & "|¾PÃB"): Df = Df + V5 * Val(Brr(i, 6))
      Z(T & "|¾PÃB") = Df
   End If
Next
Set Ra = Range([¤u§@ªí1!S2], [¤u§@ªí1!M65536].End(3)): Brr = Ra
For i = 2 To UBound(Brr)
   T = Brr(i, 1)
   Brr(i, 2) = Z(T & "|¶i")
   Brr(i, 3) = Z(T & "|¾P") + Z(T & "|ÃؼÆ")
   Brr(i, 4) = Brr(i, 2) - Brr(i, 3)
   Brr(i, 5) = Z(T & "|¶iÃB")
   Brr(i, 6) = Z(T & "|¾PÃB")
   Brr(i, 7) = Z(T & "|ÃرÔ")
Next
Ra = Brr: [S2] = "³Æµù"
Set Z = Nothing: Erase Brr: Set Ra = Nothing
End Sub

Andy2483

¦^´_ 15# cypd


    ÁÂÁ«e½ú¦^´_
«á¾Ç¾Ç²ß¤è®×¦p¤U,½Ð«e½ú°Ñ¦Ò

°õ¦æµ²ªG:

¤U¸ü (136.29 KB)

2023-6-28 14:44

Sub TEST()
Dim Brr, V4&, V5&, Z, i&, T$, A&, Af&, B$, Bn&, D&, Df, Ra As Range
Set Z = CreateObject("Scripting.Dictionary")
Set Ra = Range([¤u§@ªí1!H3], [¤u§@ªí1!A65536].End(3)): Brr = Ra
For i = 1 To UBound(Brr)
   T = Brr(i, 3): V4 = Val(Brr(i, 4)): V5 = Val(Brr(i, 5))
   If V4 > 0 Then
      A = Z(T & "|¶i"): A = A + V4: Z(T & "|¶i") = A
      Af = Z(T & "|¶iÃB"): Af = Af + V4 * Val(Brr(i, 6))
      Z(T & "|¶iÃB") = Af
   End If
   Bn = Val(Brr(i, 8))
   If Bn > 0 Then
      B = Z(T & "|ÃرÔ")
      Z(T & "|ÃؼÆ") = Z(T & "|ÃؼÆ") + Bn
      If B = "" Then
         B = Brr(i, 1) & "¶µ_" & Brr(i, 2) & "_ÃØ_" & Bn
         Else
         B = B & " ¡¹" & Brr(i, 1) & "¶µ_" & Brr(i, 2) & "_ÃØ_" & Bn
      End If
      Z(T & "|ÃرÔ") = B: B = ""
   End If
   If V5 > 0 Then
      D = Z(T & "|¾P"): D = D + V5: Z(T & "|¾P") = D
      Df = Z(T & "|¾PÃB"): Df = Df + V5 * Val(Brr(i, 6))
      Z(T & "|¾PÃB") = Df
   End If
Next
Set Ra = Range([¤u§@ªí1!V2], [¤u§@ªí1!N65536].End(3)): Brr = Ra
For i = 2 To UBound(Brr)
   T = Brr(i, 1)
   Brr(i, 2) = Z(T & "|¶i")
   Brr(i, 3) = Z(T & "|¾P") + Z(T & "|ÃؼÆ")
   Brr(i, 4) = Brr(i, 2) - Brr(i, 3)
   Brr(i, 5) = Z(T & "|¶iÃB")
   Brr(i, 6) = Z(T & "|¾PÃB")
   Brr(i, 7) = Brr(i, 6) - Brr(i, 5)
   Brr(i, 8) = "¦@ÃØ¥X: " & Z(T & "|ÃؼÆ")
   Brr(i, 9) = Z(T & "|ÃرÔ")
Next
Ra = Brr: [U2] = "³Æµù"
Set Z = Nothing: Erase Brr: Set Ra = Nothing
End Sub

Andy2483

¦^´_ 17# cypd


    ÁÂÁ«e½ú¦A¦^´_
¥H¤UIJµo¾Ç²ß¤è®×½Ð«e½ú°Ñ¦Ò


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xR As Range
With Target
   Set xR = Intersect([A:H], ActiveSheet.UsedRange)
   If Not Intersect(.Cells, xR) Is Nothing Then Call TEST
End With
Set xR = Nothing
End Sub

Andy2483

¦^´_ 23# ­ã´£³¡ªL


    ½Ð±Ð«e½ú¥H¤U¬ðµo©_·Qªº¤è¦¡,¥i¥H¦³´_­ì¥\¯à,¦ý¨S¬Ý¹L³o¼Ëªº¤èªk,½Ð«e½ú«ü¾É¦³¦ó§Q¹ú?
ÁÂÁ«e½ú

°õ¦æµ²ªG:

¤U¸ü (136.57 KB)

2023-6-30 15:08

20230630_1.zip (16.81 KB)

¤U¸ü¦¸¼Æ: 4

2023-6-30 15:10

Andy2483

¦^´_ 26# ­ã´£³¡ªL


    ÁÂÁ«e½ú,«á¾Ç¦A¸Õ§Oªº®×¨Ò,³oEvaluate «Ü©_§®

Andy2483

¦^´_ 29# cypd


    ÁÂÁ«e½úÀ°¦£´ú¸Õ¦^´_
½Ð«e½ú¨Ï¥Î¤@¯ë¤½¦¡ªº¤èªk
¸g¹L´ú¸Õ³o¤èªk¤£¾A¦X¤u§@ªí¦³«OÅ@ªº±¡¹Ò,©Î¦³±ø¥óªº«OÅ@¤u§@ªí¦ý¥Ø¼Ð®æ­n³]©w¥i½s¿è
³o¤èªk¬O«á¾Ç¾Ç¤£·|½ÆÂø¤½¦¡°½Ãi©Ò·Q¥X¨Óªº¤Ñ°¨¦æªÅ¤è®×,«Ý«á¾Ç¦³¨ä¥L¤ß±o¦A¤À¨Éµ¹¤j®a

Andy2483

¦^´_ 31# cypd


    ÁÂÁ«e½ú¦^´_
¦pªG«e½ú·Q¥Î«á¾Çªº¤èªk,«á¾Ç«Øij¥H¤U¤èªk
1.±N$A:$F,$H:$W³]¬° ¤¹³\¨Ï¥ÎªÌ½s¿è½d³ò
2.¦A³]©w«OÅ@¤u§@ªí