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

[µo°Ý] ¦Û©w¨ç¼Æ-¿ï¾ÜÅvÅv§Qª÷³ø»ù³æ°ÝÃD

[µo°Ý] ¦Û©w¨ç¼Æ-¿ï¾ÜÅvÅv§Qª÷³ø»ù³æ°ÝÃD

Åv§Qª÷³ø»ù³æ¦ì       
³ø»ù¥¼º¡10ÂI¡G0.1ÂI(5¤¸)
³ø»ù10ÂI¥H¤W¡A¥¼º¡50ÂI¡G0.5ÂI(25¤¸)
³ø»ù50ÂI¥H¤W¡A¥¼º¡500ÂI¡G1ÂI(50¤¸)
³ø»ù500ÂI¥H¤W¡A¥¼º¡1,000ÂI¡G5ÂI(250¤¸)
³ø»ù1,000ÂI¥H¤W¡G10ÂI(500¤¸)

¤p§Ì°Ñ¦Ò¥H¤W³W«h¦Û¤v¹Á¸Õ§Q¥Î¦Û©w¨ç¼Æ¤è¦¡½s¼g¥X¥H¤Uªº¼gªk
¤£ª¾¹D³o¼Ë¤l¼g»Ý¤£»Ý­n¦b­×¥¿©O ¡C
¯à§_½Ð«e½ú«üÂI¤@¤U¡C
ÁÂÁ¡C
  1. Function MyOP(V As String, V1 As Double)
  2.   
  3.    If V <= 10 Then
  4.     MyOP = Round(V1, 1)
  5.     End If
  6.    
  7.     If (Len(V) = 2 Or Len(V) >= 4) Then
  8.     If V > 10 And V < 50 And Mid(V, 1, 4) >= 0 And Mid(V, 1, 4) <= 4 Then
  9.     MyOP = Round(V1, 0)
  10.     ElseIf V > 10 And V < 50 And Mid(V, Len(V), Len(V)) = 5 Then
  11.     MyOP = Mid(Int(V1), 1, 2) + 0.5
  12.     ElseIf V > 10 And V < 50 And Mid(V, Len(V), Len(V)) > 5 Then
  13.     MyOP = Round(V1, 0)
  14.     End If
  15.     End If
  16.    
  17.    
  18.    
  19.    
  20.     If V <= 100 And V1 >= 50 Then
  21.     MyOP = Mid(Int(V1), 1, 2) + 1
  22.    
  23.     ElseIf V < 500 And V1 > 100 Then
  24.     MyOP = Mid(Int(V1), 1, 3) + 1
  25.    
  26.     ElseIf V <= 1000 And V >= 500 Then
  27.     MyOP = (Mid(Round(V1, 0), 1, 2) * 10) + 5
  28.    
  29.     ElseIf V <= 10000 And V > 1000 Then
  30.     MyOP = (Mid(Round(V1, 0), 1, 3) * 10) + 10
  31.     ElseIf V > 10000 Then
  32.     MyOP = (Mid(Round(V1, 0), 1, 4) * 10) + 10
  33.    
  34.     End If
  35. End Function
½Æ»s¥N½X

¥»©«³Ì«á¥Ñ GBKEE ©ó 2016-4-4 06:25 ½s¿è

¦^´_ 1# frantz
  1. Option Explicit
  2. Sub Ex()
  3.     MsgBox "Åv§Qª÷³ø»ù³æ¦ì" & MyOP(48)
  4. End Sub
  5. '****** °}¦C¤¤ Match ****
  6. Function MyOP(V As Double) As Double
  7.     Dim AR(), ArOp(), m As Integer
  8.     AR = Array(1000, 999, 499, 49, 9, 0)
  9.     ArOp = Array(10, 5, 1, 0.5, 0.1, 0)
  10.     m = Application.Match(V, AR, -1)
  11.     MyOP = ArOp(m - 1)
  12. End Function
  13. '******Select Case ****
  14. Function MyOP(V As Double) As Double
  15.     Select Case V
  16.         Case Is <= 0
  17.             MyOP = 0
  18.         Case Is < 10            '³ø»ù¥¼º¡10ÂI¡G0.1ÂI(5¤¸)
  19.             MyOP = 0.1
  20.         Case 10 To 49        '³ø»ù10ÂI¥H¤W¡A¥¼º¡50ÂI¡G0.5ÂI(25¤¸)
  21.             MyOP = 0.5
  22.         Case 50 To 499      '³ø»ù50ÂI¥H¤W¡A¥¼º¡500ÂI¡G1ÂI(50¤¸)
  23.             MyOP = 1
  24.         Case 500 To 999     '³ø»ù500ÂI¥H¤W¡A¥¼º¡1,000ÂI¡G5ÂI(250¤¸)
  25.             MyOP = 5
  26.         Case Is > 999    '³ø»ù1,000ÂI¥H¤W¡G10ÂI(500¤¸)
  27.             MyOP = 10
  28.     End Select
  29. End Function
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2016-4-4 12:59 ½s¿è

