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

¤£¦P¤u§@ªí§ä¹ï¤ñ¸ê®Æ¡A¤u§@ªí¦WºÙ¤£©T©w

¤£¦P¤u§@ªí§ä¹ï¤ñ¸ê®Æ¡A¤u§@ªí¦WºÙ¤£©T©w

¥»©«³Ì«á¥Ñ 198188 ©ó 2024-2-23 17:34 ½s¿è

¤u§@ªí¡§A" ³o­Ó¬O©T©wªº¤u§@ªí¦WºÙ
¨ä¥L¤u§@ªíªº¦WºÙ¤£©T©w¡A¦Ó¥B¤£©T©w¤u§@ªí¼Æ¶q¡A¦ý¬O·|±Æ¦b¤u§@ªí¡§A"¤§«á
·Q­nªº¥\¯à¬O
®Ú¾Ú¤u§@ªí "A" ùتºÄæ C - H¡A´M§ä¨ä¥L©Ò¦³¤u§@ªíªºÄæ B - G ¬O§_¦³§¹¥þ¤@­Pªº¼Æ¾Ú¡A«ö·Ó¤U­±ªº¹ï¤ñ´M§ä¸ê®Æ¡C¦pªG§ä¨ì¥þ³¡¤@­Pªº¡A¦bÄæ I ª`©ú "Y", ¦pªG¨S¦³¬Û¦P¸ê®Æªº¡A¦bÄæ I ª`©ú "N"
¤u§@ªí"A" Äæ C   ¹ï¤ñ  ¨ä¥L¤u§@ªí ÄæB
¤u§@ªí"A" Äæ D   ¹ï¤ñ  ¨ä¥L¤u§@ªí ÄæC
¤u§@ªí"A" Äæ E   ¹ï¤ñ   ¨ä¥L¤u§@ªí ÄæD
¤u§@ªí"A" Äæ F   ¹ï¤ñ   ¨ä¥L¤u§@ªí ÄæE
¤u§@ªí"A" Äæ G  ¹ï¤ñ   ¨ä¥L¤u§@ªí ÄæF
¤u§@ªí"A" Äæ H  ¹ï¤ñ   ¨ä¥L¤u§@ªí ÄæG

TEST1.rar (314.25 KB)

¦^´_ 1# 198188


    ³o­Ó¥D­n¬O¥d¦b¤u§@ªíªº¤£©T©w¼Æ¶q¤Î¦WºÙ°ÝÃD¤W¡C

TOP

¦^´_ 1# 198188

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

Option Explicit
Sub TEST()
Dim Arr, Brr, Crr, Z, i&, j%, R&, s%, T$, T2$, T3$
Set Z = CreateObject("Scripting.Dictionary")
Brr = Range(Sheets(1).[H12], Sheets(1).[A65536].End(3))
ReDim Crr(1 To UBound(Brr), 1 To 2)
For i = 1 To UBound(Brr)
   T3 = Trim(Brr(i, 3))
   If T3 = "" Then GoTo i01
   For j = 4 To 8
      T = T & "/" & Val(Brr(i, j))
   Next
   Z(T3 & T) = i
   T = ""
   Crr(i, 1) = "N"
i01: Next
For s = 2 To Sheets.Count
   Arr = Range(Sheets(s).[G13], Sheets(s).[A65536].End(3))
   For i = 1 To UBound(Arr)
      T2 = Trim(Arr(i, 2))
      If T2 = "" Then GoTo i02
      For j = 3 To 7
         T = T & "/" & Val(Arr(i, j))
      Next
      R = Z(T2 & T)
      If R <> 0 Then Crr(R, 1) = "Y": Crr(R, 2) = Sheets(s).Name & "/ " & i + 12 & "¦C"
      T = ""
i02: Next
Next
Sheets(1).[I12].Resize(UBound(Crr), 2) = Crr
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 3# Andy2483


    ¤j­P¥i¥H¡A¤£¹L¦³­Ó³¡¤À¦³°ÝÃD¡AAM-JO58-01-1, AM-JO58-01-2, AM-JO58-02 ªºÄæB ¦]爲¤£¬O¼Æ¦r¡A¬O¤å¦r¡A©Ò¥H¾É­P«e­±1-16¦C¤j³¡¤À³£¦³¡A¦ý¬Oµ²ªG«o¼gµL¡C

TOP

¦^´_ 4# 198188


    ÃD·N¬Ý°_¨Ó¬O5Äæ¥þ¦P¤~¼Ð"Y",
½d¨ÒºÉ¶q¬O¦³¤â°Ê³B²z¦n·Q­nªºµ²ªGªþ¦b½d¨Ò¸Ì
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP




    ùØ­±5­Ó¸ê®Æ³£¬O¤@¼Ëªº¡A¤£¹L«È¤á´£¨Ñªºª©¥»¦³Àx¦s®æ®æ¦¡°ÝÃD¡A©Ò¥H¾É­P¹ï¤£¤W¡C
