作者:乔山办公网日期:
返回目录:excel表格制作
设置显示格式为大写中文就可以了,如下图:
下面来这个公式可以自实现百:
=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]")&"分")))
把“(大写):”调整到前面单元格来,在需要填入金额自的单元格中写入下面公式:
=SUBSTITUTE(IF(N12,IF(N12<0,"负",)&TEXT(TRUNC(N12),"[dbnum2]g/通用百格式元;[dbnum2]g/通用格式元;")&TEXT(RIGHT(RMB(N12),2),"[>9][dbnum2]0角0分度;[>][dbnum2]0分;整"),"零元"),"零分","整")
单元格格式调整为左对齐,知注意的是公式中“N13”根据实际需写入大写数字的单元格修道改。
改好了,试试呢
="大写:zhidao"&IF(MOD(A2,10)=0,SUBSTITUTE(SUBSTITUTE(IF(ROUND(A2,2),TEXT(A2,";负")&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元版;;")&TEXT(RIGHT(TEXT(A2,".00"),2),"[dbnum2]0角0分;;整"),),"零角",IF(A2^2<1,,"零")),"零分权","整"),
SUBSTITUTE(SUBSTITUTE(IF(ROUND(A2,2),TEXT(A2,";负")&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(TEXT(A2,".00"),2),"[dbnum2]0角0分;;"),),"零角",IF(A2^2<1,,"零")),"零分",""))