超全的人民币金额大写excel公式

EXCEL 小写
金额转换大
写公式：
示例：
小写金额：大写金额：
28546.37大写（人民币）贰万捌仟伍佰肆拾陆元叁角柒说明：不讨论公式设计，单从应用方法上简单介绍，将大写金额单元格的公式复制到需要体现大写的单元格，然后对公示引用的小写单元格进行替换操作即可，比如小写金额在D5单元格，那将下述大写单元格公示中的A7全部替换成为D5即可；
公示描述："大写（人民币）"&IF((INT(A7*10)-
INT(A7)*10)=0,TEXT(INT(A7),"[DBNum2]G/通用格式")&"元
"&IF((INT(A7*100)-INT((A7)*10)*10)=0,"整","零
"&TEXT(INT(A7*100)-INT(A7*10)*10,"[DBNum2]G/通用格式")&"分"),TEXT(INT(A7),"[DBNum2]G/通用格式")&"元"&IF((INT(A7*100)-INT((A7)*10)*10)=0,TEXT((INT(A7*10)-INT(A7)*10),"[DBNum2]G/通用格式")&"角整",TEXT((INT(A7*10)-INT(A7)*10),"[DBNum2]G/通用格式")&"角"&TEXT(INT(A7*100)-INT(A7*10)*10,"[DBNum2]G/通用格式")&"分"))。

EXCEL财务小技巧--人民币数字金额转汉字大写公式前几天又被问到人民币大写，正好想起来以前收集的这个公式，当时是做一个空调安装报价系统，所以在网上找到了这个公式，公式看起来很复杂，不过结构并不复杂，有兴趣的可以研究下，不过学会用还是很简单的公式如下：='大写：'&IF(ABS(E1)<0.005,'',IF(E1<0,'负',)&IF(INT(ABS(E1)),TEXT(INT(ABS(E1)),'[dbnum2]')&'元',)&IF(INT(ABS(E1)*10)-INT(ABS(E1))*10,TEXT(INT(ABS(E1)*10)-INT(ABS(E1))*10,'[dbnum2]')&'角',IF(INT(ABS(E1))=ABS(E1),,IF(ABS(E1)<0.1,,'零')))&IF(ROUND(ABS(E1)*100-INT(ABS(E1)*10)*10,),TEXT(ROUND(ABS(E1)*100-INT(ABS(E1)*10)*10,),'[dbnum2]')&'分','整'))在实际用的时候先把这个公式复制到记事本里，比如你要将A1的数字转化成人民币大写，那就用记事本的替换功能将E1替换成A1，在将公式复制粘贴到要显示大写的单元格即可应用的结果示范如下另外这个公式是个引子，需要的最终格式和这有点不一样，那也可以根据这个公式做一些简单改变，比如说总是要得到要得到 *元*角*分的样子那就写成='人民币'&IF(ABS(AG10)<0.005,'',IF(AG10<0,'负',)&IF(INT(ABS(AG10)),TEXT(INT(ABS(AG10)),'[dbnum2]')&'元',)&IF(INT(ABS(AG10)*10)-INT(ABS(AG10))*10,TEXT(INT(ABS(AG10)*10)-INT(ABS(AG10))*10,'[dbnum2]')&'角',IF(INT(ABS(AG10))=ABS(AG10),'零角',IF(ABS(AG10)<0.1,,'零')))&TEXT(ROUND(ABS(AG10)*100-INT(ABS(AG10)*10)*10,),'[dbnum2]')&'分')注意这里目标单元格时AG10，结果如下人民币伍万肆仟零叁拾元零角零分540302010-10-12 by excel880。

超全的金额大写excel公式作为财会人，一定会在excel中涉及到人民币金额大写的问题，这里搜集了一些，公式很多，也很复杂，有兴趣的同学可以研究一下，不想研究的就收藏起来备用。

使用方法很简单，把下面公式中的A2换成你表中数字所在单元格地址即可。

1 =SUBSTITUTE(SUBSTITUTE(TEXT(TRUNC(FIXED(A2)),"[>0][dbnum2]G/通用格式元;[<0]负[dbnum2]G/通用格式元;;")&TEXT(RIGHT(FIXED(A2),2),"[dbnum2]0角0分;;"&IF(ABS(A2)>1%,"整",)),"零角",IF(ABS(A2)<1,,"零")),"零分","整")2 =SUBSTITUTE(SUBSTITUTE(IF(A2>-0.5%,,"负")&TEXT(INT(FIXED(ABS(A2))),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(FIXED(A2),2),"[dbnum2]0角0分;;"&IF(ABS(A2)>1%,"整",)),"零角",IF(ABS(A2)<1,,"零")),"零分","整")3 =SUBSTITUTE(SUBSTITUTE(IF(A2>-0.5%,,"负")&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(FIXED(A2),2),"[dbnum2]0角0分;;"&IF(ABS(A2)>1%,"整",)),"零角",IF(ABS(A2)<1,,"零")),"零分","整")4 =SUBSTITUTE(SUBSTITUTE(IF(A2>-0.5%,,"负")&TEXT(INT(FIXED(ABS(A2))),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(FIXED(A2),2),"[dbnum2]0角0分;;"&IF(ABS(A2)>1%,"整",)),"零角",IF(ABS(A2)<1,,"零")),"零分","整")5 =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(A2>-0.5%,,"负")&TEXT(INT(FIXED(ABS(A2))),"[dbnum2]")&TEXT(RIGHT(FIXED(A2),2),"[dbnum2]元0角0分;;元"&IF(ABS(A2)>1%,"整",)),"零角",IF(ABS(A2)<1,,"零")),"零元",),"零分","整")6 =SUBSTITUTE(SUBSTITUTE(IF(A2>-0.5%,,"负")&IF(ABS(A2)+0.5%<1,,TEXT(INT(ABS(A2)+0.5%),"[dbnum2]")&"元")&TEXT(RIGHT(FIXED(A2),2),"[dbnum2]0角0分;;"&IF(ABS(A2)>1%,"整",)),"零角",IF(ABS(A2)<1,,"零")),"零分","整")7 =IF(A2=0,"零",IF(A2>-0.5%,,"负")&TEXT(INT(ABS(A2)),"[dbnum2]G/通用格式元;;")&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(FIXED(A2),2),"[dbnum2]0角0分;;"),"零角",IF(ABS(A2)<1,,"零")),"零分","整"))8 =SUBSTITUTE(SUBSTITUTE(TEXT(TRUNC(FIXED(A2)),"[dbnum2]G/通用格式元;负[dbnum2]G/通用格式元;"&IF(A2>-0.5%,,"负"))&TEXT(RIGHT(FIXED(A2),2),"[dbnum2]0角0分;;"&IF(ABS(A2)>1%,"整",)),"零角",IF(ABS(A2)<1,,"零")),"零分","整")9 =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(B8<0,"负",)&TEXT(INT(ABS(B8)),"[dbnum2];; ")&TEXT(MOD(ABS(B8)*100,100),"[>9][dbnum2]圆0角0分;[=0]圆整;[dbnum2]圆零0分"),"零分","整")," 圆零",)," 圆",)10 =SUBSTITUTE(SUBSTITUTE(TEXT(INT(A1),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(A1/1%,2),"[dbnum2]0角0分;;"&IF(A1,"整",)),"零角","零"),"零分","整")"大写（人民币）："&IF(A1-INT(A1)<0.005,TEXT(INT(A1),"[dbnum2]")&"元整",IF(A1*10-INT(A1*10)<0.05,TEXT(INT(A1),"[dbnum2]")&"元"&TEXT(INT(A1*10-INT(A1)*10),"[dbnum2]")&"角整",TEXT(INT(A1),"[dbnum2]")&"元"&TEXT(INT(A1*10-INT(A1)*10),"[dbnum2]")&"角"&TEXT((FIXED(A1*100,0)-INT(A1*10)*10),"[dbnum2]")&"分"))11 =IF(ABS(A2)<0.5%,"",SUBSTITUTE(SUBSTITUTE(IF(A2<0,"负",)&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(FIXED(A2),2),"[dbnum2]0角0分;;整"),"零角",IF(ABS(A2)<1,,"零")),"零分","整"))12 =SUBSTITUTE(SUBSTITUTE(IF(A1>-0.5%,,"负")&TEXT(INT(ABS(A1)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A1),2),"[dbnum2]0角0分;;"&IF(ABS(A1)>1%,"整",)),"零角",IF(ABS(A1)<1,,"零")),"零分","整")13 =IF(ABS(A2)<0.5%,"",SUBSTITUTE(SUBSTITUTE(IF(A2<0,"负",)&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A2),2),"[dbnum2]0角0分;;整"),"零角",IF(ABS(A2)<1,,"零")),"零分","整"))14 =IF(-RMB(A2),SUBSTITUTE(SUBSTITUTE(IF(A2<0,"负",)&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A2),2),"[dbnum2]0角0分;;整"),"零角",IF(ABS(A2)<1,,"零")),"零分","整"),"")15 =SUBSTITUTE(IF(-RMB(A2),IF(A2<0,"负",)&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A2),2),"[>9][dbnum2]0角0分;[>][dbnum2]0分;整"),""),"零分","整")16 =SUBSTITUTE(IF(-RMB(A2),IF(A2<0,"负",)&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A2),2),"[>9][dbnum2]0角0分;零[>][dbnum2]0分;整"),""),"零分","整")17 =SUBSTITUTE(SUBSTITUTE(IF(-RMB(A1),IF(A1<0,"负",)&TEXT(INT(ABS(A1)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A1),2),"[dbnum2]0角0分;;整"),),"零角",IF(ABS(A1)<1,,"零")),"零分","整")18 =TEXT(RMB(A1),"[=]g;"&TEXT(INT(ABS(A1)+0.5%),"[dbnum2]G/通用格式元;;")&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(RMB(A1),2),"[dbnum2]0角0分;;整"),"零角",IF(ABS(A1)<1,,"零")),"零分","整"))19 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(ABS(A2)<1,,0)&"[>][dbnum2]0分;整"),),"零分",)20 =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分;整"),),"零分","整")21 =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,,"零")),"零分","整")22 =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分;整"),),"零分",)23 =TEXT(A2,";负")&SUBSTITUTE(TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&IF(-RMB(A2),TEXT(RIGHT(RMB(A2),2),"[>9][dbnum2]0角0分整;"&IF(A2^2<1,,0)&"[>][dbnum2]0分;整"),),"零分",)24 =TEXT(RMB(A1),"[=]g;"&TEXT(INT(ABS(A1)+0.5%),"[dbnum2]G/通用格式元;;")&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(RMB(A1),2),"[dbnum2]0角0分;;整"),"零角",IF(A1^2<1,,"零")),"零分","整"))25 =SUBSTITUTE(IF(-RMB(A2),TEXT(A2,";负")&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A2),2),"[>9][dbnum2]0角0分;"&IF(A2^2<1,,0)&"[>][dbnum2]0分;整"),),"零分","整")26 =SUBSTITUTE(SUBSTITUTE(IF(-RMB(A2,2),TEXT(A2,";负")&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A2,2),2),"[dbnum2]0角0分;;整"),),"零角",IF(A2^2<1,,"零")),"零分","整")27 =TEXT(LEFT(RMB(A1),LEN(RMB(A1))-3),"[>0][dbnum2]G/通用格式元;[<0]负[dbnum2]G/通用格式元;;") & TEXT(RIGHT(RMB(A1),2),"[dbnum2]0角0分;;整")28 = TEXT(INT(A3),"[dbnum2]")&"元"&IF(INT(A3*10)-INT(A3)*10=0,"",TEXT(INT(A3*10)-INT(A3)*10,"[dbnum2]")&"角")&IF(INT(A3*100)-INT(A3*10)*10=0,"整",TEXT(INT(A3*100)-INT(A3*10)*10,"[dbnum2]")&"分")29 =IF(OR(B1="",B1=0),"",TEXT(INT(B1),"[dbnum2]G/通用格式元;[dbnum2]G/通用格式元;;")&TEXT(--RIGHT(INT(B1*10)),"[dbnum2]#角;;;")&TEXT(--RIGHT(INT(B1*100)),"[dbnum2]#分;;整;"))▎本文作者蓝色幻想-赵志东。

