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

[µo°Ý] ¥H¦r¨å¤è¦¡»s§@¾lÃB©ú²Óªí

¦^´_ 20# Andy2483
Andy¤j¦n!
§A³o®Ä²v¤ÓÅå¤H¤F...§Ú¦³ªÅ¦A°µ´ú¸Õ¡A
ÁÙ´£¨Ñ¦hºØ¸Ñªk¤Îµù¸Ñ..ÁÂÁ§A!

TOP

¦^´_ 20# Andy2483
Andy¤j!
¸g´ú¸Õ¹L«á¥i¨Ï¥Î¡A·PÁ§A¹ð¦¸¨ó§U...

TOP

¦^´_ 22# shuo1125


    «e½ú¦­¦w,ÁÂÁµoªí¥DÃD»P½d¨Òµ¹«á¾Ç¾Ç²ß,ÁÂÁ½׾Â
1.´£¿ô«e½ú ¸ê®Æ°Ïªíªº¸ê®Æ¦b ¨t²Î¸Ì±Æ§Ç¦n¤~¼´¥X,´N¤£¥²¦bexcel±Æ§Ç
2.­Y¦³«áÄò°ÝÃD,½Ð¤£§[¦A´£¥X,¦pªG°¾Â÷¦¹¥DÃD,½Ð¥t¶}·s¥DÃD
3.½Ð«e½ú±`¤W½×¾Â¤@°_¾Ç²ß
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 23# Andy2483
Andy¤j!

1.´£¿ô«e½ú ¸ê®Æ°Ïªíªº¸ê®Æ¦b ¨t²Î¸Ì±Æ§Ç¦n¤~¼´¥X,´N¤£¥²¦bexcel±Æ§Ç
  >¦nªº¡AÁÂÁ´£¿ô...
2.­Y¦³«áÄò°ÝÃD,½Ð¤£§[¦A´£¥X,¦pªG°¾Â÷¦¹¥DÃD,½Ð¥t¶}·s¥DÃD
  >¦³®É­Ô¬O©È¯ÔÀÁ¤j®a®É¶¡¡A­Y¦³¯S§O°ÝÃD¦b´£¥X¤F..
3.½Ð«e½ú±`¤W½×¾Â¤@°_¾Ç²ß
  >§Úµ{«×¤£°÷¡A¥u¯àºCºCºN¯Á¤F...ÁÂÁ§Aªº¨ó§U!

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-3-27 08:42 ½s¿è

¦^´_ 7# shuo1125


    ÁÂÁ«e½ú
¥H¤U¬O«á¾Ç½m²ßÀx¦s®æ»P°}¦C¹ï·ÓÀ³¥ÎªºRÄæ¾lÃB­pºâ¸Ñ¨M¤è®×,½Ð«e½ú°Ñ¦Ò

Option Explicit
Sub ¸ê®Æ°Ï¾lÃB()
Dim Arr, T16&, T17&, T18&, i&, S&, T$, T2$, T3$, T20$, xR As Range
'¡ô«Å§iÅܼÆ:Arr¬O³q¥Î«¬ÅܼÆ,(T16,T17,T18,i)¬Oªø¾ã¼Æ,
'(T,T2,T3,T20)¬O¦r¦êÅܼÆ

