|
【导读】 本文介绍如何写出真正符合财务规范的，小写金额转大写的公式。实际工作中，小写金额转大写有两种形式：支票式和套打式。 一、支票形式的小写金额转大写 二、套打形式的小写金额转大写 对一个给出的小写金额，通过公式自动地转换成大写，这可以减少人工转换时出现的差错，而且提高了工作效率。对于财务资料，对转换后的大写金额格式有较为严格的要求。下面介绍如何写出这种真正合格的公式。 一、支票形式的小写金额转大写 如图，单元格A1为小写金额107000.50，转成的大写有两种形式，C2的“壹拾万柒仟元伍角整”可以称为宽松形式，C3的“壹拾万零柒仟元零伍角整”可称为严格形式。我们看看如何用公式转成这两种形式。 在转换前，我们应确保单元格A1的小写金额只有两位小数。建议设置单元格格式为“#,##0.00”，并在选项中设置计算时“将精度设为所显示的精度”。 公式1（字符数132）： =IF(A1>=1,NUMBERSTRING(INT(A1),2)&"元",)&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(RMB(A1),2),"[dbnum2]0角0分;;整"),"零角",IF(A1<1,,"零")),"零分","整") 公式1返回的是宽松形式，并且假设转换的金额大于零。 公式1可以看成由两部分构成：元以上部分和角分部分。 公式中元以上的部分：IF(A1>=1,NUMBERSTRING(INT(A1),2)&"元",) 公式中的角分部分：SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(RMB(A1),2),"[dbnum2]0角0分;;整"),"零角",IF(A1<1,,"零")),"零分","整") 元以上部分的核心是NUMBERSTRING函数。角分部分比较复杂，这里只强调最关键的一点：为了避免浮点数造成的误差，不能通过乘除运算从小写金额中提取角分的数字，必须首先把小写金额转换成文本。 公式2（字符数147）： =IF(A1>0,IF(A1>=1,NUMBERSTRING(INT(A1),2)&"元",)&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(RMB(A1),2),"[dbnum2]0角0分;;整"),"零角",IF(A1<1,,"零")),"零分","整"),"零元整") 公式2在公式1外面套了一个IF，当转换的金额为零时，返回“零元整”（可按自己的需要改为其他文字，或改为空文本）。 公式3（字符数179）： =IF(A1,IF(A1<0,"负",)&IF(ABS(A1)>=1,NUMBERSTRING(INT(ABS(A1)),2)&"元",)&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(RMB(ABS(A1)),2),"[dbnum2]0角0分;;整"),"零角",IF(ABS(A1)<1,,"零")),"零分","整"),"零元整") 公式3在公式2的基础上，再考虑转换的金额为负数时，在大写金额前加个“负”字。 以上公式都转换成宽松形式，下面介绍严格形式的公式。 公式4（字符数325）： =IF(A1>=1,IF(AND(A1>10^4,INT((A1/10^5-INT(A1/10^5))*10)=0,INT((A1%%-INT(A1%%))*10)>0),SUBSTITUTE(NUMBERSTRING(INT(A1),2),"万","万零"),NUMBERSTRING(INT(A1),2))&"元",)&IF(AND(A1>1,INT((A1/10-INT(A1/10))*10)=0,INT((A1-INT(A1))*10)>0),"零",)&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(RMB(A1),2),"[dbnum2]0角0分;;整"),"零角",IF(A1<1,,"零")),"零分","整") 公式4在公式1的基础上，增加了两个判断：判断“万”后是否加“零”，判断“元”后是否加“零”。 如果不想在“元”后加“零”，可删掉公式中这部分的判断，公式变成（字符数减少71）： =IF(A1>=1,IF(AND(A1>10^4,INT((A1/10^5-INT(A1/10^5))*10)=0,INT((A1%%-INT(A1%%))*10)>0),SUBSTITUTE(NUMBERSTRING(INT(A1),2),"万","万零"),NUMBERSTRING(INT(A1),2))&"元",)&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(RMB(A1),2),"[dbnum2]0角0分;;整"),"零角",IF(A1<1,,"零")),"零分","整") 公式5（字符数340）： =IF(A1>0,IF(A1>=1,IF(AND(A1>10^4,INT((A1/10^5-INT(A1/10^5))*10)=0,INT((A1%%-INT(A1%%))*10)>0),SUBSTITUTE(NUMBERSTRING(INT(A1),2),"万","万零"),NUMBERSTRING(INT(A1),2))&"元",)&IF(AND(A1>1,INT((A1/10-INT(A1/10))*10)=0,INT((A1-INT(A1))*10)>0),"零",)&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(RMB(A1),2),"[dbnum2]0角0分;;整"),"零角",IF(A1<1,,"零")),"零分","整"),"零元整") 公式6（字符数427）： =IF(A1,IF(A1<0,"负",)&IF(ABS(A1)>=1,IF(AND(ABS(A1)>10^4,INT((ABS(A1)/10^5-INT(ABS(A1)/10^5))*10)=0,INT((ABS(A1)%%-INT(ABS(A1)%%))*10)>0),SUBSTITUTE(NUMBERSTRING(INT(ABS(A1)),2),"万","万零"),NUMBERSTRING(INT(ABS(A1)),2))&"元",)&IF(AND(ABS(A1)>1,INT((ABS(A1)/10-INT(ABS(A1)/10))*10)=0,INT((ABS(A1)-INT(ABS(A1)))*10)>0),"零",)&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(RMB(ABS(A1)),2),"[dbnum2]0角0分;;整"),"零角",IF(ABS(A1)<1,,"零")),"零分","整"),"零元整") 公式5、公式6是在公式4的基础上，增加判断小写金额是否零或负数。如果是打印支票，用公式4就足够了，支票金额不会出现零和负数的。 在这几个公式中，都需要多次调用小写金额所在的单元格A1。如果小写金额不在单元格A1，比如在G5，在公式中一个个地修改会比较麻烦，而且容易漏改。这时候可用“替换”功能，按Ctrl+H，然后在“查找内容”文本框中输入“A1”，在“替换为”文本框中输入“G5”，最后点“替换”按钮。 二、套打形式的小写金额转大写 下面介绍一种套打形式的小写金额转大写的方法。这种方法写出来的公式非常简单，做出来的视觉效果非常棒。如下图： 图中，B9:F9区域设置为合并单元格，字体设置为加粗，水平对齐方式设置为“分散对齐”。B9输入下面的公式： =SUBSTITUTE(TEXT(D8,"[DBNum2]00000000.00"),".",) 图中的公式返回文本"零零壹贰零零零伍零肆"，返回值中并没有“仟佰拾万元角分”这些字，也没有小数点。 然后，插入一个横排文本框，在文本框中输入“仟佰拾万仟佰拾元角分”，把文本框拉到B9:F9区域上方的适当位置，调整文本框大小，在文本框的各个文字中间用空格隔开适当的距离。 通过文本框的右键快捷菜单，打开“设置形状格式”对话框，在里面设置“无线条”，并把透明度设置为100%。 【相关阅读】 单元格格式代码详解http://blog.sina.com.cn/s/blog_14e89401f0102wc38.html |
|
|
