Excel数字小写金额转换汉字大写金额公式的简单设置

________

Excel数字小写金额转换汉字大写金额公式的简单设置

 华能呼伦贝尔能源有限公司 卞学敏

Excel数字小写金额转换汉字大写金额公式在会计工作中是经常应用到的，它能提高工作效率和会计数据的准确性。

网上有很多设置Excel数字小写金额转汉字大写金额公式的方法，基本上都很繁琐，且功能不全面（对负金额不能转换）。

本人在Excel会计工作实践中，设计了一个数字小写金额转换汉字大写金额的公式，公式仅500多个字符，个人感觉还很满意，现拿出来供大家分享，不足之处，也请大家不吝赐教。

公式如下：

=IF(ROUND(A1,2)=0,"",IF(A1<0,"负","")&IF(ABS(A1)>=1,TEXT(INT(ROUND(ABS(A1),2)),"[dbnum2]")&"元","")&IF(VALUE(MID(RIGHT(TEXT((ABS(A1)*100+100),"###"),2),1,1))=0,IF(VALUE(RIGHT(TEXT(ABS(A1)*100,"###"),1))=0,"整",IF(ABS(A1)>=1,"零","")&TEXT(VALUE(RIGHT(TEXT(ABS(A1)*100,"###"),1)),"[dbnum2]")&"分"),IF(VALUE(RIGHT(TEXT(ABS(A1)*100,"###"),1))=0,TEXT(VALUE(MID(RIGHT(TEXT((ABS(A1)*100+100),"###"),2),1,1)),"[dbnum2]")&"角整",TEXT(VALUE(MID(RIGHT(TEXT((ABS(A1)*100+100),"###"),2),1,1)),"[dbnum2]")&"角"&TEXT(VALUE(RIGHT(TEXT(ABS(A1)*100,"###"),1)),"[dbnum2]")&"分")))

公式中A1为小写金额所在单元格，实际使用中只需将其替换为实际小写金额所在单元格即可，非常方便。

公式可以对正数、零（本公式转换为空白，与支票一致，如需转换为“零元整”，只需在公式第一个双引号内加入即可）、负数（会计数据有时也涉及到）均可进行转换。

最近，在网上看到高手设计的更简洁的转换公式，缺点是只能把“0”转换为空白，不能转换为“零元整”，公式如下：

=SUBSTITUTE(SUBSTITUTE(IF(-RMB(A1),IF(A1>0,,"负")&TEXT(INT(ABS(A1)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A1,2),2),"[dbnum2]0角0分;;整"),),"零角",IF(A1^2<1,,"零")),"零分","整")

将以上两个公式结合一下，达到既简短，又能达到“0”的空白和“零元整”两种（根据需要只能选其中一种）转换结果的目的。公式如下：

=IF(ROUND(A1,2)=0,"",IF(A1<0,"负","")&IF(ABS(A1)>=1,TEXT(INT(ROUND(ABS(A1),2)),"[dbnum2]")&"元","")&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(RMB(A1,2),2),"[dbnum2]0角0分;;整"),"零角",IF(A1^2<1,,"零")),"零分","整"))

以上三个公式的转换效果是一样的，如下图所示：

 

 _____________________________________________________________________________________________________________________________

