Excel函数公式应用：小写数字转换成人民币大写9种方法

Excel函数公式应用：小写数字转换成人民币大写9种方法

方法1
=IF(TRUNC(H16)=H16,TEXT(H16,"[DBNum2]G/通用格式")&"元整",TEXT(TRUNC(H16),"[DBNum2]G/通用格式"&"元"))&IF(AND(TRUNC(H16)<>H16,RIGHT(TRUNC(H16*10))<>"0"),TEXT(TRUNC(MOD(H16*10,10)),"[DBNum2]G/通用格式")&"角","")&IF(AND(RIGHT(TRUNC(H16*10))="0",TRUNC(H16)<>H16),"零","")&IF(TRUNC(H16*10)<>H16,TRUNC(H16*10)=H16*10),"整","")

方法2
=IF(F10=0,"",CONCATENATE(IF(INT(F10)=0,"",TEXT(INT(F10),"[DBNum2]G/通用格式元")),IF(INT(MID(RIGHT(FIXED(F10,2,1),2),1,1))=0,IF(INT(MID(RIGHT(FIXED(F10,2,1),1),1,1))=0,"",IF(INT(F10)=0,"","零")),TEXT(INT(MID(RIGHT(FIXED(F10,2,1),2),1,1)),"[DBNum2]G/通用格式角")),IF(INT(MID(RIGHT(FIXED(F10,2,1),1),1,1))=0,"整",TEXT(INT(MID(RIGHT(FIXED(F10,2,1),1),1,1)),"[DBNum2]G/通用格式分"))))

方法3
人民币大写的函数公式，可正负，最多两位小数。
=IF(A1<0,"负","")&IF(TRUNC(A1)=A1,TEXT(IF(A1<0,-A1,A1),"[DBNum2]")&"元整",IF(TRUNC(A1*10)=A1*10,TEXT(TRUNC(IF(A1<0,-A1,A1)),"[DBNum2]")&"元"&TEXT(RIGHT(A1),"[DBNum2]")&"角整",TEXT(TRUNC(A1),"[DBNum2]")&"元"&IF(ISNUMBER(FIND(".0",A1)),"零",TEXT(LEFT(RIGHT(A1,2)),"[DBNum2]")&"角")&TEXT(RIGHT(A1),"[DBNum2]")&"分"))

方法4
修改一下4：根据剑魔兄的测试，发现有一个问题，如-100.05，现修正如下：
=IF(A1<0,"负","")&IF(TRUNC(A1)=A1,TEXT(IF(A1<0,-A1,A1),"[DBNum2]")&"元整",IF(TRUNC(A1*10)=A1*10,TEXT(TRUNC(IF(A1<0,-A1,A1)),"[DBNum2]")&"元"&TEXT(RIGHT(A1),"[DBNum2]")&"角整",TEXT(TRUNC(IF(A1<0,-A1,A1)),"[DBNum2]")&"元"&IF(ISNUMBER(FIND(".0",A1)),"零",TEXT(LEFT(RIGHT(A1,2)),"[DBNum2]")&"角")&TEXT(RIGHT(A1),"[DBNum2]")&"分"))

方法5
=IF(A1<0,"负",)&TEXT(TRUNC(ABS(A1)),"[DBNum2]G/通用格式")&"元"&IF(ROUND(A1,3)=ROUND(A1,),"整",TEXT(RIGHT(TRUNC(A1*10),1),"[DBNum2]G/通用格式")&"角"&IF(ROUND(A1,3)=ROUND(A1,1),"整",TEXT(RIGHT(ROUND((A1*100),),1),"[DBNum2]G/通用格式")&"分"))

