c语言人民币大小写转换,人民币大小写转换函数.xls

水深无声 2022-10-15 06:54 212阅读 0赞

人民币大小写转换函数.xls

Sheet3

Sheet1

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(RIGHT(TEXT(A1,".00"),2))\*1=0,"","角")&IF(RIGHT(TEXT(A1,".00"))\*1=0,"整",TEXT(RIGHT(TEXT(A1,".00")),"\[dbnum2\]")&"分")))

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\]")&"分")))

IF(AND(RIGHT(INT(ROUND(ABS(A1),2)/10000))="0",RIGHT(INT(ROUND(ABS(A1),2)/1000))<>"0"),SUBSTITUTE(TEXT(INT(ROUND(ABS(A1),2)),"\[DBNum2\]"),"万","万零"),TEXT(INT(ROUND(ABS(A1),2)),"\[DBNum2\]"))

IF(AND(RIGHT(TRUNC(ABS(A1)/10000))="0",RIGHT(TRUNC(ABS(A1)/1000))<>"0"),SUBSTITUTE(TEXT(TRUNC(ABS(A1)),"\[DBNum2\]"),"万","万零"),TEXT(TRUNC(ABS(A1)),"\[DBNum2\]"))

近来转了转几个EXCEL相关论坛，发现有部分网友在寻求数字金额转换为人民币大写的方法。出于好奇，我在网上搜索了一番，什么VBA、加载宏，公式函数各式方法争相亮相，应有尽有。但我的习惯是，只要能用公式解决的问题，坚决不使用VBA。所以我就特别地关注使用公式来生成的方法，结果是用公式的方法可谓是更加地灿烂缤纷，使人眼花缭乱。

通过分析我收集到的二十几个公式，发现比较牛，适合我口味的公式有三个，在此我将其列出。

公式一：

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)),"\[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,"负",""),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","","分"))

公式三：

IF(ROUND(A1,2)=0,"",IF(ROUND(ABS(A1),2)>=1,TEXT(INT(ROUND(ABS(A1),2)),"\[DBNum2\]")&"元","")&IF(RIGHT(TEXT(A1,".00"),2)\*1=0,"整",IF(RIGHT(TEXT(A1,".00"),4