EXCEL表格的数字转换成人民币金额大写，只有五个汉字"负元角整分"且只出现一次的最短公式只有240字符，只有六个汉字"负元角零整分"且只出现一次的最短公式只有210字符，“负元角零整分”只出现一次的最短公式只有199个字符：
第一个公式（基础版，只有五个汉字，且"负元角整分"只出现一次）[2011.8.7]（282字符）=IF(-FIXED(B3),IF(B3<0,"负",)&IF(ABS(B3)<0.995,,NUMBERSTRING(INT(ABS(B3)+0.5%),2)&"元")&IF(OR(ROUND(B3,0)=ROUND(B3,2),ABS(B3)<9.5%),,TEXT(LEFT(RIGHT(FIXED(B3),2)),"[dbnum2]")&IF(TRUNC(B3)=TRUNC(ROUND(B3,2),1),,"角"))&IF(-RIGHT(FIXED(B3))=0,"整",NUMBERSTRING(RIGHT(FIXED(B3)),2)&"分"),"")
第二个公式（简洁版1，"负元角零整分"只出现一次）[2011.8.9]（199字符）=IF(-RMB(B3),TEXT(B3,";负")&TEXT(INT(ABS(B3)+0.5%),"[dbnum2]G/通用格式元;;")&IF(OR(MOD(ROUND(B3,2),1)=0,ABS(B3)<9.5%),,TEXT(LEFT(RIGHT(RMB(B3),2)),"[dbnum2]0角;;零"))&TEXT(RIGHT(RMB(B3)),"[dbnum2]0分;;整"),"")
第三个公式（简洁版2，把RMB(B3)替换为RMB(B3,2)或FIXED(B3)）[2011.8.9]（205字符）=IF(-RMB(B3,2),TEXT(B3,";负")&TEXT(INT(ABS(B3)+0.5%),"[dbnum2]G/通用格式元;;")&IF(OR(MOD(ROUND(B3,2),1)=0,ABS(B3)<9.5%),,TEXT(LEFT(RIGHT(RMB(B3,2),2)),"[dbnum2]0角;;零"))&TEXT(RIGHT(RMB(B3,2)),"[dbnum2]0分;;整"),"")
第四个公式（加强版1，只有五个汉字，且"负元角整分"只出现一次）[2011.8.10）（240字符）=IF(-RMB(B3),TEXT(B3,";负")&IF(RMB(B3)^2<1,,NUMBERSTRING(INT(ABS(B3)+0.5%),2)&"元")&IF(OR(MOD(ROUND(B3,2),1)=0,ABS(B3)<9.5%),,TEXT(LEFT(RIGHT(RMB(B3),2)),"[dbnum2]")&IF(-LEFT(RIGHT(RMB(B3),2))=0,,"角"))&TEXT(RIGHT(RMB(B3)),"[dbnum2]0分;;整"),"")
第五个公式（加强版2，只有六个汉字，且"负元角零整分"只出现一次）[2011.8.10]（210字符）=IF(-RMB(B3),TEXT(B3,";负")&IF(RMB(B3)^2<1,,NUMBERSTRING(INT(ABS(B3)+0.5%),2)&"元")&IF(OR(MOD(ROUND(B3,2),1)=0,ABS(B3)<9.5%),,TEXT(LEFT(RIGHT(RMB(B3),2)),"[dbnum2]0角;;零"))&TEXT(RIGHT(RMB(B3)),"[dbnum2]0分;;整"),"")
第六个公式（融合版，非SUBSTITUTE函数的简短公式）[2011.8.10]（213字符）=IF(-RMB(B3),TEXT(B3,";负")&TEXT(INT(RMB(ABS(B3))),"[dbnum2]G/通用格式元;;")&IF(-RIGHT(RMB(B3))=0,TEXT(LEFT(RIGHT(RMB(B3),2)),"[dbnum2]0角整;;整"),TEXT(RIGHT(RMB(B3),2),""&IF(B3^2<1,,0)&"[<10][dbnum2]0分;[dbnum2]0角0分")),"")

在EXCELhome论坛见到wshcw写的“[原创]人民币大写数字相互转换”的两个公式，太强了，我平时只用些简单的函数，第一次认识SUBSTITUTE函数，但感觉SUBSTITUTE有些取巧。第一个基础版公式是按照我原来思路编写的，看了wshcw的公式后，初步了解了TEXT的(,";;")用法，把基础版公式修改为简洁版公式和加强版公式，另外写出融合版公式。
这六个转换公式完美通过六大测试数字（-1.01，11.995，0.55，0.1，0.01，0.001），可处理文本型数字，可正确显示负数，并可正确显示元角分的各种情况，其中简洁版是"负元角零整分"六个汉字不重复的最短公式。六个公式其实只有两种思路，基础版、简洁版和加强版属于同一种思路，融合版是另一种思路，而且都不使用SUBSTITUTE函数，力求最短的公式，考虑全方面情况。