chunlin1wang 发表于 2012-5-17 12:41

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

本帖最后由 chunlin1wang 于 2012-5-18 09:23 编辑




我的评分对多9分，答案正确的给3分，字符小于200的给9分，最短的评最佳。（预设的公式184字符）
魔魔 老师给出了和我同样思路的字符比我短的公式，开贴求不同思路和精简。11楼Wjc2090742 给出的思路很新颖，学习。
预设公式： =SUBSTITUTE(SUBSTITUTE(TEXT(SUM(COUNTIF(A2,{"","*加","","*减"}&SUBSTITUTE(NUMBERSTRING(ROW($1:999),1),"一十","十")&{"加*","","减*",""})*ROW($1:999)*{1,1,1,-1}),""),"一十","十"),"-","负")
另外：那位帮帮我，为什么我TEXT函数不能使用TEXT(12,";负G/通用格式")这样的表达

lotte 发表于 2012-5-17 13:23

题目真变态{:3712:}

lotte 发表于 2012-5-17 13:38

=SUBSTITUTE(TEXT(MATCH(MID(A2,1,MIN(FIND({"加","减"},A2&"加减"))-1),SUBSTITUTE(TEXT(ROW($1:999),""),"一十","十"),)+IF(COUNTIF(A2,"*加*"),1,-1)*MATCH(MID(A2,MIN(FIND({"加","减"},A2&"加减"))+1,9),SUBSTITUTE(TEXT(ROW($1:999),""),"一十","十"),),""),"-","负")262个字符

飞雨飘 发表于 2012-5-17 14:41

本帖最后由 飞雨飘 于 2012-5-17 15:36 编辑

偶275个字，不发了。:'(




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


222个字符，细节未精减。

lotte 发表于 2012-5-17 14:42

=SUBSTITUTE(SUBSTITUTE(TEXT(MATCH(MID(A2,1,MIN(FIND({"加","减"},A2&"加减"))-1),SUBSTITUTE(TEXT(ROW($1:999),""),"一十","十"),)+IF(COUNTIF(A2,"*加*"),1,-1)*MATCH(MID(A2,MIN(FIND({"加","减"},A2&"加减"))+1,9),SUBSTITUTE(TEXT(ROW($1:999),""),"一十","十"),),""),"-","负"),"一十","十"){:4112:}还要再套个SUBSTITUTE，太变态了

飞雨飘 发表于 2012-5-17 16:26

本帖最后由 飞雨飘 于 2012-5-17 17:53 编辑

换个思路 =SUBSTITUTE(TEXT(SUM(ISNUMBER((FIND({"加","减","减"}&SUBSTITUTE(NUMBERSTRING(ROW($1:999),1),"一十","十")&{"加","减","减"},{"加","加","减"}&A2&{"加","减","减"})))*{1,-2,1}*ROW($1:999)),";负G/通用格式"),"一十","十")207了
=SUBSTITUTE(TEXT(SUM(ISNUMBER((FIND({"加","减","减"}&SUBSTITUTE(NUMBERSTRING(ROW($1:999),1),"一十","十")&{"加","减","减"},{"加",9,"减"}&A2&{"加","减","减"})))*{1,-2,1}*ROW($1:999)),";负G/通用格式"),"一十","十")
还差一点点。DBNum1];负G/通用格式               这一段太长。


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

飞雨飘 发表于 2012-5-17 18:18

本帖最后由 飞雨飘 于 2012-5-17 19:46 编辑

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

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

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

zhh823543 发表于 2012-5-17 20:05

=SUBSTITUTE(SUBSTITUTE(TEXT(LOOKUP(1,0/(SUBSTITUTE(TEXT(ROW(1:1000)-1,"G/通用格式"),"一十","十")=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/通用格式"),"一十","十")=MID(A2,MIN(FIND({"加","减"},A2&"加减"))+1,9)),ROW(1:1000)-1),"G/通用格式"),"一十","十"),"-","负")

zhh823543 发表于 2012-5-17 22:15

本帖最后由 zhh823543 于 2012-5-17 23:16 编辑

=SUBSTITUTE(TEXT(LOOKUP(1,0/(SUBSTITUTE(TEXT(ROW(A:A)-1,""),"一十","十")=LEFT(A2,MIN(FIND({"加","减"},A2&"加减"))-1)),ROW(A:A)-1)+-1^ISERR(FIND("加",A2))*LOOKUP(1,0/(SUBSTITUTE(TEXT(ROW(A:A)-1,""),"一十","十")=MID(A2,MIN(FIND({"加","减"},A2&"加减"))+1,9)),ROW(A:A)-1),";负G/通用格式"),"一十","十")稍微缩短了一些

魔魔 发表于 2012-5-17 23:02

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

页: [1] 2
查看完整版本: 闲来无事，做一中文小写数字的加减题（已开贴求精简）