披肩外套男:Excel?中小写数字转换为大写金额的方法
来源:百度文库 编辑:偶看新闻 时间:2024/05/05 13:57:05
1。A6中是小写的数据,在B6中用公式转换大写,公式如下:
=IF(A6<0,"(负)"&IF(ABS(A6)<1,"",TEXT(INT(ABS(A6)),"[dbnum2]")&"元")&IF(MOD(INT(ABS(A6*10)),10)=0,IF(INT(ABS(A6))*MOD(INT(ABS(A6*100)),10)=0,"","零"),TEXT(MOD(INT(ABS(A6*10)),10),"[dbnum2]")&"角")&IF(MOD(INT(ABS(A6*100)),10)=0,"整",TEXT(MOD(INT(ABS(A6*100)),10),"[dbnum2]")&"分"),IF(A6=0,"零",IF(A6<1,"",TEXT(INT(A6),"[dbnum2]")&"元")&IF(MOD(INT(A6*10),10)=0,IF(INT(A6)*MOD(INT(A6*100),10)=0,"","零"),TEXT(MOD(INT(A6*10),10),"[dbnum2]")&"角")&IF(MOD(INT(A6*100),10)=0,"整",TEXT(MOD(INT(A6*100),10),"[dbnum2]")&"分")))
说明:A6中是小写的数据。公式中已考虑负数与零数的问题。但没有考虑四舍五入,所以如小数三位的话,请在数据原始区域先做好四舍五入处理。如一定要在公式中一并处理,公式如下:
=IF(ROUND(A3,2)<0,"(负)"&IF(ABS(ROUND(A3,2))<1,"",TEXT(INT(ABS(ROUND(A3,2))),"[dbnum2]")&"元")&IF(MOD(INT(ABS(ROUND(A3,2)*10)),10)=0,IF(INT(ABS(ROUND(A3,2)))*MOD(INT(ABS(ROUND(A3,2)*100)),10)=0,"","零"),TEXT(MOD(INT(ABS(ROUND(A3,2)*10)),10),"[dbnum2]")&"角")&IF(MOD(INT(ABS(ROUND(A3,2)*100)),10)=0,"整",TEXT(MOD(INT(ABS(ROUND(A3,2)*100)),10),"[dbnum2]")&"分"),IF(ROUND(A3,2)=0,"零",IF(ROUND(A3,2)<1,"",TEXT(INT(ROUND(A3,2)),"[dbnum2]")&"元")&IF(MOD(INT(ROUND(A3,2)*10),10)=0,IF(INT(ROUND(A3,2))*MOD(INT(ROUND(A3,2)*100),10)=0,"","零"),TEXT(MOD(INT(ROUND(A3,2)*10),10),"[dbnum2]")&"角")&IF(MOD(INT(ROUND(A3,2)*100),10)=0,"整",TEXT(MOD(INT(ROUND(A3,2)*100),10),"[dbnum2]")&"分")))
2。用自定义函数转换。代码如下:
Public Function gly1126(n)
dx = Replace(Application.Text(Round(n + 0.00000001, 2), "[DBnum2]"), ".", "元")
dx = IIf(Left(Right(dx, 3), 1) = "元", Left(dx, Len(dx) - 1) & "角" & Right(dx, 1) & "分", IIf(Left(Right(dx, 2), 1) = "元", dx & "角整", IIf(dx = "零", "", dx & "元整")))
gly1126 = Replace(Replace(Replace(Replace(dx, "零元零角", ""), "零元", ""), "零角", "零"), "-", "(负)")
End Function
说明:这代码是网友gly1126提供的,请把代码复制到VB代码窗口的相应模块中。自定义函数的使用方法与EXCEL函数的使用方法一样的,请参照使用。
=IF(A6<0,"(负)"&IF(ABS(A6)<1,"",TEXT(INT(ABS(A6)),"[dbnum2]")&"元")&IF(MOD(INT(ABS(A6*10)),10)=0,IF(INT(ABS(A6))*MOD(INT(ABS(A6*100)),10)=0,"","零"),TEXT(MOD(INT(ABS(A6*10)),10),"[dbnum2]")&"角")&IF(MOD(INT(ABS(A6*100)),10)=0,"整",TEXT(MOD(INT(ABS(A6*100)),10),"[dbnum2]")&"分"),IF(A6=0,"零",IF(A6<1,"",TEXT(INT(A6),"[dbnum2]")&"元")&IF(MOD(INT(A6*10),10)=0,IF(INT(A6)*MOD(INT(A6*100),10)=0,"","零"),TEXT(MOD(INT(A6*10),10),"[dbnum2]")&"角")&IF(MOD(INT(A6*100),10)=0,"整",TEXT(MOD(INT(A6*100),10),"[dbnum2]")&"分")))
说明:A6中是小写的数据。公式中已考虑负数与零数的问题。但没有考虑四舍五入,所以如小数三位的话,请在数据原始区域先做好四舍五入处理。如一定要在公式中一并处理,公式如下:
=IF(ROUND(A3,2)<0,"(负)"&IF(ABS(ROUND(A3,2))<1,"",TEXT(INT(ABS(ROUND(A3,2))),"[dbnum2]")&"元")&IF(MOD(INT(ABS(ROUND(A3,2)*10)),10)=0,IF(INT(ABS(ROUND(A3,2)))*MOD(INT(ABS(ROUND(A3,2)*100)),10)=0,"","零"),TEXT(MOD(INT(ABS(ROUND(A3,2)*10)),10),"[dbnum2]")&"角")&IF(MOD(INT(ABS(ROUND(A3,2)*100)),10)=0,"整",TEXT(MOD(INT(ABS(ROUND(A3,2)*100)),10),"[dbnum2]")&"分"),IF(ROUND(A3,2)=0,"零",IF(ROUND(A3,2)<1,"",TEXT(INT(ROUND(A3,2)),"[dbnum2]")&"元")&IF(MOD(INT(ROUND(A3,2)*10),10)=0,IF(INT(ROUND(A3,2))*MOD(INT(ROUND(A3,2)*100),10)=0,"","零"),TEXT(MOD(INT(ROUND(A3,2)*10),10),"[dbnum2]")&"角")&IF(MOD(INT(ROUND(A3,2)*100),10)=0,"整",TEXT(MOD(INT(ROUND(A3,2)*100),10),"[dbnum2]")&"分")))
2。用自定义函数转换。代码如下:
Public Function gly1126(n)
dx = Replace(Application.Text(Round(n + 0.00000001, 2), "[DBnum2]"), ".", "元")
dx = IIf(Left(Right(dx, 3), 1) = "元", Left(dx, Len(dx) - 1) & "角" & Right(dx, 1) & "分", IIf(Left(Right(dx, 2), 1) = "元", dx & "角整", IIf(dx = "零", "", dx & "元整")))
gly1126 = Replace(Replace(Replace(Replace(dx, "零元零角", ""), "零元", ""), "零角", "零"), "-", "(负)")
End Function
说明:这代码是网友gly1126提供的,请把代码复制到VB代码窗口的相应模块中。自定义函数的使用方法与EXCEL函数的使用方法一样的,请参照使用。
在EXCEL中将数字转换为英文大写的方法
Excel表如何将输入的小写数字自动转换成人民币大写金额?
在EXCEL中如果将小写金额转换为大写金额
请问:EXCEL软件里,如果想把所有的以元为金额单位的数字转换成以万元为金额单位的数字,如何操作?
金额大写数字
数字金额的大写怎么写?为什么要写成这样呢?
在Excel同一单元格中,如何能将所输入的数字直接变换成大写金额格式?
求助:小写金额转换成大写金额
美元金额大写怎么写
10376.60这个金额用大写的怎么写
用exel输入数字如何转换为大写
100 586 379.24元转换为大写数字
EXCEL中如何把已转换成文本的数字,再转换为数字
让EXCEL自动生成大写金额
Excel 邮件合并 大写金额问题
怎样将Excel中的数字变成汉字大写的?
谁知道金额的大写?急用
excel有没有公式可以把小写阿拉伯数字转换成中文大写数字
数字0到9的大写怎么写?
关于以下数字在支票上的大写怎么写?
在Excel的使用中,数字生成大写数字的快捷办法?
关于 MS Excel 中文大写数字
请问EXCEL的单元格文本格式如何转换为数字格式
请问一个付款凭证里 多借多贷 大写的金额如何写呢 只写现金支出吗