求教大神!为何下面公式遇到0就无法显示大写零出来的?显示Value!谢谢

 时间:2019-08-14 05:01:15 贡献者:雄印

J6=IF(IF(LEN(INT(W6))>=3,LEFT(RIGHT(INT(W6),3)),"")="","",CHOOSE(IF(IF(LEN(INT(W6))>=3,LEFT(RIGHT(INT(W6),3)),"")=0,10,IF(LEN(INT(W6))>=3,LEFT(RIGHT(INT(W6),3)),"")),"壹","贰","叁","肆","伍","陆","柒","捌","玖","零"))

L6=IF(IF(LEN(INT(W6))>=2,LEFT(RIGHT(INT(W6),2)),"")="","",CHOOSE(IF(IF(LEN(INT(W6))>=2,LEFT(RIGHT(INT(W6),2)),"")=0,10,IF(LEN(INT(W6))>=2,LEFT(RIGHT(INT(W6),2)),"")),"壹","贰","叁","肆","伍","陆","柒","捌","玖","零"))

O6=IF(IF(LEN(INT(W6))>=1,IF(INT(W6)=0,"",RIGHT(INT(W6))),"")="","",CHOOSE(IF(IF(LEN(INT(W6))>=1,IF(INT(W6)=0,"",RIGHT(INT(W6))),"")=0,11,IF(LEN(INT(W6))>=1,IF(INT(W6)=0,"",RIGHT(INT(W6))),"")),"壹","贰","叁","肆","伍","陆","柒","捌","玖","零"))


求教大神!为何下面公式遇到0就无法显示大写零出来的?显示Value!谢谢

J6:=IF(IF(W6上百,百位,"")="","",CHOOSE(IF(IF(W6上百,百位,"")=0,10,IF(W6上百,百位,"")),"壹","贰","叁","肆","伍","陆","柒","捌","玖","零"))

可以改写为:

=IF(IF(W6不上百,百位,"",CHOOSE(IF(IF(W6上百,百位,"")=0,10,IF(W6上百,百位,"")),"壹","贰","叁","肆","伍","陆","柒","捌","玖","零"))


=IF(IF(LEN(INT(W6))>=3,LEFT(RIGHT(INT(W6),3)),"")="","",CHOOSE(IF(IF(LEN(INT(W6))>=3,LEFT(RIGHT(INT(W6),3)),"")=0,10,IF(LEN(INT(W6))>=3,LEFT(RIGHT(INT(W6),3)),"")),"壹","贰","叁","肆","伍","陆","柒","捌","玖","零"))

这是一个多么糟糕的公式?拖沓、累赘、晦涩

后面的就不分析了。

一点知道微信

微信扫一扫 关注一点知道
微信提问题 答案马上自动回复

其他回答
可能是单元格中的内容不是纯数字格式。
(1) IF(IF(LEN(INT(W6))>=3,LEFT(RIGHT(INT(W6),3)),"")="","",  …… 这个判断可以改成: IF(W6<100,"", …… (2) IF(IF(LEN(INT(W6))>=3,LEFT(RIGHT(INT(W6),3)),"")=0,10, …… 简单地改成这样: IF(IF(LEN(INT(W6))>=3,LEFT(RIGHT(INT(W6),3)),"")="0",10, …… 或 IF(IF(LEN(INT(W6))>=3,--LEFT(RIGHT(INT(W6),3)),"")=0,10, …… 考虑到(1)的判断可改成: IF(--LEFT(RIGHT(INT(W6),3))=0,10, …… 综合上面两点,可以将公式简化为: =IF(W6<100,"",CHOOSE(LEFT(RIGHT(INT(W6),3))+1,"零","壹","贰","叁","肆","伍","陆","柒","捌","玖")) 用TEXT函数代替CHOOSE函数,公式更为简单,如下: =IF(W6<100,"",TEXT(LEFT(RIGHT(INT(W6),3)),"[DBNum2][$-804]G/通用格式"))
何必那么麻烦,这样不行: =TEXT(w6,"[dbnum2]")&"元整"
你想实现什么呢? 将你的数据、目标说明,说不定有更好的方法。
相关推荐
  • 暂无相关推荐!
  •