Question

在EXCEL表格编辑中，如何将100元转换为壹佰元整

在开具现金支票及电汇中使用。

Answer 1

＝IF(A1<0,"金额为负无效",IF(OR(A1=0,A1=""),"零元",IF(A1<1,"",TEXT(INT(A1),"[dbnum2]G/通用格式")&"元"))) & IF(INT(A1*10)-INT(A1)*10=0,IF(INT(A1)*(INT(A1*100)-INT(A1*10)*10)=0,"","零"),TEXT(INT(A1*10)-INT(A1)*10,"[dbnum2]")&"角")&IF((INT(A1*100)-INT(A1*10)*10)=0,"整",TEXT((INT(A1*100)-INT(A1*10)*10),"[dbnum2]")&"分")

或

＝IF(A1<0,"（负）","")&TEXT(INT(ABS(A1)),"[dbnum2]")&"元"&IF(ABS(A1)-INT(ABS(A1))<0.005,"整",IF(INT(ROUND((ABS(A1)-INT(ABS(A1)))*100,0)/10)>0,TEXT(INT(ROUND((ABS(A1)-INT(ABS(A1)))*100,0)/10),"[dbnum2]")&"角","零")&IF(MOD(ROUND((ABS(A1)-INT(ABS(A1)))*100,0),10)>0,TEXT(MOD(ROUND((ABS(A1)-INT(ABS(A1)))*100,0),10),"[dbnum2]")&"分","整"))

Answer 2

使用选择性粘贴-除来处理
excel版本参考：2010
1、任意空白单元格输入100
2、选中数据源，右击选择性粘贴-除
3、查看效果