数值为负数时转大写

eyesonme22

当数值是负数时大写表就不正确了，如何才能变成正确的值

aleaflin

="合同总金额："&("RMB￥"&TEXT(A2,"#.00")&"(大写："&IF(SIGN(A2)<0,"-","")&IF(MOD(TEXT(ABS(A2),"#.00"),1)=0,""&NUMBERSTRING(TEXT(ABS(A2),"#.00"),2)&"元整)",TEXT(INT(TEXT(ABS(A2),"#.00")),"[DBNum2]")&"元"&TEXT(INT(TEXT(ABS(A2),"#.00")*10)-INT(TEXT(ABS(A2),"#.00"))*10,"[DBNum2]")&"角"&TEXT(INT(TEXT(ABS(A2),"#.00")*100)-INT(TEXT(ABS(A2),"#.00")*10)*10,"[DBNum2]")&"分)"))&"，其中：
1）不含增值税合同金额："&("RMB￥"&TEXT((A2/(1+B2)),"#.00")&"(大写："&IF(SIGN(A2)<0,"-","")&IF(MOD(TEXT((A2/(1+B2)),"#.00"),1)=0,NUMBERSTRING(TEXT((ABS(A2)/(1+B2)),"#.00"),2)&"元整)",TEXT(INT(TEXT((ABS(A2)/(1+B2)),"#.00")),"[DBNum2]")&"元"&TEXT(INT(TEXT((ABS(A2)/(1+B2)),"#.00")*10)-INT(TEXT((ABS(A2)/(1+B2)),"#.00"))*10,"[DBNum2]")&"角"&TEXT(INT(TEXT((ABS(A2)/(1+B2)),"#.00")*100)-INT(TEXT((ABS(A2)/(1+B2)),"#.00")*10)*10,"[DBNum2]")&"分)"))&"；
2）合同增值税税款(增值税率"&B2*100&"%)："&("RMB￥"&TEXT((A2-(A2/(1+B2))),"#.00")&"(大写："&IF(SIGN(A2)<0,"-","")&IF(MOD(TEXT(ABS(A2)*B2/(1+B2),"#.00"),1)=0,NUMBERSTRING(TEXT(ABS(A2)*B2/(1+B2),"#.00"),2)&"元整)",TEXT(INT(TEXT(ABS(A2)*B2/(1+B2),"#.00")),"[DBNum2]")&"元"&TEXT(INT(TEXT(ABS(A2)*B2/(1+B2),"#.00")*10)-INT(TEXT(ABS(A2)*B2/(1+B2),"#.00"))*10,"[DBNum2]")&"角"&TEXT(INT(TEXT(ABS(A2)*B2/(1+B2),"#.00")*100)-INT(TEXT(ABS(A2)*B2/(1+B2),"#.00")*10)*10,"[DBNum2]")&"分)"))&"。"

用sign取负号，剩下的计算用绝对值ABS()，你的算式里 A2-A2/(1+B2)=A2*B2/(1+B2)，大写俩字不用重复写，既然是固定出现的，放到if外面去

aleaflin

用sign()取负号
计算简化了一下
大写俩字必然出现，从if里提到外面去了

eyesonme22

