ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 效率神器,一键搞定繁琐工作
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
让更多数据处理,一键完成 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 20353|回复: 27

[分享] [完整]人民币大写转换金额大写,"负元角零整分"不重复出现的简短快速公式

[复制链接]

TA的精华主题

TA的得分主题

发表于 2011-8-11 20:24 | 显示全部楼层 |阅读模式
本帖最后由 lunkuo 于 2011-8-19 20:25 编辑

EXCEL表格的数字转换成人民币金额大写,只有五个汉字"负元角整分"且只出现一次的最短公式只有225个字符,只有六个汉字"负元角零整分"且只出现一次的最短公式只有215个字符,“负元角零整分”只出现一次的最短公式只有195个字符:

第一个公式(基础版,只有五个汉字,且"负元角整分"只出现一次)[2011.8.16](278字符)
=IF(ROUND(B3,2),TEXT(B3,";负")&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(RMB(B3),2)),"[dbnum2]")&IF(TRUNC(B3)=TRUNC(ROUND(B3,2),1),,"角"))&IF(RIGHT(RMB(B3))="0","整",NUMBERSTRING(RIGHT(RMB(B3)),2)&"分"),"")

第二个公式(简洁版,"负元角零整分"只出现一次)[2011.8.17](195字符)
=IF(-RMB(B3),TEXT(B3,";负")&TEXT(INT(ABS(B3)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(LEFT(RIGHT(RMB(B3),2)),"[dbnum2]0角;;"&IF((-RMB(B3,0)=-RMB(B3))+(B3^2<1),,"零"))&TEXT(RIGHT(RMB(B3)),"[dbnum2]0分;;整"),"")

第三个公式(百炼版1,非常整洁)[2011.8.16](205字符)
=IF(ROUND(B3,2),TEXT(B3,";负")&TEXT(INT(ABS(B3)+0.5%),"[dbnum2]G/通用格式元;;")&IF((ROUND(B3,0)=ROUND(B3,2))+(ABS(B3)<9.5%),,TEXT(LEFT(RIGHT(RMB(B3),2)),"[dbnum2]0角;;零"))&TEXT(RIGHT(RMB(B3)),"[dbnum2]0分;;整"),"")

第四个公式(百炼版2,另一种写法,且用RMB(B3,2))[2011.8.17](206字符)
=IF(ROUND(B3,2),TEXT(B3,";负")&TEXT(INT(ABS(B3)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(LEFT(RIGHT(RMB(B3,2),2)),"[dbnum2]0角;;"&IF((ROUND(B3,0)=ROUND(B3,2))+(B3^2<1),,"零"))&TEXT(RIGHT(RMB(B3,2)),"[dbnum2]0分;;整"),"")

第五个公式(特别版1,只有五个汉字,且"负元角整分"只出现一次)[2011.8.18](225字符)
=IF(ROUND(B3,2),TEXT(B3,";负")&IF(ABS(B3)<0.995,,NUMBERSTRING(INT(ABS(B3)+0.5%),2)&"元")&IF((ROUND(B3,0)=ROUND(B3,2))+(ABS(B3)<9.5%),,TEXT(LEFT(RIGHT(RMB(B3),2)),"[dbnum2]0角;;[dbnum2]"))&TEXT(RIGHT(RMB(B3)),"[dbnum2]0分;;整"),"")

第六个公式(特别版2,只有六个汉字,且"负元角零整分"只出现一次)[2011.8.17](215字符)
=IF(ROUND(B3,2),TEXT(B3,";负")&IF(ABS(B3)<0.995,,NUMBERSTRING(INT(ABS(B3)+0.5%),2)&"元")&TEXT(LEFT(RIGHT(RMB(B3),2)),"[dbnum2]0角;;"&IF((ROUND(B3,0)=ROUND(B3,2))+(B3^2<1),,"零"))&TEXT(RIGHT(RMB(B3)),"[dbnum2]0分;;整"),"")

第七个公式(融合版,非SUBSTITUTE函数的简短公式)[2011.8.18](214字符)
=IF(ROUND(B3,2),TEXT(B3,";负")&TEXT(INT(ABS(B3)+0.5%),"[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分")),"")

20110818人民币大写

20110818人民币大写


第一个基础版公式是按照原来思路编写的,看了wshcw的两个公式后,了解了TEXT的(,";;")用法,把基础版公式修改为简洁版公式、百炼版公式和特别版公式,另外写出融合版公式。

这七个转换公式完美通过八大测试数字(1E+15,-1.01,1.995,0.995,0.55,0.095,0.01,0.001),可处理文本型数字,可处理千兆元,可正确显示负数,并可正确显示元角分的各种情况,公式非常严谨而且完善,运行非常快速。
七个公式其实只有两种思路,基础版、简洁版、百炼版和特别版属于同一种思路,融合版是另一种思路,力求最短的公式,考虑全方面情况。

人民币大写转换简洁版百炼版公式20110818.rar

108.31 KB, 下载次数: 889

人民币大写转换简洁版百炼版公式20110818.rar

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2011-8-11 21:10 | 显示全部楼层
轮廓朋友,你这公式写得有版有眼.对新人学习很有帮助.

TA的精华主题

TA的得分主题

 楼主| 发表于 2011-8-11 21:26 | 显示全部楼层
谢谢你,我从你公式学了不少

[ 本帖最后由 lunkuo 于 2011-8-11 21:51 编辑 ]

TA的精华主题

TA的得分主题

发表于 2011-8-12 11:29 | 显示全部楼层

TA的精华主题

TA的得分主题

 楼主| 发表于 2011-8-12 15:19 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
把基础版公式的IF(B3<0,"负",)改为TEXT(B3,";负"),再把全部公式的ROUND(B3,2)改为RMB函数,再次精简。

TA的精华主题

TA的得分主题

 楼主| 发表于 2011-8-14 19:54 | 显示全部楼层
帖子昨天修改好的,可能因为论坛升级,又变回前天的样子,只得重新修改。
经测试,MOD(RMB(B3),1)=0不能处理134217728.00以上的数字,原来是考虑用MOD函数比较简短,现用-RMB(B3,0)=-RMB(B3)代替

TA的精华主题

TA的得分主题

发表于 2011-8-14 22:32 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
感谢楼主,很好用,解决大问题了!

TA的精华主题

TA的得分主题

发表于 2011-8-16 14:47 | 显示全部楼层
在Excel2010中不行啊,数字都变回阿拉伯数字。。。所有公式都一样,有解决方案吗?

TA的精华主题

TA的得分主题

 楼主| 发表于 2011-8-16 23:48 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 lunkuo 于 2011-8-18 23:18 编辑
maozhy 发表于 2011-8-16 14:47
在Excel2010中不行啊,数字都变回阿拉伯数字。。。所有公式都一样,有解决方案吗?

在EXCEL2003和2007调试通过,今晚刚装了EXCEL2010,可以使用。。或者把你的单元格格式设置为“常规”状态

TA的精华主题

TA的得分主题

 楼主| 发表于 2011-8-16 23:53 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 lunkuo 于 2011-8-17 22:16 编辑

经测试,RMB、FIXED与ROUND这三个函数,在数字状态下,FIXED与ROUND的速度比较快,RMB最慢;在字符状态下,RMB速度最快,FIXED与ROUND比较慢,再次修改之。

公式优化有些又恢复原样了,-RIGHT(RMB(B3))=0恢复原来的RIGHT(RMB(B3))="0",RMB(B3)^2<1恢复原来的ABS(B3)<0.995
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-11-22 03:10 , Processed in 0.040641 second(s), 15 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表