人民幣小寫轉(zhuǎn)大寫公式匯總

1、人民幣小寫轉(zhuǎn)換大寫公式1 、公式=IF(A1="","",IF(A1<0," 負(fù) ",)&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)<

2、0.1,"零")&IF(ROUND(ABS(A1)*100-INT(ABS(A1)*10)*10,),TEXT(ROUND(ABS(A1)*100-INT(ABS(A1)*10)*10,),"dbnum2")&" 分","整")2、公式=SUBSTITUTE(SUBSTITUTE(TEXT(TRUNC(FIXED(A1),"dbnum2G/ 通 用 格 式 元 ; 負(fù)dbnum2G/通用格式元；"&IF(A1>-0.5%,"負(fù)")&TEX

3、T(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(R

4、OUND(A1,),2),”dbnum20 角 0分;整")，"零角",IF(ROUND(A1,)A2<1,"零"),"零分"，"整")4、公式精品資料=IF(M9<0,""")&TEXT(TRUNC(ABS(ROUND(M9,2),"DBNum2")&""&IF(ISERR(FIND(".",ROUND(M9,2),"",TEXT(RIGHT(TRUNC(RO

5、UND(M9,2)*10),"DBNum2")&IF(ISERR(FIND(".0",TEXT(M9,"0.00"),"角","")&IF(LEFT(RIGHT(ROUND(M9,2),3)=".",TEXT(RIGHT(ROUND(M9,2),"DBNum2")&" 分","整")5. 公式=SUBSTITUTE(IF(-RMB(A2),IF(A2>0,"負(fù)")&a

6、mp;TEXT(INT(ABS(A2)+0.5%),”dbnum2G/通用格 式 元 ;")&TEXT(RIGHT(RMB(A2),2),">9dbnum20角 0分;"&IF(A2A2<1,0)&">dbnum20 分;整"),),"零分","整")6. 公式=SUBSTITUTE(SUBSTITUTE(IF(-RMB(A2),IF(A2>0,"負(fù)")&TEXT(INT(ABS(A2)+0.5%),"dbnum2G/通

7、用格式元;")&TEXT(RIGHT(RMB(A2),2),”dbnum20角 0 分;整"),),"零角",IF(A2A2<1,"零"),"零分","整")7. 公式="大 寫 人 民 幣 :"&IF(TRUNC(A1)=0,"",TEXT(TRUNC(A1),"DBNum2G/ 通 用 格 式")&" 元")&IF(TRUNC(A1*10)-TRUNC(A1*10,-1)=0

8、,"",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、公式=SUBST

9、ITUTE(SUBSTITUTE(IF(A1<0,"負(fù)","")&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"),"

10、角","")&IF(LEFT(RIGHT(ROUND(A1,2),3)=".",TEXT(RIGHT(ROUND(A1,2),"DBNum2")&"分”,IF(ROUND(A1,2)=0,"","整"),"零元零",""),"零元","")9、公式=CONCATENATE(IF(A1<0,"負(fù)",""),TEXT(IF(TRUNC(A1

11、)=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&q

12、uot;,"",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,I

13、F(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,&

14、quot;.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(A

15、1*10),"dbnum2")&IF(RIGHT(TRUNC(A1*10)*1=0,"","角")&IF(RIGHT(TRUNC(A1*100)*1=0,"整",TEXT(RIGHT(TRUNC(A1*100),"dbnum2")&" 分")12 . 公式=IF(a1<0," 負(fù) ","")&IF(a1=0,"",IF(ABS(a1)<0.995,"",

16、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

17、,".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(

18、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,

19、"整”,TEXT(INT(MID(RIGHT(FIXED(H14,2,1),1),1,1),"DBNum2G/通用格式分")14 .公式=IF(I17<0," 金 額 為 負(fù) 無 效",IF(OR(I17=0,I17=""片零 元 整”,IF(I17<1,"",TEXT(INT(I17),”DBNum2G/ 通 用 格 式 ")&” 元”)&IF(NT(I17*10)-INT(I17)*10=0,IF(NT(I17)*(INT(I17*100)-INT(I17*10)

20、*10)=0,"","零"),TEXT(INT(I17*10)-INT(I17)*10,”DBNum2G/ 通 用 格 式 ")&" 角”)&IF(INT(I17*100)-INT(I17*10)*10)=0,"整”,TEXT(INT(I17*100)-INT(I17*10)*10),”DBNum2G/通用格式")&"分")15 .公式=IF(ROUND(A1,2)=0,"”,IF(ROUND(ABS(A1),2)>=1,TEXT(INT(ROUND(AB

21、S(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)*1>9,"","零"),IF(ROUND(ABS(A1),2)>=1,"零”,”")&IF(RIGHT(TEXT(A1；00"),2)*1>9,TE

22、XT(LEFT(RIGHT(TEXT(A1；00"),2),"DBNum2")&"角精品資料","")&IF(RIGHT(TEXT(A1；00")*1>0,TEXT(RIGHT(TEXT(A1；00"),"DBNum2r)&"分"，"整")16 .公式="大 寫 金 額： "&IF(ROUND(A1,2)<0," 無 效 數(shù) 值",IF(ROUND(A1,2)=0,&quo

23、t; 零”,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&q

24、uot;)&"角")&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),”dbnum2G/通用格式元整；負(fù)dbnum2G/通用格式元整；零元整；”),IF(B2>0,"負(fù)")&TEXT(INT(ABS(B2),&q

25、uot;dbnum2G/ 通用格式元;")&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(FIXED(B2),2),”dbnum20角 0 分;"),"零角",IF(ABS(B2)<>0,"零"),"零分","")18、公式精品資料","拾","佰","仟 ","萬","拾=TEXT(MID(INT(B1),1,1),"dbnum2")&

26、amp;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,"",