aleaflin 发表于 2019-8-2 14:28
="合同总金额："&("RMB￥"&TEXT(A2,"#.00")&"(大写："&IF(SIGN(A2)

怎么显示
“-”为“负”

libo5563

SUBSTITUTE("合同总金额："&("RMB￥"&TEXT(A2,"#.00")&"("&IF(MOD(TEXT(A2,"#.00"),1)=0,"大写："&NUMBERSTRING(TEXT(A2,"#.00"),2)&"元整)","大写："&TEXT(INT(TEXT(A2,"#.00")),"[DBNum2]")&"元"&TEXT(INT(TEXT(A2,"#.00")*10)-INT(TEXT(A2,"#.00"))*10,"[DBNum2]")&"角"&TEXT(INT(TEXT(A2,"#.00")*100)-INT(TEXT(A2,"#.00")*10)*10,"[DBNum2]")&"分)"))&"，其中：
1）不含增值税合同金额："&("RMB￥"&TEXT((A2/(1+B2)),"#.00")&"("&IF(MOD(TEXT((A2/(1+B2)),"#.00"),1)=0,"大写："&NUMBERSTRING(TEXT((A2/(1+B2)),"#.00"),2)&"元整)","大写："&TEXT(INT(TEXT((A2/(1+B2)),"#.00")),"[DBNum2]")&"元"&TEXT(INT(TEXT((A2/(1+B2)),"#.00")*10)-INT(TEXT((A2/(1+B2)),"#.00"))*10,"[DBNum2]")&"角"&TEXT(INT(TEXT((A2/(1+B2)),"#.00")*100)-INT(TEXT((A2/(1+B2)),"#.00")*10)*10,"[DBNum2]")&"分)"))&"；
2）合同增值税税款(增值税率"&B2*100&"%)："&("RMB￥"&TEXT((A2-(A2/(1+B2))),"#.00")&"("&IF(MOD(TEXT((A2-(A2/(1+B2))),"#.00"),1)=0,"大写："&NUMBERSTRING(TEXT((A2-(A2/(1+B2))),"#.00"),2)&"元整)","大写："&TEXT(INT(TEXT((A2-(A2/(1+B2))),"#.00")),"[DBNum2]")&"元"&TEXT(INT(TEXT((A2-(A2/(1+B2))),"#.00")*10)-INT(TEXT((A2-(A2/(1+B2))),"#.00"))*10,"[DBNum2]")&"角"&TEXT(INT(TEXT((A2-(A2/(1+B2))),"#.00")*100)-INT(TEXT((A2-(A2/(1+B2))),"#.00")*10)*10,"[DBNum2]")&"分)"))&"。","-","负")



加个substitute    把“-“ 换成”负”

aleaflin

eyesonme22 发表于 2019-8-3 00:00
怎么显示
“-”为“负”

="合同总金额："&("RMB￥"&TEXT(A2,"#.00")&"(大写："&IF(SIGN(A2)<0,"负","")&IF(MOD(TEXT(ABS(A2),"#.00"),1)=0,""&NUMBERSTRING(TEXT(ABS(A2),"#.00"),2)&"元整)",TEXT(INT(TEXT(ABS(A2),"#.00")),"[DBNum2]")&"元"&TEXT(INT(TEXT(ABS(A2),"#.00")*10)-INT(TEXT(ABS(A2),"#.00"))*10,"[DBNum2]")&"角"&TEXT(INT(TEXT(ABS(A2),"#.00")*100)-INT(TEXT(ABS(A2),"#.00")*10)*10,"[DBNum2]")&"分)"))&"，其中：
1）不含增值税合同金额："&("RMB￥"&TEXT((A2/(1+B2)),"#.00")&"(大写："&IF(SIGN(A2)<0,"负","")&IF(MOD(TEXT((A2/(1+B2)),"#.00"),1)=0,NUMBERSTRING(TEXT((ABS(A2)/(1+B2)),"#.00"),2)&"元整)",TEXT(INT(TEXT((ABS(A2)/(1+B2)),"#.00")),"[DBNum2]")&"元"&TEXT(INT(TEXT((ABS(A2)/(1+B2)),"#.00")*10)-INT(TEXT((ABS(A2)/(1+B2)),"#.00"))*10,"[DBNum2]")&"角"&TEXT(INT(TEXT((ABS(A2)/(1+B2)),"#.00")*100)-INT(TEXT((ABS(A2)/(1+B2)),"#.00")*10)*10,"[DBNum2]")&"分)"))&"；
2）合同增值税税款(增值税率"&B2*100&"%)："&("RMB￥"&TEXT((A2-(A2/(1+B2))),"#.00")&"(大写："&IF(SIGN(A2)<0,"负","")&IF(MOD(TEXT(ABS(A2)*B2/(1+B2),"#.00"),1)=0,NUMBERSTRING(TEXT(ABS(A2)*B2/(1+B2),"#.00"),2)&"元整)",TEXT(INT(TEXT(ABS(A2)*B2/(1+B2),"#.00")),"[DBNum2]")&"元"&TEXT(INT(TEXT(ABS(A2)*B2/(1+B2),"#.00")*10)-INT(TEXT(ABS(A2)*B2/(1+B2),"#.00"))*10,"[DBNum2]")&"角"&TEXT(INT(TEXT(ABS(A2)*B2/(1+B2),"#.00")*100)-INT(TEXT(ABS(A2)*B2/(1+B2),"#.00")*10)*10,"[DBNum2]")&"分)"))&"。"