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

willin2000 · 发表于 2008-12-24 15:52

本期总结.

从回复的答案来看,主要有以下3中类型.

第一类是逐步去除法,如FDD:
优化FDD后的:
=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))

第二类是将数字展开后,取首尾都是文本的数字串,如冻豆腐,和WDDN:
WDDN:
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"))
冻豆腐:
=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))

第三类,也是理解起来比较容易的就是以XCD,YOKA为代表的,直接在字符串中按长度取数,而长度通过mmult求含数字的个数. 这样取得的数字会包含前面的子数字串.所以需要用前面首个是否只文本来确定.
XCD:
=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")
YOKA:
=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"))
其中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"))

其中第三类应该是比较容易想到的办法,也是本题答案中用的较多的一种,关键使用并联判断并优化,不要重覆判断.

第二类答案的思路方向其实是本题的核心考点之一,即在2维面里的比较判断技巧(BTW:这也是本期函数另外一题的技巧考点);另一个考点是满足180字符以下的公式都要使用到的TEXT双条件判断功能,辅助的一个小技巧是0%格式保留2位数.

我的答案:
第二类思路的答案,153个字符:
=SUM(-TEXT(IF(ISERR(-MID(A1,ROW($1:99)-1,2)),0&MID(A1,ROW($1:99),MMULT(1-ISERR(-MID(A1,ROW($1:99),COLUMN(A:P))),ROW(1:16)^0)),),"[>1e13]!0;[<10]!0;-0%"))
需要注意一下的是首文本判断用ISERR(-MID(A1,ROW($1:99)-1,2))即可. 只要是合法的数字串,不可能2个字符中有一个文本的.其中利用的>10隐含条件的技巧.
中规中矩的写法是158:
=SUM(-TEXT(IF(ISERR(-MID(A1,ROW($1:$99)-1,2)),0&MID(A1,ROW($1:$99),MMULT(1-ISERR(-MID(A1,ROW($1:$99),COLUMN(A:P))),ROW($1:$16)^0)),),"[>1e13]!0;[<10]!0;-0%"))

第三类思路的答案,130个字符
=SUM(TEXT(LEFT(TEXT(MID(A1&"a",ROW($1:99),COLUMN(A:P)),),COLUMN(A:P)-1),"[>1e13]!0;[<10]!0;0%;!0")*ISERR(-MID(A1,ROW($1:99)-1,2)))
用2个TEXT结合LEFT留下尾部是文本的数字串,用ISERR去除首部不是文本的数字串后,直接求2维数组里的和.
此公式适用除日期和科学记数以外的所有情况,包括含负数.
中规中矩的写法是132:
=SUM(TEXT(LEFT(TEXT(MID(A1&"a",ROW($1:$99),COLUMN(A:P)),),COLUMN(A:P)-1),"[>1e13]!0;[<10]!0;0%;!0")*ISERR(-MID(A1,ROW($1:$99)-1,2)))

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

fdd · 发表于 2008-12-24 16:30

看了上面版主的讲解，很感慨啊！高手们的思维真是高深莫测啊！我想这也是建立在高手们扎实的基本功，渊博的知识基础上的！而自己欠缺的不仅仅是“灵感”，还有“知识”！

wddn · 发表于 2008-12-24 17:52

令人叹为观止的点评，通俗，具体，详细，文字有功底，不同层次的会员们，阅读后皆可有所收获。
不得不服。

wshcw · 发表于 2008-12-26 16:36

很实用,建议加为精华.

wenshui2006 · 发表于 2008-12-27 00:41

佩服!佩服!

fdd · 发表于 2008-12-27 12:59

[quote]原帖由 willin2000 于 2008-12-24 15:52 发表

觉得本次竞赛题及各种解法挺有意思的，因此这几天在仔细琢磨各种公式的含义及精妙之处。
不过，对于WDDN和冻豆腐的公式有些自己的想法。特指出与大家切磋！
一、按题目要求（字符串少于100）来做的话：
冻豆腐的公式：
=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))在字符串达到99个字符，数字串处在字符串末端（第89位之后）时，这些数字串无法取出。结果可能与题目要求不符。
WDDN的公式：
=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"))也有与上面的类似的问题。也许是“手误”吧

二、如果想移植公式用到别处，假设去掉题目中：<=10^13的要求，其它条件不变的情况下：
=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))在字符串中含有刚好16位数字（按版主说法，最长数字为带小数点的16位数字)时，此16位数字将被重复提取，结果与期望的不符。
同样，版主最精典的公式：=SUM(TEXT(LEFT(TEXT(MID(A1&"a",ROW($1:$99),COLUMN(A:P)),),COLUMN(A:P)-1),"[>1e13]!0;[<10]!0;0%;!0")*ISERR(-MID(A1,ROW($1:$99)-1,2)))是否会无法完整提取16位的数字呢？
不知道上面的意见是否有理？

