小写金额转换大写金额公式
真八岐大蛇阵容-退休发言稿
2023年2月20日发(作者：拔萝卜故事)Excel中金额大小写转换
一、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)),"[DBNu
m2]")&"角
","")&IF(RIGHT(TEXT(E19,".00"))*1>0,TEXT(RIGHT(TEXT(E19,".00")),"[DBNum2]")&"分
","整")))”
公式2：“=IF(F4<1,IF(F4<0.1,TEXT(INT(F4*100),"[DBNum2]G/通用格式")&"分
",IF((INT(F4*100)-INT(F4*10)*10=0),TEXT(INT(F4*10),"[DBNum2]G/通用格式")&"角整
",TEXT(INT(F4*10),"[DBNum2]G/通用格式")&"角
"&TEXT(INT(F4*100)-INT(F4*10)*10,"[DBNum2]G/通用格式")&"分
")),TEXT(INT(F4),"[DBNum2]G/通用格式"&"元
")&IF((INT(F4*10)-INT(F4)*10)=0,IF((INT(F4*100)-INT(F4*10)*10)=0,"","零
"),IF((INT(F4*0.1)-INT(F4)*0.1)=0,"零","")&TEXT(INT(F4*10)-INT(F4)*10,"[DBNum2]G/通
用格式")&"角")&IF((INT(F4*100)-INT(F4*10)*10)=0,"整
",TEXT(INT(F4*100)-INT(F4*10)*10,"[DBNum2]G/通用格式")&"分"))”
公式3：“=IF(B10<1,IF(B10<0.1,TEXT(INT(B10*100),"[DBNum2]G/通用格式")&"分
",IF((INT(B10*100)-INT(B10*10)*10=0),TEXT(INT(B10*10),"[DBNum2]G/通用格式")&"角
整",TEXT(INT(B10*10),"[DBNum2]G/通用格式")&"角
"&TEXT(INT(B10*100)-INT(B10*10)*10,"[DBNum2]G/通用格式")&"分
")),TEXT(INT(B10),"[DBNum2]G/通用格式"&"元
")&IF((INT(B10*10)-INT(B10)*10)=0,IF((INT(B10*100)-INT(B10*10)*10)=0,"","零
"),IF((INT(B10*0.1)-INT(B10)*0.1)=0,"零
","")&TEXT(INT(B10*10)-INT(B10)*10,"[DBNum2]G/通用格式")&"角
")&IF((INT(B10*100)-INT(B10*10)*10)=0,"整
",TEXT(INT(B10*100)-INT(B10*10)*10,"[DBNum2]G/通用格式")&"分"))”
二、Excel中金额大小写转换函数[=ntoc(单元格)]
使用加载宏方法：
“工具栏”→“宏”→“VisualBasic编辑器”，在“VisualBasic编辑器”中顶部选“插入”
→“模块”，在“模块”对话框中选“通用”，并在编辑区域内粘贴以下内容：
FunctionNtoC(n)'nassingle
ConstcNum="零壹贰叁肆伍陆柒捌玖-万仟佰拾亿仟佰拾万仟佰拾元角分"
ConstcCha="零仟零佰零拾零零零零零亿零万零元亿万零角零分零整-零零零零零亿万元亿零整整"
NtoC=""
sNum=Trim(Str(Int(n*100)))
Fori=1ToLen(sNum)'逐位转换
NtoC=NtoC+Mid(cNum,(Mid(sNum,i,1))+1,1)+Mid(cNum,26-Len(sNum)+i,1)
Next
Fori=0To11'去掉多余的零
NtoC=Replace(NtoC,Mid(cCha,i*2+1,2),Mid(cCha,i+26,1))
Next
EndFunction
然后单击“保存”，关闭“VisualBasic编辑器”，回到“工作表１”，输入函数“=NtoC(单元
格)”即可。