[第42期]字符串中符合范围数的和[已结]

willin2000

1.答题前先请仔细阅读本版正式竞赛区运行规则说明.
2.请勿在跟贴中直接发答案(跟帖中如附答案一律不得分).
3.答案附件请以标准文件名格式发送至我的邮箱：willin2000@yahoo.cn

补充说明: A列不含科学计数字符串, 如"12e2"
除非特别说明,要求默认为必须也能在2003中实现,只能在2007中的公式无效.

[ 本帖最后由 willin2000 于 2008-12-24 15:52 编辑 ]

wenshui2006

占个位，再看一下。
真的好难！
狂晕，公式老是超过嵌套层数一层。。。
已发不完全正确答案！

答案:
=SUM(--TEXT(0&MID(TEXT(MID("a"&A1,ROW($1:$99),MMULT(TEXT(MID(A1,ROW($1:$99),COLUMN(A:O)),"!1;!1;!1;!0")*1,MOD(ROW($1:$15),1)+1)+1),),2,15),"[<10]!0;[>1E+13]!0"))  

公式超过2003中的嵌套限制,致使无法使用.
本意是用TEXT来确定是否是数字后,再用MMULT求得在某个开始位置后有数字的位数.再用MID("a"&)取相应长度+1前含一个字符的数字.再用一个TEXT(... ,)去除不含字母汉字的纯数字然后用MID(..2,15)取得数字,最后用TEXT判断符合区间的数字,用SUM求和.

使用到的主要技巧有,用MMULT来计算位数. TEXT(????,)去除纯数字."[<10]!0;[>1E+13]!0"两个条件判断区间.

然而虽然TEXT的作用虽然很大,但也不是在所有地方都有优势,比如:
其中的TEXT(MID(A1,ROW($1:$99),COLUMN(A:O)),"!1;!1;!1;!0")*1,用1-ISERR(-MID(A1,ROW($1:$99),COLUMN(A:P)))反而可以节省12字符.
而其中的MOD(ROW($1:$15),1)+1)应该用ROW($1:$15)^0或ROW(1:15)^0来简化.

总体上来说,解题的方法是有的,产生嵌套多的原因是使用了较多的串联判断,类似模型为IF(IF(IF,),  而解决嵌套的基本方法是尽可能转换为并列判断,类似模型为IF()*IF()*IF().

答案不能使用, 不得分. -willin2000

[ 本帖最后由 willin2000 于 2008-12-24 18:11 编辑 ]

gxuan2008

记号....
难不难啊?
多谢版主手下留情，
下次注意，一直在尝试，但是水平有限，还是没能做出来。。。

[ 本帖最后由 gxuan2008 于 2008-12-24 17:38 编辑 ]

冻豆腐

真不容易啊！
占位，邮件已发。

答案(220个字符):
=SUM(-TEXT(TEXT(MID(TEXT(MID(REPLACE("a"&ASC(A1)&"A",COLUMN(1:1),1,REPT(" ",16)),ROW($1:$89),17),),2,16),"0%;0%;0%;!0"),"[>"&10^13&"]!0;[<10]!0;-0%")*IF(ISERR(FIND(MID("a"&ASC(A1)&"A",COLUMN(1:1),1),".0123456789")),1,0))

思路是用加入空格的方式来取得数字,然后用IF(ISERR(取消前面不是.或0~9的数字.
估计受以前讨论中的答复影响.其实采用空格的方式在这里比较麻烦.
采用的主要技巧是: TEXT(,"0%;0%;0%;!0")去除文本. "[>"&10^13&"]!0;[<10]!0;-0%")判断区间

其中思路不变的情况下,可以稍微再简化的有:
"[>"&10^13&"]!0;[<10]!0;-0%") 应该用"[>1e13]!0;[<10]!0;-0%")
*IF(ISERR(FIND(MID("a"&ASC(A1)&"A",COLUMN(1:1),1),".0123456789")),1,0)可以用*ISERR(FIND(MID("a"&ASC(A1)&"A",COLUMN(1:1),1),"."&5^19))
第一个ASC是可以不用的.

