郭振清孙子郭晓小:在EXECL中实现数字转大写金额

在EXECL中实现数字转大写金额

IF(ISNUMBER(Sheet1!B7),IF(INT(Sheet1!B7),TEXT(INT(Sheet1!B7),"[dbnum2]")&"元",)&IF(INT

(Sheet1!B7*10)-INT(Sheet1!B7)*10,TEXT(INT(Sheet1!B7*10)-INT(Sheet1!B7)*10,"[dbnum2]")

&"角",IF(INT(Sheet1!B7)=Sheet1!B7,,IF(Sheet1!B7<0.1,,"零")))&IF(ROUND((Sheet1!B7)*100-INT

(Sheet1!B7*10)*10,),TEXT(ROUND(Sheet1!B7*100-INT(Sheet1!B7*10)*10,),"[dbnum2]")&"分",""))

=IF((INT(A2*10)-INT(A2)*10)=0,TEXT(INT(A2),"[DBNum2]G/通用格式")&"元"&IF((INT(A2*100)-INT

((A2)*10)*10)=0,"整","零"&TEXT(INT(A2*100)-INT(A2*10)*10,"[DBNum2]G/通用格式")&"分"),TEXT

(INT(A2),"[DBNum2]G/通用格式")&"元"&IF((INT(A2*100)-INT((A2)*10)*10)=0,TEXT((INT(A2*10)-INT

(A2)*10),"[DBNum2]G/通用格式")&"角整",TEXT((INT(A2*10)-INT(A2)*10),"[DBNum2]G/通用格式")&"角

"&TEXT(INT(A2*100)-INT(A2*10)*10,"[DBNum2]G/通用格式")&"分"))

=IF(D20<1,IF(D20<0.1,TEXT(INT(D20*100),"[DBNum2]G/通用格式")&"分",IF((INT(D20*100)-INT

(D20*10)*10=0),TEXT(INT(D20*10),"[DBNum2]G/通用格式")&"角整",TEXT(INT(D20*10),"[DBNum2]G/通

用格式")&"角"&TEXT(INT(D20*100)-INT(D20*10)*10,"[DBNum2]G/通用格式")&"分")),TEXT(INT(D20),"(

人民币)[DBNum2]G/通用格式"&"圆")&IF((INT(D20*10)-INT(D20)*10)=0,IF((INT(D20*100)-INT

(D20*10)*10)=0,"","零"),IF((INT(D20*0.1)-INT(D20)*0.1)=0,"零","")&TEXT(INT(D20*10)-INT(D20)

*10,"[DBNum2]G/通用格式")&"角")&IF((INT(D20*100)-INT(D20*10)*10)=0,"整",TEXT(INT(D20*100)-

INT(D20*10)*10,"[DBNum2]G/通用格式")&"分"))

=IF(ROUND(G8,2)<0,"无效数值",IF(ROUND(G8,2)=0,"零",IF(ROUND(G8,2)<1,"",TEXT(INT(ROUND

(G8,2)),"[dbnum2]")&"元")&IF(INT(ROUND(G8,2)*10)-INT(ROUND(G8,2))*10=0,IF(INT(ROUND(G8,2))*

(INT(ROUND(G8,2)*100)-INT(ROUND(G8,2)*10)*10)=0,"","零"),TEXT(INT(ROUND(G8,2)*10)-INT(ROUND

(G8,2))*10,"[dbnum2]")&"角")&IF((INT(ROUND(G8,2)*100)-INT(ROUND(G8,2)*10)*10)=0,"整",TEXT

((INT(ROUND(G8,2)*100)-INT(ROUND(G8,2)*10)*10),"[dbnum2]")&"分")))

=IF(INT(B16)=0,"",TEXT(INT(B16),"[DBNum2]")&"元")&IF(OR(INT(B16*10)=0,INT(B16)=B16),"",IF

(INT(B16*10)=INT(B16)*10,"零",RIGHT(TEXT(INT(B16*10),"[DBNum2]"),1)&"角"))&IF(INT(B16*100)

=INT(B16*10)*10,"整",RIGHT(TEXT(ROUND(B16,2),"[DBNum2]"),1)&"分")

也可利用自定义函数来实现

（1）执行菜单栏中的【工具】|【宏】|【Visual Basic编辑器】命令，进入“Visual Basic编辑”窗口

。

    （2）在“Visual Basic编辑”窗口中，执行菜单栏中的【插入】|【模块】命令，插入一个模块。双

击左侧的“模块1”，在窗口右边展开“模块1（代码）”编辑窗口，输入如下代码：

Function dx(q)
ybb = Round(q * 100) '将输入的数值扩大100倍，进行四舍五入
y = Int(ybb / 100) '截取出整数部分
j = Int(ybb / 10) - y * 10 '截取出十分位
f = ybb - y * 100 - j * 10 '截取出百分位
zy = Application.WorksheetFunction.Text(y, "[dbnum2]") '将整数部分转为中文大写
zj = Application.WorksheetFunction.Text(j, "[dbnum2]") '将十分位转为中文大写
zf = Application.WorksheetFunction.Text(f, "[dbnum2]") '将百分位转为中文大写
dx = zy & "元" & "整"
d1 = zy & "元"
If f <> 0 And j <> 0 Then
dx = d1 & zj & "角" & zf & "分"
If y = 0 Then
dx = zj & "角" & zf & "分"
End If
End If
If f = 0 And j <> 0 Then
dx = d1 & zj & "角" & "整"
If y = 0 Then
dx = zj & "角" & "整"
End If
End If
If f <> 0 And j = 0 Then
dx = d1 & zj & zf & "分"
If y = 0 Then
dx = zf & "分"
End If
End If
If q = "" Then
dx = 0 '如没有输入任何数值为0
End If
End Function

提示：代码中“dx”是自定义函数名称，读者可以修改为其他字符。

    （3）输入完成后，关闭VBA编辑窗口返回工作表状态。

    （4）在D13单元格中输入公式：=dx(F12)，确认后即可将F12单元格中所示的小写数字金额转换为大

写的数字金额，并将结果显示在D13单元格中