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

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

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

¥»©«³Ì«á¥Ñ shuo1125 ©ó 2023-3-18 18:56 ½s¿è

¦U¦ì«e½ú¦n!
·Q¥H¦r¨åªk»s§@¬ì¥Ø¾lÃB©ú²Ó¡A¦ý±ø¥ó¯A¤Î½ÆÂø¯uªº§Ë¤£¥X¨Ó...
»Ý¨D¦p¤U:
¤u§@ªí¬°Sheet01¸ê®Æ°Ï¡ASheet02¬ì¥Ø¾lÃBªí(®Mªí)¡A¨ä¾l¬°Âà¥X¤§¦U¬ì¥Øªí¡C
¤@¡B¸ê®Æ°ÏSÄ欰­É¶U¾l¡A¦¹³¡¤À¥Î©ó¾lÃB¨M©w­É-¶UÁÙ¬O¶U-­É¡C
¤G¡B§PÂ_±ø¥ó¬°1.¬ì¥Ø½s¸¹(BÄæ) 2.¶Ç²¼½s¸¹-§Ç(HÄæ) 3.¹ô§O(LÄæ) 4.­ì¹ôª÷ÃB(N : OÄæ) 5.¥»¹ôª÷ÃB(P : QÄæ) 6.¨R¾Pªº¸¹½Xµù°O(KÄæ) 7.­É¶U¾l(SÄæ)¡C
¤T¡B§PÂ_¸ê®Æ°ÏBÄæ¬ì¥Ø¡A¦P¬ì¥Ø©ú²Ó ¨Ì¤W­z±ø¥ó·í¤ë¨R¾PÅܤƱa¤J¬ì¥Ø¾lÃBªí(®Mªí)¤¤¡C
¬ì¥Ø¾lÃBªí(®Mªí)1-12¤ë¬°¥»¹ô¼Æ¤£»Ý³B²z­ì¹ô¤§ÅܤơA¦ý­ì¹ô¾lÃB»Ýªí¹F¦bSÄæ¡C
¸Ô¦pªþ¥ó¡A½Ð°Ý¦U¦ì°ª¤â³o¦³¿ìªk³B²z¶Ü...
¥H¤W¡A¦A³Â·Ð¤F¡AÁÂÁ¡I¡I

TEXT20230318.zip (17.83 KB)

¥u©Ô¥X"¥ß±b"ªº¦æ???
¦³¸ó¦~, ¼ÒÀÀµ²ªG»P¸ê®Æ¤ë¥÷¤]¹ï¤£¤W!!!

TOP

¥»©«³Ì«á¥Ñ shuo1125 ©ó 2023-3-19 16:40 ½s¿è

¦^´_ 2# ­ã´£³¡ªL
·Ç¤j¦n!
½T¹ê¬O¥u»Ý­n¦C¥X¥ß±bªº©ú²Ó....¼ÒÀÀ¸ê®Æ¦³ÂI»~®t§Ú§ó¥¿¤@ª©¨Ãªþ¤W»Ý­n"µ²ªG»¡©ú"¡C
¥H¬ì¥Øªí¹F¦ÜºI¤î¤é¨R¾P§¹¦¨¤§©ú²Ó(¨Ò:­É¾l¡A¼ÒÀÀµ²ªG¬°¦©±¼¶U¤è¨R¾P«á³Ñ¾l¤§©ú²Ó¡A¦ý¨R¾P«á¬°0ªÌÁÙ¬O­nÅã¥Ü¡C)
¼È®É¥u»Ý­n°O¿ý·í¦~ÅܤƴN¦n¡A¦]¦~ªì§Ú·|§ó·s¾lÃB¡A©Ò¥H¬O¥H¦~ªì¦U¬ì¥Ø¾lÃB+¥»¦~©ú²Ó±bÅܤƥh°µ¡C
¸Ô¦pªþÀÉ¡A©êºpªí¹F¤£²M..¥i¦b³Ò·Ð·Ç¤j¨ó§U¶Ü?