excel 金额大写转换公式摘要：一、介绍Excel 金额大写转换公式二、分析金额大写转换公式的原理三、讲解如何使用Excel 金额大写转换公式四、总结Excel 金额大写转换公式的优点与局限性五、提供使用Excel 金额大写转换公式时的注意事项正文：在Excel 中，我们常常需要将数字金额转换为大写金额。

为了方便用户实现这一功能，Excel 提供了一个金额大写转换公式。

本文将详细介绍这个公式，并分析其原理及使用方法。

首先，我们来了解一下Excel 金额大写转换公式的原理。

这个公式基于汉字数字与阿拉伯数字的对应关系，通过嵌套IF 函数，实现将阿拉伯数字转换为大写金额的功能。

接下来，我们来讲解如何使用Excel 金额大写转换公式。

假设A1 单元格中存储的数字金额为1234.56，我们可以在B1 单元格中输入以下公式：```=IF(A1>=10000, "壹万零" & TRANSPOSE(TEXT(A1-10000, ";负数;零;壹;贰;叁;肆;伍;陆;柒;捌;玖;拾")), IF(A1>=1000, "壹仟零" & TRANSPOSE(TEXT(A1-1000, ";负数;零;壹;贰;叁;肆;伍;陆;柒;捌;玖;拾")),IF(A1>=100, "壹佰零" & TRANSPOSE(TEXT(A1-100, ";负数;零;壹;贰;叁;肆;伍;陆;柒;捌;玖;拾")), IF(A1>=10, "十元" & TRANSPOSE(TEXT(A1-10, ";负数;零;壹;贰;叁;肆;伍;陆;柒;捌;玖;拾")), TEXT(A1, ";负数;零;壹;贰;叁;肆;伍;陆;柒;捌;玖;拾")))```以上公式可以根据需要进行调整，以适应不同的金额格式需求。

