如何让excel表格中的数字转换成阿拉伯数字的元角分,网上的一些公式都看不懂,请高手指点.

Question

最好说出公式的来历和用途,谢谢!

Answer 1

自己写了下，基本可以用的，如果数字在A1，输入
=IF(TRUNC(A1)>0,TRUNC(A1)&"元","")&ROUNDDOWN(10*A1-10*TRUNC(A1),0)&"角"&IF(ROUNDDOWN(A1,2)-ROUNDDOWN(A1,1)=0,"",(ROUNDDOWN(A1*100,0)-10*ROUNDDOWN(A1*10,0))&"分")

Answer 2

光标锁定单元格，点右键——设置单元格格式——数字分类下——点击数值——右列，小数位置定为“2”——确定。试试吧

Answer 3

=IF(B4<0,"无效数值",IF(B4=0,"",IF(B4<1,"",TEXT(INT(B4),"[dbnum2]")&"元")&IF(INT(B4*10)-INT(B4)*10=0,IF(INT(B4)*(INT(B4*100)-INT(B4*10)*10)=0,"","零"),IF(AND((INT(B4)-INT(B4/10)*10)=0,INT(B4)>0),"零"&TEXT(INT(B4*10)-INT(B4)*10,"[dbnum2]")&"角",TEXT(INT(B4*10)-INT(B4)*10,"[dbnum2]")&"角"))&IF((INT(B4*100)-INT(B4*10)*10)=0,"整",TEXT(INT(B4*100)-INT(B4*10)*1,"[dbnum2]")&"0,"[dbnum2]")&"分")))
[dbnum2]是将数字转换成大写的数字,利用INT函数取整,假如*10减去*100正好是前一个数字,依次类推追问

可以做个表格设置一下发给我看吗，邮箱：[email protected],非常谢谢！