在EXCEL的一个表格中输入金额，在另一个表格中会自动翻译成大写

Question

如题所述

Answer 1

单击一个带转换的单元格，命名为X，然后在其他任何一单元格输入=IF(ROUND(x,2)<0,"无效数值",IF(ROUND(x,2)=0,"零",IF(ROUND(x,2)<1,"",TEXT(INT(ROUND(x,2)),"[dbnum2]")&"元")&IF(INT(ROUND(x,2)*10)-INT(ROUND(x,2))*10=0,IF(INT(ROUND(x,2))*(INT(ROUND(x,2)*100)-INT(ROUND(x,2)*10)*10)=0,"","零"),TEXT(INT(ROUND(x,2)*10)-INT(ROUND(x,2))*10,"[dbnum2]")&"角")&IF((INT(ROUND(x,2)*100)-INT(ROUND(x,2)*10)*10)=0,"整",TEXT((INT(ROUND(x,2)*100)-INT(ROUND(x,2)*10)*10),"[dbnum2]")&"分")))，看看，保证符合财经大写的表达。

Answer 2

sheet1:A1:A10 输入金额；sheet2:A1:A10显示大写金额
sheet2中的A1中的公式：=IF(Sheet1!A1<0,"负","")&TEXT(TRUNC(ABS(ROUND(Sheet1!A1,2))),"[DBNum2]")&"元"&IF(ISERR(FIND(".",ROUND(Sheet1!A1,2))),"",TEXT(RIGHT(TRUNC(ROUND(Sheet1!A1,2)*10)),"[DBNum2]"))&IF(ISERR(FIND(".0",TEXT(Sheet1!A1,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(Sheet1!A1,2),3))=".",TEXT(RIGHT(ROUND(Sheet1!A1,2)),"[DBNum2]")&"分","整")
向下带公式复制本回答被提问者采纳

Answer 3

=IF(A1<0,"负","")&TEXT(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]")&"分","整")

Answer 4

设输入金额的单元格是A1，在B1输入
=IF(OR(A1="",A1=0),"",TEXT(INT(A1),"[dbnum2]")&IF(INT(A1)=A1,"元整","元")&IF(AND(INT(A1)<>A1,--RIGHT(INT(A1*10))=0),"零",TEXT(--RIGHT(INT(A1*10)),"[dbnum2]#角;;;"))&TEXT(--RIGHT(INT(A1*100)),"[dbnum2]#分;;"))
试试了！！

Answer 5

NO红袖添香 的正解。