資源描述：
《金額小寫轉(zhuǎn)大寫.doc》由會員上傳分享，免費在線閱讀，更多相關(guān)內(nèi)容在教育資源-天天文庫。
1、EXCEL中將數(shù)字轉(zhuǎn)換為人民幣大寫鑒于EXCEL本身提供將數(shù)字轉(zhuǎn)換為大寫表示的功能根本不能正常應(yīng)用在實際投標或財務(wù)應(yīng)用之中，所以要自己建設(shè)，暫時找到了三種實現(xiàn)途徑，經(jīng)過測試均功能正常。方法1，通過在EXCEL表格框（例如在“B1”單元）中直接輸入以下公式：=IF(A1<0,"金額為負無效",(IF(OR(A1=0,A1=""),"(人民幣)零元",IF(A1<1,"(人民幣)",TEXT(INT(A1),"[dbnum2](人民幣)G/通用格式")&"元"))))&IF((INT(A1*10)-INT(A1)*10)=0,IF(INT(A1*100)-INT(A1*10)*10=0,"","
2、零"),(TEXT(INT(A1*10)-INT(A1)*10,"[dbnum2]")&"角"))&IF((INT(A1*100)-INT(A1*10)*10)=0,"整",TEXT((INT(A1*100)-INT(A1*10)*10),"[dbnum2]")&"分")然后在“A1”中輸入數(shù)字，就可看到效果。方法2，通過VBA（宏）輸入轉(zhuǎn)換公式:點擊菜單“工具”->“宏”->“VisualBasic編輯器”,在編輯器窗口中，點擊菜單“插入”->“模塊”，在出現(xiàn)的窗口中輸入以下內(nèi)容：Functiondaxie(ByValNum)'人民幣中文大寫函數(shù)Application.VolatileTr
3、uePlace="分角元拾佰仟萬拾佰仟億拾佰仟萬"Dn="壹貳叁肆伍陸柒捌玖"D1="整零元零零零萬零零零億零零零萬"IfNum<0ThenFuHao="（負）"Num=Format(Abs(Num),"###0.00")*100IfNum>9999#Then:daxie="數(shù)字超出轉(zhuǎn)換范圍!!":ExitFunctionIfNum=0Then:daxie="零元零分":ExitFunctionNumA=Trim(Str(Num))NumLen=Len(NumA)ForJ=NumLenTo1Step-1'數(shù)字轉(zhuǎn)換過程temp=Val(Mid(NumA,NumLen-J+1,1))Iftemp
4、<>0Then'非零數(shù)字轉(zhuǎn)換NumC=NumC&Mid(Dn,temp,1)&Mid(Place,J,1)Else'數(shù)字零的轉(zhuǎn)換IfRight(NumC,1)<>"零"ThenNumC=NumC&Mid(D1,J,1)ElseSelectCaseJ'特殊數(shù)位轉(zhuǎn)換Case1NumC=Left(NumC,Len(NumC)-1)&Mid(D1,J,1)Case3,11NumC=Left(NumC,Len(NumC)-1)&Mid(D1,J,1)&"零"Case7IfMid(NumC,Len(NumC)-1,1)<>"億"ThenNumC=Left(NumC,Len(NumC)-1)&Mid(D1
5、,J,1)&"零"EndIfCaseElseEndSelectEndIfEndIfNextdaxie="(人民幣)"&FuHao&Trim(NumC)EndFunction然后切換回excel,在“A2”單元中輸入數(shù)字，在“B2”單元中輸入：“=DaXie(A2)”，就可看到效果。方法3，同樣是通過VBA公式，方法同上，公式如下：Functiondaxie1(moneyAsString)AsString'DimxAsString,yAsStringConstzimu=".sbqwsbqysbqwsbq"'定義位置代碼Constletter="sbqwy.zjf"'定義漢字縮寫Constupc
6、ase="零壹貳叁肆伍陸柒捌玖拾佰仟萬億圓整角分"'定義大寫漢字DimtempAsStringtemp=moneyIfInStr(temp,".")>0Thentemp=Left(temp,InStr(temp,".")-1)IfLen(temp)>16ThenMsgBox"數(shù)目太大，無法換算！請輸入一億億以下的數(shù)字",64,"錯誤提示":ExitFunction'只能轉(zhuǎn)換一億億元以下數(shù)目的貨幣！x=Format(money,"0.00")'格式化貨幣y=""Fori=1ToLen(x)-3y=y&Mid(x,i,1)&Mid(zimu,Len(x)-2-i,1)NextIfRight(x,
7、3)=".00"Theny=y&"z"'***元整Elsey=y&Left(Right(x,2),1)&"j"&Right(x,1)&"f"'*元*角*分EndIfy=Replace(y,"0q","0")'避免零千(如：40200肆萬零千零貳佰)y=Replace(y,"0b","0")'避免零百(如：41000肆萬壹千零佰)y=Replace(y,"0s","0")'避免零十(如：204貳佰零拾零肆)Do