答案正确, 得2分.-willin2000

[ 本帖最后由 willin2000 于 2008-12-24 15:13 编辑 ]

HHAAMM

站位
好难呀！！
239字符，应该还可以简化。

答案(222个字符):
=SUM(--TEXT(--(0&MID(A1,ROW($1:$101),MMULT(TEXT(MID(A1,ROW($1:$101),COLUMN($A:$CV)),"!1;;0;!0")*(TEXT(MID("a"&A1,ROW($1:$101),1),"!0;;0;!1")-(MID("a"&A1,ROW($1:$101),1)={"."})),ROW($1:$100)^0))),"[>1E+13]!0;[<10]!0;0.00"))

公式思路: MMULT求数字位数(同样不建议用TEXT,用iserr更简洁,参考上面),其中用MID()-MID()来屏蔽前一个不是文本的数字串. 用MID(A1,ROW(0,MMULT)取数.
主要技巧, "a"&A1用来错位 MID()-MID()进行并列判断,不过不应该嵌套在mmult里.

思路方向基本正确,但优化的不是很好,特别是MID()-MID()判断前面是否的文本的方法,有点复杂了.

答案正确. 评2分. -willin2000

[ 本帖最后由 willin2000 于 2008-12-24 00:41 编辑 ]

jxrbz

所有的数据都显现在眼前，眼看就要到达目的地，182个字符！惊险的时刻，按Ctrl+Shift+Enter 啪一声，奶奶的，错误！找呀找呀找原因，原来是函数嵌套超过了7层，撞南墙了！只好另寻出路~~~ 搞呀搞，搞了一个277字符的，将近多了一半。不服看看再说。。。。



答案已经发送：willin2000@yahoo.cn 邮箱
================================================
第一个公式是和2003版 字符多了点，本还可以简化，但还是250字符左右，就不重发了。第二个公式只适合2007版。

答案(2003,273个字符):
=SUM(--TEXT(0&MID(A1,ROW($1:$54),((IF(ISERR(-(MID(A1&REPT("q",99),ROW($1:$54),1)&0)),0,1)-IF(ISERR(-(MID("q"&A1&REPT("q",99),ROW($1:$54),1)&0)),0,1))=1)*MMULT(--TEXT(MID(A1,ROW($1:$54),COLUMN(A:O))&0,"!1;!1;!1;!0"),ROW($1:$15)^0)),"[>10000000000000]""0"";[<10]""0"";0.00"))
答案(2007,175个字符,在2003中超出嵌套)
=SUM(--TEXT(0&MID(TEXT(MID("q"&A1,ROW($1:$89),MMULT(--TEXT(MID(A1,ROW($1:$89),COLUMN(A:O))&0,"!1;!1;!1;!0"),ROW($1:$15)^0)+1),";0"),2,15),"[>"&10^13&"]""0"";[<10]""0"";0.00"))

2007的公式思路同2楼. 2003的公式使用了IF(ISERR -IF(ISERR来排除前面不含文本的字符串,这样的方式属并联判定,减少了嵌套,不过是比5楼更为复杂,优化见总结分析中.
同样不该用TEXT(,,"!1;!1;!1;!0")判断是否是数字. 另外2003中也可以用10^13.

答案正确,评2分. -willin2000

[ 本帖最后由 willin2000 于 2008-12-24 20:04 编辑 ]

xcd

168字符，EXCEL2003，已发，占位

答案(168个字符):
=SUM(--TEXT(MID(A1,ISNUMBER(-(MID("A"&A1,ROW($1:$99),1)&0))*99+ROW($1:$99),MMULT(1-ISERR(-MID(A1,ROW($1:$99),COLUMN(A:CU))),ROW($1:$99)^0)),"[<10]!0;[<=1e13]0%;!0;!0"))

公式思路是用MMULT来求数字位数. ISNUMER()*99 使得数字前是文本的情况下取数至遥远的东方,那里有个空"",这样解决屏蔽子数字串.  同样用到了本题考点"[<10]!0;[<=1e13]0%;!0;!0")的TEXT多条件判断.其中,"A"& 形成错位,使得和要的数字同行. &0来解决小数点的问题.

其中第一个用了ISNUMBER而没有1-ISERR可谓精益求精,因为这样少1个字符. 而第二个如果用ISNUMBER代替1-ISERR则会多3个字符.

而COLUMN(A:CU)似乎不需要这么长, 数字最长为16位就够了.

属容易理解的思路中优化得较好的答案之一. 答案正确,评2分. -willin2000

[ 本帖最后由 willin2000 于 2008-12-24 18:14 编辑 ]

wddn

签到支持，做起来很累人的题目。
今日答案：176字符。

2003答案(172字符):
B1=SUM(--TEXT(MID(LEFT(A1,ISERR(-(1&MID(A1&"@",ROW($2:$100),1)))*ROW($1:$99)),IF(ISERR(-(1&MID("@"&A1,COLUMN(A:AV),1))),COLUMN(A:AV),99),99),"[>"&10^13&"]!0;[<10]!0;.00;!0"))
以下公式，虽少了4个字符，效率低了一些：
B1=SUM(--TEXT(MID(LEFT(A1,ISERR(-(1&MID(A1&"@",ROW($2:100),1)))*ROW($1:99)),IF(ISERR(-(1&MID("@"&A1,COLUMN(1:1),1))),COLUMN(1:1),99),99),"[>"&10^13&"]!0;[<10]!0;.00;!0"))

公式主要思路是根据每个数字串的特征是前后必须都是非数字(即文本), 用"尾是文本"=ISERR(-(1&MID(A2&"@",ROW($2:$100),1)))来判断结尾是否是文本,"尾是文本"*ROW(1:99)的结果是如果是数字LEFT的长度就是0,否则全取,这样LEFT()的结果是截到结尾数字串后至少有一个文本的串.

但有的串前面还包含其他的数字串,所以需要进行下一步判断来确定各段数字串的起始位置.而"首是文本"=ISERR(-(1&MID("@"&A2,COLUMN(A:AV),1))),xxx=IF("首是文本",,COLUMN(A:AV),99)的结果是当是文本时就取到该位置,否则取全部.

MID(xxx,)则将前面的文本去除,由于xxx已经将后面的文本去除,所以得到了数字串,其中有些结果由于不能同时满足两个条件,这样会留下文本(要么是在头部,要么是在尾部),最后用TEXT(,"[>"&10^13&"]!0;[<10]!0;.00;!0")取在区间的数字,并且将文本串置!0.

本取数的方法和上面加空的类似,不过实现的手段不同.本解法更加优化,判断次数更少,并且取值在判断后直接取,所以字符少了,也简洁了很多.

思路方向非常正确,而COLUMN(A:AV)严格意义上来说是不够的,应该到98(1位数反正会被舍去),除部分细节可略加优化,比如"[>"&10^13&"]!0;[<10]!0;.00;!0")用"[>1e13]!0;[<10]!0;0%;!0")可以少8个字符. 所以公式大概是160个字符左右.

