问一个小写转大写的问题

xqzzgboy

=IF(TRUNC(B3,0)=B3,TEXT(B3,"[DBNum2]G/通用格式")&"元整",IF(TRUNC(B3,1)=B3,TEXT(TRUNC(B3,0),"[DBNum2]G/通用格式")&"元"&TEXT((B3-TRUNC(B3,0))*10,"[DBNum2]0")&"角整",IF(TRUNC(B3,1)=TRUNC(B3,0),TEXT(TRUNC(B3,0),"[DBNum2]G/通用格式")&"元"&"零"&TEXT((B3-TRUNC(B3,1))*100,"[DBNum2]0")&"分",TEXT(TRUNC(B3,0),"[DBNum2]G/通用格式")&"元"&TEXT((B3-TRUNC(B3,0))*10,"[DBNum2]0")&"角"&TEXT((B3-TRUNC(B3,1))*100,"[DBNum2]0")&"分")))

6554792.69
读成：陆佰伍拾伍万肆仟柒佰玖拾贰元柒角玖分

我发现凡是百分位是“9“的，十分位除了0，其余读的数十分位的都比实际的多1，不知道什么原因？

xqzzgboy

分大于今”5“的就前面加1

看见星光

http://club.excelhome.net/thread-337492-1-1.html

xqzzgboy

看见星光 发表于 2015-8-31 10:00
http://club.excelhome.net/thread-337492-1-1.html

帮我看下这个公式错在哪？谢谢！

ff8feng

{:soso_e141:}小写转大写容易，大写转小写就麻烦了点
=TEXT(A2,";负")&SUBSTITUTE(TEXT(INT(ABS(A2)),"[dbnum2]G/通用格式元;;")&TEXT(TEXT(RIGHT(TEXT(A2,"[<>].00;"),2),"0;;整"),"[>9][dbnum2]0角0分;[dbnum2]"&IF(ABS(A2)>1,"零","")&"0分"),"零分","整")

xqzzgboy

ff8feng 发表于 2015-8-31 10:35
小写转大写容易，大写转小写就麻烦了点
=TEXT(A2,";负")&SUBSTITUTE(TEXT(INT(ABS(A2)),"[db ...

问一个问题，我的公式错在哪？

象山海鲜

只用正数
TEXT((B3-TRUNC(B3,0))*10,"[DBNum2]0")&"角"
改为TEXT((B3-TRUNC(B3,0)-5%)*10,"[DBNum2]0")&"角"

text对于小数，会四舍五入，
例：
text(7.4,"0")    ="7"
text(7.5,"0")    ="8"
text(7.9,"0")    ="8"

xqzzgboy

象山海鲜 发表于 2015-8-31 12:17
只用正数
TEXT((B3-TRUNC(B3,0))*10,"[DBNum2]0")&"角"
改为TEXT((B3-TRUNC(B3,0)-5%)*10,"[DBNum2]0")&" ...

谢谢，老师！

xqzzgboy

xqzzgboy 发表于 2015-8-31 12:42
谢谢，老师！

到前面加个“负”怎么加，不读成"一”？

xqzzgboy

xqzzgboy 发表于 2015-8-31 12:42
谢谢，老师！

到前面加个“负”怎么加，不读成"一”？