人民币大小写转换Excel公式以下假设你在A1单元格中输入金额数字，在其它单元格输入以下公式就可以了公式1=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)),"[DBN um2]"))&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,"","整")),"零元零",""),"零元","")公式2=IF((A1-INT(A1))=0,TEXT(A1,"[DBNUM2]")&"元整",IF(INT(A1*10)-A1*10=0,TEXT(INT(A1),"[DBNUM2]")&"元"&TEXT((INT(A1*10)-INT(A1)*10),"[DBNUM2]")&"角整",TEXT(INT(A1),"[DBNUM2]")&"元"&IF(INT(A1*10)-INT(A1)*10=0,"零",TEXT(INT(A1*10)-INT(A1)*10,"[DBNUM2]")&"角")&TEXT(RIGHT(A1,1),"[DBNUM2]")&"分"))公式3=IF(ABS(A1)<0.005,"",IF(A1<0,"负",)&IF(INT(ABS(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(A BS(A1)*10)*10,),"[dbnum2]")&"分","整"))以上公式包含在附件的excel表格中。

在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)”，就可看到效果。

EXCEL‎金额大小写‎转换公式大‎全2007-12-15 16:08先选中需要‎转换的单元‎格，在格式（或者右击设‎置单元格格‎式中）——单元格格式‎——数据——特殊——右边类型：中文小写、中文大写=NUMBE‎R STRI‎N G($A$1,1)1=IF(A5=0,"",CONCA‎TENA T‎E(IF(INT(A5)=0,"",TEXT(INT(A5),"[DBNum‎2]G/通用格式元‎")),IF(INT(MID(RIGHT‎(FIXED‎(A5,2,1),2),1,1))=0,IF(INT(MID(RIGHT‎(FIXED‎(A5,2,1),1),1,1))=0,"",IF(INT(A5)=0,"","零")),TEXT(INT(MID(RIGHT‎(FIXED‎(A5,2,1),2),1,1)),"[DBNum‎2]G/通用格式角‎")),IF(INT(MID(RIGHT‎(FIXED‎(A5,2,1),1),1,1))=0,"整",TEXT(INT(MID(RIGHT‎(FIXED‎(A5,2,1),1),1,1)),"[DBNum‎2]G/通用格式分‎"))))2=IF(A5<0,"負","")&IF(ABS(A5)>1,TEXT(TRUNC‎(ABS(ROUND‎(A5,2))),"[DBNum‎2]")&"元","")&IF(ISERR‎(FIND(".",ROUND‎(A5,2))),"",TEXT(RIGHT‎(TRUNC‎(ROUND‎(A5,2)*10)),"[DBNum‎2]"))&IF(ISERR‎(FIND(".0",TEXT(A5,"0.00"))),"角","")&IF(LEFT(RIGHT‎(ROUND‎(A5,2),3))=".",TEXT(RIGHT‎(ROUND‎(A5,2)),"[DBNum‎2]")&"分","整")3=IF(A5<0,"负","")&IF(TRUNC‎(ROUND‎(A5,2))=0,"",TEXT(TRUNC‎(ABS(ROUND‎(A5,2))),"[DBNum‎2]")&"元")&IF(ISERR‎(FIND(".",ROUND‎(A5,2))),"",TEXT(RIGHT‎(TRUNC‎(ROUND‎(A5,2)*10)),"[DBNum‎2]"))&IF(ISERR‎(FIND(".0",TEXT(A5,"0.00"))),"角","")&IF(LEFT(RIGHT‎(ROUND‎(A5,2),3))=".",TEXT(RIGHT‎(ROUND‎(A5,2)),"[DBNum‎2]")&"分","整")4=SUBST‎ITUTE‎(SUBST‎ITUTE‎(IF(A5<0,"負","")&TEXT(TRUNC‎(ABS(ROUND‎(A5,2))),"[DBNum‎2]")&"元"&IF(ISERR‎(FIND(".",ROUND‎(A5,2))),"",TEXT(RIGHT‎(TRUNC‎(ROUND‎(A5,2)*10)),"[DBNum‎2]"))&IF(ISERR‎(FIND(".0",TEXT(A5,"0.00"))),"角","")&IF(LEFT(RIGHT‎(ROUND‎(A5,2),3))=".",TEXT(RIGHT‎(ROUND‎(A5,2)),"[DBNum‎2]")&"分",IF(ROUND‎(A5,2)=0,"","整")),"零元零",""),"零元","")5=IF(ROUND‎(A5,2)<0,"无效数值",IF(ROUND‎(A5,2)=0,"零",IF(ROUND‎(A5,2)<1,"",TEXT(INT(ROUND‎(A5,2)),"[dbnum‎2]")&"元")&IF(INT(ROUND‎(A5,2)*10)-INT(ROUND‎(A5,2))*10=0,IF(INT(ROUND‎(A5,2))*(INT(ROUND‎(A5,2)*100)-INT(ROUND‎(A5,2)*10)*10)=0,"","零"),TEXT(INT(ROUND‎(A5,2)*10)-INT(ROUND‎(A5,2))*10,"[dbnum‎2]")&"角")&IF((INT(ROUND‎(A5,2)*100)-INT(ROUND‎(A5,2)*10)*10)=0,"整",TEXT((INT(ROUND‎(A5,2)*100)-INT(ROUND‎(A5,2)*10)*10),"[dbnum‎2]")&"分")))6=IF(ISNUM‎B ER(A5),IF(A5<0,"无效数值",IF(A5<0.005,"零",IF(A5<0.995,"",TEXT(INT(A5+0.005),"[dbnum‎2]")&"元")&IF(LEFT(RIGHT‎(FIXED‎(A5,2),2),1)="0",IF(RIGHT‎(FIXED‎(A5,2),1)="0","",IF(A5>0.995,"零","")),TEXT(LEFT(RIGHT‎(FIXED‎(A5,2),2),1),"[dbnum‎2]")&"角")&IF(RIGHT‎(FIXED‎(A5,2),1)="0","整",TEXT(RIGHT‎(FIXED‎(A5,2),1),"[dbnum‎2]")&"分"))),"非数值!")7=IF(ISNUM‎B ER(A5),IF(ROUND‎(A5,2)<0,"无效数值",IF(ROUND‎(A5,2)=0,"零",IF(ROUND‎(A5,2)<1,"",TEXT(INT(ROUND‎(A5,2)),"[dbnum‎2]")&"元")&IF(INT(ROUND‎(A5,2)*10)-INT(ROUND‎(A5,2))*10=0,IF((INT(ROUND‎(A5,2)*100)-INT(ROUND‎(A5,2)*10)*10)=0,"","零"),TEXT(INT(ROUND‎(A5,2)*10)-INT(ROUND‎(A5,2))*10,"[dbnum‎2]")&"角")&IF((INT(ROUND‎(A5,2)*100)-INT(ROUND‎(A5,2)*10)*10)=0,"整",TEXT((INT(ROUND‎(A5,2)*100)-INT(ROUND‎(A5,2)*10)*10),"[dbnum‎2]")&"分"))),"非数值！！！")8=IF(ROUND‎(A5,2)<0,"无效数值",IF(ROUND‎(A5,2)=0,"零",IF(ROUND‎(A5,2)<1,"",TEXT(INT(ROUND‎(A5,2)),"[dbnum‎2]")&"元")&IF(INT(ROUND‎(A5,2)*10)-INT(ROUND‎(A5,2))*10=0,IF(INT(ROUND‎(A5,2))*(INT(ROUND‎(A5,2)*100)-INT(ROUND‎(A5,2)*10)*10)=0,"","零"),TEXT(INT(ROUND‎(A5,2)*10)-INT(ROUND‎(A5,2))*10,"[dbnum‎2]")&"角")&IF((INT(ROUND‎(A5,2)*100)-INT(ROUND‎(A5,2)*10)*10)=0,"整",TEXT((INT(ROUND‎(A5,2)*100)-INT(ROUND‎(A5,2)*10)*10),"[dbnum‎2]")&"分")))9=TEXT(INT(A5),"[dbnum‎2]")&"元"&IF(INT(A5*10)-INT(A5)*10=0,"",TEXT(INT(A5*10)-INT(A5)*10,"[dbnum‎2]")&"角")&IF(INT(A5*100)-INT(A5*10)*10=0,"整",TEXT(INT(A5*100)-INT(A5*10)*10,"[dbnum‎2]")&"分")10Funct‎i on BAITU‎R MBDX‎(ByVal‎n) As Strin‎g 'n as Curre‎n cyConst‎ cNum As Strin‎g = "零壹贰叁肆‎伍陆柒捌玖‎-万仟佰拾亿‎仟佰拾万仟‎佰拾元角分‎" Const‎ cCha As Strin‎g= "零仟零佰零‎拾零零零零‎零亿零万零‎元亿万零角‎零分零整-零零零零零‎亿万元亿零‎整整"Dim sNum As Strin‎gDim i As LongIf (n <> 0) And (Abs(n) < 10000‎00000‎0000#) ThensNum = Trim(Str(Int(Abs(n) * 100)))For i = 1 To Len(sNum) '逐位转换BAITU‎R MBDX‎= BAITU‎R MBDX‎+ Mid(cNum, (Mid(sNum, i, 1)) + 1, 1) + Mid(cNum, 26 - Len(sNum) + i, 1)NextFor i = 0 To 11 '去掉多余的‎零BAITU‎R MBDX‎= Repla‎c e(BAITU‎R MBDX‎, Mid(cCha, i * 2 + 1, 2), Mid(cCha, i + 26, 1)) NextIf n < 0 Then BAITU‎RMBDX‎= "(负)" + BAITU‎RMBDX‎ElseBAITU‎RMBDX‎= IIf(n = 0, "零元", "溢出")End IfEnd Funct‎i on11Funct‎i on Upper‎N um(n) 'n as singl‎e '数字大写函‎数If n < 0 Then正负判断= "负"n = -nEnd Ifn = n + 0.0001If Int(n * 1000) - Int(n * 100) * 10 > 4 Thenn = (Int(n * 100) + 1) / 100 + 0.001Elsen = Int(n * 100) / 100 + 0.001End IfSelec‎t Case nC ase Is > 99999‎99999‎999.99Upper‎N um = "数据不符"MsgBo‎x "金额不能大‎于9999‎99999‎9999.99！", vbOKO‎nl y, "出错提示"Case ElseConst‎ cNum = "零壹贰叁肆‎伍陆柒捌玖‎-万仟佰拾亿‎仟佰拾万仟‎佰拾元角分‎"Const‎ cCha = "零仟零佰零‎拾零零零零‎零亿零万零‎元亿万零角‎零分零整-零零零零零‎亿万元亿零‎整整"Upper‎N um = ""sNum = ""s = Trim(Str(n))For i = 1 To Len(s) - 1If Mid(s, i, 1) <> "." Then sNum = sNum + Mid(s, i, 1)Next iFor i = 1 To Len(sNum) '逐位转换U pper‎N um = Upper‎N um + Mid(cNum, (Mid(sNum, i, 1)) + 1, 1) + Mid(cNum, 26 - Len(sNum) + i, 1)Next iFor i = 0 To 11 '去掉多余的‎零U pper‎N um = Repla‎c e(Upper‎N um, Mid(cCha, i * 2 + 1, 2), Mid(cCha, i + 26, 1)) Next iEnd Selec‎tIf 正负判断= "负" Then Upper‎N um = "负" & Upper‎N umEnd Funct‎i on12Funct‎i on JEZH(X As Range‎)If X >= 1 ThenIf Int(X) = X Or Round‎(X, 2) = Int(X) ThenJEZH = Appli‎c atio‎n.Works‎h eetF‎u ncti‎o n.Text(Int(X), "[DBNUM‎2]") & "元"ElseI‎f Int(X * 10) = X * 10 Or Int(X * 10) = Round‎(X, 2) * 10 ThenJEZH = Appli‎c atio‎n.Works‎h eetF‎u ncti‎o n.Text(Int(X), "[DBNUM‎2]") & "元" & Appli‎catio‎n.Works‎h eetF‎u ncti‎o n.Text(Right‎(Round‎(X, 2), 1), "[DBNUM‎2]") & "角"ElseJEZH = Appli‎c atio‎n.Works‎h eetF‎u ncti‎o n.Text(Int(X), "[DBNUM‎2]") & "元" & Appli‎catio‎n.Works‎h eetF‎u ncti‎o n.Text(Left(Right‎(Round‎(X, 2), 2), 1), "[DBNUM‎2]") & "角" & Appli‎catio‎n.Works‎h eetF‎u ncti‎o n.Text(Right‎(Round‎(X, 2), 1), "[DBNUM‎2]") & "分"End IfElseI‎f X = 0 ThenJEZH = Appli‎c atio‎n.Works‎h eetF‎u ncti‎o n.Text(Int(X), "[DBNUM‎2]") & "元"ElseI‎f X < 1 And X > 0 ThenIf Int(X * 10) = X * 10 ThenJEZH = Appli‎c atio‎n.Works‎h eetF‎u ncti‎o n.Text(Right‎(X, 1), "[DBNUM‎2]") & "角"ElseJEZH = Appli‎c atio‎n.Works‎h eetF‎u ncti‎o n.Text(Left(Right‎(Round‎(X, 2), 2), 1), "[DBNUM‎2]") & "角" & Appli‎c atio‎n.Works‎h eetF‎u ncti‎o n.Text(Right‎(Round‎(X, 2), 1), "[DBNUM‎2]") & "分"End IfElseIf Int(X) = X Or Round‎(Abs(X), 2) = Int(Abs(X)) ThenJEZH = "负" & Appli‎ca tio‎n.Works‎h eetF‎u ncti‎o n.Text(Int(Abs(X)), "[DBNUM‎2]") & "元"ElseI‎f Int(X * 10) = X * 10 ThenJEZH = "负" & Appli‎c atio‎n.Works‎h eetF‎u ncti‎o n.Text(Int(Abs(X)), "[DBNUM‎2]") & "元" & Appli‎c atio‎n.W orks‎h eetF‎u ncti‎o n.Text(Right‎(X, 1), "[DBNUM‎2]") & "角"ElseJEZH = "负" & Appli‎c atio‎n.Works‎h eetF‎u ncti‎o n.Text(Int(Abs(X)), "[DBNUM‎2]") & "元" & Appli‎ca tio‎n.Works‎h eetF‎u ncti‎o n.Text(Left(Right‎(Round‎(X, 2), 2), 1), "[DBNUM‎2]") & "角" & Appli‎c atio‎n.Works‎h eetF‎u ncti‎o n.Text(Right‎(Round‎(X, 2), 1), "[DBNUM‎2]") & "分"End IfEnd IfEnd Funct‎i on。

