excel单元格中大写金额前面怎样自动加“人民币”?
首先谢谢大家!我是自动生成大写金额的单元格,采用你们的方法都不行。该单元格的函数是:=SUBSTITUTE(SUBSTITUTE(IF(D4<0,"负","")&TEXT...
首先谢谢大家!我是自动生成大写金额的单元格,采用你们的方法都不行。该单元格的函数是:=SUBSTITUTE(SUBSTITUTE(IF(D4<0,"负","")&TEXT(TRUNC(ABS(ROUND(D4,2))),"[DBNum2]")&"元"&IF(ISERR(FIND(".",ROUND(D4,2))),"",TEXT(RIGHT(TRUNC(ROUND(D4,2)*10)),"[DBNum2]"))&IF(ISERR(FIND(".0",TEXT(D4,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(D4,2),3))=".",TEXT(RIGHT(ROUND(D4,2)),"[DBNum2]")&"分",IF(ROUND(D4,2)=0,"","整")),"零元零",""),"零元","零")
展开
8个回答
展开全部
选中你要设置的单元格,右键,设置单元格格式,自定义——在类型下的空白框中输入
"人民币"[DBNum2][$-804]G/通用格式
把我写的这一行复制到按CTRL+V粘贴进去就行了。
"人民币"[DBNum2][$-804]G/通用格式
把我写的这一行复制到按CTRL+V粘贴进去就行了。
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
展开全部
将需要的单元格选定→右键→设置单元格格式→数字→自定义→然后将左边显示的“G/通用格式”改成"人民币"# →最后确定,即可,试试哦!
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
展开全部
假设你的数值在a1单元格,则在b1写入:="大写金额:"&IF(TRIM(a1)="","",IF(a1=0,"","人民币"&IF(a1<0,"负",)&IF(INT(a1),TEXT(INT(ABS(a1)),"[dbnum2]")&"元",)&IF(INT(ABS(a1)*10)-INT(ABS(a1))*10,TEXT(INT(ABS(a1)*10)-INT(ABS(a1))*10,"[dbnum2]")&"角",IF(INT(ABS(a1))=ABS(a1),,IF(ABS(a1)<0.1,,"零")))&IF(ROUND(ABS(a1)*100-INT(ABS(a1)*10)*10,),TEXT(ROUND(ABS(a1)*100-INT(ABS(a1)*10)*10,),"[dbnum2]")&"分","整")))
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
展开全部
="人民币"&(SUBSTITUTE(SUBSTITUTE(IF(D4<0,"负","")&TEXT(TRUNC(ABS(ROUND(D4,2))),"[DBNum2]")&"元"&IF(ISERR(FIND(".",ROUND(D4,2))),"",TEXT(RIGHT(TRUNC(ROUND(D4,2)*10)),"[DBNum2]"))&IF(ISERR(FIND(".0",TEXT(D4,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(D4,2),3))=".",TEXT(RIGHT(ROUND(D4,2)),"[DBNum2]")&"分",IF(ROUND(D4,2)=0,"","整")),"零元零",""),"零元","零"))
本回答被提问者采纳
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询