答案正确,评2分. -willin2000

[ 本帖最后由 willin2000 于 2008-12-24 18:17 编辑 ]

fdd

虽然用2007做出一个260个字符左右的答案，结果也完全正确。但是……结果嵌套过多，2003版通不过！厚着脸占个位，方便学习和向版主请教！
可以吗？
……………………………………………………………………………………

昨晚终于在2003模式中写出一个符合要求和结果的公式（字符：290个左右）！好高兴！虽然字符多了点，但是，相对于我这样的新手来讲，能完成如此难度的题目也算不小的进步了！要感谢版主提供了这样的好题目！

先占位吧，等版主回答了我的问题再发邮件！

……………………………………………………………………………………

邮件已发，请版主查收！

答案(286个字符):
=SUM(TEXT(TEXT(REPLACE(0&TEXT(MID(A1&"CC",ROW($1:$100),COLUMN(A1:P1)),),LEN(0&TEXT(MID(A1&"CC",ROW($1:$100),COLUMN(A1:P1)),)),1,""),"[<="&10^13&"]0.00;!0;!0;!0"),"[>=10]0.00;!0")*ISERR(-MID("C"&A1,ROW($1:$100),1))*ISNUMBER(-MID("C"&A1,ROW($2:$101),1))*(MID("C"&A1,ROW($1:$100),1)<>"."))

