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

[µo°Ý] ½Ð°Ý¯à§_¨Ï¥Î¥¨¶°¨ú¥N¤H¤u¤§¿é¤J ·PÁ¯àªÌ¤j¤OÀ°§U

[µo°Ý] ½Ð°Ý¯à§_¨Ï¥Î¥¨¶°¨ú¥N¤H¤u¤§¿é¤J ·PÁ¯àªÌ¤j¤OÀ°§U

¥»©«³Ì«á¥Ñ lcctno ©ó 2015-9-22 18:10 ½s¿è

½Ð°Ý¯à§_¨Ï¥Î¥¨¶°¨ú¥N¤H¤u¤§¿é¤J ·PÁ¯àªÌ¤j¤OÀ°§U
1.³o¬OªÑ²¼ÁʶR°t¸mµ{¦¡ ­ì¥»§Ú¬O¨Ï¥Î¤H¤uºCºC¿é¤J(©ó¶À¦â®æ¤º) §Ú·QÀ³¸Ó¥i¥H¨Ï¥Î¥¨¶°¨Ó°õ¦æ ³Â·Ð°ª¤â¬Û§U ¥ýÁÂÁ±z¤F
2.­ìÀɮפӤjµLªk¤W¶Ç ¬GÁY´î¦¨³¡¤Àµ{¦¡(¥»¤HVBA¯à¤O¤£¦n ¬Gµµ¦â³¡¤À½Ð¤Å¨Ï¥Î ©Î¨ä¥L¦³¥Î¨ìªº¦a¤è¤]¾¨¶q¤£­n°Ê¨ì)
3.¶R±i¼Æ(¥u¯à¨Ï¥Î¾ã¼Æ) ¥ÑªÅ¥Õ©Î0¨ì I=I+1 ¨ì¤£¯àÅý³Ñ¾lª÷ÃB¤p©ó0
4.C12¨ìC2571¬°VBA¿é¤J¶R±i¼Æ¤§¦ì¸m








ªÑ²¼°t¸m.zip (252.45 KB)

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

·PÁ§¹¬üªº§¹¦¨§Ú©Ò»Ý­nªºµ²ªG

´ú¸Õ¦^³ø ¸Õ»~ªk¶O®É¬ù¬O¤£¨ì2­¿ªº®É¶¡ ¦ý¤]¤£¶W¹L10¬í ¥i¥Hºâ¬O«Ü§¹¬ü¤F






³Ì«á§¹¦¨ÀÉ
ªÑ²¼°t¸mV3-2.zip (290.47 KB)

TOP

¦^´_ 11# lcctno

¸Õ¶ñªk¡G§ï¦p¤U¡A³t«×§óºC
For Each xR In .Cells
¡@¡@If xR(1, 8) <= 0 Then GoTo 101
¡@¡@SU = Int(xR(1, 8) / xR / 1000)
¡@¡@If SU <= 0 Then GoTo 101
¡@¡@xR(1, -1) = SU
¡@¡@If xR(1, 8) < 0 Then SU = SU - 1
¡@¡@If SU > 0 Then xR(1, -1) = SU Else xR(1, -1) = ""
¡@¡@'If xR(1, -1) <> "" Then xR(1, -1).Select
101: Next
¡@

TOP

¥»©«³Ì«á¥Ñ lcctno ©ó 2015-9-23 13:19 ½s¿è

