Question

excel单元格中大写金额前面怎样自动加“人民币”？

首先谢谢大家！我是自动生成大写金额的单元格，采用你们的方法都不行。该单元格的函数是：=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,"","整")),"零元零",""),"零元","零")

Answer 1

将需要的单元格选定→右键→设置单元格格式→数字→自定义→然后将左边显示的“G/通用格式”前面添加"人民币" →最后确定，即可，试试哦！

Answer 2

选中你要设置的单元格，右键，设置单元格格式，自定义——在类型下的空白框中输入
"人民币"[DBNum2][$-804]G/通用格式
把我写的这一行复制到按CTRL+V粘贴进去就行了。

Answer 3

将需要的单元格选定→右键→设置单元格格式→数字→自定义→然后将左边显示的“G/通用格式”改成"人民币"# →最后确定，即可，试试哦！

Answer 4

假设你的数值在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]")&"分","整")))

Answer 5

="人民币"&(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,"","整")),"零元零",""),"零元","零"))