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

±ø¥ó¦¡®æ¦¡³]©w

±ø¥ó¦¡®æ¦¡³]©w

®Ú¾ÚInvoice ªí ªºÄæC - E §äDataªí ªºÄæ A - C ¸ê®Æ¬Û¦Pªº
Invoice ªí ªºÄæC ¹ï¤ñ Dataªí ªºÄæ A
Invoice ªí ªºÄæD ¹ï¤ñ Dataªí ªºÄæ B
Invoice ªí ªºÄæE ¹ï¤ñ Dataªí ªºÄæ C
¦pªG3­Ó³£¬Û¦P¡AŪ¨úDataªí ªºÄæ E ¨ì Invoice ªíªºÄæJ

Invoice ªíªºÄæK = ÄæJ - ÄæF  (µª®×¬O¥¿¼Æ¡A¦rÅéÅÜÂŦâ¡Aµª®×¬O­t¼Æ¡A¦rÅéÅܬõ¦â)
Invoice ªíªºÄæL = ROUND (ÄæD * ÄæE / 1000000,3)
Invoice ªíªºÄæM =ÄæL - ÄæG (µª®×¤£µ¥©ó0¡A¦rÅé¬õ¦â¡^
Invoice ªíªºÄæN= ROUND (ÄæL * ÄæF,3)
Invoice ªíªºÄæO =ÄæN - ÄæH (µª®×¤£µ¥©ó0¡A¦rÅé¬õ¦â¡^

½d¨ÒB.rar (133.09 KB)

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2024-2-27 15:20 ½s¿è

¦^´_ 1# 198188

ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
«á¾ÇÂǦ¹©«¾Ç¨ì¤F ROUND() «Ü¦hª¾ÃÑ,¾Ç²ß¤è®×¦p¤U,½Ð«e½ú°Ñ¦Ò
https://learn.microsoft.com/zh-t ... help/round-function

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

TOP

¦^´_ 2# Andy2483

¦pªG¤¤¶¡¦³¨â¤T­ÓªÅ¦æ¡A³o­Ó»Ý­n«ç¼Ë§ï¡A¤~¯à­pºâ³Ì«á¤@¦æ

    Arr = Range([Invoice!H12], [Invoice!C65536].End(3))

TOP

¦^´_ 3# 198188

Sorry
ReDim Crr©ñ¿ù¦ì¸m¤F,½Ð¬Ý2#ªº­×¥¿
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 4# Andy2483

§Ú¦b³oùØ¥[¤F¤@¥y§ï顔¦â¡A§Ú¥Î¤F¤U­±5­Ó¤è¦¡¡A³£¬O¿ù¡A½Ð°Ý¬O­þùؤ£¹ï¡H

     Crr(i, 2) = V - Val(Arr(i, 4))

     If Crr(i, 2) > 0 Then Crr(i, 2).Font.ColorIndex = 3

     If Crr(i, 2) > 0 Then Crr(i, 2).Font.ColorIndex = RGB(205, 238, 202)

     If Crr(i, 2) > 0 Then Worksheets("Invoice").Range("K" & i).Font.ColorIndex = 3
   
     If Crr(i, 2) > 0 Then Worksheets("Invoice").Range("K" & i).Font.ColorIndex = RGB(205, 238, 202)

     If Crr(i, 2) = 0 then Crr(i, 2).Font.ColorIndex = 6 else Crr(i, 2) > 0 Then Crr(i, 2).Font.ColorIndex = 3 else Crr(i, 2).Font.ColorIndex = 4

TOP

¦^´_ 5# 198188

«Øij«e½ú¤£­n±q¸Ì­±§ï,Åý°}¦C­È¶K¤JÀx¦s®æ«á¼g¥t¤@¬q¥N½X§PÂ_­È¦AÅܧó¦r¦â
¦]Àx¦s®æ¯Á¤Þ¸¹»P°}¦C¯Á¤Þ¸¹¿ù¦ì,®e©ö²V²c
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 6# Andy2483


    ©ú¥Õ¡AÁÂÁ¡C

TOP

¤å¦rÃC¦â¥iª½±µ¥Î®æ¦¡³]©w§Y¥i,
«Øij¹w¥ý³]©w¦nªí®æ®æ¦¡, ¾¨¶q¤£­n¦bµ{¦¡¤¤³v¤@³]¸m, ©ìºC³t«×,
KÄæ//[ÂŦâ]0_ ;[¬õ¦â]-0 ;[¶Â¦â]0
M.OÄæ//[¬õ¦â][<>0]0.000;[¶Â¦â]0.000   ©Î [¶Â¦â][=0]0.000;[¬õ¦â]0.000

±ø¥ó®æ¦¡¤]¥i¥H, ³o«Ü°ò¥»ªº, À³¸Ó¥i¥H¦Û¤v¨Ó~~

TOP

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


    ¤F¸Ñ¡A§Ú¤w¸g°µ¤F®æ¦¡³]©w

TOP

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

­ã¤j¡A¶}§_À°§Ú¬Ý¬Ý³o­Ó¶K
    https://forum.twbts.com/viewthread.php?tid=24205&pid=123061&page=1&extra=pageD1#pid123061

TOP

        ÀR«ä¦Û¦b : ¡i¦æµ½­n¤Î®É¡j¦æµ½­n¤Î®É¡A¥\¼w­n«ùÄò¡C¦p¿N¶}¤ô¤@¯ë¡A¥¼¿N¶}¤§«e¤d¸U¤£­n°±º¶¤õ­Ô¡A§_«h­«¨Ó´N¤Ó¶O¨Æ¤F¡C
ªð¦^¦Cªí ¤W¤@¥DÃD