资源描述：
《表格自动换算金额大写.doc》由会员上传分享，免费在线阅读，更多相关内容在应用文档-天天文库。
1、EXCEL表格怎么自动换算金额大写：A1是123.45,A2我怎么能出现壹佰贰拾叁元肆角伍分!!!第一步，打开已有的表格，同时把这个网页也打开，稍后能用到这个公式。因为公式太长，必须得用复制，粘贴的方法，才保准。第二步，复制公式，冒号后一个也不能落下。即：=SUBSTITUTE(SUBSTITUTE(IF(A1<0,"负","")&TEXT(TRUNC(ABS(ROUND(A1,2))),"[DBNum2]")&"元"&IF(ISERR(FIND(".",ROUND(A1,2))),"",TEXT(RIGHT(TRUNC(ROUND(A1,2)*
2、10)),"[DBNum2]"))&IF(ISERR(FIND(".0",TEXT(A1,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(A1,2),3))=".",TEXT(RIGHT(ROUND(A1,2)),"[DBNum2]")&"分",IF(ROUND(A1,2)=0,"","整")),"零元零",""),"零元","")。第三步，双击进入A2，使A2单元格中有光标在闪，点粘贴。第四步，回车。2.如果没有小数点的，就直接在单元格右击-设置单元格格式-特殊-中文大写数字。3.a列格式为数值-货币,b列格式为特殊
3、-中文大写数字-自定义-格式后加上圆整函数:b1=a1EXCEL表格中怎么设置美元的大写金额1.比如说1.2美元输入自动变成“壹美元贰美分”这样。选中要输入数据的区域,右击>"设置单元格格式">"数字">"自定义">0"美元"0"美分",将数据扩大10倍输入,如1.2美元只输入"12"即可.数字要设置成大写呢？应该怎么设置？选中要输入数据的区域,右击>"设置单元格格式">"数字">"自定义">"[DBNum2][$-804]G/通用格式">"确定",效果如图：或者用如下公式转换：=TEXT(INT(A1),"[DBNum2][$-804]G/通用
4、格式")&"美元"&TEXT((A1-INT(A1))*10,"[DBNum2][$-804]G/通用格式")&"美分"效果如图：继续追问：到这里以后是如何设置成美元美分的?公式怎么出来的哦？补充回答：直接设置的话，我目前只会设置到此（）效果。用公式的话，直接从12.5单元格转换，不需设置格式。1.假设在C6，以下为中文大写2.=IF(INT(C6)-C6=0,TEXT(C6,"[DBNum2][$-804]G/通用格式""美元整"""),TEXT(INT(C6),"[DBNum2][$-804]G/通用格式""美元""")&TEXT(RIGHT
5、(C6,LEN(C6)-FIND(".",C6)),"[DBNum2][$-804]G/通用格式""美分"""))补充回答：公式短一点：=SUBSTITUTE(TEXT(C6,"[DBNum2][$-804]G/通用格式"),".","美元")&"美分"3.EXCEL表格中设置美元的大写金额假设在C6，以下为中文大写=SUBSTITUTE(TEXT(C6,"[DBNum2][$-804]G/通用格式"),".","美元")&"美分"人民币金额大写的Excel公式公式一：SUBSTITUTE(SUBSTITUTE(IF(A1<0,"负","")&T
6、EXT(TRUNC(ABS(ROUND(A1,2))),"[DBNum2]")&"元"&IF(ISERR(FIND(".",ROUND(A1,2))),"",TEXT(RIGHT(TRUNC(ROUND(A1,2)*10)),"[DBNum2]"))&IF(ISERR(FIND(".0",TEXT(A1,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(A1,2),3))=".",TEXT(RIGHT(ROUND(A1,2)),"[DBNum2]")&"分",IF(ROUND(A1,2)=0,"","整")),"零元零"
7、,""),"零元","")公式二：CONCATENATE(IF(A1<0,"负",""),TEXT(IF(TRUNC(A1)=0,"",TRUNC(ABS(A1))),"[DBNum2]"),IF(INT(TRUNC(A1))=0,"","元"),TEXT(IF(OR(ABS(A1)<0.1,TRUNC(A1)=A1),"",RIGHT(TRUNC(A1*10),1)),"[DBNum2]"),IF(RIGHT(TRUNC(A1*10),1)="0","","角"),TEXT(IF(RIGHT(TRUNC(A1*100),1)="0","",RI
8、GHT(TRUNC(A1*100),1)),"[DBNum2]"),IF(RIGHT(TRUNC(A1*100),1)="0","","
