2024年5月10日发(作者：)

人民币大小写转换Excel公式

以下假设你在A1单元格中输入金额数字，在其它单元格输入以下公式就可以了

公式1

=SUBSTITUTE(SUBSTITUTE(IF(A1<0,"负

","")&TEXT(TRUNC(ABS(ROUND(A1,2))),"[DBNum2]")&"元

"&IF(ISERR(FIND(".",ROUND(A1,2))),"",TEXT(RIGHT(TRUNC(ROUND(A1,2)*10)),"[DBN

um2]"))&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,"","整")),"零元零",""),"零元","")

公式2

=IF((A1-INT(A1))=0,TEXT(A1,"[DBNUM2]")&"元整

",IF(INT(A1*10)-A1*10=0,TEXT(INT(A1),"[DBNUM2]")&"元

"&TEXT((INT(A1*10)-INT(A1)*10),"[DBNUM2]")&"角整",TEXT(INT(A1),"[DBNUM2]")&"

元"&IF(INT(A1*10)-INT(A1)*10=0,"零",TEXT(INT(A1*10)-INT(A1)*10,"[DBNUM2]")&"角

")&TEXT(RIGHT(A1,1),"[DBNUM2]")&"分"))

公式3

=IF(ABS(A1)<0.005,"",IF(A1<0,"负

",)&IF(INT(ABS(A1)),TEXT(INT(ABS(A1)),"[dbnum2]")&"元

",)&IF(INT(ABS(A1)*10)-INT(ABS(A1))*10,TEXT(INT(ABS(A1)*10)-INT(ABS(A1))*10,"[db

num2]")&"角",IF(INT(ABS(A1))=ABS(A1),,IF(ABS(A1)<0.1,,"零

")))&IF(ROUND(ABS(A1)*100-INT(ABS(A1)*10)*10,),TEXT(ROUND(ABS(A1)*100-INT(A

BS(A1)*10)*10,),"[dbnum2]")&"分","整"))

以上公式包含在附件的excel表格中。

下面有更多网友提供的excel公式(未验证)

1

=IF(A1=0,"",CONCATENATE(IF(INT(A1)=0,"",TEXT(INT(A1),"[DBNum2]G/通用格式元

")),IF(INT(MID(RIGHT(FIXED(A1,2,1),2),1,1))=0,IF(INT(MID(RIGHT(FIXED(A1,2,1),1),1,1)

)=0,"",IF(INT(A1)=0,"","零")),TEXT(INT(MID(RIGHT(FIXED(A1,2,1),2),1,1)),"[DBNum2]G/

通用格式角")),IF(INT(MID(RIGHT(FIXED(A1,2,1),1),1,1))=0,"整

",TEXT(INT(MID(RIGHT(FIXED(A1,2,1),1),1,1)),"[DBNum2]G/通用格式分"))))

2

=IF(A1<0,"負","")&IF(ABS(A1)>1,TEXT(TRUNC(ABS(ROUND(A1,2))),"[DBNum2]")&"元

","")&IF(ISERR(FIND(".",ROUND(A1,2))),"",TEXT(RIGHT(TRUNC(ROUND(A1,2)*10)),"[D

BNum2]"))&IF(ISERR(FIND(".0",TEXT(A1,"0.00"))),"角

","")&IF(LEFT(RIGHT(ROUND(A1,2),3))=".",TEXT(RIGHT(ROUND(A1,2)),"[DBNum2]")&"

分","整")

3

=IF(A1<0,"负

","")&IF(TRUNC(ROUND(A1,2))=0,"",TEXT(TRUNC(ABS(ROUND(A1,2))),"[DBNum2]")&"

元

")&IF(ISERR(FIND(".",ROUND(A1,2))),"",TEXT(RIGHT(TRUNC(ROUND(A1,2)*10)),"[DBN

um2]"))&IF(ISERR(FIND(".0",TEXT(A1,"0.00"))),"角

","")&IF(LEFT(RIGHT(ROUND(A1,2),3))=".",TEXT(RIGHT(ROUND(A1,2)),"[DBNum2]")&"

分","整")

4

=SUBSTITUTE(SUBSTITUTE(IF(A1<0,"負

","")&TEXT(TRUNC(ABS(ROUND(A1,2))),"[DBNum2]")&"元

"&IF(ISERR(FIND(".",ROUND(A1,2))),"",TEXT(RIGHT(TRUNC(ROUND(A1,2)*10)),"[DBN

um2]"))&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,"","整")),"零元零",""),"零元","")

5

=IF(ROUND(A1,2)<0,"无效数值",IF(ROUND(A1,2)=0,"零

",IF(ROUND(A1,2)<1,"",TEXT(INT(ROUND(A1,2)),"[dbnum2]")&"元

")&IF(INT(ROUND(A1,2)*10)-INT(ROUND(A1,2))*10=0,IF(INT(ROUND(A1,2))*(INT(ROU

ND(A1,2)*100)-INT(ROUND(A1,2)*10)*10)=0,"","零

"),TEXT(INT(ROUND(A1,2)*10)-INT(ROUND(A1,2))*10,"[dbnum2]")&"角

")&IF((INT(ROUND(A1,2)*100)-INT(ROUND(A1,2)*10)*10)=0,"整

",TEXT((INT(ROUND(A1,2)*100)-INT(ROUND(A1,2)*10)*10),"[dbnum2]")&"分")))

6

=IF(ISNUMBER(A1),IF(A1<0,"无效数值",IF(A1<0.005,"零

",IF(A1<0.995,"",TEXT(INT(A1+0.005),"[dbnum2]")&"元

")&IF(LEFT(RIGHT(FIXED(A1,2),2),1)="0",IF(RIGHT(FIXED(A1,2),1)="0","",IF(A1>0.995,"

零","")),TEXT(LEFT(RIGHT(FIXED(A1,2),2),1),"[dbnum2]")&"角

")&IF(RIGHT(FIXED(A1,2),1)="0","整",TEXT(RIGHT(FIXED(A1,2),1),"[dbnum2]")&"分"))),"

非数值!")

7

=IF(ISNUMBER(A1),IF(ROUND(A1,2)<0,"无效数值",IF(ROUND(A1,2)=0,"零

",IF(ROUND(A1,2)<1,"",TEXT(INT(ROUND(A1,2)),"[dbnum2]")&"元

")&IF(INT(ROUND(A1,2)*10)-INT(ROUND(A1,2))*10=0,IF((INT(ROUND(A1,2)*100)-INT(R

OUND(A1,2)*10)*10)=0,"","零

"),TEXT(INT(ROUND(A1,2)*10)-INT(ROUND(A1,2))*10,"[dbnum2]")&"角

")&IF((INT(ROUND(A1,2)*100)-INT(ROUND(A1,2)*10)*10)=0,"整

",TEXT((INT(ROUND(A1,2)*100)-INT(ROUND(A1,2)*10)*10),"[dbnum2]")&"分"))),"非数

值！！！")

8

=IF(ROUND(A1,2)<0,"无效数值",IF(ROUND(A1,2)=0,"零

",IF(ROUND(A1,2)<1,"",TEXT(INT(ROUND(A1,2)),"[dbnum2]")&"元

")&IF(INT(ROUND(A1,2)*10)-INT(ROUND(A1,2))*10=0,IF(INT(ROUND(A1,2))*(INT(ROU

ND(A1,2)*100)-INT(ROUND(A1,2)*10)*10)=0,"","零

"),TEXT(INT(ROUND(A1,2)*10)-INT(ROUND(A1,2))*10,"[dbnum2]")&"角

")&IF((INT(ROUND(A1,2)*100)-INT(ROUND(A1,2)*10)*10)=0,"整

",TEXT((INT(ROUND(A1,2)*100)-INT(ROUND(A1,2)*10)*10),"[dbnum2]")&"分")))

9

=TEXT(INT(A1),"[dbnum2]")&"元

"&IF(INT(A1*10)-INT(A1)*10=0,"",TEXT(INT(A1*10)-INT(A1)*10,"[dbnum2]")&"角

")&IF(INT(A1*100)-INT(A1*10)*10=0,"整

",TEXT(INT(A1*100)-INT(A1*10)*10,"[dbnum2]")&"分")

10

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

(RIGHT(TRUNC(A1*100),1)="0","","分"))

11学会计论坛

=IF(ROUND(A1,2)=0,"",IF(ROUND(ABS(A1),2)>=1,TEXT(INT(ROUND(ABS(A1),2)),"[DBN

um2]")&"元","")&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)),"[DBNum

2]")&"角

","")&IF(RIGHT(TEXT(A1,".00"))*1>0,TEXT(RIGHT(TEXT(A1,".00")),"[DBNum2]")&"分","

整")))

12

=IF(A1=0,"",IF(ABS(A1)<0.995,"",TEXT(INT(ROUND(ABS(A1),2)),"[DBNum2]")&"元

")&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))),"[dbnum2]")&IF(LEFT(RIG

HT(TEXT(A1,".00"),2))*1=0,"","角")&IF(RIGHT(TEXT(A1,".00"))*1=0,"整

",TEXT(RIGHT(TEXT(A1,".00")),"[dbnum2]")&"分")))

13

=IF(A1=0,"",IF(ABS(A1)<1,"",TEXT(TRUNC(ABS(A1)),"[DBNum2]")&"元

")&IF(RIGHT(TRUNC(A1*100),2)*1=0,IF(ABS(A1)<0.01,"","整

"),IF(ABS(A1)<0.1,"",TEXT(RIGHT(TRUNC(A1*10)),"[dbnum2]"))&IF(RIGHT(TRUNC(A1*

10))*1=0,"","角")&IF(RIGHT(TRUNC(A1*100))*1=0,"整

",TEXT(RIGHT(TRUNC(A1*100)),"[dbnum2]")&"分")))