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

合集下载

EXCLE小写金额转换大写公式

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财务小技巧--人民币数字金额转汉字大写公式

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公式作为财会人，一定会在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 提供了一个金额大写转换公式。

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

首先，我们来了解一下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公式

人民币大小写转换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中实现自动生成金额大写公式

2、解除excel密码保护
Excel—“撤销工作表保护密码”的破解并获取原始密码
在日常工作中，您是否遇到过这样的情况：您用Excel编制的报表、表格、程序等，在单元格中设置了公式、函数等，为了防止其他人修改您的设置或者防止您自己无意中修改，您可能会使用Excel的工作表保护功能，但时间久了保护密码容易忘记，这该怎么办？有时您从网上下载的Excel格式的小程序，您想修改，但是作者加了工作表保护密码，怎么办？您只要按照以下步骤操作，Excel工作表保护密码瞬间即破！
DBLSPACE & "Also, remember that the password was " & _
"put there for a reason. Don't stuff up crucial formulas " & _
"or data." & DBLSPACE & "Access and use of some data " & _
'
' Norman Harker and JE McGimpsey 27-Dec-2002 (Version 1.1)
' Modified 2003-Apr-04 by JEM: All msgs to constants, and
' eliminate one Exit Sub (Version 1.1.1)
Const MSGNOPWORDS2 As String = "There was no protection to " & _
"workbook structure or windows." & DBLSPACE & _

在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金额大小写转换公式大全

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。

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

超全的人民币金额大写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/通用格式。