(EXCEL宏)小写金额转换为中文大写金额
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
|