人民币金额小写转换为大写转换公式转换公式之一：=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：精确到元角分；将公式复制到对应的单元格，将公式中的H41替换为对应的金额所在单元格即可。

（用查找替换方法更换单元格代码）="大写："&IF(TRIM(H41)="","",IF(H41=0,"","人民币"&IF(H41<0,"负",)&IF(INT(H41),TEXT(INT(ABS(H41)),"[dbnum2]")&"圆",)&IF(INT(ABS(H41)*10)-INT(ABS(H41))*10,TEXT(INT(ABS(H41)*10)-INT(ABS(H41))*10,"[dbnum2]")&"角",IF(INT(ABS(H41))=ABS(H41),,IF(ABS(H41)<0.1,,"零")))&IF(ROUND(ABS(H41)*100-INT(ABS(H41)*10)*10,),TEXT(ROUND(ABS(H41)*100-INT(ABS(H41)*10)*10,), "[dbnum2]")&"分","整")))得到的格式为：大写：（人民币）玖万肆仟叁佰壹拾贰元壹角肆分方法2：精确到元（取整数）；将公式复制到对应的单元格，将公式中的A1替换为对应的金额所在单元格即可。

＝TEXT(ROUNDDOWN(A1,0),"[dbnum2]")&"元整"得到的格式为：玖万元肆仟叁佰壹拾贰元整。

