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

[µo°Ý] ¦p¦ó¨Ï¥Î¥¨¶°¦Û°Ê­pºâªÑ»ùº¦¶^°±»ù?

[µo°Ý] ¦p¦ó¨Ï¥Î¥¨¶°¦Û°Ê­pºâªÑ»ùº¦¶^°±»ù?

¥»©«³Ì«á¥Ñ shuasa ©ó 2016-4-24 20:39 ½s¿è

½Ð°Ý­n¦p¦ó¥Î¨Ï¥¨¶°¦Û°Ê­pºâªÑ»ù²¼º¦¶^°±»ù?
1.jpg

ªÑ²¼¸ê®Æ.rar (19.11 KB)

shuasa

¤j¤jÀ³¸Ó¥ýÁ|¨Ò»¡¤@¤U
­þ¤@¤éªº­È­pºâ¦³¿ù»~
¦Ó¥¿½Tªº­ÈÀ³¸Ó¤S¬O¦h¤Ö

TOP

¦^´_ 2# jackyq


    §Ú­ì¥»¬O¥ÎÀx¦s®æ¥Î¨ç¼Æ¤½¦¡®M¥Î,¦p4/6¤éªºº¦°±»ù¬O4/1¤éªº¦¬½L»ù®M¤W¤½¦¡
   M14Àx¦s®æªº¤½¦¡¬O =IF(F15="","",FLOOR(F15*1.1,LOOKUP(F15*1.1,{0,10,50,100,500,1000},{0.01,0.05,0.1,0.5,1,5})))

º¦°±»ù¤½¦¡
=FLOOR(A1*1.1,LOOKUP(A1*1.1,{0,10,50,100,500,1000},{0.01,0.05,0.1,0.5,1,5}))
¶^°±»ù¤½¦¡
=CEILING(A1*0.9,LOOKUP(A1*0.9,{0,10,50,100,500,1000},{0.01,0.05,0.1,0.5,1,5}))

¦ý¬O¦p¦ó¥Î¥¨¶°ªº¤è¦¡§e²{?
2.jpg
shuasa

TOP

¤j¤j§Ú·Ó§A¤½¦¡Â½Ä¶¦Ó¤w

Public Function º¦¶^°±»ù(¬Q¦¬,  º¦or¶^ As Long)

º¦¶^°±»ù = ""
If ¬Q¦¬ = "" Then Exit Function

   Price_Sample = Array(0, 10, 50, 100, 500, 1000)
interval_Sample = Array(0.01, 0.05, 0.1, 0.5, 1, 5)

interval = Application.WorksheetFunction.Lookup(¬Q¦¬, Price_Sample, interval_Sample)

If º¦or¶^ > 0 Then º¦¶^°±»ù = Application.WorksheetFunction.Floor(¬Q¦¬ * 1.1, interval)
If º¦or¶^ < 0 Then º¦¶^°±»ù = Application.WorksheetFunction.Ceiling(¬Q¦¬ * 0.9, interval)

End Function

TOP

¦^´_ 4# jackyq


    ·PÁÂjackyq¤j¤j«ü±Ð,Åý§Ú¤S¦h¾Ç¨ìª¾ÃÑ:)
shuasa

TOP

