如何在EXCEL表的单位格中把变数量变成金额,如12345.5元利用公式自动变成壹万贰仟叁佰肆拾伍元伍角整。
经得知有如下公式能解决,但实在想不明白,请指教!(E4代表单元格)
=IF(ISNUMBER(E4),IF(TRUNC(E4)=0,IF(AND(MID(TEXT(TRUNC(E4,2),"0.00"),LEN(TEXT(TRUNC(E4,2),"0.00"))-1,1)="0",RIGHT(TEXT(TRUNC(E4,2),"0.00"),1)="0"),"零元",""),(IF(E4<0, "负",""))&TEXT(TRUNC(ABS(E4)),"[DBNUM2]")&"元")&(IF(MID(TEXT(TRUNC(ABS(E4),2),"0.00"),LEN(TEXT(TRUNC(ABS(E4),2),"0.00"))-1,1)="0",IF(OR(TRUNC(ABS(E4))=0,RIGHT(TEXT(TRUNC(ABS(E4),2),"0.00"),1)="0"),"","零"),TEXT(MID(ABS(E4),LEN(TEXT(TRUNC(ABS(E4),2),"0.00"))-1,1),"[DBNUM2]")&"角"))&(IF(RIGHT(TEXT(TRUNC(ABS(E4),2),"0.00"),1)="0","整",TEXT(RIGHT(TEXT(TRUNC(ABS(E4),2),"0.00"),1),"[DBNUM2]")&"分")),"不是有效金额,请重新输入!")&"。"
并且以上公式中ISNUMBER、AND 、[DBNUM2]、OR不知道表达的意思,以上问题请教,或提出更好更简单快捷的方法,谢谢!