闲来无事，做一中文小写数字的加减题（已开贴求精简）

chunlin1wang

中文小写数字加减.zip (2.27 KB, 下载次数: 108)

2012-5-17 12:40 上传

点击文件名下载附件


我的评分对多9分，答案正确的给3分，字符小于200的给9分，最短的评最佳。（预设的公式184字符）
魔魔 老师给出了和我同样思路的字符比我短的公式，开贴求不同思路和精简。11楼Wjc2090742 给出的思路很新颖，学习。
预设公式：

1. =SUBSTITUTE(SUBSTITUTE(TEXT(SUM(COUNTIF(A2,{"","*加","","*减"}&SUBSTITUTE(NUMBERSTRING(ROW($1:999),1),"一十","十")&{"加*","","减*",""})*ROW($1:999)*{1,1,1,-1}),"[DBNum1]"),"一十","十"),"-","负")

复制代码

另外：那位帮帮我，为什么我TEXT函数不能使用TEXT(12,"[DBNum1];负[DBNum1]G/通用格式")这样的表达

lotte

题目真变态{:3712:}

lotte

1. =SUBSTITUTE(TEXT(MATCH(MID(A2,1,MIN(FIND({"加","减"},A2&"加减"))-1),SUBSTITUTE(TEXT(ROW($1:999),"[dbnum1]"),"一十","十"),)+IF(COUNTIF(A2,"*加*"),1,-1)*MATCH(MID(A2,MIN(FIND({"加","减"},A2&"加减"))+1,9),SUBSTITUTE(TEXT(ROW($1:999),"[dbnum1]"),"一十","十"),),"[dbnum1]"),"-","负")

复制代码

262个字符

飞雨飘

偶275个字，不发了。

1. =SUBSTITUTE(TEXT(SUM((LEN(A2)+2-LEN(SUBSTITUTE({"加","加","减"}&A2&{"加","减","减"},{"加","减","减"}&SUBSTITUTE(NUMBERSTRING(ROW($1:$999),1),"一十","十")&{"加","减","减"},))>0)*{1,-2,1}*ROW($1:$999)),"[DBNum1];负[DBNum1]G/通用格式"),"一十","十")
   

复制代码

222个字符，细节未精减。

lotte

1. =SUBSTITUTE(SUBSTITUTE(TEXT(MATCH(MID(A2,1,MIN(FIND({"加","减"},A2&"加减"))-1),SUBSTITUTE(TEXT(ROW($1:999),"[dbnum1]"),"一十","十"),)+IF(COUNTIF(A2,"*加*"),1,-1)*MATCH(MID(A2,MIN(FIND({"加","减"},A2&"加减"))+1,9),SUBSTITUTE(TEXT(ROW($1:999),"[dbnum1]"),"一十","十"),),"[dbnum1]"),"-","负"),"一十","十")

复制代码

{:4112:}还要再套个SUBSTITUTE，太变态了

飞雨飘

换个思路

1. =SUBSTITUTE(TEXT(SUM(ISNUMBER((FIND({"加","减","减"}&SUBSTITUTE(NUMBERSTRING(ROW($1:999),1),"一十","十")&{"加","减","减"},{"加","加","减"}&A2&{"加","减","减"})))*{1,-2,1}*ROW($1:999)),"[DBNum1];负[DBNum1]G/通用格式"),"一十","十")

复制代码

207了

1. =SUBSTITUTE(TEXT(SUM(ISNUMBER((FIND({"加","减","减"}&SUBSTITUTE(NUMBERSTRING(ROW($1:999),1),"一十","十")&{"加","减","减"},{"加",9,"减"}&A2&{"加","减","减"})))*{1,-2,1}*ROW($1:999)),"[DBNum1];负[DBNum1]G/通用格式"),"一十","十")

复制代码

还差一点点。DBNum1];负[DBNum1]G/通用格式               这一段太长。


应是都没有考虑到0时的结果吧。

飞雨飘

负数没有问题呀?0是没有考虑到了.请看附件

1. =SUBSTITUTE(TEXT(SUM((LEN(A2)+2-LEN(SUBSTITUTE({"加","加","加"}&A2&{"加","减",9},{"加","减","加"}&SUBSTITUTE(NUMBERSTRING(ROW($1:$999),1),"一十","十")&{"加","减","减"},))>0)*{1,-1,1}*ROW($1:$999)),"[DBNum1];负[DBNum1]G/通用格式"),"一十","十")
   
2. 
   
3. =SUBSTITUTE(TEXT(SUM(ISNUMBER((FIND({"加","减","加"}&SUBSTITUTE(NUMBERSTRING(ROW($1:999),1),"一十","十")&{"加","减","减"},{"加","加","加"}&A2&{"加","减",9})))*{1,-1,1}*ROW($1:999)),"[DBNum1];负[DBNum1]G/通用格式"),"一十","十")

复制代码

=SUBSTITUTE(TEXT(SUM(ISNUMBER(FIND({"加","减","加"}&SUBSTITUTE(NUMBERSTRING(ROW($1:999),1),"一十","十")&{"加","减","减"},{"加",9,"加"}&A2&{"加","减",9}))*{1,-1,1}*ROW($1:999)),"[DBNum1];负[DBNum1]G/通用格式"),"一十","十")
刚刚好200个字符.

zhh823543

1. =SUBSTITUTE(SUBSTITUTE(TEXT(LOOKUP(1,0/(SUBSTITUTE(TEXT(ROW(1:1000)-1,"G/通用格式[DBNUM1]"),"一十","十")=LEFT(A2,MIN(FIND({"加","减"},A2&"加减"))-1)),ROW(1:1000)-1)+-1^(MID(A2,MIN(FIND({"加","减"},A2&"加减")),1)="减")*LOOKUP(1,0/(SUBSTITUTE(TEXT(ROW(1:1000)-1,"G/通用格式[DBNUM1]"),"一十","十")=MID(A2,MIN(FIND({"加","减"},A2&"加减"))+1,9)),ROW(1:1000)-1),"G/通用格式[DBNUM1]"),"一十","十"),"-","负")

复制代码

zhh823543

1. =SUBSTITUTE(TEXT(LOOKUP(1,0/(SUBSTITUTE(TEXT(ROW(A:A)-1,"[DBNUM1]"),"一十","十")=LEFT(A2,MIN(FIND({"加","减"},A2&"加减"))-1)),ROW(A:A)-1)+-1^ISERR(FIND("加",A2))*LOOKUP(1,0/(SUBSTITUTE(TEXT(ROW(A:A)-1,"[DBNUM1]"),"一十","十")=MID(A2,MIN(FIND({"加","减"},A2&"加减"))+1,9)),ROW(A:A)-1),"[DBNUM1];负G/通用格式[DBNUM1]"),"一十","十")

复制代码

稍微缩短了一些

魔魔

1. =SUBSTITUTE(TEXT(SUM(COUNTIF(A2,{"*加","*减","",""}&SUBSTITUTE(NUMBERSTRING(ROW($1:999),1),"一十","十")&{"","","加*","减*"})*ROW($1:999)*{1,-1,1,1}),"[DBNum1];负[DBNum1]G/通用格式"),"一十","十")

复制代码