關於EXCEL中轉換中文大寫貨幣格式

轉載

一、通過EXCEL原有函數和自定義格式轉化1、這個看起來比較煩瑣，不過終究達到了效果。=TEXT(TRUNC(ROUND(待轉換數字或單元格,2),0),"[DBNUM2]G/通用格式"&"元")&(IF((ROUND(待轉換數字或單元格,2)-TRUNC(ROUND(待轉換數字或單元格,2),0))<0.01,"",IF(MIDB(ROUND(待轉換數字或單元格,2),IF((ROUND(待轉換數字或單元格,2)-TRUNC(ROUND(待轉換數字或單元格,2),1))<0.01,(LEN(ROUND(待轉換數字或單元格,2))),(LEN(ROUND(待轉換數字或單元格,2))-1)),1)="0","零",TEXT(MIDB(ROUND(待轉換數字或單元格,2),IF((ROUND(待轉換數字或單元格,2)-TRUNC(ROUND(待轉換數字或單元格,2),1))<0.01,(LEN(ROUND(待轉換數字或單元格,2))),(LEN(ROUND(待轉換數字或單元格,2))-1)),1),"[DBNUM2]G/通用格式")&"角")))&(IF((ROUND(待轉換數字或單元格,2)-TRUNC(ROUND(待轉換數字或單元格,2),1))<0.01,"整",TEXT(RIGHT(ROUND(待轉換數字或單元格,2),1),"[DBNUM2]G/通用格式")&"分"))2、這個是最簡潔的函數實現方式=IF(待轉換數字或單元格<0,"負","")&IF(TRUNC(ROUND(待轉換數字或單元格,2))=0,"",TEXT(TRUNC(ABS(ROUND(待轉換數字或單元格,2))),"[DBNum2]")&"元")&IF(ISERR(FIND(".",ROUND(待轉換數字或單元格,2))),"",TEXT(RIGHT(TRUNC(ROUND(待轉換數字或單元格,2)*10)),"[DBNum2]"))&IF(ISERR(FIND(".0",TEXT(待轉換數字或單元格,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(待轉換數字或單元格,2),3))=".",TEXT(RIGHT(ROUND(待轉換數字或單元格,2)),"[DBNum2]")&"分","整")3、其他函數=IF(ISNUMBER(A1),IF(TRUNC(A1)=0,IF(AND(MID(TEXT(TRUNC(A1,2),"0.00"),LEN(TEXT (TRUNC(A1,2),"0.00"))-1,1)="0",RIGHT(TEXT(TRUNC(A1,2),"0.00"),1)="0"),"零元",""),TEXT(TRUNC(A1),"[DBNUM2]")&"元")&(IF(MID(TEXT(TRUNC(A1,2), "0.00"),LEN(TEXT(TRUNC(A1,2),"0.00"))-1,1)="0",IF(OR(TRUNC(A1)=0,RIGHT(TEXT (TRUNC(A1,2),"0.00"),1)="0"),"","零"),TEXT(MID(A1,LEN(TEXT(TRUNC(A1,2), "0.00"))-1,1),"[DBNUM2]")&"角"))&(IF(RIGHT(TEXT(TRUNC(A1,2), "0.00"),1)="0","",TEXT(RIGHT(TEXT(TRUNC(A1,2),"0.00"),1),"[DBNUM2]")& "分")),IF(A1="","","不是有效金額,請重新輸入!"))二、通過自定義函數轉化通過自定義函數，以後在EXCEL中就可以直接調用函數MoneyTrans()來實現了。如果熟悉VBA或者熟悉自定義函數的朋友可以嘗試一下，關於自定義函數可參考Excel自定義函數實例剖析(轉).以下是數字金額轉換成中文大寫金額自定義函數,可以在VBA模塊中添加就可以了。Public Function MoneyTrans(Money As Currency) As StringOn Error GoTo DoerrDim CN(9) As StringDim CU(15) As StringDim Temp As String, strNum As StringDim CM As StringDim tFirst As String, tEnd As StringDim i As Long, j As Long, k As LongCN(0) = "零"CN(1) = "壹"CN(2) = "貳"CN(3) = "叄"CN(4) = "肆"CN(5) = "伍"CN(6) = "陸"CN(7) = "柒"CN(8) = "捌"CN(9) = "玖"" CU(0) = "分"" CU(1) = "角"CU(0) = "元"CU(1) = "拾"CU(2) = "佰"CU(3) = "仟"CU(4) = "萬"CU(5) = "拾"CU(6) = "佰"CU(7) = "仟"CU(8) = "億"CU(9) = "拾"CU(10) = "佰"CU(11) = "仟"If Money = 0 ThenCM = "零元整"GoTo CompleteEnd IfstrNum = Trim(Str(FormatCurrency(Money, 2, vbTrue, vbFalse, vbFalse)))If Left(strNum, 1) = "-" ThentFirst = "負"strNum = Right(strNum, Len(strNum) - 1)ElsetFirst = ""End Ifi = InStrRev(strNum, ".")If i <> 0 ThenTemp = Right(strNum, i)If Len(strNum) - i = 1 Then Temp = Temp + "0"CM = CN(CInt(Left(Right(Temp, 2), 1))) + "角" + CN(CInt(Right(Temp, 1))) + "分"tEnd = ""strNum = Left(strNum, i - 1)ElsetEnd = "整"End Ifi = 0For j = Len(strNum) To 1 Step -1k = CInt(Right(Left(strNum, j), 1))If k = 0 ThenIf i <> 0 And i <> 4 And i <> 8 ThenCM = CN(k) + CMElseCM = CN(k) + CU(i) + CMEnd IfElseCM = CN(k) + CU(i) + CMEnd If" CM = CN(k) + CU(i) + CMi = i + 1Next jCM = tFirst + CM + tEndCM = Replace(CM, "零零", "零")CM = Replace(CM, "零零", "零")CM = Replace(CM, "億零萬零元", "億元")CM = Replace(CM, "億零萬", "億零")CM = Replace(CM, "萬零元", "萬元")CM = Replace(CM, "零億", "億")CM = Replace(CM, "零萬", "萬")CM = Replace(CM, "零元", "元")CM = Replace(CM, "零零", "零")CM = Replace(CM, "零零", "零") "重複替換一次Complete:Gerr = 0 "操作成功,無錯誤發生MoneyTrans = CMExit FunctionDoerr:Gerr = -1 "未知錯誤Errexit:MoneyTrans = ""End Function
推薦閱讀：