¦^´_ 3# shuasa
À³¥Î jackyq  ¤j¤jªº¨ç¦¡µy¥[­×§ï¦p¤U¡A
°Ñ¦Ò¤@¤U¡G
  1. Public Function UpDown(ud As Range, Optional UpDn As Boolean = True)
  2.     If ud.Value = 0 Then UpDown = 0: Exit Function
  3.       
  4.     Price_Sample = Array(0, 10, 50, 100, 500, 1000)
  5.     interval_Sample = Array(0.01, 0.05, 0.1, 0.5, 1, 5)
  6.          
  7.     interval = Application.WorksheetFunction.Lookup(ud.Value * IIf(UpDn, 1.1, 0.9), Price_Sample, interval_Sample)
  8.     UpDown = Application.WorksheetFunction.Floor(ud.Value * IIf(UpDn, 1.1, 0.9), interval)
  9. End Function
½Æ»s¥N½X
º¦°±»ù¨ç¦¡
=UpDown(F8)
µ¥©ó
=UpDown(F8,1)
µ¥©ó
=UpDown(F8,True)

¶^°±»ù¨ç¦¡
=UpDown(F8,0)
µ¥©ó
=UpDown(F8,False)
E.png
2016-4-25 08:09

TOP

¦^´_ 3# shuasa
¥ç¥iª½±µ¥H VBA µ{¦¡¬q¨Ó³B²z¡G
  1. Sub Ex()
  2.     Dim xVal As Double
  3.    
  4.     xVal = UpDown([F8])
  5.     MsgBox "UpDown([F8]) (º¦°±»ù) = " & xVal
  6.     xVal = UpDown([F8], 1)
  7.     MsgBox "UpDown([F8], 1) (º¦°±»ù) = " & xVal
  8.     xVal = UpDown([F8], True)
  9.     MsgBox "UpDown([F8], True) (º¦°±»ù) = " & xVal
  10.    
  11.     xVal = UpDown([F8], 0)
  12.     MsgBox "UpDown([F8], 0) (¶^°±»ù) = " & xVal
  13.     xVal = UpDown([F8], False)
  14.     MsgBox "UpDown([F8], False) (¶^°±»ù) = " & xVal
  15. End Sub
½Æ»s¥N½X
E.png
2016-4-25 08:26

TOP

¥»©«³Ì«á¥Ñ jackyq ©ó 2016-4-25 09:15 ½s¿è

¦^´_ 5# shuasa

§Ú¦³²z¸Ñ¿ù¼Ú     

À³¸Ó³o¼Ë¤~¹ï

Public Function º¦¶^°±»ù(¬Q¦¬,  º¦or¶^ As Long)

º¦¶^°±»ù = ""
If ¬Q¦¬ = "" Then Exit Function
if º¦or¶^ = 0 Then Exit Function

   Price_Sample = Array(0, 10, 50, 100, 500, 1000)
interval_Sample = Array(0.01, 0.05, 0.1, 0.5, 1, 5)

º¦¶^°±»ù = ¬Q¦¬ * ( 1 + sgn( º¦or¶^ )   )  * 0.1
interval = Application.WorksheetFunction.Lookup(º¦¶^°±»ù, Price_Sample, interval_Sample)

If º¦or¶^ > 0 Then º¦¶^°±»ù = Application.WorksheetFunction.Floor(º¦¶^°±»ù , interval)
If º¦or¶^ < 0 Then º¦¶^°±»ù = Application.WorksheetFunction.Ceiling(º¦¶^°±»ù , interval)

End Function

TOP

¶^°±¤@©w­n¥Î Ceiling ¤£¯à¥Î floor

TOP

¦^´_ 8# jackyq

«õ­ù°Ç !!!!  0.1 ©ñ¿ù¦ì¸m

¦A§ó¥¿¤@¦¸

Public Function º¦¶^°±»ù(¬Q¦¬,  º¦or¶^ As Long)

º¦¶^°±»ù = ""
If ¬Q¦¬ = "" Then Exit Function
if º¦or¶^ = 0 Then Exit Function

   Price_Sample = Array(0, 10, 50, 100, 500, 1000)
interval_Sample = Array(0.01, 0.05, 0.1, 0.5, 1, 5)

º¦¶^°±»ù = ¬Q¦¬ * ( 1 + sgn( º¦or¶^ )  * 0.1  )  
interval = Application.WorksheetFunction.Lookup(º¦¶^°±»ù, Price_Sample, interval_Sample)

If º¦or¶^ > 0 Then º¦¶^°±»ù = Application.WorksheetFunction.Floor(º¦¶^°±»ù , interval)
If º¦or¶^ < 0 Then º¦¶^°±»ù = Application.WorksheetFunction.Ceiling(º¦¶^°±»ù , interval)

End Function

TOP

        ÀR«ä¦Û¦b : ¯à·F¤£·F¡A¤£¦p­W·F¹ê·F¡C
ªð¦^¦Cªí ¤W¤@¥DÃD