jiu97 发表于 2008-5-27 21:31:24

EXCEL中金额小写巧转大写公式

财务经常在用excel做表格的时候经常会遇到将金额的小写转换成大写的困扰.以下提供三个公式供大家学习参考.采用以下公式,小写自动转化为大写.
公式1:
SUBSTITUTE(SUBSTITUTE(IF(A1<0,"负","")&TEXT(TRUNC(ABS(ROUND(A1,2))),"")&"元"&IF(ISERR(FIND(".",ROUND(A1,2))),"",TEXT(RIGHT(TRUNC(ROUND(A1,2)*10)),""))&IF(ISERR(FIND(".0",TEXT(A1,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(A1,2),3))=".",TEXT(RIGHT(ROUND(A1,2)),"")&"分",IF(ROUND(A1,2)=0,"","整")),"零元零",""),"零元","")

公式2:
CONCATENATE(IF(A1<0,"负",""),TEXT(IF(TRUNC(A1)=0,"",TRUNC(ABS(A1))),""),IF(INT(TRUNC(A1))=0,"","元"),TEXT(IF(OR(ABS(A1)<0.1,TRUNC(A1)=A1),"",RIGHT(TRUNC(A1*10),1)),""),IF(RIGHT(TRUNC(A1*10),1)="0","","角"),TEXT(IF(RIGHT(TRUNC(A1*100),1)="0","",RIGHT(TRUNC(A1*100),1)),""),IF(RIGHT(TRUNC(A1*100),1)="0","","分"))

公式3:
IF(ROUND(A1,2)=0,"",IF(ROUND(ABS(A1),2)>=1,TEXT(INT(ROUND(ABS(A1),2)),"")&"元","")&IF(RIGHT(TEXT(A1,".00"),2)*1=0,"整",IF(RIGHT(TEXT(A1,".00"),4)*1>=1,IF(RIGHT(TEXT(A1,".00"),2)*1>9,"","零"),IF(ROUND(ABS(A1),2)>=1,"零",""))&IF(RIGHT(TEXT(A1,".00"),2)*1>9,TEXT(LEFT(RIGHT(TEXT(A1,".00"),2)),"")&"角","")&IF(RIGHT(TEXT(A1,".00"))*1>0,TEXT(RIGHT(TEXT(A1,".00")),"")&"分","整")))

公式4：
IF(A1=0,"",IF(ABS(A1)<0.995,"",TEXT(INT(ROUND(ABS(A1),2)),"")&"元")&IF(RIGHT(TEXT(A1,".00"),2)*1=0,IF(ABS(A1)<0.005,"","整"),TEXT(IF(ABS(A1)<0.095,"",LEFT(RIGHT(TEXT(A1,".00"),2))),"")&IF(LEFT(RIGHT(TEXT(A1,".00"),2))*1=0,"","角")&IF(RIGHT(TEXT(A1,".00"))*1=0,"整",TEXT(RIGHT(TEXT(A1,".00")),"")&"分")))

如果上面都还不清楚,后面还提供了一个压缩包,里面有excel的例子.

[ 本帖最后由 jiu97 于 2008-5-27 22:38 编辑 ]

phemen79 发表于 2015-3-11 09:56:14

东西确实是不错的，早就在用了，不过还是赞同一下
- 本文出自阳光网驿--企业信息化专家，原文地址：http://bbs.sunwy.org/thread-21219-1-1.html

liangda6 发表于 2015-7-24 22:57:20

谢谢楼主提供三种转换方法供学习，辛苦了!

gtzhyx 发表于 2015-5-18 19:36:09

哇哦，这么厉害，楼主真棒，赞一个

abis01 发表于 2008-11-26 22:38:06

不错，支持一下！！！！！加上讲解就更好了！！！！

mfc42 发表于 2009-4-12 12:57:24

这个好，不用花钱，马上能测试

mfc42 于 2009-4-12 13:03 补充以下内容

刚才测试了一下，都能用。谢谢LZ的无私奉献

yjx_1 发表于 2009-5-9 15:26:02

测试了下　不错　比较适用

supermx 发表于 2009-5-28 16:12:06

嗯，一直受这个大小写转换的困惑，不了无意中发现楼主的公式，小试了一下，很不错。

sunrui07 发表于 2009-6-19 15:12:20

你们太强了,,,什么资料都有,真是牛人啊,学习了~~~~~

gemwo 发表于 2009-6-22 16:50:36

好东下收了，努力赚钱ing.wwwww

bocjs 发表于 2009-9-20 08:46:05

正急需呢
:D :D :D :(:D

haomenlangzi 发表于 2009-11-10 16:25:40

谢谢，使用的东西收藏

lansh01 发表于 2010-9-4 13:09:34

东西确实是不错的，早就在用了，不过还是赞同一下
页: [1] 2
查看完整版本: EXCEL中金额小写巧转大写公式