作者:乔山办公网日期:
返回目录:excel表格制作
假如数据在百度a1
B1公式下拉:
=SUBSTITUTE(SUBSTITUTE(IF(-RMB(A1,2),TEXT(A1,";负知")&TEXT(INT(ABS(A1)+0.5%),"[dbnum2]G/通用格式圆道;;")&TEXT(RIGHT(RMB(A1,2),2),"[dbnum2]0角0分内;;整"),),"零角",IF(A1^2<1,,"零")),"零分容","整")
假设数据知在H21单元道
=IF(TRIM(H21)="","",IF(H21=0,"",""&SUBSTITUTE(SUBSTITUTE(TEXT(TRUNC(FIXED(H21)),"[dbnum2]G/通用格式元专;负[dbnum2]G/通用格式元;"&IF(H21>-0.5%,,"负"))&TEXT(RIGHT(FIXED(H21),2),"[dbnum2]0角0分;;"&IF(ABS(H21)>1%,"整属",)),"零角",IF(ABS(H1)<1,,"零")),"零分","整")))
可选用以下方百法:
1、如果都是整数,可通过单元格格式设置实现。选定单元格,在度其上点鼠标知右键——“设置单元格格式”,点“道数字”标签——“特殊”,在”类型“中选择“中文大内写数字”。
2、如果存在小数,可用以下公式处理:容
=IF(ISNUMBER(A1),IF(INT(A1),TEXT(INT(A1),"[dbnum2]")&"元","")&IF(MOD(INT(A1*10),10),TEXT(MOD(INT(A1*10),10),"[dbnum2]")&"角",IF(INT(A1)=A1,,IF(A1<0.1,,"零")))&IF(MOD(INT(A1*100),10),TEXT(MOD(INT(A1*100),10),"[dbnum2]")&"分",""),"")
工具zhidao:2010版excel
1、先打开excel,以下图为例,需要将A1单元格中的数字转化成中文大写金额格式版并在B1单元格中显示结果;
2、选中B1单元格并输入公式“=SUBSTITUTE(SUBSTITUTE(TEXT(INT(A1),"[DBNum2][$-804]G/通用格式元"&IF(INT(A1)=A1,"整",""))&TEXT(MID(A1,FIND(".",A1&".0")+1,1),"[DBNum2][$-804]G/通用格式角")&TEXT(MID(A1,FIND(".",A1&".0")+2,1),"[DBNum2][$-804]G/通用格式分"),"零权角","零"),"零分","")”;
3、按下回车键后就可以在B1单元格中得出结果了。