excel中小写金额转大写金额的公式
在Excel中，我们可以使用以下公式将小写金额转换为大写金额： =IF(A1<0,'负','')&TEXT(INT(A1),'[$中文大写数字]元整
')+IF(A1-INT(A1)=0,'','')+IF(ROUND(MOD(A1,1),2)*100<10,'零','')&TEXT(ROUND(MOD(A1,1),2)*100,'[$中文大写数字]角
')+IF(ROUND(MOD(A1,1),2)*100=0,'','')+IF(ROUND(MOD(A1,0.01)
,2)*100<10,'零','')&TEXT(ROUND(MOD(A1,0.01),2)*100,'[$中文
大写数字]分')
其中，A1为需要转换的小写金额。

公式中的“[$中文大写数字]”表示使用Excel内置的中文大写数字格式。

如果需要转换成其他语言的大写金额，可以替换成相应的格式。

需要注意的是，该公式只适用于转换人民币金额，其他货币的转换需要使用相应的格式和汇率进行计算。

- 1 -。

excel函数大写金额公式
在Excel中，我们经常需要将数字转换为大写金额。

这种转换通常用于财务报表、合同等场合。

在本文中，我们将介绍如何使用Excel 函数将数字转换为大写金额。

步骤1：打开Excel，并输入需要转换的数字。

步骤2：在单元格中输入以下公式：=大写金额(A1)
其中，A1是需要转换的数字所在的单元格。

步骤3：按下回车键，将显示转换后的大写金额。

例如，如果输入数字1000，则公式将显示“壹仟元整”。

这是因为大写金额公式将数字转换为其对应的汉字，如“壹”代表“1”，“仟”代表“1000”，“整”代表“0”。

总之，使用Excel函数大写金额公式可以方便快捷地将数字转换为大写金额，使财务报表等文档更加规范和易于阅读。

- 1 -。

在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)”，就可看到效果。

EXCEL金额大小写转换公式大全在Excel中，可以使用以下公式将金额从数字形式转换为大写形式：1.数字转换为大写金额：=IF(A1<0,"负","")&NUMBERTEXT(ABS(A1))这个公式将数字转换为大写金额。

如果数字为负数，则在转换后的金额前面加上“负”字样。

2.人民币大写金额：=TEXT(NUMBERTEXT(A1),"[DBNum2][$￥-804]")&"整"此公式将数字转换为人民币大写金额，并在金额后面加上“整”字样。

3.美元大写金额：=TEXT(A1,"[$-409]0")&"美元整"此公式将数字转换为美元大写金额，并在金额后面加上“美元整”字样。

4.英文大写金额：=PROPER(TEXT(A1,"[ENG11A]General Number")) & " DOLLARS"此公式将数字转换为英文大写金额，并在金额后面加上“DOLLARS”。

5.澳大利亚元大写金额：=TEXT(A1,"[$-C09]0") & " Australian Dollars"此公式将数字转换为澳大利亚元大写金额，并在金额后面加上“Australian Dollars”。

6.日元大写金额：=TEXT(A1,"[$¥-411]")&"日元整"此公式将数字转换为日元大写金额，并在金额后面加上“日元整”。

7.欧元大写金额：=TEXT(A1,"[$€-40C]")&"欧元整"此公式将数字转换为欧元大写金额，并在金额后面加上“欧元整”。

8.加拿大元大写金额：=TEXT(A1,"[$C0A]") & " Canadian Dollars"此公式将数字转换为加拿大元大写金额，并在金额后面加上“Canadian Dollars”。

excel中人民币金额大写的函数在Excel 中，如果你想将人民币金额转换为大写形式，你可以使用以下步骤：1. 下载并安装VBA 代码模块：-打开Excel 文件。

-按下`Alt` + `F11` 打开Visual Basic for Applications (VBA) 编辑器。

-在菜单中选择"插入" -> "模块"，这将创建一个新的代码模块。

