2024年4月27日发(作者:)
Excel中金额大小写转换(解决负数无法显示问题)
一、Excel自动转换数字大小写具体的操作步骤如下:
1. 启动excel。按“alt+f11”快捷键打开“visual basic编辑器”。
2. 在“visual basic编辑器”中,单击“插入”菜单栏中的“模块”命令,插入一个
模块。双击左侧“工程”窗口中的“模块1”选项,在窗口右边展开“模块1(代码)”编
辑窗口,然后输入如下代码:
Function NtoC(n) 'n as single
Const cNum = "零壹贰叁肆伍陆柒捌玖-万仟佰拾亿仟佰拾万仟佰拾元角分"
Const cCha = "零仟零佰零拾零零零零零亿零万零元亿万零角零分零整-零零零零零
亿万元亿零整整"
NtoC = ""
sNum = Trim(Str(Int(n * 100)))
For i = 1 To Len(sNum) '逐位转换
NtoC = NtoC + Mid(cNum, (Mid(sNum, i, 1)) + 1, 1) + Mid(cNum, 26 -
Len(sNum) + i, 1)
Next
For i = 0 To 11 '去掉多余的零
NtoC = Replace(NtoC, Mid(cCha, i * 2 + 1, 2), Mid(cCha, i + 26, 1))
Next
End Function
3.练习 A1=-10135.33 A2=NTOC(ABS(A1))
二、Excel中金额大小写转换公式
公式1:
“=IF(ROUND(E19,2)=0,"",IF(ROUND(ABS(E19),2)>=1,TEXT(INT(ROUND(ABS(E19),
2)),"[DBNum2]")&"元","")&IF(RIGHT(TEXT(E19,".00"),2)*1=0,"整
",IF(RIGHT(TEXT(E19,".00"),4)*1>=1,IF(RIGHT(TEXT(E19,".00"),2)*1>9,"","零
"),IF(ROUND(ABS(E19),2)>=1,"零
",""))&IF(RIGHT(TEXT(E19,".00"),2)*1>9,TEXT(LEFT(RIGHT(TEXT(E19,".00"),2)),"[DB
Num2]")&"角
","")&IF(RIGHT(TEXT(E19,".00"))*1>0,TEXT(RIGHT(TEXT(E19,".00")),"[DBNum2]")&"
分","整")))”
公式2:
发布评论