§Ú·N«ä¬O°Ý«e½ú¡A¦³¨S¦³¤@¥yµ{¦¡¡A¥i¥H¦b®Ö¹ï¸ê®Æªº®É­Ô¡A¸õ¹L³oºØ®æ¦¡°ÝÃD¡H

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2024-2-26 14:59 ½s¿è

¦^´_ 6# 198188


    AM-JO58-01-1, AM-JO58-01-2, AM-JO58-02
³o´X­ÓªíªºEÄæ¬OÁôÂð_¨Óªº,§Aªº¤â°Ê¤ñ¹ï¦³»~
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 7# Andy2483


    ­ì¨ÓÁôÂäF¡A§Ú¨S¯d·N©O¡AÁÂÁ¸ѵª¡C

TOP

¥»©«³Ì«á¥Ñ mdr0465 ©ó 2024-3-5 23:26 ½s¿è

¦^´_ 3# Andy2483

  Andy ¾Ç¥S,§A¦n
¤S¬O§Ú³o¤@¦W·MÄøªº¾Ç§Ì

Sub TEST()
Dim Arr, Brr, Crr, V, Z, Q, i&, j%, R&, c%, Y&, X%, T$, T1$, T2$, T3$
Dim xR As Range, Ra As Range, Sh As Worksheet, xBook As Workbook
Set Z = CreateObject("Scripting.Dictionary")
Brr = Range([Data!E2], [Data!A65536].End(3))
For i = 1 To UBound(Brr)
   T = Trim(Brr(i, 1)) & "/" & Val(Brr(i, 2)) & "/" & Val(Brr(i, 3))
   If Z.EXISTS(T) Then
      MsgBox T & "­«½Æ":  Exit Sub
      Else
      Z(T) = Val(Brr(i, 5))
      Z(T & "/n") = 1
   End If
Next
Arr = Range([Invoice!H12], [Invoice!C65536].End(3))
ReDim Crr(1 To UBound(Arr), 1 To 6)
For i = 1 To UBound(Arr)
   If Trim(Arr(i, 1)) = "" Then GoTo i01
   T = Trim(Arr(i, 1)) & "/" & Val(Arr(i, 2)) & "/" & Val(Arr(i, 3))
   Z(T & "/n") = Z(T & "/n") + 1
   If Z(T & "/n") > 2 Then MsgBox T & "­«½Æ":  Exit Sub Else V = Z(T)
   If V = "" Then GoTo i01
   Crr(i, 1) = V
   Crr(i, 2) = V - Val(Arr(i, 4))
   Crr(i, 3) = Application.Round(Val(Arr(i, 2)) * Val(Arr(i, 3)) / 10 ^ 6, 3)
   Crr(i, 4) = Crr(i, 3) - Val(Arr(i, 5))
   Crr(i, 5) = Application.Round(Crr(i, 3) * Val(Arr(i, 4)), 3)
   Crr(i, 6) = Crr(i, 5) - Val(Arr(i, 6))
i01: Next
[Invoice!J12].Resize(UBound(Crr), 6) = Crr <<  ¦bºô¤W§ä¨ì¦n¦h³oÃþ«¬ªº½s½X, resize(ubound(Crr).6) §Ú³£²z¸Ñ, ¦ý=Crr§Ú©l²×³£¤£©ú¥Õ³o­Ó­ì²z, ³Â·Ð§A¥i¤£¥i¥H«üÂI¾Ç§Ì°g¬z, «ç¼ËÀ³¥Î³oºØ¤èªkªº½s½X©O , ÁÂÁÂ
End Sub

TOP

¦^´_ 9# mdr0465

ÁÂÁ½׾Â,ÁÂÁ«e½ú¤@°_¾Ç²ß
½Ð¸Õ°õ¦æ¥H¤U¥N½X:


Option Explicit
Sub Àx¦s®æ»P°}¦C() 'ª`·N! ¥ý¶}¤@­Ó·s¤u§@ªí¦A°õ¦æ,¥H§K»~§R­«­n¸ê®Æ
Dim Arr(1 To 3, 1 To 5), Brr, Crr, Drr, i%, j%, A, N%
ActiveSheet.UsedRange.ClearContents
For i = 1 To UBound(Arr)
   For j = 1 To UBound(Arr, 2)
      N = N + 1
      Arr(i, j) = N
   Next
Next
[A1].Resize(UBound(Arr), UBound(Arr, 2)) = Arr
[A1].Resize(UBound(Arr), UBound(Arr, 2)).Select
MsgBox "¥þ³¡Arr°}¦C­È±qÀx¦s®æ[A1]¶}©l¶K¤J­È"

[A6].Resize(1, UBound(Arr, 2)) = Arr
[A6].Resize(1, UBound(Arr, 2)).Select
MsgBox "²Ä1¦CArr°}¦C­È±qÀx¦s®æ[A6]¶}©l¶K¤J­È"

[A8].Resize(UBound(Arr), 1) = Arr
[A8].Resize(UBound(Arr), 1).Select
MsgBox "²Ä1ÄæArr°}¦C­È±qÀx¦s®æ[A8]¶}©l¶K¤J­È"

[A12].Resize(2, 2) = Arr
[A12].Resize(2, 2).Select
MsgBox "³Ì¥ª¤W¨¤2Äæ2¦CArr°}¦C­È±qÀx¦s®æ[A12]¶}©l¶K¤J­È"

[A15].Resize(4, 6) = Arr
[A15].Resize(4, 6).Select
MsgBox "Arr°}¦C­È¤ñ©Ò«ü©wÀx¦s®æ½d³ò¤p±¡ªp,±qÀx¦s®æ[A15]¶}©l¶K¤J­È¶W¥X³¡¤À·|¥X²{¿ù»~­È"
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¤f»¡¦n¸Ü¡B¤ß·Q¦n·N¡B¨­¦æ¦n¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD