人民币大写函数

人民币大写转换函数Function N2RMB(M)y = Int(Round(100 * Abs(M)) / 100)j = Round(100 * Abs(M) + 0.00001) - y * 100f = (j / 10 - Int(j / 10)) * 10A = IIf(y < 1, "", Application.Text(y, "[DBNum2]") & "元")b = IIf(j > 9.5, Application.Text(Int(j / 10), "[DBNum2]") & "角", IIf(y < 1, "", IIf(f > 1, "零", "")))c = IIf(f < 1, "整", Application.Text(Round(f, 0), "[DBNum2]") & "分")N2RMB = IIf(Abs(M) < 0.005, "", IIf(M < 0, "负" & A & b & c, A & b & c))End Function另外一个函数：Function dx(M) '一条语句163个字符dx = Replace(Replace(Replace(Join(Application.Text(Split(Format(M, "0. 0 0;负 0. 0 0; ")), ["[DBnum2]"&{0,"","元0角;;元零","0分;;整"}]),a), "零元零", a), "零元", a), "零整", "整")End Function代码解析：代码中使用了Replace()、Join()、Split()、Format()等4个VBA 函数，以及1个工作表函数T ext()。

小写金额转换大写金额函数公式小写金额转换成大写金额是我们在日常生活和工作中经常会遇到的问题。

无论是在填写支票、填写发票、办理贷款还是进行财务报表的编制，都需要将小写金额转换成大写金额。

这个过程虽然简单，但却是非常重要的，因为任何一个错误都可能导致不必要的损失或麻烦。

在进行小写金额转换成大写金额时，我们通常使用的是中文的读法，即将数字转换成对应的汉字。

例如，将“123”转换成“壹佰贰拾叁”。

这种方式简单直观，容易理解。

但对于一些较大的金额，汉字的读法可能会比较复杂，需要一定的规律和技巧来进行转换。

在进行小写金额转换成大写金额时，我们可以按照以下规律进行转换：1. 将小写金额从右到左每四位一组进行分组，最后不足四位的补零。

例如，将“1234567890”分成“0001 2345 6789 0000”。

2. 对每一组数字进行转换，从高位到低位依次转换。

例如，对于“0001 2345 6789 0000”，先转换第一组“0001”，再转换第二组“2345”，以此类推。

3. 对于每一组数字，首先将高位的数字转换成对应的汉字，然后加上对应的单位。

例如，对于第一组“0001”，将“1”转换成“壹”，再加上“万”。

4. 对于每一组中的每一位数字，按照规定的读法进行转换。

例如，将“2345”转换成“贰仟叁佰肆拾伍”。

按照以上规律，我们可以将任意的小写金额转换成大写金额。

例如，将“1234567890”转换成“壹拾贰亿叁仟肆佰伍拾陆万柒仟捌佰玖拾元整”。

在实际操作中，我们可以编写一个函数来实现小写金额转换成大写金额。

这个函数可以接受一个小写金额作为输入，并返回对应的大写金额。

函数的实现可以分为以下几个步骤：1. 将小写金额按照每四位一组进行分组，并补零。

2. 对每一组数字进行转换，得到对应的汉字。

3. 将每一组数字转换后的汉字加上对应的单位。

4. 将每一组数字转换后的汉字连接起来，得到最终的大写金额。

通过以上步骤，我们可以编写一个简单而有效的小写金额转换成大写金额的函数。

excel金额大小写转换公式的用法Excel中经常需要使用到金额大小写转换公式对金额进行转换，金额大小写转换公式具体该如何使用呢?接下来是小编为大家带来的excel金额大小写转换公式的用法，供大家参考。

excel金额大小写转换公式用法（一）步骤1：数字转人民币大写形式的公式是：=IF(B10,金额为负无效,(IF(OR(B1=0,B1=),(人民币)零元,IF(B11,(人民币),TEXT(INT(B1),[dbnum2](人民币)G/通用格式)元)))),零),(TEXT(INT(B1*10)-INT(B1)*10,[dbnum2])角))整,TEXT((INT(B1*100)-INT(B1*10)*10),[dbnum2])分)步骤2：数字转人民币大写形式的公式是：=大写金额：,,IF(B1=0,,人民币0,负,)[dbnum2])元,)[dbnum2])角,IF(INT(ABS(B1))=ABS(B1),,IF(ABS(B1)0.1,,零)))[dbnum2])分,整)))excel金额大小写转换公式用法（二）步骤1：通过宏VBA自定义函数实现第一步：按下ALT+F11组合键，或者是单击工具宏Visual Basic编辑器，进入Visual Basic编程环境，单击插入菜单模块，复制下面代码，保存，退出关闭。

