EXCEL财务金额大写如无，返回空白值

来源：百度知道 编辑：UC知道 时间：2024/06/16 03:29:25
EXCEL财务金额大写如无，返回空白值
例：金额栏事先编好了一组函数返回大写金额，公式如下：
=IF((A7-INT(A7))=0,TEXT(A7,"[DBNUM2]")&"元整",IF(INT(A7*10)-A7*10=0,TEXT(INT(A7),"[DBNUM2]")&"元"&TEXT((INT(A7*10)-INT(A7)*10),"[DBNUM2]")&"角整",TEXT(INT(A7),"[DBNUM2]")&"元"&IF(INT(A7*10)-INT(A7)*10=0,"零",TEXT(INT(A7*10)-INT(A7)*10,"[DBNUM2]")&"角")&TEXT(RIGHT(A7,1),"[DBNUM2]")&"分"))
但问题出来了，如果金额栏没有数据，他就会显示“零元整”，可否再加上什么函数让其为“零远整”时直接返回空白值！
谢谢！

=IF(OR(A7="",A7=0),"",IF((A7-INT(A7))=0,TEXT(A7,"[DBNUM2]")&"元整",IF(INT(A7*10)-A7*10=0,TEXT(INT(A7),"[DBNUM2]")&"元"&TEXT((INT(A7*10)-INT(A7)*10),"[DBNUM2]")&"角整",TEXT(INT(A7),"[DBNUM2]")&"元"&IF(INT(A7*10)-INT(A7)*10=0,"零",TEXT(INT(A7*10)-INT(A7)*10,"[DBNUM2]")&"角")&TEXT(RIGHT(A7,1),"[DBNUM2]")&"分")))

前面加上IF(A7="","",就行了.

=IF($A$7<=0,"",TEXT(INT($A$7),"[dbnum2]")&IF($A$7-INT($A$7)=0,"元整",IF(AND($A$7-INT($A$7)>0,LEN($A$7)-FIND(".",$A$7)=1),"元"&TEXT(MID($A$7,FIND(".",$A$7)+1,1)*1,"[dbnum2]")&"角整",IF(AND($A$7-INT($A$7)>0,LEN($A$7)-FIND(".",$A$7)=2,MID($A$7,FIND(".",$A$7)+1,2)*1<10),""&"元零"&TEXT(MID($A$7,FIND(".",$A$7)+1,2)*1,"[dbnum2]")&"分",IF(AND($