Set xR = Range([¸ê®Æ°Ï!T1], [¸ê®Æ°Ï!A1].Cells(Rows.Count, 1).End(3))
'¡ô¥OxR³oÀx¦s®æÅܼƬO ¸ê®Æ°Ï[T2]¨ì AÄæ³Ì«á¦³¤º®eÀx¦s®æ½d³òÀx¦s®æ
Arr = xR
'¡ô¥OArr³o³q¥Î«¬ÅܼƬO ¤Gºû°}¦C,¥H xRÀx¦s®æ­È±a¤J
For i = 1 To UBound(Arr)
'¡ô³]¶¶°j°é!i±q1¨ì Arr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹
   If i = 1 Then GoTo i01
   '¡ô¦pªGiÅܼƬO 1!´N¸õ¨ì i01¼Ð¥Ü¦ì¸mÄ~Äò°õ¦æ
   T2 = Arr(i, 2): T3 = Arr(i, 3): T16 = Arr(i, 16)
   T17 = Arr(i, 17): T18 = Arr(i, 18): T20 = Arr(i, 20)
   '¡ô¥OT2³o¦r¦êÅܼƬO i°j°é¦C²Ä2ÄæArr°}¦C­È,¨Ì¦¹Ãþ±À
    If Val(T16) > Val(T17) Then
    '¡ô¦pªGT16ÅܼƸgÂà¤Æªº¼Æ­È ¤j©ó T17ÅܼƸgÂà¤Æªº¼Æ­È??
       T18 = T16 - T17
       '¡ôif±ø¥ó¦¨¥ß!´N¥O T18ÅܼƬO T16 - T17ÅܼÆ
       Else
          T18 = Val(T17) - Val(T16)
          '¡ô§_«h!´N¥O T18ÅܼƬO T17ÅÜ¼Æ - T16ÅܼÆ
    End If
   If T <> T2 & "|" & T3 Then
   '¡ô¦pªGT³o¦r¦êÅÜ¼Æ ¤£µ¥©ó T2ÅܼƳs±µ T3ÅܼƲզ¨ªº·s¦r¦ê
      xR.Range("R" & i).Value = T18
      '¡ô¥OxRÅܼƪº[R2]Àx¦s®æ­È¬O T18ÅܼÆ
      T = T2 & "|" & T3
      '¡ô¥OT³o¦r¦êÅܼƬO T2ÅܼƳs±µ T3ÅܼƲզ¨ªº·s¦r¦ê
      ElseIf T20 = "¥ß±b" Then
         xR.Range("R" & i) = xR.Range("R" & i - 1) + T18
         '¡ô§_«h¥OxRÅܼƪºRÄæ(i°j°é¼Æ)¦CÀx¦s®æ­È¬O
         'xRÅܼƪºRÄæ(i°j°é¼Æ-1)¦CÀx¦s®æ­È +T18ÅܼÆ

      ElseIf T20 = "¨R±b" Then
         xR.Range("R" & i) = xR.Range("R" & i - 1) - T18
         '¡ô§_«h¥OxRÅܼƪºRÄæ(i°j°é¼Æ)¦CÀx¦s®æ­È¬O
         'xRÅܼƪºRÄæ(i°j°é¼Æ-1)¦CÀx¦s®æ­È -T18ÅܼÆ

   End If
i01:
Next
Erase Arr: Set xR = Nothing
'¡ô¥OÄÀ©ñÅܼÆ
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-3-27 10:11 ½s¿è

¦^´_ 24# shuo1125


    ÁÂÁ«e½ú
«á¾ÇÂǦ¹©«µo²{³o¦r¨å¬O­Ó¸U¯àªºÅܼƲ£¥Í¾¹
1.¦]¬°ÅܼƤ£·|­«½Æ,©Ò¥Hkey¥i¥HÂo­«½Æ
2.¦]¬°key¹ïÀ³¤@­Óitem(¼Æ¦r.¦r¦ê.Àx¦s®æ.°}¦C....),©Ò¥Hkey¥i·í¦¨¬O¤@­ÓÅܼÆ


Option Explicit
Sub ¸ê®Æ°Ï¾lÃB_1()
Dim Y, Arr, T16&, T17&, T18&, i&, S&, T$, T2$, T3$, T20$, xR As Range
'¡ô«Å§iÅܼÆ:(Y,Arr)¬O³q¥Î«¬ÅܼÆ,(T16,T17,T18,i)¬Oªø¾ã¼Æ,
'(T,T2,T3,T20)¬O¦r¦êÅܼÆ,xR¬OÀx¦s®æÅܼÆ

Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OY³o³q¥Î«¬ÅܼƬO ¦r¨å
Set xR = Range([¸ê®Æ°Ï!T2], [¸ê®Æ°Ï!A1].Cells(Rows.Count, 1).End(3))
'¡ô¥OxR³oÀx¦s®æÅܼƬO ¸ê®Æ°Ï[T2]¨ì AÄæ³Ì«á¦³¤º®eÀx¦s®æ½d³òÀx¦s®æ
Arr = xR
'¡ô¥OArr³o³q¥Î«¬ÅܼƬO ¤Gºû°}¦C,¥H xRÀx¦s®æ­È±a¤J
For i = 1 To UBound(Arr)
'¡ô³]¶¶°j°é!i±q1¨ì Arr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹
   T2 = Arr(i, 2): T3 = Arr(i, 3): T16 = Arr(i, 16)
   T17 = Arr(i, 17): T18 = Arr(i, 18): T20 = Arr(i, 20)
   '¡ô¥OT2³o¦r¦êÅܼƬO i°j°é¦C²Ä2ÄæArr°}¦C­È,¨Ì¦¹Ãþ±À
   T18 = Abs(T16 - T17)
   '¡ô¥OT18³oªø¾ã¼ÆÅܼƬO T16ÅÜ¼Æ - T17Åܼƫ᪺µ´¹ï­È¼Æ­È
   T = T2 & "|" & T3
   '¡ô¥OT³o¦r¦êÅܼƬO T2ÅܼƳs±µ T3ÅܼƲզ¨ªº·s¦r¦ê
   If Y(T) = "" And T20 = "¨R±b" Then MsgBox "ÄY­«¿ù»~": Exit Sub
   '¡ô¦pªG¤@¶}©l´N¬O¨R±b!¬OÄY­«¿ù»~±o¸ê®Æ
   If T20 = "¥ß±b" Then
   '¡ô¦pªG T20ÅܼƬO "¥ß±b" ?
      Y(T) = Y(T) + T18
      '¡ô¥OTÅܼƦbY¦r¨åitem­È²Ö¥[ T18ÅܼÆ
      ElseIf T20 = "¨R±b" Then
         '¡ô§_«h¦pªG T20ÅܼƬO "¨R±b" ?
         Y(T) = Y(T) - T18
         '¡ô¥OTÅܼƦbY¦r¨åitem­È²Ö¦© T18ÅܼÆ
      Else
         MsgBox "µLªk¿ëÃÑ": Application.Goto xR(i, 20): Exit Sub
         '¡ô§_«h´N¸õ¥X´£¥Üµ¡,Àx¦s®æ´å¼Ð¸õ¨ìRÄæi°j°é¦C¦ì¸m:µ²§ôµ{¦¡
   End If
   Arr(i, 18) = Y(T)
   '¡ô¥Oi°j°é¦C²Ä18ÄæArr°}¦C­È¬OTÅܼƬdY¦r¨åªºitem­È