Function MyOP(V As String, V1 As Double)
V¬O¼Æ­È, ¦ó¥H©w¸q¬°[¤å¦r]«¬ºA, ­YµLªk½T©w¨ä«¬ºA, ¥i¤£©w¸q, ¨ä¬°³æ¤@¤¸¯À, ¼vÅTÀ³¤£¤j!
¥t¥Î MID ¥h¨ú¼Æ, ´N¦³ÂIµe³D²K¨¬, ¤Æ²¬°Ác, ª½±µ¥Î[±`¼Æ]¤£¬O¸û¦n? ©ÎªÌ¦³¨ä¥¦²z¥Ñ¡H
¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×
°£¤F¶Wª©ªº¤èªk, ¤@¯ë¥Î IF ªº¤èªk:( ª`·N:§PÂ_¬O¥Ñ¤j¦Ó¤p, »P¤½¦¡ªº IF ³W«h¬Û¦P)
Function MyOP_1(V)
If V >= 1000 Then
¡@¡@MyOP_1 = 10
ElseIf V >= 500 Then
¡@¡@MyOP_1 = 5
ElseIf V >= 50 Then
¡@¡@MyOP_1 = 1
ElseIf V >= 10 Then
¡@¡@MyOP_1 = 0.5
ElseIf V > 0 Then
¡@¡@MyOP_1 = 0.1
End If
End Function
¡@
IF¤½¦¡¡G=IF(A1>=1000,10,IF(A1>=500,5,IF(A1>=50,1,IF(A1>=10,0.5,IF(A1>0,0.1,0)))))
¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×
¤@¯ë¤H¤Ö¥Îªº SWITCH¡G
Function MyOP_2(V)
If V > 0 Then
¡@MyOP_2 = Switch(V >= 1000, 10, V >= 500, 5, V >= 50, 1, V >= 10, 0.5, V > 0, 0.1)
End If
End Function
¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×
³o¬O¤u§@ªí¨ç¼Æªk¡G
Function MyOP_3(V)
If V > 0 Then
¡@MyOP_3 = Application.Lookup(V, Array(0, 10, 50, 500, 1000), Array(0.1, 0.5, 1, 5, 10))
End If
End Function
¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×
¤£¥ÎVBA¡A¤@¯ë¤½¦¡¡G
=IF(A1<=0,0,LOOKUP(A1,{0,10,50,500,1000},{0.1,0.5,1,5,10}))
=IF(A1<=0,0,MAX((A1>={0,10,50,500,1000})*{0.1,0.5,1,5,10}))

TOP

¦^´_ 2# GBKEE


    ÁÂÁ¶Wª©
«Ý·|§Ú¦^¥hTRY
ÁA¸Ñ·í¤¤ªº¤£¦P³B

TOP

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


    ÁÂÁ«e½ú