2. 复制并粘贴VBA 代码：-将以下VBA 代码复制并粘贴到新建的代码模块中：```vbaFunction RMBFormat(ByVal MyNumber)Dim Units As StringDim DecimalPlace As StringDim TempStr As StringDim DecimalSeparator As StringDim UnitName As StringDim Count As IntegerDim DecimalSeparatorLength As IntegerReDim Place(9) As StringPlace(2) = " Thousand "Place(3) = " Million "Place(4) = " Billion "Place(5) = " Trillion "' String representation of amount.MyNumber = Trim(CStr(MyNumber))' Position of decimal place 0 if none.DecimalPlace = InStr(MyNumber, ".")' Convert cents and set MyNumber to dollar amount.If DecimalPlace > 0 ThenTempStr = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))End IfCount = 1Do While MyNumber <> ""TempStr = GetHundreds(Right(MyNumber, 3))If TempStr <> "" Then Units = Place(Count) & TempStr & UnitsIf Len(MyNumber) > 3 ThenMyNumber = Left(MyNumber, Len(MyNumber) - 3)ElseMyNumber = ""End IfCount = Count + 1LoopRMBFormat = Units & GetTens(TempStr)End FunctionPrivate Function GetTens(TensText)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 FunctionPrivate Function 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 FunctionPrivate Function GetHundreds(MyNumber)Dim Result As StringIf Val(MyNumber) = 0 Then Exit FunctionMyNumber = Right("000" & MyNumber, 3)' Convert the hundreds place.If Mid(MyNumber, 1, 1) <> "0" ThenResult = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "End If' Convert the tens and ones place.Result = Result & GetTens(Mid(MyNumber, 2))GetHundreds = ResultEnd Function```3. 使用自定义函数：-保存并关闭VBA 编辑器。

excel表大写金额公式excel大写金额公式是【=IF（MOD（A2，1）=0，TEXT（INT（A2），"[DBNUM2]"）&"元"&"整"，TEXT（INT（A2），"[DBNUM2]"）&"元"&TEXT （MID（A2，LEN（INT（A2））+2，1），"[DBNUM2]D角"）&TEXT（MID （A2，LEN（INT（A2））+3，1），"[DBNUM2]D分"））】1、以A行为输入阿拉伯数字，B行为大写数字，在A1和B1分别填入对应的项目名称；2、在B2行录入：=IF（A2<0，"负"，""）&TEXT（TRUNC（ABS （ROUND（A2，2））），"[DBNum2]"）&"元"&IF（ISERR（FIND（"。

"，ROUND（A2，2））），""，TEXT（RIGHT（TRUNC（ROUND（A2，2）*10）），"[DBNum2]"））&IF（ISERR（FIND（"。

0"，TEXT（A2，"0.00"））），"角"，""）&IF（LEFT（RIGHT（ROUND（A2，2），3））="。

"，TEXT（RIGHT （ROUND（A2，2）），"[DBNum2]"）&"分"，"整"）；3、还可以在B2输入：=IF（MOD（A2，1）=0，TEXT（INT（A2），"[DBNUM2]"）&"元"&"整"，TEXT（INT（A2），"[DBNUM2]"）&"元"&TEXT （MID（A2，LEN（INT（A2））+2，1），"[DBNUM2]D角"）&TEXT（MID （A2，LEN（INT（A2））+3，1），"[DBNUM2]D分"））；4、您在A2输入阿拉伯数字，在B2就会自动显示大写金额。

超全的人民币金额大写excel公式有同学在微信平台提问人民币大写公式怎么写，小编就从网上搜集了一些，公式很多，也很复杂，有兴趣的同学可以研究一下，不想研究的就收藏起来备用。

使用方法很简单，把下面公式中的A2换成你表中数字所在单元格地址即可。