Next
[A2].Resize(UBound(Arr), UBound(Arr, 2)) = Arr
'¡ô¥O[A2]ÂX®i¦V¤UArrÁa¦V³Ì¤j¯Á¤Þ¦C¸¹¼Æ,ÂX®i¦V¥k³Ì¤j¯Á¤ÞÄ渹¼Æ,
'³oÂX®i½d³òÀx¦s®æ­È¥HArr°}¦C­È±a¤J

Erase Arr: Set xR = Nothing: Set Y = Nothing
'¡ô¥OÄÀ©ñÅܼÆ
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 25# Andy2483
Andy¤j!
   ¸g´ú¸Õ¥i¦æ¡A·P¿E¤£ºÉ!!

TOP

¦^´_ 25# Andy2483
Andy¤j!
­Y­n±N¾lÃB©ú²ÓªíªºB1¤é´Á¦Ò¼{¶i¥h¡A°²³]»¡¾lÃB°ÏB1¤é´Á¬O2023/01/31(¹ÏA)¡A
«h¾lÃB©ú²Ó¥u§ì¨ì1¤ë©³(¹ÏB)¡A
½Ð°Ý³o¼Ë¸Ó¦p¦ó­×§ï¤~¦n?¦p¹Ï¡A¤S­n³Ò·Ð§A¤F...ÁÂÁÂ!!!

¹ÏA.png (29.17 KB)

¹ÏA.png

¹ÏB.png (41.5 KB)

¹ÏB.png

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-3-28 09:22 ½s¿è

¦^´_ 28# shuo1125


    ÁÂÁ«e½ú,ÁÂÁ½׾Â
«á¾Ç½m²ß¹B¥Î §R°£«ü©w±ø¥ó¦C¸ê®Æ,¾Ç²ßªº¸Ñ¨M¤è®×¦p¤U,½Ð«e½ú°Ñ¦Ò
http://forum.twbts.com/thread-23898-1-2.html

