人民币小写转大写公式汇总.doc

_人民币小写转换大写公式1、公式=IF(A1=,IF(A10,负,)&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.5%,负)&TEXT(RIGHT(FIXED(A1),2),dbnum20角0分;&IF(ABS(A1)1%,整,),零角,IF(ABS(A1)1,零),零分,整)3、公式 =TEXT(RMB(ROUND(A1,),=g;&TEXT(INT(ABS(ROUND(A1,)+0.5%),dbnum2G/通用格式元;)&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(RMB(ROUND(A1,),2),dbnum20角0分;整),零角,IF(ROUND(A1,)21,零),零分,整)4、公式=IF(M90,负)&TEXT(INT(ABS(A2)+0.5%),dbnum2G/通用格式元;)&TEXT(RIGHT(RMB(A2),2),9dbnum20角0分;&IF(A22dbnum20分;整),),零分,整)6. 公式=SUBSTITUTE(SUBSTITUTE(IF(-RMB(A2),IF(A20,负)&TEXT(INT(ABS(A2)+0.5%),dbnum2G/通用格式元;)&TEXT(RIGHT(RMB(A2),2),dbnum20角0分;整),),零角,IF(A221,零),零分,整)7. 公式=大写人民币:&IF(TRUNC(A1)=0,TEXT(TRUNC(A1),DBNum2G/通用格式)&元)&IF(TRUNC(A1*10)-TRUNC(A1*10,-1)=0,TEXT(TRUNC(A1*10)-TRUNC(A1*10,-1),DBNum2G/通用格式)&角)&IF(TRUNC(A1*100)-TRUNC(A1*100,-1)=0,TEXT(TRUNC(A1*100)-TRUNC(A1*100,-1),DBNum2G/通用格式)&分)&IF(TRUNC(A1*100)-TRUNC(A1*100,-1)=0，整，)8、公式=SUBSTITUTE(SUBSTITUTE(IF(A10,负,)&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)&分,IF(ROUND(A1,2)=0,整),零元零,),零元,)9、公式=CONCATENATE(IF(A10,负,),TEXT(IF(TRUNC(A1)=0,TRUNC(ABS(A1),DBNum2),IF(INT(TRUNC(A1)=0,元),TEXT(IF(OR(ABS(A1)0.1,TRUNC(A1)=A1),RIGHT(TRUNC(A1*10),1),DBNum2),IF(RIGHT(TRUNC(A1*10),1)=0,角),TEXT(IF(RIGHT(TRUNC(A1*100),1)=0,RIGHT(TRUNC(A1*100),1),DBNum2),IF(RIGHT(TRUNC(A1*100),1)=0,分)10、公式=IF(A1=0,IF(ABS(A1)0.995,TEXT(INT(ROUND(ABS(A1),2),DBNum2)&元)&IF(RIGHT(TEXT(A1,.00),2)*1=0,IF(ABS(A1)0.005,整),TEXT(IF(ABS(A1)0.095,LEFT(RIGHT(TEXT(A1,.00),2),dbnum2)&IF(LEFT(RIGHT(TEXT(A1,.00),2)*1=0,角)&IF(RIGHT(TEXT(A1,.00)*1=0,整,TEXT(RIGHT(TEXT(A1,.00),dbnum2)&分)11、公式=IF(A1=0,IF(ABS(A1)1,TEXT(TRUNC(ABS(A1),DBNum2)&元)&IF(RIGHT(TRUNC(A1*100),2)*1=0,IF(ABS(A1)0.01,整),IF(ABS(A1)0.1,TEXT(RIGHT(TRUNC(A1*10),dbnum2)&IF(RIGHT(TRUNC(A1*10)*1=0,角)&IF(RIGHT(TRUNC(A1*100)*1=0,整,TEXT(RIGHT(TRUNC(A1*100),dbnum2)&分)12. 公式=IF(a10,负,)&IF(a1=0,IF(ABS(a1)0.995,TEXT(INT(ROUND(ABS(a1),2),dbnum2)&元)&IF(RIGHT(TEXT(a1,.00),2)=00,IF(ABS(a1)0.005,整),IF(ABS(a1)0.095,TEXT(VALUE(LEFT(RIGHT(TEXT(a1,.00),2),1),dbnum2)&IF(LEFT(RIGHT(TEXT(a1,.00),2),1)=0,角)&IF(RIGHT(TEXT(a1,.00),1)=0,整,TEXT(VALUE(RIGHT(TEXT(a1,.00),1),dbnum2)&分)13、公式=IF(H14=0,CONCATENATE(IF(INT(ABS(H14)=0,TEXT(INT(ABS(H14),DBNum2G/通用格式元),IF(INT(MID(RIGHT(FIXED(ABS(H14),2,1),2),1,1)=0,IF(INT(MID(RIGHT(FIXED(H14,2,1),1),1,1)=0,IF(INT(ABS(H14)=0,零),TEXT(INT(MID(RIGHT(FIXED(H14,2,1),2),1,1),DBNum2G/通用格式角),IF(INT(MID(RIGHT(FIXED(H14,2,1),1),1,1)=0,整,TEXT(INT(MID(RIGHT(FIXED(H14,2,1),1),1,1),DBNum2G/通用格式分)14. 公式=IF(I170,金额为负无效,IF(OR(I17=0,I17=),零元整,IF(I17=1,TEXT(INT(ROUND(ABS(A1),2),DBNum2)&元,)&IF(RIGHT(TEXT(A1,.00),2)*1=0,整,IF(RIGHT(TEXT(A1,.00),4)*1=1,IF(RIGHT(TEXT(A1,.00),2)*19,零),IF(ROUND(ABS(A1),2)=1,零,)&IF(RIGHT(TEXT(A1,.00),2)*19,TEXT(LEFT(RIGHT(TEXT(A1,.00),2),DBNum2)&角,)&IF(RIGHT(TEXT(A1,.00)*10,TEXT(RIGHT(TEXT(A1,.00),DBNum2)&分,整)16. 公式=大写金额：&IF(ROUND(A1,2)0,无效数值,IF(ROUND(A1,2)=0,零,IF(ROUND(A1,2)0,负)&TEXT(INT(ABS(B2),dbnum2G/通用格式元;)&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(FIXED(B2),2),dbnum20角0分;),零角,IF(ABS(B2)0,零),零分,)18、公式=TEXT(MID(INT(B1),1,1),dbnum2)&CHOOSE(LEN(INT(B1),圆,拾,佰,仟,万,拾万)&IF(LEN(INT(B1)-1=0,TEXT(MID(INT(B1),2,1),dbnum2)&CHOOSE(LEN(INT(B1)-1,圆,拾,佰,仟,万,拾万)&IF(LEN(INT(B1)-2=0,TEXT(MID(INT(B1),3,1),dbnum2)&CHOOSE(LEN(INT(B1)-2,圆,拾,佰,仟,万,拾万)&IF(LEN(INT(B1)-3=0,TEXT(MID(INT(B1),4,1),dbnum2)&CHOOSE(LEN(INT(B1)-3,圆,拾,佰,仟,万,拾万)&IF(LEN(INT(B1)-4=0,TEXT(MID(INT(B1),5,1),dbnum2)&CHOOSE(LEN(INT(B1)-4,圆,拾,佰,仟,万,拾万)&IF(LEN(INT(B1)-5=0,TEXT(MID(INT(B1),6,1),dbnum2)&CHOOSE(LEN(INT(B1)-5,圆,拾,佰,仟,万,拾万)&IF(B1-INT(B1)=0,零角零分,IF(LE