1=SUBSTITUTE(SUBSTITUTE(TEXT(TRUNC(FIXED(A2)),"[>0][dbnum2]G/通用格式元;[<0]负[dbnum2]G/通用格式元;;")&TEXT(RIGHT(FIXED(A2),2),"[dbnum2]0角0分;;"&IF(ABS(A2)>1%,"整",)),"零角",IF(ABS(A2)<1,,"零")),"零分","整")2 =SUBSTITUTE(SUBSTITUTE(IF(A2>-0.5%,,"负")&TEXT(INT(FIXED(ABS(A2))),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(FIXED(A2),2),"[dbnum2]0角0分;;"&IF(ABS(A2)>1%,"整",)),"零角",IF(ABS(A2)<1,,"零")),"零分","整")3 =SUBSTITUTE(SUBSTITUTE(IF(A2>-0.5%,,"负")&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(FIXED(A2),2),"[dbnum2]0角0分;;"&IF(ABS(A2)>1%,"整",)),"零角",IF(ABS(A2)<1,,"零")),"零分","整")4 =SUBSTITUTE(SUBSTITUTE(IF(A2>-0.5%,,"负")&TEXT(INT(FIXED(ABS(A2))),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(FIXED(A2),2),"[dbnum2]0角0分;;"&IF(ABS(A2)>1%,"整",)),"零角",IF(ABS(A2)<1,,"零")),"零分","整")5 =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(A2>-0.5%,,"负")&TEXT(INT(FIXED(ABS(A2))),"[dbnum2]")&TEXT(RIGHT(FIXED(A2),2)," [dbnum2]元0角0分;;元"&IF(ABS(A2)>1%,"整",)),"零角",IF(ABS(A2)<1,,"零")),"零元",),"零分","整")6 =SUBSTITUTE(SUBSTITUTE(IF(A2>-0.5%,,"负")&IF(ABS(A2)+0.5%<1,,TEXT(INT(ABS(A2)+0.5%),"[dbnum2]")&"元")&TEXT(RIGHT(FIXED(A2),2),"[dbnum2]0角0分;;"&IF(ABS(A2)>1%,"整",)),"零角",IF(ABS(A2)<1,,"零")),"零分","整")7 =IF(A2=0,"零",IF(A2>-0.5%,,"负")&TEXT(INT(ABS(A2)),"[dbnum2]G/通用格式元;;")&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(FIXED(A2),2),"[dbnum2]0角0分;;"),"零角",IF(ABS(A2)<1,,"零")),"零分","整"))8 =SUBSTITUTE(SUBSTITUTE(TEXT(TRUNC(FIXED(A2)),"[dbnum2]G/通用格式元;负[dbnum2]G/通用格式元;"&IF(A2>-0.5%,,"负"))&TEXT(RIGHT(FIXED(A2),2),"[dbnum2]0角0分;;"&IF(ABS(A2)>1%,"整",)),"零角",IF(ABS(A2)<1,,"零")),"零分","整")9 =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(B8<0,"负",)&TEXT(INT(ABS(B8)),"[dbnum2];;")&TEXT(MOD(ABS(B8)*100,100),"[>9][dbnum2]圆0角0分;[=0]圆整;[dbnum2]圆零0分"),"零分","整")," 圆零",)," 圆",)10 =SUBSTITUTE(SUBSTITUTE(TEXT(INT(A1),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(A1/1%,2),"[dbnum2]0角0分;;"&IF(A1,"整",)),"零角","零"),"零分","整")"大写（人民币）："&IF(A1-INT(A1)<0.005,TEXT(INT(A1),"[dbnum2]")&"元整",IF(A1*10-INT(A1*10)<0.05,TEXT(INT(A1),"[dbnum2]")&"元"&TEXT(INT(A1*10-INT(A1)*10),"[dbnum2]")&"角整",TEXT(INT(A1),"[dbnum2]")&"元"&TEXT(INT(A1*10-INT(A1)*10),"[dbnum2]")&"角"&TEXT((FIXED(A1*100,0)-INT(A1*10)*10),"[dbnum2]")&"分"))11 =IF(ABS(A2)<0.5%,"",SUBSTITUTE(SUBSTITUTE(IF(A2<0,"负",)&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(FIXED(A2),2),"[dbnum2]0角0分;;整"),"零角",IF(ABS(A2)<1,,"零")),"零分","整"))12 =SUBSTITUTE(SUBSTITUTE(IF(A1>-0.5%,,"负")&TEXT(INT(ABS(A1)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A1),2),"[dbnum2]0角0分;;"&IF(ABS(A1)>1%,"整",)),"零角",IF(ABS(A1)<1,,"零")),"零分","整")13 =IF(ABS(A2)<0.5%,"",SUBSTITUTE(SUBSTITUTE(IF(A2<0,"负",)&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A2),2),"[dbnum2]0角0分;;整"),"零角",IF(ABS(A2)<1,,"零")),"零分","整"))14 =IF(-RMB(A2),SUBSTITUTE(SUBSTITUTE(IF(A2<0,"负",)&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A2),2),"[dbnum2]0角0分;;整"),"零角",IF(ABS(A2)<1,,"零")),"零分","整"),"")15 =SUBSTITUTE(IF(-RMB(A2),IF(A2<0,"负",)&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A2),2),"[>9][dbnum2]0角0分;[>][dbnum2]0分;整"),""),"零分","整")16 =SUBSTITUTE(IF(-RMB(A2),IF(A2<0,"负",)&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A2),2),"[>9][dbnum2]0角0分;零[>][dbnum2]0分;整"),""),"零分","整")17 =SUBSTITUTE(SUBSTITUTE(IF(-RMB(A1),IF(A1<0,"负",)&TEXT(INT(ABS(A1)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A1),2),"[dbnum2]0角0分;;整"),),"零角",IF(ABS(A1)<1,,"零")),"零分","整")18 =TEXT(RMB(A1),"[=]g;"&TEXT(INT(ABS(A1)+0.5%),"[dbnum2]G/通用格式元;;")&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(RMB(A1),2),"[dbnum2]0角0分;;整"),"零角",IF(ABS(A1)<1,,"零")),"零分","整"))19 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(ABS(A2)<1,,0)&"[>][dbnum2]0分;整"),),"零分",)20=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分;整"),),"零分","整")21 =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,,"零")),"零分","整")22 =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分;整"),),"零分",)23 =TEXT(A2,";负")&SUBSTITUTE(TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&IF(-RMB(A2),TEXT(RIGHT(RMB(A2),2),"[>9][dbnum2]0角0分整;"&IF(A2^2<1,,0)&"[>][dbnum2]0分;整"),),"零分",)24 =TEXT(RMB(A1),"[=]g;"&TEXT(INT(ABS(A1)+0.5%),"[dbnum2]G/通用格式元;;")&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(RMB(A1),2),"[dbnum2]0角0分;;整"),"零角",IF(A1^2<1,,"零")),"零分","整"))25 =SUBSTITUTE(IF(-RMB(A2),TEXT(A2,";负")&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A2),2),"[>9][dbnum2]0角0分;"&IF(A2^2<1,,0)&"[>][dbnum2]0分;整"),),"零分","整")26=SUBSTITUTE(SUBSTITUTE(IF(-RMB(A2,2),TEXT(A2,";负")&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A2,2),2),"[dbnum2]0角0分;;整"),),"零角",IF(A2^2<1,,"零")),"零分","整")27 =TEXT(LEFT(RMB(A1),LEN(RMB(A1))-3),"[>0][dbnum2]G/通用格式元;[<0]负[dbnum2]G/通用格式元;;") &TEXT(RIGHT(RMB(A1),2),"[dbnum2]0角0分;;整")28 TEXT(INT(A3),"[dbnum2]")&"元"&IF(INT(A3*10)-INT(A3)*10=0,"",TEXT(INT(A3*10)-INT(A3)*10,"[dbn um2]")&"角")&IF(INT(A3*100)-INT(A3*10)*10=0,"整",TEXT(INT(A3*100)-INT(A3*10)*10,"[dbnum2]")&"分")29 =IF(OR(B1="",B1=0),"",TEXT(INT(B1),"[dbnum2]G/通用格式元;[dbnum2]G/通用格式元;;")&TEXT(--RIGHT(INT(B1*10)),"[dbnum2]#角;;;")&TEXT(--RIGHT(INT(B1*100)),"[dbnum2]#分;;整;"))。