金额大写的转换.doc

金额大写的转换假设A1单元格为原始数据，即小写数字。公式法一：=IF(A1=0,零元整,IF(A10,负,)&IF(INT(ABS(A1),TEXT(INT(ABS(A1),dbnum2)&元,)&IF(INT(ABS(A1)*10)-INT(ABS(A1)*10,TEXT(INT(ABS(A1)*10)-INT(ABS(A1)*10,dbnum2)&角,IF(INT(ABS(A1)=ABS(A1),零)&IF(ROUND(ABS(A1)*100-INT(ABS(A1)*10)* 10,) , TEXT(ROUND(ABS(A1)*100-INT(ABS(A1)*10)*10,),dbnum2)&分,整)公式法二：=IF(A1=5),INT(A1*10)-INT(A1)*10 1,INT(A1*10)-INT(A1)*10),DBNum2$-804G/通用格式)&角)&IF(OR(INT(A1*100)-INT(A1*10)*10=0,(IF(INT(A1*1000)-INT(A1*100)*10=5,CEILING(A1* 100,1)-INT(A1*10)*10=10,FALSE),整,(IF(INT(A1*1000)-INT(A1*100)*10=5,(IF(CEILING(A1 *100,1)-INT(A1*10)*10=10,(TEXT(CEILING(A1*100,1)-INT(A1*10)*10,DBNum2$-804G/通用格式)&分),(TEXT(INT(A1*100)-INT(A1*10)*10,DBNum2$-804G/通用格式)&分),(￥,FIXED(A1,2,TRUE),元) 四、VBA代码法（自定义函数）Public Function BigNum(xiaoxie As Currency)Application.VolatileDim fuhao As Stringfuhao = If xiaoxie 0 Thenxiaoxie = -xiaoxiefuhao = 负End IfIf xiaoxie = 0 ThenBigNum = 零元整ElseConst cNum = 零壹贰叁肆伍陆柒捌玖-万仟佰拾亿仟佰拾万仟佰拾元角分Const cCha = 零仟零佰零拾零零零零零亿零万零元亿万零角零分零整-零零零零零亿万元亿零整整BigNum = sNum = Trim(Str(Int(Round(xiaoxie, 2) * 100)For i = 1 To Len(sNum)BigNum = BigNum Mid(cNum, (Mid(sNum, i, 1) 1, 1) Mid(cNum, 26 - Len(sNum) i, 1)Next iFor i = 0 To 11BigNum = Replace(BigNum, Mid(cCha, i * 2 1, 2), Mid(cCha, i 26, 1)Next iBigNum = fuhao BigNumEnd IfEnd Function上面四种办法中，方法一和方法四等价；方法二、三和方法一、四的区别只是整数部分为零时，方法一、四不显示“零元”，而直接显示“角分”；方法三在方法2的基础上加上了数字显示，下表是不同数据在四种方法中的显示结果： 原始数据方法一方法二方法三方法四0.12壹角贰分零元壹角贰分零元壹角贰分(￥0.12元)壹角贰分1.02壹元零贰分壹元零角贰分壹元贰分(￥1.02元)壹元零贰分1001001壹佰万壹仟零壹元整壹佰万壹仟零壹元整壹佰万壹仟零壹元整(￥1001001.00元)壹佰万壹仟零