¼ÐÃD:
±ø¥ó¦¡®æ¦¡³]©w
[¥´¦L¥»¶]
§@ªÌ:
198188
®É¶¡:
2024-2-27 11:59
¼ÐÃD:
±ø¥ó¦¡®æ¦¡³]©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µª®×¬Ot¼Æ¡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Åé¬õ¦â¡^
§@ªÌ:
Andy2483
®É¶¡:
2024-2-27 14:34
¥»©«³Ì«á¥Ñ 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
§@ªÌ:
198188
®É¶¡:
2024-2-27 15:06
¦^´_
2#
Andy2483
¦pªG¤¤¶¡¦³¨â¤TӪŦæ¡A³oÓ»Ýn«ç¼Ë§ï¡A¤~¯àpºâ³Ì«á¤@¦æ
[attach]37514[/attach]
Arr = Range([Invoice!H12], [Invoice!C65536].End(3))
§@ªÌ:
Andy2483
®É¶¡:
2024-2-27 15:19
¦^´_
3#
198188
Sorry
ReDim Crr©ñ¿ù¦ì¸m¤F,½Ð¬Ý2#ªº×¥¿
§@ªÌ:
198188
®É¶¡:
2024-2-27 16:30
¦^´_
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
§@ªÌ:
Andy2483
®É¶¡:
2024-2-27 16:40
¦^´_
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
§@ªÌ:
198188
®É¶¡:
2024-2-27 16:42
¦^´_
6#
Andy2483
©ú¥Õ¡AÁÂÁ¡C
§@ªÌ:
ã´£³¡ªL
®É¶¡:
2024-2-28 09:56
¤å¦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¨Ó~~
§@ªÌ:
198188
®É¶¡:
2024-2-28 10:39
¦^´_
8#
ã´£³¡ªL
¤F¸Ñ¡A§Ú¤w¸g°µ¤F®æ¦¡³]©w
§@ªÌ:
198188
®É¶¡:
2024-2-28 10:47
¦^´_
8#
ã´£³¡ªL
ã¤j¡A¶}§_À°§Ú¬Ý¬Ý³oÓ¶K
https://forum.twbts.com/viewthread.php?tid=24205&pid=123061&page=1&extra=pageD1#pid123061
§@ªÌ:
198188
®É¶¡:
2024-2-28 11:27
¦^´_
2#
Andy2483
§Ú¥[¤F¤@¥y §R°£¤§«eªº¸ê®Æ¡A©Mקï¤FH10 & J10 ¬°¶}©l
Option Explicit
Sub TEST2()
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
Range([O10], [J65536].End(xlUp)(3)).Delete Shift:=xlUp
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 & "Repeat": Exit Sub
Else
Z(T) = Val(Brr(i, 5))
Z(T & "/n") = 1
End If
Next
Arr = Range([Invoice!H10], [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 & "Repeat": 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!J10].Resize(UBound(Crr), 6) = Crr
End Sub
§@ªÌ:
Andy2483
®É¶¡:
2024-2-29 07:41
¦^´_
11#
198188
ÁÂÁ«e½ú¤@°_¾Ç²ß
«á¾Ç½Æ²ß¤è®×¤ß±oµù¸Ñ¦p¤U,½Ð¸Õ¤F¸Ñ«á¹B¥Î
Option Explicit
Sub TEST()
Dim Arr, Brr, Crr, V, Z, i&, T$
'¡ô«Å§iÅܼÆ
Set Z = CreateObject("Scripting.Dictionary")
'¡ô¥OZÅܼƬO ¦r¨å
Brr = Range([Data!E2], [Data!A65536].End(3))
'¡ô¥OBrrÅܼƬO¤Gºû°}¦C,¥H¤u§@ªí½d³òÀx¦s®æȱa¤J
For i = 1 To UBound(Brr)
'¡ô³]¶¶°j°é!
T = Trim(Brr(i, 1)) & "/" & Val(Brr(i, 2)) & "/" & Val(Brr(i, 3))
'¡ô¥OTÅܼƬO1Äæ¦r¦ê.2Äæ¼ÆÈ»P3Äæ¼ÆÈ¥H"/"²Å¸¹¦ê±µ¦¨ªº·s¦r¦ê
If Z.EXISTS(T) Then
'¡ô¦pªGZ¦r¨å¸Ì¦³TÅܼƳokey
MsgBox T & "«½Æ": Exit Sub
'¡ô¥Nªí¸ê®Æ¦³«½Æ,¸õ¥X´£¥Üµ¡,«ö½T©w«áµ²§ôµ{¦¡°õ¦æ
Else
'§_«h
Z(T) = Val(Brr(i, 5))
'¡ô¥O¥HTÅܼƬ°key,5Äæ¼ÆȬ°item ¯Ç¤JZ¦r¨å¤¤
Z(T & "/n") = 1
'¡ô¥O¥HTÅܼƳs±µ"/n"²Õ¦¨ªº·s¦r¦ê¬°key,1¬°item ¯Ç¤JZ¦r¨å¤¤
End If
Next
Arr = Range([Invoice!H12], [Invoice!C65536].End(3))
'¡ô¥OArrÅܼƬO¤Gºû°}¦C,¥H¤u§@ªí½d³òÀx¦s®æȱa¤J
ReDim Crr(1 To UBound(Arr), 1 To 6)
'¡ô«Å§iCrrÅܼƬO¤GºûªÅ°}¦C,¨Ã«Å§i¨ä½d³ò
For i = 1 To UBound(Arr)
'¡ô³]¶¶°j°é!
If Trim(Arr(i, 1)) = "" Then GoTo i01
'¡ô¦pªG1Äæ°}¦CÈ¥h°£ÀY§ÀªÅ¥Õ¦r¤¸«á ¬OªÅ¦r¤¸,´N¸õ¨ì¼Ð¥Üi01¦ì¸mÄ~Äò°õ¦æ
T = Trim(Arr(i, 1)) & "/" & Val(Arr(i, 2)) & "/" & Val(Arr(i, 3))
Z(T & "/n") = Z(T & "/n") + 1
'¡ô¥OitemȲ֥[1
If Z(T & "/n") > 2 Then MsgBox T & "«½Æ": Exit Sub Else V = Z(T)
'¡ô¥Nªí¸ê®Æ¦³«½Æ,¸õ¥X´£¥Üµ¡,«ö½T©w«áµ²§ôµ{¦¡°õ¦æ,§_«h´N¥OVÅܼƬO¥HTÅܼƬdZ¦r¨å±o¨ìªºitemÈ
If V = "" Then GoTo i01
'¡ô¦pªGVÅܼƬOªÅ¦r¤¸,´N¸õ¨ì¼Ð¥Üi01¦ì¸mÄ~Äò°õ¦æ
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))
'¡ô¥O¥HArr°}¦CȨ̻ݨDpºâ«á±a¤J Crr°}¦C¤¤
i01: Next
[Invoice!J12].Resize(UBound(Crr), 6) = Crr
'¡ô¥OCrr°}¦Cȼg¤JÀx¦s®æ¤¤
End Sub
Åwªï¥úÁ{ ³Â»¶®a±Ú°Q½×ª©ª© (http://forum.twbts.com/)