¦^´_ 10# ­ã´£³¡ªL
°h¦þ¡×¤âÄò¶O*°h¦þ²v ³o¬O¤@¯ë¤Hªº»{ª¾ §Ú¥H«e¤]¬O³o¼Ë»{¬°
¸g¦V¨÷°Ó¬dÃÒ ±o¨ìµ²ªG¬°
¨Ò¤@:
1000 *15.4=15400
·í¤é­pºâ :
¤w¦¬¤âÄò¶O=15400*0.001425=21.945   ===> 21

¨C¤ë§éÅý³B²z:
§éÅý«áÀ³¦¬¤âÄò¶O=15400 * .0.001425 *0.28=6.1446 ==>  6

§éÅýª÷= 21-6 =15
--------------------------------------------------------------------------------------------------------------------------------------------------
¨Ò¤G:
1000 * 11.64=11640
·í¤é­pºâ :
¤w¦¬¤âÄò¶O=11640*0.001425=16.587   ===> 16(¤p©ó20¥H20­pºâ) ===>20

¨C¤ë§éÅý³B²z:
§éÅý«áÀ³¦¬¤âÄò¶O=(11640 * .0.001425)(¤p©ó20¥H20­pºâ) *0.28=5.6 ==>  5

§éÅýª÷= 20-5 =15
------------------------------------------------------------------------------------------------------------------------------------------------------
¨Ò¤T:
3000 * 11.64=34920
·í¤é­pºâ :
¤w¦¬¤âÄò¶O=34920*0.001425=49.761   ===> 49

¨C¤ë§éÅý³B²z:
§éÅý«áÀ³¦¬¤âÄò¶O=(34920 * .0.001425) *0.28=13.93308 ==> 13

§éÅýª÷= 49-13 =36

§Ú¦Û¦æ±N¤½¦¡³]¬°§Úªº°h¦þ¤è¦¡(À³¸Ó¬O¹ïªº)
§éÅý¤½¦¡=IF(-(-C12-D12)*E12*1000*$H$10<20,20-TRUNC(20*(1-$E$10)),-G12-TRUNC(ABS(F12)*$H$10*(1-$E$10)))*IF(C12+D12=0,0,1)
C12 = ¶R±i¼Æ
D12 = ½æ±i¼Æ
E12 = ¦¨¥æ»ù
F12 = ¶R or ½æ²b»ù
$H$10 = ¨é°Ó¶R½æ¤âÄò¶O²v
$E$10 = ¨é°Ó§éÅý²v

¥i¤£¥i¥H±Nvba¼g¦¨¸Õ»~ªk ¤]´N¬Oµ¹CÄæ¿é¤J°Ñ¦Ò¶R±i¼Æ®É ±µµÛÀˬd¹ïÀ³¤§LÄæ(³Ñ¾lª÷ÃB)¤§­È¬O§_¤p©ó0 ­Y¤p©ó0®É ¶R±i´N´î1±i ¨Ã­«·s¿é¤J¼Æ­È
³o¼Ë´N¤£¥ÎºÞ¤âÄò¶O §éÅý µ|²vµ¥¤§°ÝÃD ´N¦p¦P§Ú¥Î¤H¤u³v¤@¬Ýºñ¦â¨Ó¿é¤J ·í¿é¤J®æÅܦ¨¬õ¦â®É´N­«·s¿é¤J(¤Ö1±i)
ÁÂÁ±zªº¥Î¤ß»PÀ°§U

TOP

¦^´_ 9# lcctno
  1. Sub TEST()
  2. Dim xR As Range, SS, ST, SU, SX
  3. With Range([E12], Cells(Rows.Count, "E").End(xlUp)(2))
  4. ¡@¡@.Offset(0, -2).ClearContents
  5. For Each xR In .Cells
  6. ¡@¡@If xR(1, 8) <= 0 Then GoTo 101
  7. ¡@
  8. ¡@¡@SU = Int(xR(1, 8) / xR / 1000) '¹w­p¥iÁʶR±i¼Æ
  9. ¡@¡@If SU <= 0 Then GoTo 101
  10. ¡@
  11. ¡@¡@SS = Round(xR * SU * 1000, 0) '¥i¶R±i¼ÆÁ`ªÑ»ù
  12. ¡@¡@ST = Application.Max(20, Int(SS * [H10]))  '­pºâ¤âÄò¶O, ³Ì§C20
  13. ¡@¡@SX = Int(ST * [E10])  '°h¦þ=¤âÄò¶O*°h¦þ²v
  14. ¡@
  15. ¡@¡@If SS + ST - SX > xR(1, 8) Then SU = SU - 1 '(Á`ªÑ»ù+¤âÄò¶O-°h¦þ)¶W¹L®É, ¤Ö¶R¤@±i
  16. ¡@¡@If SU > 0 Then xR(1, -1) = SU:  xR(1, -1).Select
  17. 101: Next
  18. End With
  19. End Sub
