【EXCLE案例】Excel电子表格中将数字转化为大写金额(一个公式[1].八种函数应用详解)

分享10种Excel人民币小写转大写的函数公式=TEXT(INT(A5),”[DBNum2]“)&”元”&TEXT(INT(A5*10)-INT(A5)*10,”[DBNum2]“)&”角”&TEXT(INT(A5*100)-INT(A5*10)*10,”[DBNum2]“)&”分”=IF(A5<0,REPLACE(IF(TRUNC(A5)=A5,TEXT(A5,”[DBNum2]“)&”元整”,IF(TRUNC(A5*10)=A5*10,TEXT(TRUNC(A5),”[DBNum2]“)&”元”&TEXT(RIGHT(A5),”[DBNum2]“)&”角整”,TEXT(TRUNC(A5),”[DBNum2]“)&”元”&IF(ISNUMBER(FIND(“.0″,A5)),”零”,TEXT(LEFT(RIGHT(A5,2)),”[DBNum2]“)&”角”)&TEXT(RIGHT(A5),”[DBNum2]“)&”分”)),1,1,”负”),IF(TRUNC(A5)=A5,TEXT(A5,”[DBNum2]“)&”元整”,IF(TRUNC(A5*10)=A5*10,TEXT(TRUNC(A5),”[DBNum2]“)&”元”&TEXT(RIGHT(A5),”[DBNum2]“)&”角整”,TEXT(TRUNC(A5),”[DBNum2]“)&”元”&IF(ISNUMBER(FIND(“.0″,A5)),”零”,TEXT(LEFT(RIGHT(A5,2)),”[DBNum2]“)&”角”)&TEXT(RIGHT(A5),”[DBNum2]“)&”分”)))=IF(A5<0,REPLACE(IF(TRUNC(A5)=A5,TEXT(A5,”[DBNum2]“)&”元整”,IF(TRUNC(A5*10)=A5*10,TEXT(TRUNC(A5),”[DBNum2]“)&”元”&TEXT(RIGHT(A5),”[DBNum2]“)&”角整”,TEXT(TRUNC(A5),”[DBNum2]“)&”元”&IF(ISNUMBER(FIND(“.0″,A5)),”零”,TEXT(LEFT(RIGHT(A5,2)),”[DBNum2]“)&”角”)&TEXT(RIGHT(A5),”[DBNum2]“)&”分”)),1,1,”负”),IF(TRUNC(A5)=A5,TEXT(A5,”[DBNum2]“)&”元整”,IF(TRUNC(A5*10)=A5*10,TEXT(TRUNC(A5),”[DBNum2]“)&”元”&TEXT(RIGHT(A5),”[DBNum2]“)&”角整”,TEXT(TRUNC(A5),”[DBNum2]“)&”元”&IF(ISNUMBER(FIND(“.0″,A5)),”零”,TEXT(LEFT(RIGHT(A5,2)),”[DBNum2]“)&”角”)&TEXT(RIGHT(A5),”[DBNum2]“)&”分”)))=IF(ISNUMBER(A5),IF(TRUNC(A5)=0,IF(AND(MID(TEXT(TRUNC(A5,2),”0.00″),LEN(TEXT (TRUNC(A5,2),”0.00″))-1,1)=”0″,RIGHT(TEXT(TRUNC(A5,2),”0.00″),1)=”0″),”零元”,”"),TEXT(TRUNC(A5),”[DBNUM2]“)&”元”)&(IF(MID(TEXT(TRUNC(A5,2),”0.00″),LEN(TEXT(TRUNC(A5,2),”0.00″))-1,1)=”0″,IF( OR(TRUNC(A5)=0,RIGHT(TEXT(TRUNC(A5,2),”0.00″),1)=”0″),”",”零”),TEXT(MID(A5,LEN(TEXT(TRUNC(A5,2),”0.00″))-1,1),”[DBNUM2]“)&”角”))&(IF(RIGHT(TEXT(TRUNC(A5,2),”0.00″),1)=”0″,”",TEXT(RIGHT(TEXT(TRUNC(A5,2) ,”0.00″),1),”[DBNUM2]“)&”分”)),IF(A5=”",”",”不是有效金额,请重新输入!”))=IF(ISNUMBER(A5),IF(TRUNC(A5)=0,IF(AND(MID(TEXT(TRUNC(A5,2),”0.00″),LEN(TEXT (TRUNC(A5,2),”0.00″))-1,1)=”0″,RIGHT(TEXT(TRUNC(A5,2),”0.00″),1)=”0″),”零元”,”"),(IF(A5<0,”负”,”"))&TEXT(TRUNC(ABS(A5)),”[DBNUM2]“)&”元”)&(IF(MID(TEXT(TRUNC(ABS(A5),2),”0.00″),LEN(TEXT(TRUNC(ABS(A5),2),”0.00″))-1 ,1)=”0″,IF(OR(TRUNC(ABS(A5))=0,RIGHT(TEXT(TRUNC(ABS(A5),2),”0.00″),1)=”0″),”",”零”),TEXT(MID(ABS(A5),LEN(TEXT(TRUNC(ABS(A5),2),”0.00″))-1,1),”[DBNUM2]“)&”角”))&(IF(RIGHT(TEXT(TRUNC(ABS(A5),2),”0.00″),1)=”0″,”",TEXT(RIGHT(TEXT(TRUNC( ABS(A5),2),”0.00″),1),”[DBNUM2]“)&”分”)),”不是有效金额,请重新输入!”)=IF(A5=0,”",CONCATENATE(IF(INT(A5)=0,”",TEXT(INT(A5),”[DBNum2]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)),”[DBNum2]G/通用格式角”)),IF(INT(MID(RIGHT(FIXED(A5,2,1),1),1,1))=0,”整”,TEXT(INT(MID(RIGHT(FIXED(A5,2,1),1),1,1)),”[DBNum2]G/通用格式分”))))=IF(A5=0,”",CONCATENATE(IF(INT(ABS(A5))=0,”",TEXT(INT(ABS(A5)),”[DBNum2]G/通用格式元”)),IF(INT(MID(RIGHT(FIXED(ABS(A5),2,1),2),1,1))=0,IF(INT(MID(RIGHT(FIXED(A5,2 ,1),1),1,1))=0,”",IF(INT(ABS(A5))=0,”",”零”)),TEXT(INT(MID(RIGHT(FIXED(A5,2,1),2),1,1)),”[DBNum2]G/通用格式角”)),IF(INT(MID(RIGHT(FIXED(A5,2,1),1),1,1))=0,”整”,TEXT(INT(MID(RIGHT(FIXED(A5,2,1),1),1,1)),”[DBNum2]G/通用格式分”))))=IF(A5<0,”负”,”")&IF(TRUNC(A5)=A5,TEXT(IF(A5<0,-A5,A5),”[DBNum2]“)&”元整”,IF(TRUNC(A5*10)=A5*10,TEXT(TRUNC(IF(A5<0,-A5,A5)),”[DBNum2]“)&”元”&TEXT(RIGHT(A5),”[DBNum2]“)&”角整”,TEXT(TRUNC(A5),”[DBNum2]“)&”元”&IF(ISNUMBER(FIND(“.0″,A5)),”零”,TEXT(LEFT(RIGHT(A5,2)),”[DBNum2]“)&”角”)&TEXT(RIGHT(A5),”[DBNum2]“)&”分”))=IF(ISERROR(FIND(“.”,A5)),NUMBERSTRING(INT(A5),2)&”元整”,IF(ISERROR(NUMBERSTRING(MID(A5,FIND(“.”,A5)+2,1),2)),NUMBERSTRING(INT(A 5),2)&”元”&NUMBERSTRING(MID(A5,FIND(“.”,A5)+1,1),2)&”角”,NUMBERSTRING(INT(A5),2)&”元”&NUMBERSTRING(MID(A5,FIND(“.”,A5)+1,1),2)&”角”&NUMBERSTRING(MID(A5,FIND(“.”,A5)+2,1),2)&”分”))=IF(MOD(A5,1)=0,NUMBERSTRING(A5,2)&”元整”,IF(ISERROR(NUMBERSTRING(MID(A5,FIND(“.”,A5)+2,1),2)),NUMBERSTRING(INT(A 5),2)&”元”&NUMBERSTRING(MID(MOD(A5,1),3,1),2)&”角”,NUMBERSTRING(INT(A5),2)&”元”&NUMBERSTRING(MID(MOD(A5,2),3,1),2)&”角”&NUMBERSTRING(MID(ROUND(MOD(A5,1),3),4,1),2)&”分”))。

excel表人民币大写公式
首先，在单元格输入【=NUMBERSTRING（F2，2）】（F2代表需要转换成大写数值的单元格），确定即可；其次，选中需转换的单元格，鼠标右键单击，依次选择【设置单元格格式】-【数字】-【特殊】-【中文大写数字】，【确定】即可。

方法一
用【NUMBERSTRING】公式，如：将F2单元格的4500转换成大写，则在G2输入公式
【=NUMBERSTRING（F2，2）】，然后点击空白单元格或按键盘上【回车】键就能得出：肆仟伍佰；
方法二
在表格中输入数值，选中想要转化为大写的数字单元格，鼠标右键选择【设置单元格格式】，在弹出来的设置对话框选择第一排的【数字】，分类中找到【特殊】，选择【中文大写数字】，点击【确定】就可以转换成大写。

Excel-VBA自定义函数将人民币数字转换为大写应用场景财务报表中将人民币数字转换为大写知识要点1：Round(单元格, 2)返回一个数值，该数值是按照指定的小数位数进行四舍五入运算的结果2：Range.Text 属性返回或设置指定对象中的文本。

String 型，只读3：[DBnum2] 格式函数, 数字转中文大写Function 大写(单元格 As String) As StringApplication.VolatileDim Bum, rmbsIf Len(单元格) = 0 Then 大写 = ''If 单元格 = 0 Then 大写 = '零元整': Exit Functionrmbs = Replace(Replace(Application.Text(Round(单元格, 2), '[DBnum2]'), '.', '元'), '-', '负')rmbs = IIf(Left(Right(rmbs, 3), 1) = '元', Left(rmbs, Len(rmbs) - 1) & '角' & Right(rmbs, 1) & '分', IIf(Left(Right(rmbs, 2), 1) = '元', rmbs & '角', IIf(rmbs = '零', '', rmbs & '元整')))rmbs = Replace(Replace(rmbs, '零元', ''), '零角', '') '将零元零角转换为空文本大写 = rmbsEnd Function。

鉴于本身提供将数字转换为大写表示地功能根本不能正常应用在实际投标或财务应用之中，所以要自己建设，暂时找到了三种实现途径，经过测试均功能正常，还有一种是在书上找到地，懒得进来了，反正也差不多了.方法，通过在表格框（例如在“”单元）中直接输入以下公式：(<,"金额为负无效",(((""),"(人民币)零元",(<,"(人民币)",((),"[](人民币)通用格式")"元"))))(((*)()*),((*)(*)*,"","零"),(((*)()*,"[]")"角"))(((*)(*)*),"整",(((*)(*)*),"[]")"分") 个人收集整理勿做商业用途然后在“”中输入数字，就可看到效果.方法，通过（宏）输入转换公式: 点击菜单“工具”>“宏”>“编辑器”,在编辑器窗口中，点击菜单“插入”>“模块”，在出现地窗口中输入以下内容：个人收集整理勿做商业用途( ) ' 人民币中文大写函数"分角元拾佰仟万拾佰仟亿拾佰仟万""壹贰叁肆伍陆柒捌玖""整零元零零零万零零零亿零零零万"< "（负）"((), "") *> : "数字超出转换范围!!":: "零元零分":(())()' 数字转换过程((, , ))<> ' 非零数字转换(, , ) (, , )' 数字零地转换(, ) <> "零"(, , )' 特殊数位转换(, () ) (, , ),(, () ) (, , ) "零"(, () , ) <> "亿"(, () ) (, , ) "零""(人民币)" ()个人收集整理勿做商业用途然后切换回,在“”单元中输入数字，在“”单元中输入：“()”，就可看到效果.个人收集整理勿做商业用途方法，同样是通过公式，方法同上，公式如下：( ) ',"" '定义位置代码"" '定义汉字缩写"零壹贰叁肆伍陆柒捌玖拾佰仟萬億圆整角分" '定义大写汉字(, ".") > (, (, ".") )个人收集整理勿做商业用途() > "数目太大，无法换算！请输入一亿亿以下地数字", , "错误提示": '只能转换一亿亿元以下数目地货币！个人收集整理勿做商业用途(, "") '格式化货币""()(, , ) (, () , )(, ) """" '***元整((, ), ) "" (, ) "" '*元*角*分(, "", "") '避免零千(如：肆萬零千零贰佰)(, "", "") '避免零百(如：肆萬壹千零佰)(, "", "") '避免零十(如：贰佰零拾零肆)个人收集整理勿做商业用途<> (, "", "")(, "", "") '避免双零(如：壹仟零零肆)(, "", "") '避免零億(如：億贰佰壹十零億)(, "", "") '避免零萬(如：萬贰佰壹十零萬)(() (, ) "", (, () ), ) '避免壹十(如：壹拾肆；壹拾)(() , (, ".", ""), (, ".", ".")) '避免零元(如：贰拾零圆；零圆壹角贰分)个人收集整理勿做商业用途(, (, , ), (, , )) '大写汉字"(人民币)"个人收集整理勿做商业用途切换回,在“”单元中输入数字，在“”单元中输入：“()”，就可看到效果.个人收集整理勿做商业用途实现地效果比较如下：(人民币)壹仟贰佰叁拾肆元伍角整直接公式(人民币)壹仟贰佰叁拾肆元伍角整()(人民币)壹仟贰佰叁拾肆圆伍角零分()。