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

excel ±N¼Æ¦rÂà­^¤å¤j¼g

excel ±N¼Æ¦rÂà­^¤å¤j¼g

§Ú¦bºô¤W¨ì³o­Ó¤èªk http://support.microsoft.com/kb/q213360/
¦ýÂର¤j¼gªº­^¤å¤º·|¦³dollars³o­Ó¦r, §Ú¬O¥Î¦b¦C¦L¤ä²¼¤W, ©Ò¥H¤£»Ý­n¦³"dollar"

example : Six Hundred Eighty Five Dollars and Thirty Three Cents

¦U¤j°ª¤â¦³¨S¦³¤èªk¦bexcel ±N¼Æ¦rÂà­^¤å¤j¼g ¦ý¨S¦³ "dollar"¦r©O ??/

Thanks.

¦^´_ 1# seci


    ¨ä¹ê¥u­n§â¥H¤U¦æ¼Æ¡A§R°£´N¦æ¤F
' Select Case Dollars
'        Case ""
'            Dollars = "No Dollars"
'        Case "One"
'            Dollars = "One Dollar"
'         Case Else
'            Dollars = Dollars & " Dollars"
'    End Select
'    Select Case Cents
'        Case ""
'            Cents = " and No Cents"
'        Case "One"
'            Cents = " and One Cent"
'              Case Else
'            Cents = " and " & Cents & " Cents"
'    End Select

TOP