½Æ»s¥N½X
©Ò¿×ªº¡eµ|¡D¶O¡f¡A¶R¤J®É¥u¬O¡e¤âÄò¶O¡f¡AµLêµ{¦¡­pºâ¡A³o¤£¯à¬Ù²¤
°h¦þ¡×¤âÄò¶O*°h¦þ²v
¥t¡D­pºâ¨ç¼Æ int, round ¦³®É·|®t¤@¤¸¡A½Ð¦Û¦æ¿ï¾Ü¦óºØ¤è¦¡¡I
¡@
ªþ¥ó¡G
ªÑ²¼°t¸mV3-1.rar (207.24 KB)

TOP

¦^´_ 7# ­ã´£³¡ªL
    ST = Application.Max(20, Int(SS * [H10]))  '­pºâµ|¶O, ³Ì§C20
¨ä¹ê¤£»Ý­n¦³­pºâµ|¶O ¦]¬°¶R¬O¤£¥Îúµ| ¦ý­n¦Ò¼{ JÄæ[¨é°Ó§éÅý(°h´Ú)] ¦]¬°¶R»P½æ³£·|¦³°h´Ú
¦ý§Ú±N­pºâµ|¶O²¾°£ ´N¦³¤Ö¼Æ¶R¶W¤§²{¶H
¥Ø«e¥H¥[¤J¨é°Ó§éÅý(°h´Ú)¤§¨ç¼Æ¤½¦¡(©|¥¼§¹¥þ½T©w¤½¦¡ªº¥i¾a©Ê) Áٽбz±N´N¨Ó´ú¸Õ



ªÑ²¼°t¸mV3.zip (287.42 KB)

TOP

¦^´_ 7# ­ã´£³¡ªL
¦^³ø°ª¤â
¥Ø«e°õ¦æ°_¨Ó«Ü¥¿½T ·PÁ±zªºÀ°§U
¦ý½Ð°Ý ­YJÄæ[¨é°Ó§éÅý(°h´Ú)] ¦³¥[¤J¨ç¼Æ¤½¦¡®É(¤]´N¬O¦³°h´Ú­È®É) ¬O§_¤´¯à¥¿±`¨Ï¥Î
¦]¬°¥Ø«eÁÙ¦b¬ã¨s¸Ó¨ç¼Æ¤½¦¡ ¬GÁÙ¨S©ñ¶i¥h¨ç¼Æ¤½¦¡

TOP

¦^´_ 5# lcctno


¦³®É¤£¬O·Qªº¨º»ò²³æ, ¦³¨Ç¼h­±ÁÙ¬O­n¦Ò¼{ªº¡G
¨Ò¦p¡G¹w¦ô¥i¶R¢²±i¡A¦ý¥[¤J¡eµ|¡D¶O¡f«á¡A´N·|§e­Ó¦ì¼Æªº­t¼Æ¡A³o®É´N¥u¯à¤Ö¶R¤@±i¡A
¡@¡@¡@¥[¤J¡eµ|¡D¶O¡f¤~¬O­«ÂI¡ã¡ã
¥t¡D
¢Ü¢°¢±¤½¦¡¡G=-(I12+J12)+N(N11)¡@¡÷¡@N(??)¡A¹J¤å¦r¬°¢¯¡A¹J¼Æ­È«h¬°¸Ó¼Æ­È¡A¨ä¥¦Ãþ±À
  1. Sub TEST()
  2. Dim xR As Range, SS, ST, SU
  3. With Range([E12], Cells(Rows.Count, "E").End(xlUp)(2))
  4. ¡@¡@.Offset(0, -2).ClearContents
  5. For Each xR In .Cells
  6. ¡@¡@If xR(1, 8) <= 0 Then GoTo 101
  7. ¡@
  8. ¡@¡@SU = Int(xR(1, 8) / xR / 1000) '¹w­p¥iÁʶR±i¼Æ
  9. ¡@¡@If SU <= 0 Then GoTo 101
  10. ¡@
  11. ¡@¡@SS = Round(xR * SU * 1000, 0) '¥i¶R±i¼ÆÁ`ªÑ»ù
  12. ¡@¡@ST = Application.Max(20, Int(SS * [H10]))  '­pºâµ|¶O, ³Ì§C20
  13. ¡@
  14. ¡@¡@If SS + ST > xR(1, 8) Then SU = SU - 1 '(Á`ªÑ»ù+µ|¶O)¶W¹L®É, ¤Ö¶R¤@±i
  15. ¡@¡@If SU > 0 Then xR(1, -1) = SU:  xR(1, -1).Select
  16. 101: Next
  17. End With
  18. End Sub
½Æ»s¥N½X

TOP

¦^´_ 4# GBKEE

'=IF(N11="²Ö­p§ë¸êª÷ÃB",-(I12+J12),-(I12+J12)+N11)
³o¬O¬°¤FÅý¥þ³¡NÄ檺¤½¦¡¯à¥þ³¡¬Û¦P
ÁÂÁ±zªº¥Î¤ß ¬Ý¨Ó¤w¸g«Ü±µªñ§Ú©Ò­nªº ¦ý¦³¤Ö¼Æ´X¤ñ¶R³æ¶W¤ä¤F
¦pªþ¥ó(CÄ椺¤§³¡¤À®æ¬°¬õ©³¦â) Áٽбz¯àÄ~Äò­×¥¿¥¦ ÁÂÁÂ



ªÑ²¼°t¸mV2.zip (247.86 KB)

ªÑ²¼°t¸mV2.jpg (270.91 KB)

ªÑ²¼°t¸mV2.jpg

TOP

¦^´_ 3# ­ã´£³¡ªL
1.¤U³æªº®É¾÷
¨ä¹ê¥i¥H¤£­n²z·| ¤@¤j°ï¤º§t ¥u­n¿é¤J¶R±i¼Æ(CÄæ) ¦ý³Ñ¾lª÷ÃB(LÄæ)¤£¯à¬°­t­È ¤]´N¬O¤£¯àÅýCÄ椧®æÅܬõ¦â ·í¶R¶W¹L¥i§ë¸êª÷ÃB®É·|Åܬõ¦â

2.(C12+D12)>=0.001 ¨º¬O¹sªÑ ¤£¨¬¤@±iªÑ²¼ ¨ä¹ê±z¥i¥H¤£­n²z¨º¨Ç ¥u­nÅU¨ì(CÄæ)»P(LÄæ)¤§Ãö«Y´N¥i

3. k12 ¬O ÃÒ©Òµ|µ|²vÃþ§O 1 , 2 , 3 (¥Ø«e¥Î¤£¨ì)
ÃÒ©Òµ|µ|²vÃþ§O 0 (½æ¥X«e¤@¤éªº¥xªÑ¦¬½L«ü¼Æ8499.99ÂI©Î8499.99ÂI¥H¤U)
ÃÒ©Òµ|µ|²vÃþ§O 1 (½æ¥X«e¤@¤éªº¥xªÑ¦¬½L«ü¼Æ8500ÂI-9499ÂI)
ÃÒ©Òµ|µ|²vÃþ§O 2 (½æ¥X«e¤@¤éªº¥xªÑ¦¬½L«ü¼Æ9500ÂI-10499ÂI)
ÃÒ©Òµ|µ|²vÃþ§O 3  (½æ¥X«e¤@¤éªº¥xªÑ¦¬½L«ü¼Æ10500ÂI©Î10500ÂI¥H¤W )

ÁÂÁ±zªº¼ö¤ß §Æ±æ¥H¤W»¡©ú¯àÅý±z©ú¥Õ

TOP

        ÀR«ä¦Û¦b : ¤H¨ÆªºÁ}Ãø»PµZ¿i¡A´N¬O¤@ºØ¦ÒÅç¡C
ªð¦^¦Cªí ¤W¤@¥DÃD