Question

excel如何将带小数的数字转换为圆角分

Answer 1

假设你A2是金额（数字格式），在任意空单元格输入

=SUBSTITUTE(SUBSTITUTE(IF(A2>-0.5%,,"负")&TEXT(INT(FIXED(ABS(A2))),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(FIXED(A2),2),"[dbnum2]0角0分整;;"&IF(ABS(A2)>1%,"整",)),"零角整",IF(ABS(A2)<1,,"零")),"零分整","整")

Answer 2

若数字在A1，在空单元格输入：=SUBSTITUTE(SUBSTITUTE(IF(-RMB(A1),IF(A1<0,"负",)&TEXT(INT(ABS(A1)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A1),2),"[dbnum2]0角0分;;整"),),"零角",IF(A1^2<1,,"零")),"零分","整")

Answer 3

公式如下：=IF(H6<0.005,"",IF(H6<0,"负",)&IF(INT(ABS(H6)),TEXT(INT(ABS(H6)),"[dbnum2]")&"元",)&IF(INT(ABS(H6)*10)-INT(ABS(H6))*10,TEXT(INT(ABS(H6)*10)-INT(ABS(H6))*10,"[dbnum2]")&"角",IF(INT(ABS(H6))=ABS(H6),,IF(ABS(H6)<0.1,,"零")))&IF(ROUND(ABS(H6)*100-INT(ABS(H6)*10)*10,),TEXT(ROUND(ABS(H6)*100-INT(ABS(H6)*10)*10,),"[dbnum2]")&"分","整"))

Answer 4

A2为数字单元格，在其它位置输入公式
=SUBSTITUTE(SUBSTITUTE(TEXT(TRUNC(FIXED(A2)),"[>0][dbnum2];[<0]负[dbnum2];;")&TEXT(RIGHT(FIXED(A2),2),"元[dbnum2]0角0分;;"&IF(ABS(A2)>1%,"元整",)),"零角正",IF(ABS(A2)<1,,"零")),"零分","整")