在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 0 Then qianzhui = 负 Myinput = Int(Abs(Myinput) * 100 + 0.5) 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),[DBNum2]0角0分),零角零分,整),零分,),零角,零) 答案二： =人民币TEXT(INT(A1),[DBNum2])元SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(TEXT(A1,0.00),2),[DBNum2]0角0分),零角零分,整),零分,整),零