TEXT20230318-1.zip (34.71 KB)

TOP

¥»©«³Ì«á¥Ñ singo1232001 ©ó 2023-3-20 13:03 ½s¿è

¦^´_ 3# shuo1125


³o¦³2­Ó°ÝÃD «ÝÂç²M

A:
¥ß±b »P¨R±b  ¥Ø«e¬Ý¨Ó¦³ÂI°ÝÃD
1.µLÃöÁä¸ê®ÆÄæ¦ì¯à°÷§PÂ_ ¨âµ§¬O§_¦P¤@µ§
2.¥u¯à¼Ò½kªº¥Îª÷ÃB§PÂ_¬O§_¬Û¦P, ¤]¦³¥i¯àµo¥Íª÷ÃB¤£¦Pªº¨R±b
3.¤Ï¤§­Y¥u¨Ì·Ó¦P¬ì¥Ø½s¸¹ »P ª÷ÃB¬Û¦P¨Ó§PÂ_¬O§_¦P¤@µ§  ¨º´N¥i¯à·|¦³¿ù»~

B:
¨R±b®Éªº¤ë¥÷
­Y10901 ,11001,11101 ¸ó¦~«×³£­nºâ¦b¤@¤ë¶Ü?

Àɮרå¼»s§@§¹¦¨ ¥u¬O½m²ß¥Î¦Ó¤w

TEXT20230318-1 v1.zip (49.43 KB)

TOP

¥»©«³Ì«á¥Ñ shuo1125 ©ó 2023-3-20 14:29 ½s¿è

¦^´_ 4# singo1232001
¼y¤j¦n!

A:
¥ß±b »P¨R±b¥Ø«e¬Ý¨Ó¦³ÂI°ÝÃD
1.µLÃöÁä¸ê®ÆÄæ¦ì¯à°÷§PÂ_ ¨âµ§¬O§_¦P¤@µ§
  >ÃöÁä¦b¸ê®Æ°ÏªºKÄ檺"³Æµù"¡A¨ä¹ê¬O§Ú±N´Áªì¼Æ§ó·s«á¡A»P·í´Á©ú²Ó±b¦X¨Ö¦b¦P¤@¤u§@ªíªí¹F¡C
    (¥H¹ÏAB¬°¨Ò¡A111/03/11¥ß¤F¤@µ§11103110001-10ª÷ÃB$235,104¡A112/01/18µo¥Í¨R±b³Æµù¨R¾P½s¸¹¦b¸ê®Æ°ÏKÄ榹µ§¨R¾Pª÷ÃB$235,104¡A
    ¥ß±b®É¬°11103110001-10³oµ§¡A¨R±b¬Oµo¥Í¦b112/01¤ë©Ò¥H¤£¥Î°O¿ý¸Ó¶Ç²¼½s¸¹-§Ç¡A¥u±N¨R¾Pª÷ÃBªí¹F¦b¥ß±bªº1-12¤ë¤ë¨R¾P¼Æ¤¤¡C)