方法6
无条件舍去：=CONCATENATE(IF(A1<0,"负",""),TEXT(IF(TRUNC(A1)=0,"零",TRUNC(ABS(A1))),"[DBNum2]")&"元",IF(OR(AND(ABS(A1)<0.1,TRUNC(A1)=A1),RIGHT(INT(ABS(A1)*100),2)="00"),"",TEXT(RIGHT(TRUNC(A1*10),1),"[DBNum2]")),IF(RIGHT(TRUNC(A1*10),1)="0","","角"),IF(OR(TRUNC(A1*10)-(A1*10)=0,RIGHT(TRUNC(A1*100),1)="0"),"整",TEXT(RIGHT(TRUNC(A1*100),1),"[DBNum2]")&"分"))
小数点后两位四舍五入:=CONCATENATE(IF(A1<0,"负",""),TEXT(IF(TRUNC(ROUND(A1,2))=0,"零",TRUNC(ABS(ROUND(A1,2)))),"[DBNum2]")&"元",IF(TRUNC(ROUND(A1,2))=ROUND(A1,2),"",TEXT(RIGHT(TRUNC(ROUND(A1,2)*10),1),"[DBNum2]")),IF(RIGHT(TRUNC(ROUND(A1,2)*10),1)="0","","角"),IF(OR(TRUNC(ROUND(A1,2)*10)-(ROUND(A1,2)*10)=0,RIGHT(ROUND(A1,2),1)="0",TRUNC(ROUND(A1,2))=ROUND(A1,2)),"整",TEXT(RIGHT(ROUND(A1,2),1),"[DBNum2]")&"分"))

方法7
无条件舍去：=IF(A1<0,"负","")&SUBSTITUTE(TEXT(TRUNC(A1),"[DBNum2]")&"元"&IF(ISNUMBER(FIND(".",TRUNC(A1,2))),TEXT(RIGHT(TRUNC(A1*10)),"[DBNum2]")&IF(ISNUMBER(FIND(".0",A1)),"","角"),"")&IF(LEFT(RIGHT(TRUNC(A1,2),3),1)=".",TEXT(RIGHT(TRUNC(A1,2)),"[DBNum2]")&"分","整"),"-",)
小数点后两位四舍五入:=IF(A1<0,"负","")&SUBSTITUTE(TEXT(TRUNC(ROUND(A1,2)),"[DBNum2]")&"元"&IF(ISNUMBER(FIND(".",ROUND(A1,2))),TEXT(RIGHT(TRUNC(ROUND(A1,2)*10)),"[DBNum2]")&IF(ISNUMBER(FIND(".0",ROUND(A1,2))),"","角"),"")&IF(LEFT(RIGHT(TRUNC(ROUND(A1,2),2),3),1)=".",TEXT(RIGHT(ROUND(A1,2)),"[DBNum2]")&"分","整"),"-",)

方法8
再简化如后，请大家试试。
无条件舍去：
=IF(A1<0,"负","")&TEXT(TRUNC(ABS(A1)),"[DBNum2]")&"元"&IF(ISERR(FIND(".",TRUNC(A1,2))),"",TEXT(RIGHT(TRUNC(A1*10)),"[DBNum2]"))&IF(RIGHT(TRUNC(A1*10))="0","","角")&IF(LEFT(RIGHT(TRUNC(A1,2),3))=".",TEXT(RIGHT(TRUNC(A1,2)),"[DBNum2]")&"分","整")
小数点后两位四舍五入:
=IF(A1<0,"负","")&TEXT(TRUNC(ABS(ROUND(A1,2))),"[DBNum2]")&"元"&IF(ISERR(FIND(".",ROUND(A1,2))),"",TEXT(RIGHT(TRUNC(ROUND(A1,2)*10)),"[DBNum2]"))&IF(ISERR(FIND(".0",TEXT(A1,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(A1,2),3))=".",TEXT(RIGHT(ROUND(A1,2)),"[DBNum2]")&"分","整")

方法9
=IF(ISTEXT(C2),"","人民币："&TEXT(INT(C2),"[dbnum2]")&"元"&IF(INT(C2*10)-INT(C2)*10=0,"",TEXT(INT(C2*10)-INT(C2)*10,"[dbnum2]")&"角")&IF(INT(C2*100)-INT(C2*10)*10=0,"整",TEXT(INT(C2*100)-INT(C2*10)*10,"[dbnum2]")&"分"))