请教，123 显示为壹元贰角叁分！

wahahagigi · 发表于 2010-3-26 13:19

用这条公式把123显示为壹元贰角叁分，谢谢！

=IF(A21<0,REPLACE(IF(TRUNC(A21)=A21,TEXT(A21,"[DBNum2]")&"元整",IF(TRUNC(A21*10)=A21*10,TEXT(TRUNC(A21),"[DBNum2]")&"元"&TEXT(RIGHT(A21),"[DBNum2]")&"角整",TEXT(TRUNC(A21),"[DBNum2]")&"元"&IF(ISNUMBER(FIND(".0",A21)),"零",TEXT(LEFT(RIGHT(A21,2)),"[DBNum2]")&"角")&TEXT(RIGHT(A21),"[DBNum2]")&"分")),1,1,"负"),IF(TRUNC(A21)=A21,TEXT(A21,"[DBNum2]")&"元整",IF(TRUNC(A21*10)=A21*10,TEXT(TRUNC(A21),"[DBNum2]")&"元"&TEXT(RIGHT(A21),"[DBNum2]")&"角整",TEXT(TRUNC(A21),"[DBNum2]")&"元"&IF(ISNUMBER(FIND(".0",A21)),"零",TEXT(LEFT(RIGHT(A21,2)),"[DBNum2]")&"角")&TEXT(RIGHT(A21),"[DBNum2]")&"分")))

zez · 发表于 2010-3-26 17:24

那条公式是不行的，用这条可以：
=IF((A21/100)<0,REPLACE(IF(TRUNC((A21/100))=(A21/100),TEXT((A21/100),"[DBNum2]")&"元整",IF(TRUNC((A21/100)*10)=(A21/100)*10,TEXT(TRUNC((A21/100)),"[DBNum2]")&"元"&TEXT(RIGHT((A21/100)),"[DBNum2]")&"角整",TEXT(TRUNC((A21/100)),"[DBNum2]")&"元"&IF(ISNUMBER(FIND(".0",(A21/100))),"零",TEXT(LEFT(RIGHT((A21/100),2)),"[DBNum2]")&"角")&TEXT(RIGHT((A21/100)),"[DBNum2]")&"分")),1,1,"负"),IF(TRUNC((A21/100))=(A21/100),TEXT((A21/100),"[DBNum2]")&"元整",IF(TRUNC((A21/100)*10)=(A21/100)*10,TEXT(TRUNC((A21/100)),"[DBNum2]")&"元"&TEXT(RIGHT((A21/100)),"[DBNum2]")&"角整",TEXT(TRUNC((A21/100)),"[DBNum2]")&"元"&IF(ISNUMBER(FIND(".0",(A21/100))),"零",TEXT(LEFT(RIGHT((A21/100),2)),"[DBNum2]")&"角")&TEXT(RIGHT((A21/100)),"[DBNum2]")&"分")))

		自动登录	找回密码
密码			免费注册

[求助] 请教，123 显示为壹元贰角叁分！