EXCEL中将数字转换为人民币大写

时间:2008/2/1 11:12:11      阅读:8834          
      鉴于EXCEL本身提供将数字转换为大写表示的功能根本不能正常应用在实际投标或财务应用之中,所以要自己建设,暂时找到了三种实现途径,经过测试均功能正常。

方法1,通过在EXCEL表格框(例如在“B1”单元)中直接输入以下公式:

=IF(A1<0,"金额为负无效",
(IF(OR(A1=0,A1=""),"(人民币)零元",
IF(A1<1,"(人民币)",
TEXT(INT(A1),"[dbnum2](人民币)G/通用格式")&"元"))))&
IF(
(INT(A1*10)-INT(A1)*10)=0,
IF(INT(A1*100)-INT(A1*10)*10=0,"","零"),
(TEXT(INT(A1*10)-INT(A1)*10,"[dbnum2]")&"角"))
&IF(
(INT(A1*100)-INT(A1*10)*10)=0,
"整",
TEXT((INT(A1*100)-INT(A1*10)*10),"[dbnum2]")&"分")

然后在“A1”中输入数字,就可看到效果。

方法2,通过VBA(宏)输入转换公式: 点击菜单“工具”->“宏”->“VisualBasic编辑器”,在编辑器窗口中,点击菜单“插入”->“模块”,在出现的窗口中输入以下内容:
Function daxie(ByVal Num) ' 人民币中文大写函数
Application.Volatile True
Place
= "分角元拾佰仟万拾佰仟亿拾佰仟万"
Dn
= "壹贰叁肆伍陆柒捌玖"
D1
= "整零元零零零万零零零亿零零零万"
If Num < 0 Then FuHao = "(负)"
Num
= Format(Abs(Num), "###0.00") * 100
If Num > 999999999999999# Then: daxie = "数字超出转换范围!!": Exit Function
If Num = 0 Then: daxie = "零元零分": Exit Function
NumA
= Trim(Str(Num))
NumLen
= Len(NumA)
For J = NumLen To 1 Step -1 ' 数字转换过程
temp = Val(Mid(NumA, NumLen - J + 1, 1))
If temp <> 0 Then ' 非零数字转换
NumC = NumC & Mid(Dn, temp, 1) & Mid(Place, J, 1)
Else ' 数字零的转换
If Right(NumC, 1) <> "" Then
NumC
= NumC & Mid(D1, J, 1)
Else
Select Case J ' 特殊数位转换
Case 1
NumC
= Left(NumC, Len(NumC) - 1) & Mid(D1, J, 1)
Case 3, 11
NumC
= Left(NumC, Len(NumC) - 1) & Mid(D1, J, 1) & ""
Case 7
If Mid(NumC, Len(NumC) - 1, 1) <> "亿" Then
NumC
= Left(NumC, Len(NumC) - 1) & Mid(D1, J, 1) & ""
End If
Case Else
End Select
End If
End If
Next
daxie
= "(人民币)" & FuHao & Trim(NumC)
End Function

然后切换回excel,在“A2”单元中输入数字,在“B2”单元中输入:“=DaXie(A2)”,就可看到效果。

方法3,同样是通过VBA公式,方法同上,公式如下:
Function daxie1(money As String) As String '
Dim x As String, y As String
Const zimu = ".sbqwsbqysbqwsbq" '定义位置代码
Const letter = "0123456789sbqwy.zjf" '定义汉字缩写
Const upcase = "零壹贰叁肆伍陆柒捌玖拾佰仟萬億圆整角分" '定义大写汉字
Dim temp As String
temp
= money
If InStr(temp, ".") > 0 Then temp = Left(temp, InStr(temp, ".") - 1)

If Len(temp) > 16 Then MsgBox "数目太大,无法换算!请输入一亿亿以下的数字", 64, "错误提示": Exit Function '只能转换一亿亿元以下数目的货币!

x
= Format(money, "0.00") '格式化货币
y = ""
For i = 1 To Len(x) - 3
y
= y & Mid(x, i, 1) & Mid(zimu, Len(x) - 2 - i, 1)
Next
If Right(x, 3) = ".00" Then
y
= y & "z" '***元整
Else
y
= y & Left(Right(x, 2), 1) & "j" & Right(x, 1) & "f" '*元*角*分
End If
y
= Replace(y, "0q", "0") '避免零千(如:40200肆萬零千零贰佰)
y = Replace(y, "0b", "0") '避免零百(如:41000肆萬壹千零佰)
y = Replace(y, "0s", "0") '避免零十(如:204贰佰零拾零肆)

Do While y <> Replace(y, "00", "0")
y
= Replace(y, "00", "0") '避免双零(如:1004壹仟零零肆)
Loop
y
= Replace(y, "0y", "y") '避免零億(如:210億 贰佰壹十零億)
y = Replace(y, "0w", "w") '避免零萬(如:210萬 贰佰壹十零萬)
y = IIf(Len(x) = 5 And Left(y, 1) = "1", Right(y, Len(y) - 1), y) '避免壹十(如:14壹拾肆;10壹拾)
y = IIf(Len(x) = 4, Replace(y, "0.", ""), Replace(y, "0.", ".")) '避免零元(如:20.00贰拾零圆;0.12零圆壹角贰分)

For i = 1 To 19
y
= Replace(y, Mid(letter, i, 1), Mid(upcase, i, 1)) '大写汉字
Next
daxie1
= "(人民币)" & y
End Function

切换回excel,在“A3”单元中输入数字,在“B3”单元中输入:“=DaXie1(A3)”,就可看到效果。
评论
  • Re:EXCEL中将数字转换为人民币大写  (2008/3/10 16:23:36) by 丁丁 
    &<60;我也试试,谢谢
  • Re:EXCEL中将数字转换为人民币大写  (2008/4/1 19:04:10) by 多多可 
    &<60;学习了,谢谢。试试。
  • Re:EXCEL中将数字转换为人民币大写  (2008/4/1 23:01:18) by pp 
    还是在VBA里做比较好
     
    需要的联系我邮箱
  • Re:EXCEL中将数字转换为人民币大写  (2008/5/4 16:12:28) by 小小 
    真的很感谢你,帮我解决了一个大问题
  • Re:EXCEL中将数字转换为人民币大写  (2008/8/29 10:01:34) by 刘俊 
    谢谢了,收藏!
    试过后,发现对一亿亿的钱判断不准确。
  • Re:EXCEL中将数字转换为人民币大写  (2008/11/19 16:20:54) by wq805 
    挺复杂的,收藏了,谢谢楼主共享
  • Re:EXCEL中将数字转换为人民币大写  (2010/3/21 14:26:49) by 婵″倷缍嶆稉瀣祰閻㈤潧濂?Url= 

    学习一下,收藏了

  • Re:EXCEL中将数字转换为人民币大写  (2010/11/26 17:29:50) by midsummerlgy 

    谢谢..正着这个呢

  • Re:EXCEL中将数字转换为人民币大写  (2011/5/18 21:55:12) by zhxp7910 

     挺复杂的,收藏了,谢谢楼主共享

     

    e

标 题:
 
姓 名:
 
主 页:

验证码:

评论:
 

Because of the cache,you may see your comments several minutes later.