Function N2RMB(M)y = Int(Round(100 * Abs(M)) / 100)j = Round(100 * Abs(M) + 0.00001) - y * 100f = (j / 10 - Int(j / 10)) * 10A = IIf(y 1, , Application.Text(y, [DBNum2]) 元)b = IIf(j 9.5, Application.Text(Int(j / 10), [DBNum2]) 角, IIf(y 1, , IIf(f 1, 零, )))c = IIf(f 1, 整, Application.Text(Round(f, 0), [DBNum2]) 分)N2RMB = IIf(Abs(M) 0.005, , IIf(M 0, 负 b b c))End Function步骤2：返回excel操作界面，在B1单元格输入：= N2RMB (A1)，就可以将A1单元格的数字转为人民币大写，然后向下复制公式即可将全部的数字转为人民币大写。

excel中如何将数字变成大写金额的函数如何在Excel中将数字转化为大写金额在Excel中，我们经常需要将数字转化为大写金额，以便更直观地展示数值。

虽然Excel本身并没有提供直接将数字转化为大写金额的函数，但我们可以通过一些方法来实现这个功能。

本文将介绍两种常用的方法，分别是使用Excel的内置函数和使用自定义宏。

一、使用Excel的内置函数Excel提供了一个内置的函数——“人民币大写”函数，可以将数字转化为大写金额。

下面是使用该函数的步骤：步骤1：打开Excel并选择一个单元格，例如A1，用于输入要转化的数字。

步骤2：在另一个单元格中，例如B1，输入以下公式：=人民币大写(A1)步骤3：按下回车键，即可将A1单元格中的数字转化为大写金额。

需要注意的是，“人民币大写”函数只能将数字转化为中文大写金额，而不能转化为其他语言的大写金额。

如果需要转化为其他语言的大写金额，可以考虑使用自定义宏的方法。

二、使用自定义宏自定义宏是一种基于VBA（Visual Basic for Applications）语言的编程方式，可以在Excel中实现各种自定义功能。

下面是使用自定义宏将数字转化为大写金额的步骤：步骤1：按下Alt+F11键，打开VBA编辑器。

步骤2：在VBA编辑器中，选择“插入”菜单，然后选择“模块”。

步骤3：在新建的模块中，输入以下VBA代码：```Function ConvertToChineseNumber(ByVal MyNumber)Dim MyCurrency, DecimalPlace, CountReDim Place(9) As StringPlace(2) = " Thousand "Place(3) = " Million "Place(4) = " Billion "Place(5) = " Trillion "' Convert MyNumber to String, trimming extra spaces.MyNumber = Trim(CStr(MyNumber))' Check for valid input.If MyNumber = "" ThenConvertToChineseNumber = ""Exit FunctionEnd If' Convert MyNumber to Currency figures.ReDim DecimalPlace(9) As StringDecimalPlace(2) = " Hundred "DecimalPlace(3) = " Thousand "DecimalPlace(4) = " Million "DecimalPlace(5) = " Billion "' Convert MyNumber to Currency figures.MyNumber = Trim(CStr(MyNumber))' Remove leading zeros' DecimalPlace = GetVal(Trim(Mid(MyNumber, DecimalPlace + 1, 2)))Count = 1Do While MyNumber <> ""ReDim Preserve MyCurr(Count) As StringCount = Count + 1MyCurr(Count - 1) = GetTens(Trim(Mid(MyNumber, 1, 2)), MyNumber)If Len(MyNumber) > 2 ThenMyNumber = Trim(Mid(MyNumber, 3))ElseMyNumber = ""End IfLoopCount = Count - 1For DecimalPlace = Count To 1 Step -1MyCurrency = MyCurrency & " " & MyCurr(DecimalPlace) If DecimalPlace = 1 Then' -- If place is ten thousand and greater than 0 set nother name to RupeesIf Len(MyNumber) = 1 Then MyCurr(1) = " and " & MyCurr(1)MyCurrency = MyCurrency & Place(DecimalPlace)End IfNext DecimalPlaceConvertToChineseNumber = MyCurrencyEnd FunctionFunction GetTens(TensText, ByVal Amount)Dim Result As StringResult = "" ' Null out the temporary function value.If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...Select Case Val(TensText)Case 10: Result = "Ten"Case 11: Result = "Eleven"Case 12: Result = "Twelve"Case 13: Result = "Thirteen"Case 14: Result = "Fourteen"Case 15: Result = "Fifteen"Case 16: Result = "Sixteen"Case 17: Result = "Seventeen"Case 18: Result = "Eighteen"Case 19: Result = "Nineteen"Case ElseEnd SelectElse ' If value between 20-99...Select Case Val(Left(TensText, 1))Case 2: Result = "Twenty "Case 3: Result = "Thirty "Case 4: Result = "Forty "Case 5: Result = "Fifty "Case 6: Result = "Sixty "Case 7: Result = "Seventy "Case 8: Result = "Eighty "Case 9: Result = "Ninety "Case ElseEnd SelectResult = Result & GetDigit _(Right(TensText, 1)) ' Retrieve ones place. End IfGetTens = ResultEnd FunctionFunction GetDigit(Digit)Select Case Val(Digit)Case 1: GetDigit = "One"Case 2: GetDigit = "Two"Case 3: GetDigit = "Three"Case 4: GetDigit = "Four"Case 5: GetDigit = "Five"Case 6: GetDigit = "Six"Case 7: GetDigit = "Seven"Case 8: GetDigit = "Eight"Case 9: GetDigit = "Nine"Case Else: GetDigit = ""End SelectEnd Function```步骤4：按下Ctrl+S保存宏，并关闭VBA编辑器。