2.¥u¯à¼Ò½kªº¥Îª÷ÃB§PÂ_¬O§_¬Û¦P, ¤]¦³¥i¯àµo¥Íª÷ÃB¤£¦Pªº¨R±b
  >¤ñ¹ï±ø¥ó°ò¥»¤W¬O¥H¥ß±bªº¶Ç²¼½s¸¹-§Ç¨Ó¬Ý¡A°ò¥»¤W»P¸ê®Æ°ÏKÄæ(¹ÏA)¨R¾Pªº³Æµù¤@©w·|¬Ûµ¥(¤@µ§¹ï¨R¤@µ§)¡A¬Ò¤£¦Ò¼{¤ñ¹ïª÷ÃB¥u­nªí¹F¨R¾P¤ëµo¥Í¤§¨R¾PÃB´N¦n¡C

3.¤Ï¤§­Y¥u¨Ì·Ó¦P¬ì¥Ø½s¸¹ »P ª÷ÃB¬Û¦P¨Ó§PÂ_¬O§_¦P¤@µ§  ¨º´N¥i¯à·|¦³¿ù»~
  >KÄæµù°Oªº½s¸¹¥¿±`³£¬O³æµ§¨R¾P¡A­Y¶Ç²¼½s¸¹-§Ç¬°5½Xµ¥¦P¡A¤£¦Ò¼{ª÷ÃB¥uªí¹F¥ß±b¨R¾P¤ëµo¥Í¤§¨R¾Pª÷ÃB¡C
B:
¨R±b®Éªº¤ë¥÷
  >¥u­n¨R¾P¦~¤ë¦b·í¤ë¡A´N³£¬OÅܤƦA1¤ë¡A¥ß±b©ú²Ó¦³¸ó¦~¬°¥¿±`¡A¦]¬O¤ëªì³Ñ¤Uªº¾lÃB©ú²Ó¡C(¥H¹ÏA¨Ó¬Ý¡A11201µo¥Í¨R¾P¡A©Ò¥HÅܤƴN³£¦bªí¹F¦b1¤ë¡C)

¥H¤W¡Aªí¹F¤@ª½¤£²M³y¦¨§xÂZ¦b¦¹²`·P©êºp....¦A½Ð¨ó§U!!

¹ÏA.PNG (76.42 KB)

¹ÏA.PNG

¹ÏB.PNG (91.8 KB)

¹ÏB.PNG

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-3-21 14:03 ½s¿è

¦^´_ 3# shuo1125


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
«á¾ÇÂǦ¹¥DÃD½m²ß°}¦C»P¦r¨åªº¸Ñ¨M¤è®×¦p¤U,½Ð«e½ú°Ñ¦Ò

Option Explicit
Sub TEST()
Application.DisplayAlerts = False
Dim Brr, A, Y, Z, Yk, T$, T2$, T3$, T9$, T10$, S1$, S2$
Dim x%, C%, N&, i&, P&, B(3), Crr(1 To 1000, 1 To 20)
Set Y = CreateObject("Scripting.Dictionary")
Z = Array(, 4, 8, 10, 12, 14, 16, 15, 17)
Brr = Sheets("¸ê®Æ°Ï").UsedRange
For i = 2 To UBound(Brr)
   T2 = Brr(i, 2): T3 = Brr(i, 3)
   S1 = T2 & "|" & T3: Y(S1 & "/b") = T2: Y(S1 & "/c") = T3
   A = Y(S1)
   If Not IsArray(A) Then A = Crr
   T9 = Brr(i, 9)
   B(1) = Mid(T9, 1, 3): B(2) = Mid(T9, 4, 2): B(3) = Mid(T9, 6, 2)
   B(0) = B(1) & "." & B(2) & "." & B(3) & "#" & Val(Mid(T9, 8))
   T10 = Brr(i, 10)
   If T10 Like "¨R*" = False Then
      N = Y(S1 & "|r")
      N = N + 1
      Y(S1 & "|r") = N
      S2 = B(0) & "-" & T10:  Y(S2) = N
      For x = 1 To 4: A(N, x) = Brr(i, Z(x)): Next
      For x = 5 To 6
         A(N, x) = Brr(i, Z(x)) + Brr(i, Z(x + 2))
         A(N, x + 14) = A(N, x)
      Next
      Y(S1) = A
      GoTo i01
      ElseIf T10 Like "*¤ë±b´Ú" Then
         B(0) = Mid(Split(T10, "¤ë")(0), 2)
         B(0) = B(0) & Replace(T10, "¨R", ".#0-")
         B(0) = Replace(B(0), "¤ë±b´Ú", "À³¥I±b´ÚÁ`ÃB")
      ElseIf T10 Like "¨R###/*#/*#*" Then
         B(1) = Mid(T10, 2, 4)
         B(2) = Format(Split(Mid(T10, 6), "#")(0), "MM/DD")
         B(3) = "#" & Split(T10, "#")(1)
         B(0) = Replace(B(1) & B(2) & B(3), "/", ".")
      ElseIf T10 Like "¨R?????*  ###/*#/*#" Then
         B(0) = Split(Mid(T10, 3), "  ")
         B(1) = Mid(Brr(i, 11), 1, 3)
         B(2) = "." & Mid(Brr(i, 11), 4) & ".#0-"
         B(3) = B(0)(0) & "  " & B(0)(1)
         B(0) = B(1) & B(2) & B(3)
   End If
   C = Format(Brr(i, 4), "M") + 6
   A(Y(B(0)), C) = Brr(i, 16) + Brr(i, 17)
   A(Y(B(0)), 20) = A(Y(B(0)), 20) - A(Y(B(0)), C)
   P = Brr(i, 14) + Brr(i, 15)
   A(Y(B(0)), 19) = A(Y(B(0)), 19) - P
   Y(S1) = A
   