公式的主要思路是用TEXT(...,)确定字符串中必须有文本,用LEN()求得含文本的数字串的长度,也就是去除纯数字以后的包含数字串文本长度,用REPLACE去除数字串后面的文本,再用TEXT(,"[<="&10^13&"]0.00;!0;!0;!0")去除>10^13的数,并除去前面文本的数字串,这样留下来的数字串还多一些首部非文本开始的数字串,所以再用*ISERR(-MID("C"&A1,ROW($1:$100),1))去除,而.也会当成文本所以再用(MID("C"&A1,ROW($1:$100),1)<>"."). 这2个条件可以用常规方法&0(其实还有更简单的方法,常见总结)后一次去除即*ISERR(-(MID("C"&A1,ROW($1:$100),1)&0),这样公式变为:
=SUM(TEXT(TEXT(REPLACE(0&TEXT(MID(A1&"CC",ROW($1:$100),COLUMN(A:P)),),LEN(0&TEXT(MID(A1&"CC",ROW($1:$100),COLUMN(A1:P1)),)),1,""),"[<="&10^13&"]0.00;!0;!0;!0"),"[>=10]0.00;!0")*ISERR(-(MID("C"&A1,ROW($1:$100),1)&0)))

如果用TEXT的双判断则为189个字符:
=SUM(TEXT(REPLACE(0&TEXT(MID(A1&"CC",ROW($1:$100),COLUMN(A:P)),),LEN(0&TEXT(MID(A1&"CC",ROW($1:$100),COLUMN(A:P)),)),1,""),"[>1e13]!0;[<10]!0;0%;!0")*ISERR(-(MID("C"&A1,ROW($1:$100),1)&0)))

由于没有考虑到TEXT可以使用2个条件判断的技巧,所以需要2次判断,这样嵌套就可能会增加,因此公式采用了较多并联判断. 这也造成公式较长, 一半来说串联判断比并联容易少字符,这是优点,缺点是容易受到嵌套限制.

不改变思路可以优化的细节有:
COLUMN(A1:P1)改成COLUMN(A:P),可以简化4个字符.
*ISNUMBER(-MID("C"&A1,ROW($2:$101),1)) 应该不需要了,因为前面TEXT(...,)已经确定数字后面一定有文本.

答案正确, 评2分.

[ 本帖最后由 willin2000 于 2008-12-25 10:05 编辑 ]

yoka

占位，邮件已发

答案(159个字符):
=SUM(--TEXT(MID("Z"&A1,ISERR(-(0&MID(A1,ROW($1:99)-1,1)))*ROW($1:99)+1,MMULT(1-ISERR(-MID(A1,ROW($1:99),COLUMN(A:O))),ROW(1:15)^0)),"[<10]!0;[>1e13]!0;0%;!0"))

公式思路与XCD版主类似,一个变化是取近(通过"Z"&来取的Z)而不是取远, 这样减少几个字符(抵消增加的"Z"& 少4个. 后面mmult里3个$可以不用,不过实际使用考虑速度等还是应该加上. 另外严格意义上讲COLUMN(A:O)到15是不够的(但本题要求2位小数和小于1e13所以不影响). 含小数点符号的数字在EXCEL中可以有16位.

容易理解的思路中优化的较好的之一. 答案正确,评2分. -willin2000

[ 本帖最后由 willin2000 于 2008-12-24 00:59 编辑 ]