在Excel中根据小写的金额自动生成大写金额.doc

在Excel中根据小写的金额自动生成大写金额2008年05月24日 星期六 10:44在Excel中，我想根据小写的金额自动生成大写金额例一：问：如附件所示，我希望根据已有的小写金额让Excel自动生成规范的大写金额。 谢谢！ 附件：例表.人民币大小写之间的转换.xls答案一：=IF(ISNUMBER(A3),IF(INT(A3),TEXT(INT(A3),dbnum2)&元,)&IF(INT(A3*10)-INT(A3)*10,TEXT(INT(A3*10)-INT(A3)*10,dbnum2)&角,IF(INT(A3)=A3,IF(A30.1,零)&IF(ROUND(A3)*100-INT(A3*10)*10,),TEXT(ROUND(A3*100-INT(A3*10)*10,),dbnum2)&分,整),) 附件：例表.人民币大小写之间的转换2.xls答案二：给你一个,放在工具里： Function convert_digital_chinese(ByVal Myinput) Dim Temp, TempA, MyinputA, MyinputB, MyinputC Dim Place As String Dim J As Integer Place = 分角元拾佰仟万拾佰仟亿拾佰仟万 shuzi1 = 壹贰叁肆伍陆柒捌玖 shuzi2 = 整零元零零零万零零零亿零零零万 qianzhui = If Myinput 999999999999999# Then mychange = 数字太大了吧？ Exit Function End If If Myinput = 0 Then mychange = 零元零分 Exit Function End If MyinputA = Trim(Str(Myinput) shuzilong = Len(MyinputA) For J = 1 To shuzilong MyinputB = Mid(MyinputA, J, 1) & MyinputB Next For J = 1 To shuzilong Temp = Val(Mid(MyinputB, J, 1) If Temp = 0 Then MyinputC = Mid(shuzi2, J, 1) & MyinputC Else MyinputC = Mid(shuzi1, Temp, 1) & Mid(Place, J, 1) & MyinputC End If Next shuzilong = Len(MyinputC) For J = 1 To shuzilong - 1 If Mid(MyinputC, J, 1) = 零 Then Select Case Mid(MyinputC, J + 1, 1) Case 零, 元, 万, 亿, 整: MyinputC = Left(MyinputC, J - 1) & Mid(MyinputC, J + 1, 30) J = J - 1 End Select End If Next shuzilong = Len(MyinputC) For J = 1 To shuzilong - 1 If Mid(MyinputC, J, 1) = 亿 And Mid(MyinputC, J + 1, 1) = 万 Then MyinputC = Left(MyinputC, J) & Mid(MyinputC, J + 2, 30) Exit For End If Next mychange = qianzhui & Trim(MyinputC) End Function来源：/b/7702446.html?from=related例二：如何设公式将￥654654.36自动生成人民币陆拾伍万肆仟陆佰伍拾肆元叁角陆分答案一：=TEXT(INT(A1),DBNum2)&元&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(TEXT(A1,0.00),2),DBNum20角0分),零角零分,整),零分,),零角,零)答案二：=人民币&TEXT(INT(A1),DBNum2)&元&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(TEXT(A1,0.00),2),DBNum20角0分),零角零分,整),零分,整),零角,零)1、楼主需要在前面加上人民币2、如果没有分的话，角后面应该也加个“整”字这个我用过试了，不错，好用，呵呵来源：/dispbbs.asp?BoardID=38&ID=38088三、复制下列公式，然后“选择性粘贴”（以“文本”方式）到除“A1”单元格外的任意一个单元格中，比如“B1”单元格，然后在“A1”单元格中输入数字即可实现小写金额转大写金额的效果。特棒！为便于你对公式的理解，特附公式的解释于后。=IF(a10,负,)&IF(a1=0,IF(ABS(a1)0.995,TEXT(INT(ROUND(ABS(a1),2),dbnum2)&元)&IF(RIGHT(TEXT(a1,.00),2)=00,IF(ABS(a1)0.005,整),IF(ABS(a1)0.095,TEXT(VALUE(LEFT(RIGHT(TEXT(a1,.00),2),1),dbnum2)&IF(LEFT(RIGHT(TEXT(a1,.00),2),1)=0,角)&IF(RIGHT(TEXT(a1,.00),1)=0,整,TEXT(VALUE(RIGHT(TEXT(a1,.00),1),dbnum2)&分)难点解释：1.IF(ABS(a1)0.995,TEXT(INT(ROUND(ABS(a1),2),dbnum2)&元)如果整数部分刚好是“0”，则整数部分输出为空,否则输出整数部分2.IF(RIGHT(TEXT(a1,.00),2)=00,IF(ABS(a1)0.005,整),)如果是纯整数，则进一步判断是否为“0”的纯整数，若是则不输出“整”字，否则输出“整”字(补注：若是纯整数，“”号后的语句已经不用处理，即小数部分已不用考虑)3.IF(ABS(a1)-0.5%,负)&TEXT(INT(ABS(A2)+0.5%),dbnum2G/通用格式元;)&TEXT(RIGHT(FIXED(A2),2),dbnum20角0分;&IF(ABS(A2)1%,整,),零角,IF(ABS(A2)-0.5%,负)&TEXT(INT(FIXED(ABS(A2),dbnum2G/通用格式元;)&TEXT(RIGHT(FIXED(A2),2),dbnum20角0分;&IF(ABS(A2)1%,整,),零角,IF(ABS(A2)-0.5%,负)&TEXT(INT(FIXED(ABS(A2),dbnum2)&TEXT(RIGHT(FIXED(A2),2),dbnum2元0角0分;元&IF(ABS(A2)1%,整,),零角,IF(ABS(A2)-0.5%,负)&IF(ABS(A2)+0.5%1%,整,),零角,IF(ABS(A2)0,负)&TEXT(INT(ABS(A2)+0.5%),dbnum2G/通用格式元;)&TEXT(RIGHT(RMB(A2),2),9dbnum20角0分;&IF(A22dbnum20分;整),),零分,整) 167个字符 =SUBSTITUTE(SUBSTITUTE(IF(-RMB(A2),IF(A20,负)&TEXT(INT(ABS(A2)+0.5%),dbnum2G/通用