大小写转换
- 格式：doc
- 大小：33.00 KB
- 文档页数：4
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(ROU ND(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(RO UND(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函数的使用方法一样的，请参照使用。
Function dx(q)ybb = Round(q * 100) '将输入的数值扩大100倍，进行四舍五入y = Int(ybb / 100) '截取出整数部分j = Int(ybb / 10) - y * 10 '截取出十分位f = ybb - y * 100 - j * 10 '截取出百分位zy = Application.WorksheetFunction.Text(y, "[dbnum2]") '将整数部分转为中文大写zj = Application.WorksheetFunction.Text(j, "[dbnum2]") '将十分位转为中文大写zf = Application.WorksheetFunction.Text(f, "[dbnum2]") '将百分位转为中文大写dx = zy & "元" & "整"d1 = zy & "元"If f <> 0 And j <> 0 Thendx = d1 & zj & "角" & zf & "分"If y = 0 Thendx = zj & "角" & zf & "分"End IfEnd IfIf f = 0 And j <> 0 Thendx = d1 & zj & "角" & "整"If y = 0 Thendx = zj & "角" & "整"End IfEnd IfIf f <> 0 And j = 0 Thendx = d1 & zj & zf & "分"If y = 0 Thendx = zf & "分"End IfEnd IfIf q = "" Thendx = 0 '如没有输入任何数值为0End IfEnd FunctionFunction JEZH(X As Range)If X >= 1 ThenIf Int(X) = X Or Round(X, 2) = Int(X) ThenJEZH = Application.WorksheetFunction.Text(Int(X), "[DBNUM2]") & "元"ElseIf Int(X * 10) = X * 10 Or Int(X * 10) = Round(X, 2) * 10 ThenJEZH = Application.WorksheetFunction.Text(Int(X), "[DBNUM2]") & "元" & Application.WorksheetFunction.Text(Right(Round(X, 2), 1), "[DBNUM2]") & "角"ElseJEZH = Application.WorksheetFunction.Text(Int(X), "[DBNUM2]") & "元" & Application.WorksheetFunction.Text(Left(Right(Round(X, 2), 2), 1), "[DBNUM2]") & "角" & Application.WorksheetFunction.Text(Right(Round(X, 2), 1), "[DBNUM2]") & "分"End IfElseIf X = 0 ThenJEZH = Application.WorksheetFunction.Text(Int(X), "[DBNUM2]") & "元"ElseIf X < 1 And X > 0 ThenIf Int(X * 10) = X * 10 ThenJEZH = Application.WorksheetFunction.Text(Right(X, 1), "[DBNUM2]") & "角"ElseJEZH = Application.WorksheetFunction.Text(Left(Right(Round(X, 2), 2), 1), "[DBNUM2]") & "角" & Application.WorksheetFunction.Text(Right(Round(X, 2), 1), "[DBNUM2]") & "分"End IfElseIf Int(X) = X Or Round(Abs(X), 2) = Int(Abs(X)) ThenJEZH = "负" & Application.WorksheetFunction.Text(Int(Abs(X)), "[DBNUM2]") & "元"ElseIf Int(X * 10) = X * 10 ThenJEZH = "负" & Application.WorksheetFunction.Text(Int(Abs(X)), "[DBNUM2]") & "元" & Application.WorksheetFunction.Text(Right(X, 1), "[DBNUM2]") & "角"ElseJEZH = "负" & Application.WorksheetFunction.Text(Int(Abs(X)), "[DBNUM2]") & "元" & Application.WorksheetFunction.Text(Left(Right(Round(X, 2), 2), 1), "[DBNUM2]") & "角" & Application.WorksheetFunction.Text(Right(Round(X, 2), 1), "[DBNUM2]") & "分"End IfEnd IfEnd Function。