·íªì·|¼g³o­Ó¬O¦]¬°¦³¥Î¤@¯ë¤½¦¡­pºâ¿ï¾ÜÅvªº¼µÀ£»ù¡C
·|¦³¦b»ù®æ¤j©ó¤­¤Q¤¸¤§«á¡A¤´¦³¤p¼Æ¦ìªº°ÝÃD¡C
¦]¦¹·Q§â³o¨Ç¤j©ó¤­¤Q¤¸¤§¤Wªº¼Æ¦r¥i¥H¥|±Ë¤­¤J¶i¦ì¡C
¥Î¶i¦ì¤è¦¡¥h§e²{²Å¦X³W«hªº¼Æ¦r¡C
«Ý·|¦^¥h§Ú¤@­Ó­ÓTRY¬Ý¬Ý¤£¦P³B¡C

TOP

¦^´_ 2# GBKEE
¦^´_ 3# ­ã´£³¡ªL

ÁÂÁ¨â¦ì«e½ú´£¨Ñªº¼gªk
­è­è´ú¸Õ«á¡Aµo²{¼gªk§e²{¥X¨Ó®ÄªG³£¬O¤@¼Ë¡C
¦p§Ú«ö48.1 ©ÎªÌ48.5¡B48.6µ¥±¡§Î®É¡A
¥X¨Ó³£¬O0.5
¦ý¦pªG­n¥|±Ë¤­¤J¶i¦ì§PÂ_ªº¸Ü¡C
¯à¤£¯à¼g¦¨¦p¤U©O?
  1. Function MyOP(V)
  2. MyOP = Switch(V >= 1000, 10, V >= 500, 5, V >= 50, 1, V >= 10, 0.5, V > 0, 0.1)

  3. If (V > 0 And V <= 10) Then
  4.   MyOP = V
  5. End If
  6.   
  7. If V > 10 Then
  8.     If Round(V, 2) - Int(V) = 0 Then
  9.     MyOP = Int(V)
  10.     ElseIf (Round(V, 4) - Int(V) >= 0.001 And Round(V, 4) - Int(V) <= 0.5) Then
  11.     MyOP = Int(V) + MyOP
  12.     Else
  13.     MyOP = Round(V, 0)
  14.     End If


  15. End If
  16. End Function
½Æ»s¥N½X

TOP

­è­è¤p§Ì¦Û¤v¦b­«·s«ä¦Ò¼gªk¦p¤U
°t¦X³W«h¶i¦æÂI¼Æ­pºâ®É¡A¯à§ó¶K¶i³W«h¡C
10ÂI¥H¤U¸õ¼Æ®É¡A¡y¨C¸õ°Ê¡z¬O0.1ÂI­pºâ(­pºâ¥X¨ÓÀ³¸Ó¬O9.5¡B9.6¡B9.7)
10ÂI¥H¤W¥¼º¡50ÂI¡A¡y¨C¸õ°Ê¡z¬O0.5ÂI­pºâ(­pºâ¥X¨ÓÀ³¸Ó¬O10¡B10.5¡B11)
50ÂI¥H¤W¥¼º¡500ÂI¡A¡y¨C¸õ°Ê¡z¬O1ÂI­pºâ(­pºâ¥X¨ÓÀ³¸Ó¬O51¡B52¡B53)
500ÂI¥H¤W¥¼º¡1000ÂI¡A¡y¨C¸õ°Ê¡z¬O5ÂI­pºâ(­pºâ¥X¨ÓÀ³¸Ó¬O500¡B505¡B510)
1000ÂI¥H¤W®É¡A¡y¨C¸õ°Ê¡z¬O10ÂI­pºâ(­pºâ¥X¨ÓÀ³¸Ó¬O1000¡B1020¡B1030)
  1. Function MyOP(V As Double) As Double
  2. Dim t, a
  3. t = Round(V, 4) - Int(V)
  4.     Select Case V
  5.         Case Is <= 0
  6.             a = 0
  7.         Case Is < 10            '³ø»ù¥¼º¡10ÂI¡G0.1ÂI(5¤¸)
  8.             a = 0.1
  9.         Case 10 To 49        '³ø»ù10ÂI¥H¤W¡A¥¼º¡50ÂI¡G0.5ÂI(25¤¸)
  10.             a = 0.5
  11.         Case 50 To 499      '³ø»ù50ÂI¥H¤W¡A¥¼º¡500ÂI¡G1ÂI(50¤¸)
  12.             a = 1
  13.         Case 500 To 999     '³ø»ù500ÂI¥H¤W¡A¥¼º¡1,000ÂI¡G5ÂI(250¤¸)
  14.             a = 5
  15.         Case Is > 999    '³ø»ù1,000ÂI¥H¤W¡G10ÂI(500¤¸)
  16.             a = 10
  17.     End Select


  18. If (V >= 0 And V < 10) Then
  19.     Select Case t
  20.     Case Is <= 0
  21.         MyOP = 0.1
  22.     Case Is < 10
  23.         MyOP = V
  24.     End Select
  25. ElseIf (V >= 10 And V < 50) Then
  26.     Select Case t
  27.     Case Is <= 0
  28.         MyOP = Int(V)
  29.     Case 0.0001 To 0.5
  30.         MyOP = Int(V) + a
  31.     Case Is > 0.5
  32.        MyOP = Round(V, 0)
  33.     End Select
  34. ElseIf (V >= 50 And V < 500) Then
  35.     Select Case t
  36.     Case Is <= 0
  37.         MyOP = Int(V)
  38.     Case 0.0001 To 0.4999
  39.         MyOP = Int(V)
  40.     Case Is = 0.5
  41.        MyOP = Round(V, 0) + a
  42.     Case Is > 0.5
  43.        MyOP = Round(V, 0)
  44.     End Select
  45. ElseIf (V >= 500 And V < 1000) Then
  46.     Select Case t
  47.     Case Is <= 0
  48.         MyOP = WorksheetFunction.RoundDown(V, -1)
  49.     Case 0.0001 To 0.4999
  50.         MyOP = WorksheetFunction.RoundDown(V, -1) + a
  51.     Case Is = 0.5
  52.        MyOP = WorksheetFunction.RoundDown(V, -1) + a
  53.     Case Is > 0.5
  54.        MyOP = WorksheetFunction.RoundDown(V, -1) + a
  55.     End Select
  56. ElseIf V >= 1000 Then
  57.     Select Case t
  58.     Case Is <= 0
  59.         MyOP = WorksheetFunction.RoundDown(V, -1)
  60.     Case 0.0001 To 0.4999
  61.         MyOP = WorksheetFunction.RoundDown(V, -1)
  62.     Case Is = 0.5
  63.        MyOP = WorksheetFunction.RoundDown(V, -1) + a
  64.     Case Is > 0.5
  65.        MyOP = WorksheetFunction.RoundDown(V, -1) + a
  66.    
  67.     End Select
  68. End If


  69. End Function
½Æ»s¥N½X

TOP

        ÀR«ä¦Û¦b : ¥Ç¿ù¥XÄb®¬¤ß¡A¤~¯à²M²bµL·Ð´o¡C
ªð¦^¦Cªí ¤W¤@¥DÃD