excel表格怎么自動(dòng)換算金額大寫

《excel表格怎么自動(dòng)換算金額大寫》由會(huì)員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在行業(yè)資料-天天文庫(kù)。

1、EXCEL表格怎么自動(dòng)換算金額大寫：A1是123.45,A2我怎么能出現(xiàn)壹佰貳拾叁元肆角伍分!!!第一步，打開(kāi)已有的表格，同時(shí)把這個(gè)網(wǎng)頁(yè)也打開(kāi)，稍后能用到這個(gè)公式。因?yàn)楣教L(zhǎng)，必須得用復(fù)制，粘貼的方法，才保準(zhǔn)。第二步，復(fù)制公式，冒號(hào)后一個(gè)也不能落下。即：=SUBSTITUTE(SUBSTITUTE(IF(A1<0,"負(fù)","")&TEXT(TRUNC(ABS(ROUND(A1,2))),"[DBNum2]")&"元"&IF(ISERR(FIND(".",ROUND(A1,2))),"",TEXT(RIGHT(TRUNC(ROUND(A1,2)*10)),"[DBNum2]"))&I

2、F(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,"","整")),"零元零",""),"零元","")。第三步，雙擊進(jìn)入A2，使A2單元格中有光標(biāo)在閃，點(diǎn)粘貼。第四步，回車。2.如果沒(méi)有小數(shù)點(diǎn)的，就直接在單元格右擊-設(shè)置單元格格式-特殊-中文大寫數(shù)字。3.a列格式為數(shù)值-貨幣,b列格式為特殊-中文大寫數(shù)字-自定義-格式后加上圓整函數(shù):b1=a1EXCEL表格中怎么設(shè)

3、置美元的大寫金額1.比如說(shuō)1.2美元輸入自動(dòng)變成“壹美元貳美分”這樣。選中要輸入數(shù)據(jù)的區(qū)域,右擊>"設(shè)置單元格格式">"數(shù)字">"自定義">0"美元"0"美分",將數(shù)據(jù)擴(kuò)大10倍輸入,如1.2美元只輸入"12"即可.數(shù)字要設(shè)置成大寫呢？應(yīng)該怎么設(shè)置？選中要輸入數(shù)據(jù)的區(qū)域,右擊>"設(shè)置單元格格式">"數(shù)字">"自定義">"[DBNum2][$-804]G/通用格式">"確定",效果如圖：或者用如下公式轉(zhuǎn)換：=TEXT(INT(A1),"[DBNum2][$-804]G/通用格式")&"美元"&TEXT((A1-INT(A1))*10,"[DBNum2][$-804]G/通用格式")&"

4、美分"效果如圖：繼續(xù)追問(wèn)：到這里以后是如何設(shè)置成美元美分的?公式怎么出來(lái)的哦？補(bǔ)充回答：直接設(shè)置的話，我目前只會(huì)設(shè)置到此（）效果。用公式的話，直接從12.5單元格轉(zhuǎn)換，不需設(shè)置格式。1.假設(shè)在C6，以下為中文大寫2.=IF(INT(C6)-C6=0,TEXT(C6,"[DBNum2][$-804]G/通用格式""美元整"""),TEXT(INT(C6),"[DBNum2][$-804]G/通用格式""美元""")&TEXT(RIGHT(C6,LEN(C6)-FIND(".",C6)),"[DBNum2][$-804]G/通用格式""美分"""))補(bǔ)充回答：公式短一點(diǎn)：=SUBSTIT

5、UTE(TEXT(C6,"[DBNum2][$-804]G/通用格式"),".","美元")&"美分"3.EXCEL表格中設(shè)置美元的大寫金額假設(shè)在C6，以下為中文大寫=SUBSTITUTE(TEXT(C6,"[DBNum2][$-804]G/通用格式"),".","美元")&"美分"人民幣金額大寫的Excel公式公式一：SUBSTITUTE(SUBSTITUTE(IF(A1<0,"負(fù)","")&TEXT(TRUNC(ABS(ROUND(A1,2))),"[DBNum2]")&"元"&IF(ISERR(FIND(".",ROUND(A1,2))),"",TEXT(RIGHT(TRUNC

6、(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,"","整")),"零元零",""),"零元","")公式二：CONCATENATE(IF(A1<0,"負(fù)",""),TEXT(IF(TRUNC(A1)=0,"",TRUNC(ABS(A1))),"[DBNum2]"),IF(INT(TRUNC(A1))=

7、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","",RIGHT(TRUNC(A1*100),1)),"[DBNum2]"),IF(RIGHT(TRUNC(A1*100),1)="0","","