镜湖区人才服务中心:excel人民币小写转大写
来源:百度文库 编辑:偶看新闻 时间:2024/05/04 22:05:16
收藏请评论两句
1、假设在B1中输入小写数字,在A1中得出大写,即将下面公式复制到A1回车即可:
已知:B1=100
函数: A1="住院医疗费(大写):"&IF(TRIM(B1)="","",IF(B1=0,"",""&SUBSTITUTE(SUBSTITUTE(TEXT(TRUNC(FIXED(B1)),"[dbnum2]G/通用格式元;负[dbnum2]G/通用格式元;"&IF(B1>-0.5%,,"负"))&TEXT(RIGHT(FIXED(B1),2),"[dbnum2]0角0分;;"&IF(ABS(B1)>1%,"整",)),"零角",IF(ABS(B1)<1,,"零")),"零分","整")))
结果:住院医疗费(大写):壹百元整
2、假设在B2中输入小写数字,在A1中得出大写,即将下面公式复制到A2回车即可:
已知:B2= 780.23
函数:="医疗统筹基金支付总额(大写):"&IF(TRIM(B2)="","",IF(B2=0,"",""&SUBSTITUTE(SUBSTITUTE(TEXT(TRUNC(FIXED(B2)),"[dbnum2]G/通用格式元;负[dbnum2]G/通用格式元;"&IF(B2>-0.5%,,"负"))&TEXT(RIGHT(FIXED(B2),2),"[dbnum2]0角0分;;"&IF(ABS(B2)>1%,"整",)),"零角",IF(ABS(B2)<1,,"零")),"零分","整")))
结果:医疗统筹基金支付总额(大写):柒佰捌拾元贰角叁分
已知:B2= -780.23
结果:医疗统筹基金支付总额(大写):负柒佰捌拾元贰角叁分
已知:B2= 6988799123456780(注:小数输不上,即:B2= 6988799123456780.23 只能显示为B2= 6988799123456780)
结果:医疗统筹基金支付总额(大写):陆仟玖佰捌拾捌兆柒仟玖佰玖拾壹亿贰仟叁佰肆拾伍万陆仟柒佰捌拾元整
问题:
已知:99999123456789.23(位数太长3显示不出来)
函数:=IF(ROUND(G8,2)<0,"无效数值",IF(ROUND(G8,2)=0,"零",IF(ROUND(G8,2)<1,"",TEXT(INT(ROUND(G8,2)),"[dbnum2]")&"元")&IF(INT(ROUND(G8,2)*10)-INT(ROUND(G8,2))*10=0,IF(INT(ROUND(G8,2))*(INT(ROUND(G8,2)*100)-INT(ROUND(G8,2)*10)*10)=0,"","零"),TEXT(INT(ROUND(G8,2)*10)-INT(ROUND(G8,2))*10,"[dbnum2]")&"角")&IF((INT(ROUND(G8,2)*100)-INT(ROUND(G8,2)*10)*10)=0,"整",TEXT((INT(ROUND(G8,2)*100)-INT(ROUND(G8,2)*10)*10),"[dbnum2]")&"分")))