人民币金额小写转换为大写转换公式转换公式之一：=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)*(INT(A1*100)-INT(A1*10)*10 )=0,"","零"),TEXT(INT(A1*10)-INT(A1)*10,"[DBNum2]G/通用格式")&"角")&IF((INT(A1*100)-INT(A1*10)*10)=0,"整",TEXT((INT(A1*100)-INT(A1*10)*10),"[DBNum2]G/通用格式")&"分")))转换公式之二：=IF(B8=0,"",CONCATENATE(IF(INT(ABS(B8))=0,"",TEXT(INT(ABS(B8)),"[DB Num2]G/通用格式元")),IF(INT(MID(RIGHT(FIXED(ABS(B8),2,1),2),1,1))=0,IF(INT(MID(RIGHT (FIXED(B8,2,1),1),1,1))=0,"",IF(INT(ABS(B8))=0,"","零")),TEXT(INT(MID(RIGHT(FIXED(B8,2,1),2),1,1)),"[DBNum2]G/通用格式角")),IF(INT(MID(RIGHT(FIXED(B8,2,1),1),1,1))=0,"整",TEXT(INT(MID(RIGHT(FIXED(B8,2,1),1),1,1)),"[DBNum2]G/通用格式分"))))转换公式之四：=IF(ROUND(D1,2)<0,"无效数字",IF(ROUND(D1,2)=0,"零",IF(ROUND(D1,2)<1,"",TEXT(INT(ROUND(D1,2)),"[dbnum2]")&"元")&IF(INT(ROUND(D1,2)*10)-INT(ROUND(D1,2))*10=0,IF(INT(ROUND(D1,2)) *(INT(ROUND(D1,2)*100)-INT(ROUND(D1,2)*10)*10)=0,"","零"),TEXT(INT(ROUND(D1,2)*10)-INT(ROUND(D1,2))*10,"[dbnum2]")&"角")&IF((INT(ROUND(D1,2)*100)-INT(ROUND(D1,2)*10)*10)=0,"整",TEXT((INT(ROUND(D1,2)*100)-INT(ROUND(D1,2)*10)*10),"[dbnum2]")&"分")))=IF(E1=0,"人民幣:零元整","人民幣:"&SUBSTITUTE(SUBSTITUTE(IF(E1<0,"负","")&TEXT(TRUNC(ABS(ROUND(E1,2))),"[DBNum2]")&"元"&IF(ISERR(FIND(".",ROUND(E1,2))),"",TEXT(RIGHT(TRUNC(ROUND(E1,2)*1 0)),"[DBNum2]"))&IF(ISERR(FIND(".0",TEXT(E1,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(E1,2),3))=".",TEXT(RIGHT(ROUND(E1,2)),"[D BNum2]")&"分",IF(ROUND(E1,2)=0,"","整")),"零元零",""),"零元",""))附注——１、实际运用时只需将该公式（红色显示部分）复制并粘贴到需要的EXCEL文档中的单元格中即可。

人民币小写转换大写公式1、公式=IF(A1="","",IF(A1<0,"负",)&IF(INT(A1),TEXT(INT(ABS(A1)),"[dbnum2]")&"元",)&IF(INT(ABS(A1)*10)-INT(ABS(A1))*10,TEXT(INT(ABS(A1)*10)-INT(ABS(A1))*10,"[db num2]")&"角",IF(INT(ABS(A1))=ABS(A1),,IF(ABS(A1)<0.1,,"零")))&IF(ROUND(ABS(A1)*100-INT(ABS(A1)*10)*10,),TEXT(ROUND(ABS(A1)*100-INT(ABS(A1 )*10)*10,),"[dbnum2]")&"分","整"))2、公式=SUBSTITUTE(SUBSTITUTE(TEXT(TRUNC(FIXED(A1)),"[dbnum2]G/通用格式元;负[dbnum2]G/通用格式元;"&IF(A1>-0.5%,,"负"))&TEXT(RIGHT(FIXED(A1),2),"[dbnum2]0角0分;;"&IF(ABS(A1)>1%,"整",)),"零角",IF(ABS(A1)<1,,"零")),"零分","整")3、公式=TEXT(RMB(ROUND(A1,)),"[=]g;"&TEXT(INT(ABS(ROUND(A1,))+0.5%),"[dbnum2]G/通用格式元;;")&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(RMB(ROUND(A1,)),2),"[dbnum2]0角0分;;整"),"零角",IF(ROUND(A1,)^2<1,,"零")),"零分","整"))4、公式=IF(M9<0,"负","")&TEXT(TRUNC(ABS(ROUND(M9,2))),"[DBNum2]")&"元"&IF(ISERR(FIND(".",ROUND(M9,2))),"",TEXT(RIGHT(TRUNC(ROUND(M9,2)*10)),"[DBNum2] "))&IF(ISERR(FIND(".0",TEXT(M9,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(M9,2),3))=".",TEXT(RIGHT(ROUND(M9,2)),"[DBNum2]")&"分","整")5. 公式=SUBSTITUTE(IF(-RMB(A2),IF(A2>0,,"负")&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A2),2),"[>9][dbnum2]0角0分;"&IF(A2^2<1,,0)&"[>][dbnum2]0分;整"),),"零分","整")6. 公式=SUBSTITUTE(SUBSTITUTE(IF(-RMB(A2),IF(A2>0,,"负")&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A2),2),"[dbnum2]0角0分;;整"),),"零角",IF(A2^2<1,,"零")),"零分","整")7. 公式="大写人民币:"&IF(TRUNC(A1)=0,"",TEXT(TRUNC(A1),"[DBNum2]G/通用格式")&"元")&IF(TRUNC(A1*10)-TRUNC(A1*10,-1)=0,"",TEXT(TRUNC(A1*10)-TRUNC(A1*10,-1),"[DBNu m2]G/通用格式")&"角")&IF(TRUNC(A1*100)-TRUNC(A1*100,-1)=0,"",TEXT(TRUNC(A1*100)-TRUNC(A1*100,-1),"[ DBNum2]G/通用格式")&"分")&IF(TRUNC(A1*100)-TRUNC(A1*100,-1)=0，"整"，"")8、公式=SUBSTITUTE(SUBSTITUTE(IF(A1<0,"负","")&TEXT(TRUNC(ABS(ROUND(A1,2))),"[DBNum2]")&"元"&IF(ISERR(FIND(".",ROUND(A1,2))),"",TEXT(RIGHT(TRUNC(ROUND(A1,2)*10)),"[DBNum2] "))&IF(ISERR(FIND(".0",TEXT(A1,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(A1,2),3))=".",TEXT(RIGHT(ROUND(A1,2)),"[DBNum2]")&"分",IF(ROUND(A1,2)=0,"","整")),"零元零",""),"零元","")9、公式=CONCATENATE(IF(A1<0,"负",""),TEXT(IF(TRUNC(A1)=0,"",TRUNC(ABS(A1))),"[DBNum2]"),IF(INT(TRUNC(A1))=0,"", "元"),TEXT(IF(OR(ABS(A1)<0.1,TRUNC(A1)=A1),"",RIGHT(TRUNC(A1*10),1)),"[DBNum2]"),IF (RIGHT(TRUNC(A1*10),1)="0","","角"),TEXT(IF(RIGHT(TRUNC(A1*100),1)="0","",RIGHT(TRUNC(A1*100),1)),"[DBNum2]"),IF( RIGHT(TRUNC(A1*100),1)="0","","分"))10、公式=IF(A1=0,"",IF(ABS(A1)<0.995,"",TEXT(INT(ROUND(ABS(A1),2)),"[DBNum2]")&"元")&IF(RIGHT(TEXT(A1,".00"),2)*1=0,IF(ABS(A1)<0.005,"","整"),TEXT(IF(ABS(A1)<0.095,"",LEFT(RIGHT(TEXT(A1,".00"),2))),"[dbnum2]")&IF(LEFT(R IGHT(TEXT(A1,".00"),2))*1=0,"","角")&IF(RIGHT(TEXT(A1,".00"))*1=0,"整",TEXT(RIGHT(TEXT(A1,".00")),"[dbnum2]")&"分")))11、公式=IF(A1=0,"",IF(ABS(A1)<1,"",TEXT(TRUNC(ABS(A1)),"[DBNum2]")&"元")&IF(RIGHT(TRUNC(A1*100),2)*1=0,IF(ABS(A1)<0.01,"","整"),IF(ABS(A1)<0.1,"",TEXT(RIGHT(TRUNC(A1*10)),"[dbnum2]"))&IF(RIGHT(TRUNC(A1*10) )*1=0,"","角")&IF(RIGHT(TRUNC(A1*100))*1=0,"整",TEXT(RIGHT(TRUNC(A1*100)),"[dbnum2]")&"分")))12.公式=IF(a1<0,"负","")&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(V ALUE(LEFT(RIGHT(TEXT(a1,".00"),2),1)),"[dbnum2]"))&IF(LEFT(RIGHT(T EXT(a1,".00"),2),1)="0","","角")&IF(RIGHT(TEXT(a1,".00"),1)="0","整",TEXT(V ALUE(RIGHT(TEXT(a1,".00"),1)),"[dbnum2]")&"分")))13、公式=IF(H14=0,"",CONCATENATE(IF(INT(ABS(H14))=0,"",TEXT(INT(ABS(H14)),"[DBNum2]G/通用格式元")),IF(INT(MID(RIGHT(FIXED(ABS(H14),2,1),2),1,1))=0,IF(INT(MID(RIGHT(FIXED(H14,2 ,1),1),1,1))=0,"",IF(INT(ABS(H14))=0,"","零")),TEXT(INT(MID(RIGHT(FIXED(H14,2,1),2),1,1)),"[DBNum2]G/通用格式角")),IF(INT(MID(RIGHT(FIXED(H14,2,1),1),1,1))=0,"整",TEXT(INT(MID(RIGHT(FIXED(H14,2,1),1),1,1)),"[DBNum2]G/通用格式分"))))14. 公式=IF(I17<0,"金额为负无效",IF(OR(I17=0,I17=""),"零元整",IF(I17<1,"",TEXT(INT(I17),"[DBNum2]G/通用格式")&"元")&IF(INT(I17*10)-INT(I17)*10=0,IF(INT(I17)*(INT(I17*100)-INT(I17*10)*10)=0,"","零"),TEXT(INT(I17*10)-INT(I17)*10,"[DBNum2]G/通用格式")&"角")&IF((INT(I17*100)-INT(I17*10)*10)=0,"整",TEXT((INT(I17*100)-INT(I17*10)*10),"[DBNum2]G/通用格式")&"分")))15. 公式=IF(ROUND(A1,2)=0,"",IF(ROUND(ABS(A1),2)>=1,TEXT(INT(ROUND(ABS(A1),2)),"[DBNum2] ")&"元","")&IF(RIGHT(TEXT(A1,".00"),2)*1=0,"整",IF(RIGHT(TEXT(A1,".00"),4)*1>=1,IF(RIGHT(TEXT(A1,".00"),2)*1>9,"","零"),IF(ROUND(ABS(A1),2)>=1,"零",""))&IF(RIGHT(TEXT(A1,".00"),2)*1>9,TEXT(LEFT(RIGHT(TEXT(A1,".00"),2)),"[DBNum 2]")&"角","")&IF(RIGHT(TEXT(A1,".00"))*1>0,TEXT(RIGHT(TEXT(A1,".00")),"[DBNum2]")&"分","整")))16. 公式="大写金额："&IF(ROUND(A1,2)<0,"无效数值",IF(ROUND(A1,2)=0,"零",IF(ROUND(A1,2)<1,"",TEXT(INT(ROUND(A1,2)),"[dbnum2]")&"元")&IF(INT(ROUND(A1,2)*10)-INT(ROUND(A1,2))*10=0,IF(INT(ROUND(A1,2))*(INT(ROUND(A1,2)*100)-INT(ROUND(A1,2)*10)*10)=0,"","零"),TEXT(INT(ROUND(A1,2)*10)-INT(ROUND(A1,2))*10,"[dbnum2]")&"角")&IF((INT(ROUND(A1,2)*100)-INT(ROUND(A1,2)*10)*10)=0,"整",TEXT((INT(ROUND(A1,2)*100)-INT(ROUND(A1,2)*10)*10),"[dbnum2]")&"分")))17. 公式=IF(MOD(B2,1)=0,TEXT(INT(B2),"[dbnum2]G/通用格式元整;负[dbnum2]G/通用格式元整;零元整;"),IF(B2>0,,"负")&TEXT(INT(ABS(B2)),"[dbnum2]G/通用格式元;;")&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(FIXED(B2),2),"[dbnum2]0角0分;;"),"零角",IF(ABS(B2)<>0,,"零")),"零分",""))18、公式=TEXT(MID(INT(B1),1,1),"[dbnum2]")&CHOOSE(LEN(INT(B1)),"圆","拾","佰","仟","万","拾万")&IF(LEN(INT(B1))-1<=0,"",TEXT(MID(INT(B1),2,1),"[dbnum2]")&CHOOSE(LEN(INT(B1)) -1,"圆","拾","佰","仟","万","拾万"))&IF(LEN(INT(B1))-2<=0,"",TEXT(MID(INT(B1),3,1),"[dbnum2]")&CHOOSE(LEN(INT(B1) )-2,"圆","拾","佰","仟","万","拾万"))&IF(LEN(INT(B1))-3<=0,"",TEXT(MID(INT(B1),4,1),"[dbnum2]")&CHOOSE(LEN(INT(B1) )-3,"圆","拾","佰","仟","万","拾万"))&IF(LEN(INT(B1))-4<=0,"",TEXT(MID(INT(B1),5,1),"[dbnum2]")&CHOOSE(LEN(INT(B1) )-4,"圆","拾","佰","仟","万","拾万"))&IF(LEN(INT(B1))-5<=0,"",TEXT(MID(INT(B1),6,1),"[dbnum2]")&CHOOSE(LEN(INT(B1) )-5,"圆","拾","佰","仟","万","拾万"))&IF(B1-INT(B1)=0,"零角零分",IF(LEN(ROUND(B1-INT(B1),2))=3,TEXT(RIGHT(ROUND(B1-INT(B1),2),1),"[dbnum2]")&"角零分",TEXT(MID(ROUND(B1-INT(B1),2),3,1),"[dbnum2]")&"角"&TEXT(RIGHT(ROUND(B1-INT(B1),2),1),"[dbnum2]")&"分"))。

