1.怎么写负数大写A1输入数字,在A2或其它单元格输入公式:=IF(A1="","",IF(ROUND(A1,2)=0,"零",IF(ROUND(A1,2)<0,"负","")&IF(ROUND(ABS(A1),2)<1,"",TEXT(INT(ROUND(ABS(A1),2)),"[dbnum2]")&"圆")&IF(INT(ROUND(ABS(A1),2)*10)-INT(ROUND(ABS(A1),2))*10=0,IF(INT(ROUND(ABS(A1),2)*100)-INT(ROUND(ABS(A1),2)*10)*10=0,"","零"),TEXT(INT(ROUND(ABS(A1),2)*10)-INT(ROUND(ABS(A1),2))*10,"[dbnum2]")&"角")&IF((INT(ROUND(ABS(A1),2)*100)-INT(ROUND(ABS(A1),2)*10)*10)=0,"整",TEXT((INT(ROUND(ABS(A1),2)*100)-INT(ROUND(ABS(A1),2)*10)*10),"[dbnum2]")&"分")))例:人民币大写:-71587.96 成为:负柒万壹仟伍佰捌拾柒圆玖角陆分 。
2.怎么写负数大写A1输入数字,在A2或其它单元格输入公式:=IF(A1="","",IF(ROUND(A1,2)=0,"零",IF(ROUND(A1,2)<0,"负","")&IF(ROUND(ABS(A1),2)<1,"",TEXT(INT(ROUND(ABS(A1),2)),"[dbnum2]")&"圆")&IF(INT(ROUND(ABS(A1),2)*10)-INT(ROUND(ABS(A1),2))*10=0,IF(INT(ROUND(ABS(A1),2)*100)-INT(ROUND(ABS(A1),2)*10)*10=0,"","零"),TEXT(INT(ROUND(ABS(A1),2)*10)-INT(ROUND(ABS(A1),2))*10,"[dbnum2]")&"角")&IF((INT(ROUND(ABS(A1),2)*100)-INT(ROUND(ABS(A1),2)*10)*10)=0,"整",TEXT((INT(ROUND(ABS(A1),2)*100)-INT(ROUND(ABS(A1),2)*10)*10),"[dbnum2]")&"分")))
例:人民币大写:-71587.96 成为:负柒万壹仟伍佰捌拾柒圆玖角陆分
3.excel 数字转人民币大写带负数的公式EXCEL中人民币转换大写,要注意以下几点:
1. 为防止浮点小数误差,用ROUND函数修正为两位小数;
2. 人民币大写时,不存在"零角零分","零分","零角"等写法,要统一换成“整”;
3. 存在负数时,用绝对值函数ABS转换为正数再转换为大写;
4. 根据上面几点,可以列出公式:="人民币"&IF(A1<0,"负",)&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(INT(ROUND(ABS(A1),2)),"[dbnum2]")&"元"&TEXT(MOD(ROUND(ABS(A1),2),1)*100,"[dbnum2]0角0分"),"零角零分","整"),"零分","整"),"零角","零") 。
【人民币负数大写怎么写】
文章插图