i01:
Next
'====================================
For Each Yk In Y.keys
   If IsArray(Y(Yk)) Then
      On Error Resume Next
      Sheets(Val(Yk) & "").Delete
      On Error GoTo 0
      Sheets("¬ì¥Ø¾lÃBªí").Copy Before:=Sheets(1)
      With Sheets(1)
         .Name = Val(Yk)
         .UsedRange.Offset(5, 0).Delete
         With .[A5].Resize(Y(Yk & "|r"), 20)
            .Value = Y(Yk)
            Intersect([E:T], .Cells).NumberFormatLocal = _
            "_-* #,##0_-;-* #,##0_-;_-* ""-""??_-;_-@_-"
         End With
         .[C3] = Y(Yk & "/c")
         .[C3] = .[C3] & "¡m" & Y(Yk & "/b") & "¡n"
         N = .Cells(Rows.Count, "F").End(3).Row
         With .Cells(N + 1, "F").Resize(1, 15)
            .Value = "=SUM(F5:F" & N & ")"
         End With
      End With
   End If
Next
Set Y = Nothing: Erase Brr, Crr, Z, A, B
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¥»©«³Ì«á¥Ñ shuo1125 ©ó 2023-3-21 21:21 ½s¿è

¦^´_ 6# Andy2483
Andy2483¤j¦n!
§Úµo²{¨ä¹ê¬O¤W¶Çªº¼ÒÀÀµ²ªG¤Î¸ê°T°Ï¬Ò¦³¿ù»~¡A©Ç¤£±o·í®É·Ç¤j·|»¡¦³°ÝÃD...
§A°w¹ï¿ùªº¸ê°TÁٯవ¥X¹ïÀ³ªºµ²ªG..
ªþ¤W¥¿½T¤§ª©¥»..®ö¶O¤F¤j®aªº®É¶¡...¦b¦¹»¡Án©êºp!!
¹w­p²£¥Xªºµ²ªG¬°¦ÜºI¤î¤é¤î¥ß±b-¨R±b«á©ú²Óªº¾lÃB¡A¨R±b®ÉKÄæµù°Oªº½s¸¹°ò¥»¤W·|¤@¹ï¤@¨R¾P¡C
(½d¨Ò¬ì¥Ø¬°2­Ó¡A¹ê»Ú¤W·|¦³¦h­Ó..©Ò¥H¤~·Q»¡¥H®Mªí¨Ó²£¥X¦U¬ì¥Ø¡C)

TEXT20230318-2.zip (34.39 KB)

TOP

¦^´_ 7# shuo1125


    ÁÂÁ«e½ú¦^´_
1.¥¿©Ò¿×·í§½ªÌ°g,¸Ñ½d¨Ò¤~ª¾½d¨Ò°ÝÃD¦b­þ¸Ì
2.«e½ú¦h¼{¤F,¨Ã¨S¦³®ö¶O®É¶¡,«á¾Ç¦b½×¾Â¤W¦b¦¹©«¤W¾Ç²ß¨ì«Ü¦hª¾ÃÑ

3.´N«á¾Çªº»{ª¾»P¸gÅç,³ÆµùÄæÄÝ©óÂø¶µÄæ¦ì,°µ¬°ÃöÁä¦rÄ榳«Ü¦hºÃ¼{,
½Ð«e½ú¬dÃÒ¤@¤UKÄæ³ÆµùÄæ¬O§_³£¥u©ñ ¶Ç²¼½s¸¹-§Ç(¨R¾P¸¹½X)
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

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

¦^´_ 7# shuo1125


    ¥HKÄæ³ÆµùÄæ³£¥u©ñ ¶Ç²¼½s¸¹-§Ç(¨R¾P¸¹½X)ªº±¡¹Ò¤U°µ¦r¨å¹ï·Ó¸Ñ¨M¤è®×¦p¤U,
½Ð«e½ú°Ñ¦Ò

KÄæ³ÆµùÄæ(¶Ç²¼½s¸¹-§Ç)¨R¾PHÄæ(¶Ç²¼½s¸¹-§Ç),¤@¹ï¦h¹ï¨R¾P¬O¤°»ò³W«h??



Option Explicit
Sub TEST()
Application.DisplayAlerts = False
Dim Brr, A, Y, Z, Yk, T2$, T3$, T8$, T11$, S1$
Dim x%, C%, N&, i&, P&, Crr(1 To 1000, 1 To 20)
Set Y = CreateObject("Scripting.Dictionary")
Z = Array(, 4, 8, 10, 12, 14, 16, 15, 17)
Brr = Sheets("¸ê®Æ°Ï").UsedRange
For i = 2 To UBound(Brr)
   T2 = Brr(i, 2): T3 = Brr(i, 3)
   S1 = T2 & "|" & T3: Y(S1 & "/b") = T2: Y(S1 & "/c") = T3
   A = Y(S1)
   If Not IsArray(A) Then A = Crr
   T8 = Brr(i, 8): T11 = Brr(i, 11)
   If T11 Like "#####*" = False Then
      N = Y(S1 & "|r")
      N = N + 1
      Y(S1 & "|r") = N
      Y(T8) = N
      For x = 1 To 4: A(N, x) = Brr(i, Z(x)): Next
      For x = 5 To 6
         A(N, x) = Brr(i, Z(x)) + Brr(i, Z(x + 2))
         A(N, x + 14) = A(N, x)
      Next
      Y(S1) = A
      GoTo i01
   End If
   C = Format(Brr(i, 4), "M") + 6
   A(Y(T11), C) = Brr(i, 16) + Brr(i, 17)
   A(Y(T11), 20) = A(Y(T11), 20) - A(Y(T11), C)
   P = Brr(i, 14) + Brr(i, 15)
   A(Y(T11), 19) = A(Y(T11), 19) - P
   Y(S1) = A
   
i01:
Next
'====================================
For Each Yk In Y.keys
   If IsArray(Y(Yk)) Then
      On Error Resume Next
      Sheets(Val(Yk) & "").Delete
      On Error GoTo 0
      Sheets("¬ì¥Ø¾lÃBªí").Copy Before:=Sheets(1)
      With Sheets(1)
         .Name = Val(Yk)
         .UsedRange.Offset(5, 0).Delete
         With .[A5].Resize(Y(Yk & "|r"), 20)
            .Value = Y(Yk)
            Intersect([E:T], .Cells).NumberFormatLocal = _
            "_-* #,##0_-;-* #,##0_-;_-* ""-""??_-;_-@_-"
         End With
         .[C3] = Y(Yk & "/c")
         .[C3] = .[C3] & "¡m" & Y(Yk & "/b") & "¡n"
         N = .Cells(Rows.Count, "F").End(3).Row
         With .Cells(N + 1, "F").Resize(1, 15)
            .Value = "=SUM(F5:F" & N & ")"
         End With
      End With
   End If
Next
Set Y = Nothing: Erase Brr, Crr, Z, A
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

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

¦^´_ 7# shuo1125

1.¬O¥H(¹ô§O + ¶Ç²¼½s¸¹-§Ç)¬üª÷¨R¾P¬üª÷,¥x¹ô¨R¾P¥x¹ô¶Ü?

2.µ²ªGªíªºSÄæ­ì¹ô¾lÃB Á`¦X­p¦³·N¸q¶Ü? «O¯d¦X­p©Î¨ú®ø¦X­p?
PS:­ì¹ô¾lÃB¥i¯à¦³¦hºØ¹ô§Oªº¾lÃB,¬Û¥[³Q¤Þ¥Î³ô¼{

Option Explicit
Sub TEST_¹ô§O_¶Ç²¼½s¸¹§Ç()
Application.DisplayAlerts = False
Dim Brr, A, Y, Z, Yk, T2$, T3$, T8$, T11$, T12$, S1$, S2$
Dim x%, C%, N&, i&, P&, Crr(1 To 1000, 1 To 20)
Set Y = CreateObject("Scripting.Dictionary")
Z = Array(, 4, 8, 10, 12, 14, 16, 15, 17)
Brr = Sheets("¸ê®Æ°Ï").UsedRange
For i = 2 To UBound(Brr)
   T2 = Brr(i, 2): T3 = Brr(i, 3)
   S1 = T2 & "|" & T3: Y(S1 & "/b") = T2: Y(S1 & "/c") = T3
   A = Y(S1)
   If Not IsArray(A) Then A = Crr
   T8 = Brr(i, 8): T11 = Brr(i, 11): T12 = Brr(i, 12)
   If T11 Like "#####*" = False Then
      N = Y(S1 & "|r"): N = N + 1: Y(S1 & "|r") = N
      S2 = T8 & "|" & T12: Y(S2) = N
      For x = 1 To 4: A(N, x) = Brr(i, Z(x)): Next
      For x = 5 To 6
         A(N, x) = Brr(i, Z(x)) + Brr(i, Z(x + 2))
         A(N, x + 14) = A(N, x)
      Next
      Y(S1) = A: GoTo i01
   End If
   C = Format(Brr(i, 4), "M") + 6
   S2 = T11 & "|" & T12
   A(Y(S2), C) = Brr(i, 16) + Brr(i, 17)
   A(Y(S2), 20) = A(Y(S2), 20) - A(Y(S2), C)
   P = Brr(i, 14) + Brr(i, 15)
   A(Y(S2), 19) = A(Y(S2), 19) - P
   Y(S1) = A
   
i01:
Next
'====================================
For Each Yk In Y.keys
   If IsArray(Y(Yk)) Then
      On Error Resume Next
      Sheets(Val(Yk) & "").Delete
      On Error GoTo 0
      Sheets("¬ì¥Ø¾lÃBªí").Copy Before:=Sheets(1)
      With Sheets(1)
         .Name = Val(Yk)
         .UsedRange.Offset(5, 0).Delete
         With .[A5].Resize(Y(Yk & "|r"), 20)
            .Value = Y(Yk)
            Intersect([E:T], .Cells).NumberFormatLocal = _
            "_-* #,##0_-;-* #,##0_-;_-* ""-""??_-;_-@_-"
         End With
         .[C3] = Y(Yk & "/c")
         .[C3] = .[C3] & "¡m" & Y(Yk & "/b") & "¡n"
         N = .Cells(Rows.Count, "F").End(3).Row
         With .Cells(N + 1, "F").Resize(1, 15)
            .Value = "=SUM(F5:F" & N & ")"
            If .Item(14) <> .Item(15) Then .Item(14) = "NA"
         End With
      End With
   End If
Next
Set Y = Nothing: Erase Brr, Crr, Z, A
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : §Ú­Ì­n°µ¦nªÀ·|ªºÀô«O¡A¤]­n°µ¦n¤º¤ßªºÀô«O¡C
ªð¦^¦Cªí ¤W¤@¥DÃD