§Ú§â¨º¤@¦ê§R±¼«áÅܦ¨¤U­±ªº¼Ë¤l(513.51---¡÷ Five Hundred ThirteenFifty One)¡A¦ýAnd¤£¨£¤F¡AÁÙ¦³ThirteenÀ³¸Ó¬OªÅ¤@®æ¦A±µFifty¡A¥i²{¦b³s¦b¤@°_¤F¡C
¦pªGAnd«á­±·Q¥[Cents­n«ç»ò°µ©O¡HÁÂÁ±z¡I
§Ú·Q­nªº¬OFive Hundred Thirteen And Cents Fifty One

TOP

¥»©«³Ì«á¥Ñ yen956 ©ó 2014-5-24 13:55 ½s¿è

¦^´_ 3# C9212038
¸Õ¸Õ¬Ý:
  1. Select Case Dollars
  2.     Case ""
  3. '        Dollars = "No Dollars"
  4.         Dollars = ""
  5.     Case "One"
  6. '        Dollars = "One Dollar"
  7.         Dollars = "One"
  8.     Case Else
  9. '        Dollars = Dollars & " Dollars"
  10.         Dollars = Dollars & ""
  11. End Select

  12. Select Case Cents
  13.     Case ""
  14. '        Cents = " and No Cents"
  15.         Cents = ""
  16.     Case "One"
  17.         'Five Hundred Thirteen And Cents Fifty One
  18.         '¦pªG§Æ±æ¦p¤W Cents ©ñ«e­±,
  19.         '¨º´N¤U¦æ§ï¦¨ Cents = " and Cent One"
  20.         Cents = " and One Cent"
  21.     Case Else
  22.         '¦pªG§Æ±æ Cents ©ñ«e­±,
  23.         '¨º´N¤U¦æ§ï¦¨ Cents = " and Cents " & Cents
  24.         Cents = " and " & Cents & " Cents"
  25. End Select
½Æ»s¥N½X
»¡©ú¡G«e­±¥[ ' ¨º¤@¦C¬Oµù¸Ñ, ¤£·|³QVBA°õ¦æ

TOP

µ{¦¡½X¤£¶·§ï¡A¦b¤½¦¡¤WµÛ¤â¡G
=SUBSTITUTE(¦Û­q¨ç¼Æ,"Dollars ","")

TOP

¡e©w¸q¦WºÙ+¤½¦¡¡f¼Æ¦rÂà­^¤å¤j¼g¡G
http://www.funp.net/246430

­Y¦³¿³½ì¡A¥i¦Û¦æ®M§ï¬Ý¬Ý¡ã¡ã

TOP

¦^´_ 1# seci
  1. Option Explicit
  2. 'Main Function
  3. Function SpellNumber(ByVal MyNumber)
  4. Dim Dollars, Cents, Temp
  5. Dim DecimalPlace, Count
  6. ReDim Place(9) As String
  7. Place(2) = " Thousand "
  8. Place(3) = " Million "
  9. Place(4) = " Billion "
  10. Place(5) = " Trillion "
  11. ' ¥Nªí¼Æ¦rªº¦r¦ê¡C
  12. MyNumber = Trim(Str(MyNumber))
  13. ' ¦pªG­È¬°µL¡A«h¤Q¶i¦ì 0 ªº¦ì¸m¡C
  14. DecimalPlace = InStr(MyNumber, ".")
  15. ' ´«ºâ¤ÀµM«á±N MyNumber ªº¼Æ¦r³]¬°¤¸¡C
  16. If DecimalPlace > 0 Then
  17. Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
  18.                   "00", 2))
  19. MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
  20. End If
  21. Count = 1
  22. Do While MyNumber <> ""
  23. Temp = GetHundreds(Right(MyNumber, 3))
  24. If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
  25. If Len(MyNumber) > 3 Then
  26. MyNumber = Left(MyNumber, Len(MyNumber) - 3)
  27. Else
  28. MyNumber = ""
  29. End If
  30. Count = Count + 1
  31. Loop
  32. Select Case Dollars
  33. Case ""
  34. Dollars = "No Dollars"
  35. Case "One"
  36. 'Dollars = "One Dollar"
  37. Dollars = "One"
  38. Case Else
  39. 'Dollars = Dollars & " Dollars"
  40. Dollars = Dollars
  41. End Select
  42. Select Case Cents
  43. Case ""
  44. Cents = " and No Cents"
  45. Case "One"
  46. Cents = " and One Cent"
  47. Case Else
  48. Cents = " and " & Cents & " Cents"
  49. End Select
  50. SpellNumber = Dollars & Cents
  51. End Function
  52.       
  53. ' ±N¼Æ¦r 100-999 Âà´«¬°¤å¦r
  54. Function GetHundreds(ByVal MyNumber)
  55. Dim Result As String
  56. If Val(MyNumber) = 0 Then Exit Function
  57. MyNumber = Right("000" & MyNumber, 3)
  58. ' ´«ºâ¦Ê¶i¦ìªº¡C
  59. If Mid(MyNumber, 1, 1) <> "0" Then
  60. Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
  61. End If
  62. ' ´«ºâ¤Q¶i¦ì©M¤@¶i¦ì¡C
  63. If Mid(MyNumber, 2, 1) <> "0" Then
  64. Result = Result & GetTens(Mid(MyNumber, 2))
  65. Else
  66. Result = Result & GetDigit(Mid(MyNumber, 3))
  67. End If
  68. GetHundreds = Result
  69. End Function
  70.       
  71. ' ±N¼Æ¦r 10 ¨ì 99 Âà´«¬°¤å¦r¡C
  72. Function GetTens(TensText)
  73. Dim Result As String
  74. Result = ""           ' Null out the temporary function value.
  75. If Val(Left(TensText, 1)) = 1 Then   ' If value between 10-19...
  76. Select Case Val(TensText)
  77. Case 10: Result = "Ten"
  78. Case 11: Result = "Eleven"
  79. Case 12: Result = "Twelve"
  80. Case 13: Result = "Thirteen"
  81. Case 14: Result = "Fourteen"
  82. Case 15: Result = "Fifteen"
  83. Case 16: Result = "Sixteen"
  84. Case 17: Result = "Seventeen"
  85. Case 18: Result = "Eighteen"
  86. Case 19: Result = "Nineteen"
  87. Case Else
  88. End Select
  89. Else                                 ' ¦pªG­È¤¶©ó 20-99...
  90. Select Case Val(Left(TensText, 1))
  91. Case 2: Result = "Twenty "
  92. Case 3: Result = "Thirty "
  93. Case 4: Result = "Forty "
  94. Case 5: Result = "Fifty "
  95. Case 6: Result = "Sixty "
  96. Case 7: Result = "Seventy "
  97. Case 8: Result = "Eighty "
  98. Case 9: Result = "Ninety "
  99. Case Else
  100. End Select
  101. Result = Result & GetDigit _
  102. (Right(TensText, 1))  ' Retrieve ones place.
  103. End If
  104. GetTens = Result
  105. End Function
  106.      
  107. ' ±N¼Æ¦r 1 ¨ì 9 Âà´«¬°¤å¦r¡C
  108. Function GetDigit(Digit)
  109. Select Case Val(Digit)
  110. Case 1: GetDigit = "One"
  111. Case 2: GetDigit = "Two"
  112. Case 3: GetDigit = "Three"
  113. Case 4: GetDigit = "Four"
  114. Case 5: GetDigit = "Five"
  115. Case 6: GetDigit = "Six"
  116. Case 7: GetDigit = "Seven"
  117. Case 8: GetDigit = "Eight"
  118. Case 9: GetDigit = "Nine"
  119. Case Else: GetDigit = ""
  120. End Select
  121. End Function
½Æ»s¥N½X
§ï±¼¨â­Ó¦a¤è´N¦n
Case "One"
'Dollars = "One Dollar" ­ì¥»ªº
Dollars = "One"
Case Else
'Dollars = Dollars & " Dollars" ­ì¥»ªº
Dollars = Dollars
50 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

        ÀR«ä¦Û¦b : ¤£­n¤p¬Ý¦Û¤v¡A¦]¬°¤H¦³µL­­ªº¥i¯à¡C
ªð¦^¦Cªí ¤W¤@¥DÃD