Question

在Excel 如何实现把阿拉伯数字变成大写人民币金额？

Answer 1

=IF(ISNUMBER(A2),SUBSTITUTE(SUBSTITUTE(IF(ROUND(A2,2),TEXT(A2,";负")&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式圆;;")&TEXT(RIGHT(TEXT(A2,".00"),2),"[dbnum2]0角0分;;整"),),"零角",IF(A2^2<1,,"零")),"零分",""),"")

Answer 2

所以就想了利用Excel函数公式自动转化成人民币大写金额。现在和大家分享一样：=IF(B9=1,TEXT(INT(ROUND(ABS(B9),2)),"[DBNum2]")&"元","")&IF(RIGHT(TEXT(B9,".00"),2)*1=0,"整",IF(RIGHT(TEXT(B9,".00"),4)*1>=1,IF(RIGHT(TEXT(B9,".00"),2)*1>9,"","零"),IF(ROUND(ABS(B9),2)>=1,"零",""))&IF(RIGHT(TEXT(B9,".00"),2)*1>9,TEXT(LEFT(RIGHT(TEXT(B9,".00"),2)),"[DBNum2]")&"角","")&IF(RIGHT(TEXT(B9,".00"))*1>0,TEXT(RIGHT(TEXT(B9,".00")),"[DBNum2]")&"分","整")))注：B9是填写的小写数字。

Answer 3

1、使用宏，这个有点麻烦，无法简单的写出来
2、使用函数
=IF(TRIM(Y25)="","",IF(Y25=0,"",IF(Y25<0,"负",)&IF(INT(Y25),TEXT(INT(ABS(Y25)),"[dbnum2]")&"元",)&IF(INT(ABS(Y25)*10)-INT(ABS(Y25))*10,TEXT(INT(ABS(Y25)*10)-INT(ABS(Y25))*10,"[dbnum2]")&"角",IF(INT(ABS(Y25))=ABS(Y25),,IF(ABS(Y25)<0.1,,"零")))&IF(ROUND(ABS(Y25)*100-INT(ABS(Y25)*10)*10,),TEXT(ROUND(ABS(Y25)*100-INT(ABS(Y25)*10)*10,),"[dbnum2]")&"分","整")))

Answer 4

假如你数字在a1，只能在除a1外的其他单元格转变，则放结果的单元格=IF(A1<=0,"",TEXT(INT(A1),"[dbnum2]")&IF(INT(A1)=A1,"元整","元")&IF(INT(A1)=A1,"",TEXT(--RIGHT(INT(A1*10)),IF(--RIGHT(INT(A1*100))>0,"[=0]零;[dbnum2]#角","[dbnum2]#角;;;"))&TEXT(--RIGHT(A1*100),"[dbnum2]#分;;;")))