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

[µo°Ý] ¨Ì¶µ¥Ø¦WºÙ²Î­p¶i¾PÁ`ÃB¤§­pºâ...?

¦^´_ 10# ¬PªÅÉ@¦ÐÁl

  «D±`·PÁ ¬PªÅÉ@¦ÐÁl ¼ö¤ß@¦^ÂÐ
«D±`¯«©_ªº²[¼Æ¤½¦¡
¤ñ¹ï²Å¦X­n¥ó¤§«á¬Û­¼¬Û¥[...
§Æ±æµ§¼Æ­Y¦h¤£¼vÅT¹Bºâ...·P®¦¸Ñµª  ^^

TOP

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

°õ¦æµ²ªG:



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
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¦^´_ 13# hcm19522

¤£¦n·N«ä  hcm19522

µLªkµn¤J©O  ?

TOP

¦^´_ 12# Andy2483

«D±`·PÁ«e½ú  Andy2483  ¼ö¤ß¸Ñµª
Ãö©ó¦¹°Ý¦³¥H¤U¸É¥R
¡°¬O§_¯à¼W¥[¬ÕÁ«ª÷ÃB¤@Äæ ( T Äæ)

¥t¥~ U Äæ³ÆµùÃØ«~¼Æ¤§µù°O
¦P¤@°Ó«~¦]¤£¦P¾P³f¤é´Á©Ò­P
­Yµ§¼Æ¼W¦h...U³ÆµùÄ椺®e¤å¦r·|ÀH¤§©ìªø°ÝÃD  ?

TOP

¦^´_ 15# cypd


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

°õ¦æµ²ªG:



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
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 16# Andy2483

«D±`·PÁ«e½ú  Andy2483  ¼ö¤ß¸Ñµª
­Y¬O¤£¸g°õ¦æ¥¨¶°¤§°Ê§@
¦Ó¬O¤u§@ªí¤ºÀx¦s®æD(¶i³f)E(¾P³f)F(³æ»ù)H(ÃØ«~)µ¥¬ÛÃöÄæ¦ì¿é¤J¼Æ¾Ú©Î²§°Ê
IJµo¨Æ¥ó´N·|°õ¦æVBA¡K¸Óµ{¦¡¦p¦ó­×¥¿  ??
Private Sub Worksheet_Change(ByVal Target As Range)

TOP

¦^´_ 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
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

«ØijMÄæ¸ê®Æ¥i¥Î¨ç¼Æ¥h§ì¥XCÄæ¦WºÙªº¸ê®Æ
¨Ã§R°£­«½Æ¸ê®Æ¡C

­Yª©¥»¬°2021¥H¤Wªºª©¥»
«Øij¥i¥H¥ÎUNIQUE¨ç¼Æ

©óM3Àx¦s®æ¿é¤J¥H¤U¤½¦¡
=UNIQUE(C3:C32)

TOP

¦^´_ 18# Andy2483

«D±`·PÁ«e½ú  Andy2483  ¼ö¤ß´£¨Ñ¸Ñµª
IJµo¾Ç²ß¤è®×¬ÛÃö¤½¦¡¸g¹ê´ú¤w¥¿½T°õ¦æ¶¶ºZ¡K·P®¦  ^^

TOP

        ÀR«ä¦Û¦b : ¡i°±º¢¤£«e¡A²×µL©Ò±o¡j¤H³£°g©ó´M§ä©_ÂÝ¡A¦]¦Ó°±º¢¤£«e¡FÁa¨Ï®É¶¡¦A¦h¡B¸ô¦Aªø¡A¤]¤FµL¥Î³B¡A²×µL©Ò±o¡C
ªð¦^¦Cªí ¤W¤@¥DÃD