《人民币小写转大写公式汇总》由会员分享,可在线阅读,更多相关《人民币小写转大写公式汇总(4页珍藏版)》请在金锄头文库上搜索。
1、人民币小写转换大写公式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
2、;&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
3、,负)&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)-
4、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(
5、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
6、(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
7、、公式=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(AB
8、S(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(
9、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(I17
10、0,金额为负无效,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(RIG
11、HT(TEXT(A1,.00),DBNum2)&分,整)16. 公式=大写金额:&IF(ROUND(A1,2)0,无效数值,IF(ROUND(A1,2)=0,零,IF(ROUND(A1,2)1,TEXT(INT(ROUND(A1,2),dbnum2)&元)&IF(INT(ROUND(A1,2)*10)-INT(ROUND(A1,2)*10=0,IF(INT(ROUND(A1,2)*(INT(ROUND(A1,2)*100)-INT(ROUND(A1,2)*10)*10)=0,零),TEXT(INT(ROUND(A1,2)*10)-INT(ROUND(A1,2)*10,dbnum2)&角)&IF(INT(ROUND(A1,2)*100)-INT(ROUND(A1,2)*10)*10)=0,整,TEXT(INT(ROUND(A1,2)*100)-INT(ROUND(A1,2)*10)*10),dbnum2)&分)17. 公式=IF(MOD(B2,1)=0,TEXT(INT(B2),dbnum