Option Explicit
Sub ²M°£¤£²Å±ø¥óªº¦C_¨Ã±Æ§Ç()
Dim Arr, Brr(), xArea As Range, x&, Xm&, y&, Ym&, N&, Da As Date
'¡ô«Å§iÅܼÆ:Arr¬O ³q¥Î«¬ÅܼÆ,Brr¬O°}¦C,xArea¬OÀx¦s®æÅܼÆ,¨ä¥L¬Oªø¾ã¼ÆÅܼÆ
Da = Application.Text([¬ì¥Ø¾lÃBªí!B1], "[$-404]e/m/d;@")
'¡ô¥ODa³o¤é´ÁÅܼƬO [B1]¥H°ê¦~®æ¦¡Âର¤å¦r¦AÂର¤é´ÁªºÅܼÆ
With Range([¸ê®Æ°Ï!U1], [¸ê®Æ°Ï!A65536].End(3))
'¡ô¥H¤U¬OÃö©ó¤W­zÀx¦s®æªºµ{§Ç
     Arr = .Value
     '¡ô¥OArr³o³q¥Î«¬ÅܼƬO¤Gºû°}¦C,¥H³oWithµ{§ÇÀx¦s®æ­È±a¤J
     Ym = UBound(Arr, 1)
     '¡ô¥OYm³oªø¾ã¼ÆÅܼƬOArr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹¼Æ
     'PS:¥i¥H¬Ù²¤¬° Ym = UBound(Arr)
     Xm = UBound(Arr, 2)
     '¡ô¥OXm³oªø¾ã¼ÆÅܼƬOArr°}¦C¾î¦V³Ì¤j¯Á¤ÞÄ渹¼Æ
     Set xArea = .Resize(Ym, Xm + 1)
     '¡ô¥OxArea³oÀx¦s®æÅܼƬO ¥H³oWithµ{§ÇÀx¦s®æÂX®i¦V¤UYmÅܼƦC,
     '¦V¥k(XmÅÜ¼Æ + 1)Äæ
     '(PS:¼W¥[1»²§UÄæ)

     ReDim Brr(1 To Ym, 0): Brr(1, 0) = 0
     '¡ô«Å§iBrr³o°}¦CÅܼƬO¤Gºûªº,
     '¤j¤p:Áa¦V±q1¨ìYmÅܼƦC,¾î¦V1Äæ,¯Á¤Þ¸¹¬O0¨ì0
     '¡ô¥O²Ä1­ÓBrr°}¦C­È¬O 0

     For y = 2 To Ym
     '¡ô³]¶¶°j°é!y±q1¨ìYmÅܼÆ
         If CDate(Arr(y, 4)) > Da Then GoTo 101
         '¡ô¦pªGy°j°é¦C²Ä4ÄæArr°}¦C­ÈÂন¤é´Á«á¤j©ó DaÅܼÆ!´N¸õ¨ì101¦ì¸mÄ~Äò°õ¦æ
         N = N + 1: Brr(y, 0) = N
         '¡ô¥ON³oªø¾ã¼ÆÅܼƲ֥[ 1:¥OyÅܼƦC0¯Á¤Þ¸¹ÄæBrr°}¦C­È¬O NÅܼÆ
101: Next y
     If N = Ym - 1 Then Exit Sub
     '¡ô¦pªGNÅܼÆ=YmÅܼÆ-1!´Nµ²§ôµ{¦¡°õ¦æ(¥Nªí¨S¦³²Å¦X±ø¥óªº¦C)
     xArea.Columns(Xm + 1) = Brr
     '¡ô¥OxAreaÅܼƪº(XmÅܼÆ+1)Äæ½d³òÀx¦s®æ¥HBrr°}¦C­È±a¤J(PS:»²§UÄæ±a¤J»²§U¯Á¤Þ­È)
End With
With xArea
'¡ô¥H¤U¬OÃö©ó xAreaÅܼƪºµ{§Ç
     .Sort KEY1:=.Item(Xm + 1), Order1:=xlAscending, Header:=xlNo
     '¡ô¥OxArea(Àx¦s®æ)¥H(XmÅܼÆ+1)Äæ²Ä1¦CÀx¦s®æ©Ò¦bªºÄæ(ABÄæ)·í°ò·Ç,
     '°µ¨S¦³¼ÐÃD¦Cªº¶¶±Æ§Ç±Æ§Ç

     .Rows(N + 2 & ":" & Ym).Delete
     '¡ô¥OxAreaÅܼƽd³ò¸Ìªº²Ä (NÅÜ¼Æ + 2)¦C¨ì ½d³ò¸ÌªºYmÅܼƦC,
     '³o½d³òÀx¦s®æ¦C§R°£

     .Columns(Xm + 1).Delete
     '¡ô¥OxAreaÅܼƪº(XmÅܼÆ+1)Äæ½d³òÀx¦s®æÄæ§R°£ (PS:»²§UÄæ§R°£)
     .Sort _
     KEY1:=[B1], Order1:=xlAscending, _
     Key2:=[C1], Order2:=xlAscending, _
     key3:=[D1], Order3:=xlAscending, _
     Header:=xlYes, Orientation:=xlTopToBottom
     '¡ô¥O¸ê®Æ°µ3¼h¦¸±Æ§Ç
End With
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 29# Andy2483
Andy¤j!
³o¤èªk¥i¦æ¡A¥i¬O¦³­Ó°ÝÃD..¦]¬°¸Ó¤è¦¡¬Oª½±µ¾ã²z¸ê®Æ°Ï¡A­Y§Ú­n¦A¦^ÀY¬d2023/02/28ªº¸ê®Æ´N¨S¦³¤F...
½d¨Ò¬O§Ú¿z¿ï¥X1¤ëªº¸ê®Æ¡A½Ð°Ý¥i¥H¦b¤£§R°£±¡ªp¤U¥u§ì¬d¸ß¤éªº©ú²Ó¶Ü?³Ò·Ð¤F...!!

TOP

        ÀR«ä¦Û¦b : ª¾ÃÑ­n¥Î¤ßÅé·|¡A¤~¯àÅܦ¨¦Û¤vªº´¼¼z¡C
ªð¦^¦Cªí ¤W¤@¥DÃD