在EXCEL中将数字转换为人民币大写的三种方式鉴于EXCEL本身提供将数字转换为大写表示的功能根本不能正常应用在实际投标或财务应用之中，所以要自己建设，暂时找到了三种实现途径，经过测试均功能正常，还有一种是在书上找到的，懒得KEYIN进来了，反正也差不多了。

方法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,"","零"),(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”中输入数字，就可看到效果。

方法2，通过VBA（宏）输入转换公式: 点击菜单“工具”->“宏”->“VisualBasic编辑器”,在编辑器窗口中，点击菜单“插入”->“模块”，在出现的窗口中输入以下内容：Function daxie(ByVal Num) ' 人民币中文大写函数Application.Volatile TruePlace = "分角元拾佰仟万拾佰仟亿拾佰仟万"Dn = "壹贰叁肆伍陆柒捌玖"D1 = "整零元零零零万零零零亿零零零万"If Num < 0 Then FuHao = "（负）"Num = Format(Abs(Num), "###0.00") * 100If Num > 999999999999999# Then: daxie = "数字超出转换范围!!": Exit FunctionIf Num = 0 Then: daxie = "零元零分": Exit FunctionNumA = Trim(Str(Num))NumLen = Len(NumA)For J = NumLen To 1 Step -1 ' 数字转换过程temp = Val(Mid(NumA, NumLen - J + 1, 1))If temp <> 0 Then ' 非零数字转换NumC = NumC & Mid(Dn, temp, 1) & Mid(Place, J, 1) Else ' 数字零的转换If Right(NumC, 1) <> "零" ThenNumC = NumC & Mid(D1, J, 1)ElseSelect Case J ' 特殊数位转换Case 1NumC = Left(NumC, Len(NumC) - 1) & Mid(D1, J, 1)Case 3, 11NumC = Left(NumC, Len(NumC) - 1) & Mid(D1, J, 1) & "零"Case 7If Mid(NumC, Len(NumC) - 1, 1) <> "亿" ThenNumC = Left(NumC, Len(NumC) - 1) & Mid(D1, J, 1) & "零"End IfCase ElseEnd SelectEnd IfEnd IfNextdaxie = "(人民币)" & FuHao & Trim(NumC)End Function然后切换回excel,在“A2”单元中输入数字，在“B2”单元中输入：“=DaXie(A2)”，就可看到效果。

人民币大小写转化
当在制作财务表格的时候，需要将金额的大写跟小写进行转换，如果要一个一个手打出来的话，那就太浪费时间了，一两个数额还好，如果一整个表格的数额，那就不得了了，那要花费多少时间才能将小写金额转换成大写金额呀！如何将人民币大小写转换？找到以下4种方法！
方法1：设置单元格格式
步骤一：将小写金额复制粘贴到大写金额的单元格中，点击鼠标右键，点击“设置单元格格式”。

步骤二：点击“特殊”，然后选择“人民币大写”，最后点击“确定”即可。

方法2：运用Numberstring函数
在需要填写大写金额的空白单元格内输入Numberstring函数，选择第一个小写金额，选项输入2，下拉全部数据就会出来了。

根据方法1和方法2就会得出大写格式的金额，但是要注意的是，这2种方法并不能对含有小数点的金额进行转换，这也是这2种方法的缺点。

方法3：运用text函数转换成简单版的大写金额
在空白单元格内输入text函数，并选择小写的金额，在数值格式中输入"[DBNum2][$-804]G/通用格式"，这段格式有点难记，所以建议大家想要用这种方法的可以收藏起来，日后需要用到的时候直接复制粘贴即可。

方法4：运用text函数转换成较为通用的大写金额
在空白单元格输入text函数，在数值格式中输入"[dbnum2]0 万 0 仟 0 佰0 拾 0 元 0 角 0 分"，跟方法3一样，这段个事可以直接复制粘贴，收藏起来，方便日后使用。

方法1和方法2说到不能转换小数点，但是方法3和方法4是可以转换有小数点的金额的，所以分两种情况使用。

加了小数点的金额转换如下图所示。

EXCEL表中用函数把数字转化成中文大写金额格式在EXCEL表中把数字转化成中文大写金额格式是财务人员在日常工作中经常遇到的一个问题，虽然在EXCEL中的单元格格式设置中有一个特殊格式中可以转换在中文大写数字，但不能给出元、角、分整的中文金额特定格式，许多人最后只能手工输入，降低了EXCEL的使用效率，但如果TRUNC工作表函数，这个问题将迎刃而解，如单元格E11=23421.72，须在某一格记录其中文大写金额，则在该格内输入函数:="大写人民币:"&IF(TRUNC(E11)=0,"零元",TEXT(TRUNC(E11),"[DBNum2]G/通用格式")&"元")&IF(TRUNC(E11*10)-TRUNC(E11*10,-1)=0,"",TEXT(TRUNC(E11*10)-TRUNC(E11*10,-1),"[DBNum2]G/通用格式")&"角")&IF(TRUNC(E11*100)-TRUNC(E11*100,-1)=0,"",TEXT(TRUNC(E11*100)-TRUNC(E11*100,-1),"[DBNum2]G/通用格式")&"分")&IF(TRUNC(E11*100)-TRUNC(E11*100,-1)=0,"整","")回车后将得到完整的大写中文金额格式，这个函数将广泛应用于支票打印、合同打印、发票和收据的开具等，方便快捷，提高工作效率。

这个函数的工作原理：把23421.72这个数字分成三段，整数位为一段，小数点后的第一位和第二位各为一段,利用&符号把三段和元、角、分及整字连接起来，关键是如何取到三段的值，就应用了一个TRUNC的工作表函数，这个函数的作用是将数字的小数部分截去，返回整数。