Excel 想在格中显示人民币大写圆整，公式是什么？

Excel 想在格中显示人民币大写圆整，公式是什么？想在B6 中输入 ¥123 ，在C6格显示：人民币壹佰贰拾叁圆整，这种情况应该怎么弄？谢谢

第1个回答 2017-04-05

这是前面没有美元符号的公式看你的能不能去掉美元符号=SUBSTITUTE(SUBSTITUTE(IF(C8<0,"负","")&TEXT(TRUNC(ABS(ROUND(C8,2))),"[DBNum2]")&"元 "&IF(ISERR(FIND(".",ROUND(C8,2))),"",TEXT(RIGHT(TRUNC(ROUND(C8,2)*10)),"[DBNum2]"))&IF(ISERR(FIND(".0",TEXT(C8,"0.00"))),"角 ","")&IF(LEFT(RIGHT(ROUND(C8,2),3))=".",TEXT(RIGHT(ROUND(C8,2)),"[DBNum2]")&"分",IF(ROUND(C8,2)=0,"","整")),"零元零",""),"零元","")

第2个回答 2017-04-05

参考公式：
=SUBSTITUTE(SUBSTITUTE(TEXT(TRUNC(FIXED(B6)),"[>0][dbnum2];[<0]负[dbnum2];;")&TEXT(RIGHT(FIXED(B6),2),"元[dbnum2]0角0分;;"&IF(ABS(B6)>1%,"元整",)),"零角",IF(ABS(B6)<1,,"零")),"零分","整")
如果B6是输入的文本￥，计算可能会出错。本回答被网友采纳

第3个回答 2017-04-05

C6公式：
=TEXT(B6*100,"人民币[dbnum2]0万0仟0佰0拾0元0角0分整")

第4个回答 2017-04-05

C6单元格输入
="人民币："&IF(TRIM(C6)="","",IF(C6=0,"","人民币"&IF(C6<0,"负",)&IF(INT(C6),TEXT(INT(ABS(C6)),"[dbnum2]")&"圆",)&IF(INT(ABS(C6)*10)-INT(ABS(C6))*10,TEXT(INT(ABS(C6)*10)-INT(ABS(C6))*10,"[dbnum2]")&"角",IF(INT(ABS(C6))=ABS(C6),,IF(ABS(C6)<0.1,,"零")))&IF(ROUND(ABS(C6)*100-INT(ABS(C6)*10)*10,),TEXT(ROUND(ABS(C6)*100-INT(ABS(C6)*10)*10,),"[dbnum2]")&"分","整")))本回答被提问者采纳

第5个回答 2017-04-05

C6="人民币"&TEXT(RIGHT(B6,LEN(B6)-1),"[dbnum2]")&"圆整"

相似回答

在EXCEL表格里,怎样把小写金额转换为大写金额,小的金额是有多个单元格...答：大写公式：=IF(C1,TEXT(INT(C1),"[dbnum2]G/通用格式;[dbnum2]负G/通用格式;")&"元"&IF(INT(C1*10)-INT(C1)*10=0,IF(INT(C1)*(INT(C1*100)-INT(C1*10)*10)=0,"","零"),TEXT(INT(C1*10)-INT(C1)*10,"[dbnum2]")&"角")&IF((INT(C1*100)-INT(C1*10)*10)=0,...

如何在Excel 2010中实现人民币大写的转换公式?答：在B2单元格键入以下公式，这个聪明的公式会根据输入的数值生成相应的大写格式：=IF(ISBLANK(B1), "", IF(B1=0, "", IF(B1 < 0, "负", ""))) & IF(INT(B1), TEXT(INT(ABS(B1)), "[dbnum2]") & "元", "") & IF(INT(ABS(B1)*10)-INT(ABS(B1))*10, TEXT(INT(ABS(B1...

大家正在搜

圆是人民币大写吗人民币大写用元还是圆人民币大写规范元还是圆人民币上的圆为什么不是元大写是圆还是圆大写元整还是圆整人民币大写元人民币元圆大写人民币