资源描述：
《excel电子表格小写人民币转换成大写》由会员上传分享，免费在线阅读，更多相关内容在应用文档-天天文库。
1、EXCEL电子表格小写人民币转换成大写 推荐公式1=IF(AND(A1<=-0.01,A1>=-0.99),"负","")&SUBSTITUTE(SUBSTITUTE(TEXT(TRUNC(A1),"[dbnum2]G/通用格式元;负[dbnum2]G/通用格式元")&TEXT(RIGHT(FIXED(A1),2),"[dbnum2]0角0分;[dbnum2]0角;[dbnum2]整"),"零角","零"),"零分",)推荐公式2=IF(AND(A1<=-0.01,A1>=-0.99),"负","")&TEXT(TRUNC(FIXED(A2)),"[dbnum2]g/通用格式元;负
2、[dbnum2]g/通用格式元")&SUBSTITUTE(TEXT(RIGHT(FIXED(A2),2),"[=0][dbnum2]整;[<10][dbnum2]零0分;[dbnum2]0角0分"),"零分",)=SUBSTITUTE(IF(RIGHT(FIXED(A1)*100,2)*1=0,TEXT(FIXED(A1),"[dbnum2]")&"元整",SUBSTITUTE(SUBSTITUTE(REPLACE(TEXT(FIXED(A1),"[dbnum2]"),LEN(TEXT(FIXED(A1),"[dbnum2]")),0,"角")&"分",".","元"),"零角","
3、零")),"-","负") 此公式处理小于-0.99与-0.1之间的数据有问题补充1：EXCEL中隐藏了一个数字转大写的函数NUMBERSTRING，但它仅支持正整数，用中文版的朋友可以试试这个函数:=NUMBERSTRING(VALUE,TYPE)NumberString(1234567890,1)＝一十二亿三千四百五十六万七千八百九十NumberString(1234567890,2)＝壹拾贰亿叁仟肆佰伍拾陆万柒仟捌佰玖拾NumberString(1234567890,3)＝一二三四五六七八九〇 =IF(A1<0,"负",)&NUMBERSTRING(ABS(A1),
4、2)&"元"&SUBSTITUTE(TEXT(MOD(ABS(A1)/1%,100),"[=0]整;[<10][dbnum2]零0分;[dbnum2]0角0分"),"零分",)角后面无分，后面可有“整”，也可无“整”，实际应用中，无“整”的情况更多一些。“整”也可写成“正”。 补充2：在转换成大写金额前，最好用round(a1,2)将小写金额数字四舍五入保留两位小数。补充3：使用自定义函数的方法：FunctionDX(ByValNum) '人民币中文大写函数 Application.VolatileTrue Place="分角元拾佰仟万拾佰仟亿拾佰仟万" Dn=
5、"壹贰叁肆伍陆柒捌玖" D1="整零元零零零万零零零亿零零零万" IfNum<0ThenFuHao="负" Num=Format(Abs(Num),"###0.00")*100 IfNum>999999999999999#Then:DX="超出转换范围!!":ExitFunction IfNum=0Then:DX="零元零分":ExitFunction NumA=Trim(Str(Num)) NumLen=Len(NumA) ForJ=NumLenTo1Step-1 '转换过程 Temp=Val(Mid(NumA,NumLen-J+1,1
6、)) IfTemp<>0Then '非零数字转换 NumC=NumC&Mid(Dn,Temp,1)&Mid(Place,J,1) Else '数字零的转换 IfRight(NumC,1)<>"零"Then NumC=NumC&Mid(D1,J,1) Else SelectCaseJ '特殊数位转换 Case1 NumC=Left(NumC,Le
7、n(NumC)-1)&Mid(D1,J,1) Case3,11 NumC=Left(NumC,Len(NumC)-1)&Mid(D1,J,1)&"零" Case7 IfMid(NumC,Len(NumC)-1,1)<>"亿"Then NumC=Left(NumC,Len(NumC)-1)&Mid(D1