[ 本帖最后由 fdd 于 2008-12-27 13:01 编辑 ]

wddn · 发表于 2008-12-27 14:08

原帖由 fdd 于 2008-12-27 12:59 发表
[quote]原帖由 willin2000 于 2008-12-24 15:52 发表
觉得本次竞赛题及各种解法挺有意思的，因此这几天在仔细琢磨各种公式的含义及精妙之处。
不过，对于WDDN和 ...

这小家伙，真是论坛的一黑马，注册时间不久，技术分拿得不少。
应该是COLUMN(A:CV)，这个问题我的第二个公式，可以避免。

另：感谢版主对本期公式函数竞赛题的精彩点评和总结。

fdd · 发表于 2008-12-27 15:33

0在公式中的妙用
在下面两位版主的公式，0有很精妙的作用：
1、把空字符串，变成了"0"，让它与其它已经有的数字作同一种处理。
2、成功的把小数点变成".0"或"0."，避免了把有小数点的数字串分成两串的尴尬结果。
真是奇妙啊！

XCD:
=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")
YOKA:
=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"))

2与1的区别

ISERR(-(MID("C"&A1,ROW($1:$100),1)&0))
ISERR(-(MID(A1,ROW($1:$99)-1,2))

上面两个公式所起的作用是一致的：判断数字串首位位置（当然还包括非数字字符的位置）。但为什么一个是取1个字符，一个取2个字符呢？
第一个公式中“C”起错位作用：让非数字字符对应两种字符：非数字字符和数字串首位。那第二个公式同样有“错位”的方法，就是ROW($1:$99)-1起到了这样的作用。只不过，前一个是“向后移”，后一个“向前移”而已。
因此，第一个公式之所以用1就可以了，主要是因为后面有一个0。它可以在错位的基础上把“小数点”变成数字字符，把空字符串变成数字字符。免除这两种情况对取数字的干扰。
第二个公式由于没有用0，因此用2个字符连在一起判断，同样可以把“小数点”变成数字（小数点加上后面的数字），至于空字符串在相应的公式里不会干扰到取数字串。

归纳在一起，自己觉得要点有几个：
1、基本方法分两种：分段截取和直接提取

分段截取是指：把原字符串按位置分成多段，再从这些分后的字符串中提取出数字串。提取的数字串一般有多余的数字串（没有重复的，但有一个数字串被分成几个数字串的情况。但WDDN的方法是依据首尾包含文本的方法，提取的数字串是唯一的，也没有一个数字串被分成几个数字串的情况）。因此一般需要进一步的去除判断（即数字串首位位置判断）。

直接提取是指：确定数字串首位置，确定数字串的长度，然后用MID函数提取数字串。结果是唯一的，无重复的，也无一个数字串被分成几个数字串的情况。无需进一步的去除判断。XCD,YOKA版主的公式即是这样的公式。

2、数字串首位位置判断

当提取的数字串有多余的情况时，就需要做进一步的去除判断，即数字串首位位置判断。方法有两种：

第一种：错位后移法。如：ISERR(-(0&MID("A"&A1,ROW($1:$99),1))).
其中，起错位的作用的是"A"。0的作用在于排除“小数点”与“空字符串”的干扰。

第二种：错位前移法。如：ISERR(-MID(A1,ROW($1:$99)-1,2))，2的使用，排除了“小数点”的干扰；要排除空字符串，可以改为：ISERR(-(0&MID(A1,ROW($1:$99)-1,2)))
其中，起错位的是ROW($1:$99)-1，错位前移了。

两种方法中，ISERR函数在于去除数字位开始的数字串（除数字串首位位置以外）。

[ 本帖最后由 fdd 于 2008-12-28 10:15 编辑 ]

冻豆腐 · 发表于 2008-12-29 09:02

其实真正正确的答案只有几个。
大家把A1改为向西3向东100000000000000向北54....6向南76，看看就知道答案了。
关于小数点的处理。
如果改为向西3向东1000000000000.01向北54.6向南76，错误更多了。这题还可以好好讨论

[ 本帖最后由冻豆腐于 2008-12-29 09:54 编辑 ]

fdd · 发表于 2008-12-29 09:15

原帖由 冻豆腐 于 2008-12-29 09:02 发表
其实真正正确的答案只有几个。
大家把A1改为向西3向东100000000000000向北54....6向南76，看看就知道答案了。
关于小数点的处理。

这是不是太极端了吧？一般还是针对“正常数字形式”的吧！

		自动登录	找回密码
密码			免费注册

[Excel 函数与公式] [第42期]字符串中符合范围数的和[已结]

评分

评分

评分