【Excel公式教程】小写金额转换为大写金额的4个公式套路解析 |
您所在的位置:网站首页 › excel怎么数字变大写金额 › 【Excel公式教程】小写金额转换为大写金额的4个公式套路解析 |
在会计做账的时候经常会用到大写金额,那么由小写金额如何转换为大写金额呢? 在网上搜一下会发现这类公式有很多,有的还特别长,今天就整理了四个不同效果的公式与大家分享。 前三个公式只考虑金额大于零的情况,第四个公式考虑到金额小于零的情况;第二个公式和第四个公式显示比较符号实际需要,接下来就让我们逐个分析这四个公式的思路。 四个公式带来的不同结果如图所示: 公式1 =TEXT(INT(A2),"[DBNUM2]")&"元"&TEXT(MID(A2,LEN(INT(A2))+2,1),"[DBNUM2]D角")&TEXT(MID(A2,LEN(INT(A2))+3,1),"[DBNUM2]D分")&"整"← 左右滑动查看完整公式 → 公式一的显示效果为:这个公式整体结构比较好理解,使用了三个text,分别将金额的整数部分(元)、第一位小数(角)和第二位小数(分)提取出来,再指定对应的大写格式。 公式涉及到四个基本函数:text、int、mid和len,这几个函数在之前的文章都有讲解,自己看看帮助或者百度也很容易理解,就不过多解释了。 如果不要求最后必须有个“整”字的话,仅仅是金额转为大写,这个公式非常方便。 公式2=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(NUMBERSTRING(INT(A2),2)&"元"&TEXT(MOD(A2,1)*100,"[dbnum2]0角0分"),"零角零分","整"),"零角","零"),"零分","")← 左右滑动查看完整公式 → 公式二的显示效果为: 这个公式使用了三个SUBSTITUTE函数进行嵌套,同时用到了NUMBERSTRING这个隐藏函数,再就是int、text和mod函数,我们来分析一下公式的整体思路: 公式的核心部分是: NUMBERSTRING(INT(A2),2)&"元"&TEXT(MOD(A2,1)*100,"[dbnum2]0角0分") 这里还是把金额分成两部分,NUMBERSTRING(INT(A2),2)是利用了int将金额的整数部分提取出来,使用NUMBERSTRING函数转为大写,关于这个函数的用法可以参考: Excel隐藏函数 NUMBERSTRING 后一部分是把金额的小数部分提取出来,利用text指定为大写格式,MOD(A2,1)是用来提取一个数字的小数部分,有兴趣的朋友可以自己试试。 我们看看经过这样处理后显示的效果: 其实这一步之后,已经大体上实现了大写金额的转换,只是会出现"零角零分"、"零角"、"零分"之类的字眼,因此使用三次SUBSTITUTE函数进行替换,就整体效果而言,这个公式也比较容易理解,同时转换结果也比较通用,值得一用。 公式3=RIGHT(TEXT(A2*100,"[DBNUM2]0仟0佰0拾0万0仟0佰0拾0元0角0分整"),LEN(A2)*2+IF(ISERR(FIND(".",A2)),5,IF(LEN(A2)-FIND(".",A2)=1,1,-1)))← 左右滑动查看完整公式 → 公式三的显示效果为: 这个公式又采用了另一个思路,核心部分是: TEXT(A2*100,"[DBNUM2]0仟0佰0拾0万0仟0佰0拾0元0角0分整") 这部分公式的效果为: 先对金额扩大100倍,变成一个整数,再用text在对应的数字后面加上单位。 之后使用right函数将这个结果进行截取,我们知道,right这个函数是将指定数据从右边的若干位进行截取,公式三在确定right的第二个参数也就是截取的位数上下了一番功夫: LEN(A2)*2+IF(ISERR(FIND(".",A2)),5,IF(LEN(A2)-FIND(".",A2)=1,1,-1))← 左右滑动查看完整公式 → 这么长的一段就是为了确定截取几位字符,在上一步使用text给金额的每一个数字都增加了一个单位,所以使用LEN(A2)*2将实际截取的字符长度加倍,后面的if是考虑到数据是不是有小数点的情况,分别计算需要截取的长度,主要利用的len和find函数,采用了根据特定内容确定长度的常规思路。 整体而言,公式三采用的是字符提取的思路,关于这个思路,是一类比较典型的函数嵌套用法,今后会有专门的讨论,就不过多解释了。 公式4=IF(ROUND(A2,2)=0,"",IF(A2=1,TEXT(INT(ROUND(ABS(A2),2)),"[dbnum2]")&"圆","")&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(RMB(A2,2),2),"[dbnum2]0角0分;;整"),"零角",IF(A2^2 |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |