怎样将数字转换成汉字函数

发布网友发布时间：2022-04-23 03:53

共1个回答

热心网友时间：2022-06-26 04:59

选中那些数字所在的单元格，点击右键，在弹出菜单中选择“设置单元格格式”命令，打开“设置单元格格式”对话框。点击“数字”选项卡，在左侧的“分类”列表中选择“特殊”，然后在右侧的“类型”列表中点击“中文大写数字”，如图1所示。确定后就可以了。若点击上方的那个“中文小写数字”，可以得到“一十二亿三千四百五十六万七千八百九十”。
或者这几个：
公式一
=IF(ABS(K20)=0,"",IF(K20<0,"负",)&IF(INT(ABS(K20)),TEXT(INT(ABS(K20)),"[dbnum2]")&"元",)&IF(INT(ABS(K20)*10)-INT(ABS(K20))*10,TEXT(INT(ABS(K20)*10)-INT(ABS(K20))*10,"[dbnum2]")&"角",IF(INT(ABS(K20))=ABS(K20),,IF(ABS(K20)<0.1,,"零")))&IF(ROUND(ABS(K20)*100-INT(ABS(K20)*10)*10,),TEXT(ROUND(ABS(K20)*100-INT(ABS(K20)*10)*10,),"[dbnum2]")&"分","整"))
=SUBSTITUTE(SUBSTITUTE(IF(K20<0,"负","")&TEXT(TRUNC(ABS(ROUND(K20,2))),"[DBNum2]")&"元"&IF(ISERR(FIND(".",ROUND(K20,2))),"",TEXT(RIGHT(TRUNC(ROUND(K20,2)*10)),"[DBNum2]"))&IF(ISERR(FIND(".0",TEXT(K20,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(K20,2),3))=".",TEXT(RIGHT(ROUND(K20,2)),"[DBNum2]")&"分",IF(ROUND(K20,2)=0,"","整")),"零元零",""),"零元","")
公式二：
=CONCATENATE(IF(K20<0,"负",""),TEXT(IF(TRUNC(K20)=0,"",TRUNC(ABS(K20))),"[DBNum2]"),IF(INT(TRUNC(K20))=0,"","元"),TEXT(IF(OR(ABS(K20)<0.1,TRUNC(K20)=K20),"",RIGHT(TRUNC(K20*10),1)),"[DBNum2]"),IF(RIGHT(TRUNC(K20*10),1)="0","","角"),TEXT(IF(RIGHT(TRUNC(K20*100),1)="0","",RIGHT(TRUNC(K20*100),1)),"[DBNum2]"),IF(RIGHT(TRUNC(K20*100),1)="0","","分"))
公式三：
=IF(ROUND(K20,2)=0,"",IF(ROUND(ABS(K20),2)>=1,TEXT(INT(ROUND(ABS(K20),2)),"[DBNum2]")&"元","")&IF(RIGHT(TEXT(K20,".00"),2)*1=0,"整",IF(RIGHT(TEXT(K20,".00"),4)*1>=1,IF(RIGHT(TEXT(K20,".00"),2)*1>9,"","零"),IF(ROUND(ABS(K20),2)>=1,"零",""))&IF(RIGHT(TEXT(K20,".00"),2)*1>9,TEXT(LEFT(RIGHT(TEXT(K20,".00"),2)),"[DBNum2]")&"角","")&IF(RIGHT(TEXT(K20,".00"))*1>0,TEXT(RIGHT(TEXT(K20,".00")),"[DBNum2]")&"分","整")))
公式四：
=IF(K20=0,"",IF(ABS(K20)<0.995,"",TEXT(INT(ROUND(ABS(K20),2)),"[DBNum2]")&"元")&IF(RIGHT(TEXT(K20,".00"),2)*1=0,IF(ABS(K20)<0.005,"","整"),TEXT(IF(ABS(K20)<0.095,"",LEFT(RIGHT(TEXT(K20,".00"),2))),"[dbnum2]")&IF(LEFT(RIGHT(TEXT(K20,".00"),2))*1=0,"","角")&IF(RIGHT(TEXT(K20,".00"))*1=0,"整",TEXT(RIGHT(TEXT(K20,".00")),"[dbnum2]")&"分")))
公式五：
=IF(K20=0,"",IF(ABS(K20)<1,"",TEXT(TRUNC(ABS(K20)),"[DBNum2]")&"元")&IF(RIGHT(TRUNC(K20*100),2)*1=0,IF(ABS(K20)<0.01,"","整"),IF(ABS(K20)<0.1,"",TEXT(RIGHT(TRUNC(K20*10)),"[dbnum2]"))&IF(RIGHT(TRUNC(K20*10))*1=0,"","角")&IF(RIGHT(TRUNC(K20*100))*1=0,"整",TEXT(RIGHT(TRUNC(K20*100)),"[dbnum2]")&"分")))
公式六：
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(K20<0,"负",)&TEXT(INT(ABS(K20)),"[dbnum2];; ")&TEXT(MOD(ABS(K20)*100,100),"[>9][dbnum2]圆0角0分;[=0]圆整;[dbnum2]圆零0分"),"零分","整")," 圆零",)," 圆",)
公式七：
=IF(ABS(A2)=0,"",IF(A2<0,"负",)&IF(INT(ABS(A2))=ABS(A2),TEXT(INT(ABS(A2)),"[dbnum2]")&"元整",TEXT(INT(ABS(A2)),"[dbnum2]")&"元")&IF(INT(ABS(A2)*10)-INT(ABS(A2))*10,TEXT(INT(ABS(A2)*10)-INT(ABS(A2))*10,"[dbnum2]")&"角",IF(INT(ABS(A2))=ABS(A2),,IF(ABS(A2)<0.1,,"零")))&IF(ROUND(ABS(A2)*100-INT(ABS(A2)*10)*10,),TEXT(ROUND(ABS(A2)*100-INT(ABS(A2)*10)*10,),"[dbnum2]")&"分",""))