[第42期]按出现顺序求出现的数字[已结]

chrisfang

凑个数，答案已发送，请查收。

答案(258个字符):
=MID(SUM(RIGHT(SMALL(LARGE(IF(ISERR(FIND({0,1,2,3,4,5,6,7,8,9},A1:A18)),6^6,FIND(COLUMN(A:J)-1,A1:A18)+ROW(1:18)*10^3)+COLUMN(A:J)*10^6,ROW(1:10)*18)*10+ROW(1:10)*10^7+10-ROW(1:10),ROW(1:10)))/10^ROW(1:10))&0,3,COUNT(1/COUNTIF(A1:A18,"*"&(ROW(1:10)-1)&"*")))

公式思路与HHAAMM类似,但优化做的很好,构建的信息数字串的方式是:列位置&行位置&数字位置&存在数字,解法相同部分不再复述.
区别1.LARGE取得数后屏蔽列信息的方式不是去除列信息,而是加上ROW(1:10)*10^7这样列信息就统一以11来起到屏蔽的作用,即再排序时列信息将不再起作用,和减去列信息的效果是相同的.
区别2.存在数字的信息是后来先移位(之前得到只是排位信息)加上去的,+10-row(1:10),这样解决了前期加上去而产生的不正确的存在数字.
区别3.求和方式不是在小数点前进行,而是在小数点后进行/10^ROW(1:10)), 其中&0是为了防止丢失最后的0(小数前进行需要采取措施防止丢失第一个0. 而&0 比前面补位10^11更简洁.

由于数字串含信息较多,取值时嵌套较多,致使需要用参数0-9来代替函数参数0-9.

答案正确,小于280个字符, 评3分. -willin2000

[ 本帖最后由 willin2000 于 2008-12-27 12:29 编辑 ]

willin2000

我的答案:
1. 0值整体处理(210个字符,适用全空):
=RIGHT(0&SUM(RIGHT(LARGE(ISERR(1/(ROW(1:18)-MATCH("*"&COLUMN(A:J)-1&"*",A:A,)))*(ROW(1:18)*10^9+FIND(COLUMN(A:J)-1,A1:A18&5^19)/1%+COLUMN(J:S)),ROW(1:10)))%*10^ROW(2:11)),COUNT(MATCH("*"&ROW(1:10)-1&"*",A:A,)))
2. 0值单独处理(208个字符,不适用全空):
=IF(-LEFT(VLOOKUP("*",A:A,1,)),,0)&TEXT(SUM(RIGHT(LARGE(ISERR(1/(ROW(1:18)-MATCH("*"&COLUMN(A:J)-1&"*",A:A,)))*(ROW(1:18)*10^9+FIND(COLUMN(A:J)-1,A1:A18&5^19)/1%+COLUMN(J:S)),ROW(1:10)))%*10^ROW(2:11)),"0;;")

2个公式的都还留有一层嵌套可用.

答案中的大多思路和技巧在上面的评述或公式优化建议中基本上已经讲过了,所以下面大概解释一下.

总体思路是找出0-9在各行中的位置,如果找到了就产生一个包含行位置,和该行字符串中的位置(列位置),及出现的那个数字.如果该行没有出现,或上面已出现就标记为0.
ROW(1:18)*10^9+FIND(COLUMN(A:J)-1,A1:A18&5^19)/1%+COLUMN(J:S) 用于产生信息串.
ISERR(1/(ROW(1:18)-MATCH("*"&COLUMN(A:J)-1&"*",A:A,))) 用于对该行没有出现,或上面已出现置0.

MATCH("*"&COLUMN(A:J)-1&"*",A:A,)能判断各行是否有0-9. 如果找到了就有一个数字,否则返回错误值.也就是错误值的那一行是没有.
MATCH("*"&COLUMN(A:J)-1&"*",A:A,)=ROW(1:18) 的结果是只有第一次出现的行才为真,用于排除后面出现的行中的信息串.
MATCH("*"&COLUMN(A:J)-1&"*",A:A,)-ROW(1:18) 结果为0是要的结果.(第一次出现)
1/(MATCH("*"&COLUMN(A:J)-1&"*",A:A,)-ROW(1:18)) 错误的是不要的结果, 合并了重复和找不到2中情况.
信息串中的5^19用于排错,构成无错的串,这样就可以和条件用* 而不是IF,以节省嵌套. (由于MATCH会排除找不到的数字,所以找出的在5^19中的数字会被排除)

XCD和WDDN也用了这个思路,其中WDDN最为接近,不过在2维比较判断时有重复,不需要再用ISERROR(FIND),因为MATCH已经包含了这种情况,只要用5^19配合来进行数字串排除即可.

本期答案使用到了较多的手段和技巧,可谓琳琅满目,手段的使用为了尽量进行并联处理,技巧则侧重解决嵌套问题.

掌握这些手段或技巧,在解决问题时是非常有用的. 虽然平时也有出现,但本题作为实例,显示了它们的魅力, 这里再回顾一下:
TEXT(FIND(),"[>"&LEN(A1:A18)           用于得到存在与否时得到2种不同的结果.
LARGE(,ROW(1:10)*18)                   用来取最小数.
FIND(COLUMN(A:J)-1,A1:A18&5^19)>LEN(A1:A18))*(10^11-ROW(1:18)*10^4)+ROW(1:18)*10^4 也是用于得到存在与否时得到2种不同的结果
COUNT(MATCH("*"&ROW(1:10)-1&"*",A:A,)) 用来计数
COUNTIF(OFFSET(A1,,,ROW(1:18)),"*"&COLUMN(A:J)-1&"*") 用来计算各行前面的单元格里有多少被查询的数(或字符).
RIGHT(0&...) 用来避免丢失0.
FIND( A1:A19&IF(ROW(1:19)=19,5^19)     用来回避返回错误值.
COLUMN(J:S)  的尾巴是0-9
*100 可以用/1%
SUM(...*10^(ROW(1:10)-1)) 用*10^ROW(2:11))%
MATCH("*"&COLUMN(A:J)-1&"*",A:A,) 用来查找单元格里是否含有某字符.
SMALL(...,ROW(1:10)*18)*10+ROW(1:10)*10^7+10-ROW(1:10) 屏蔽辅助信息,并添加需要信息.

由此看来,为了解决问题,首先是要有比较全面的知识,也就是"独上高楼,望尽天涯路",广泛涉猎,尽可能地多看看其他人使用过的技巧和手段,这是一个"知道"阶段. 然后是花功夫理解和掌握它们,把它们变成自己的东西,"衣带渐宽终不悔,为伊消得人憔悴",说的就是这需要付出一定的心血, 这是个"掌握"阶段. 最后是能根据具体情况灵活地使用,并且有所创造,这样在解决问题时才会有"众里寻他千百度，蓦然回首，那人却在，灯火阑珊处"的感受.

本期函数题,涉及到的内容较多,而且需要综合解决问题的能力, 所以解释的比较多一些, 希望对大家有所帮助, 不当之处,在所难免,希望大家斧正!

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

fdd

版主啊，您太伟大了吧！您的评讲，让人看得“如痴如醉”啊！评语精当，“一针见血”！特别是这一段：
“由此看来,为了解决问题,首先是要有比较全面的知识,也就是"独上高楼,望尽天涯路",广泛涉猎,尽可能地多看看其他人使用过的技巧和手段,这是一个"知道"阶段. 然后是花功夫理解和掌握它们,把它们变成自己的东西,"衣带渐宽终不悔,为伊消得人憔悴",说的就是这需要付出一定的心血, 这是个"掌握"阶段. 最后是能根据具体情况灵活地使用,并且有所创造,这样在解决问题时才会有"众里寻他千百度，蓦然回首，那人却在，灯火阑珊处"的感受.”
简直是“经验之谈”啊！胜过读那些骗人的书了！
象我一样的新手们，还等什么，赶紧把它记在心里吧！不仅可用来学EXCEL，其它方面未尝不是这样呀！

wenshui2006

强 !!!!
读书三个境界: 独上高楼,望尽天涯路,  衣带渐宽终不悔,为伊消得人憔悴, 众里寻他千百度，蓦然回首，那人却在，灯火阑珊处!!  很喜欢这些唐诗宋词..

wshcw

为E消得人憔悴

HHAAMM

一句话，willin2000 版主辛苦了！！

fdd

终于把所有的公式“看”完了一遍！妙啊，妙不可言！
看版主公式中的这一段：ISERR(1/(ROW(1:18)-MATCH("*"&COLUMN(A:J)-1&"*",A:A,)))
居然能把“#DIV/0!”与“#N/A”两种错误值区分开来，直接就可以确定数字首次出现的位置了。妙！平常只是把ISERR函数用来区分“错误值”与“非错误值”，没太注意也能区别“不同的错误值”。其实函数本来就包括了这种用法，只是潜意识里没有注意到这样的用法的妙处啊！
包括MATCH("*"&COLUMN(A:J)-1&"*",A:A,)用法，平常只是更多地用在数字或全文本方面，没有想到还有……真是孤陋寡闻啊！

[ 本帖最后由 fdd 于 2008-12-30 20:33 编辑 ]

红牡丹

由此看来,为了解决问题,首先是要有比较全面的知识,也就是"独上高楼,望尽天涯路",广泛涉猎,尽可能地多看看其他人使用过的技巧和手段,这是一个"知道"阶段. 然后是花功夫理解和掌握它们,把它们变成自己的东西,"衣带渐宽终不悔,为伊消得人憔悴",说的就是这需要付出一定的心血, 这是个"掌握"阶段. 最后是能根据具体情况灵活地使用,并且有所创造,这样在解决问题时才会有"众里寻他千百度，蓦然回首，那人却在，灯火阑珊处"的感受.

syz105729913

MATCH("*"&COLUMN(A:J)-1&"*",A:A,)   完全被这个用法给折服了
忍不住要赞美一下，收藏越来慢慢消化。

lgcmeli

原帖由 willin2000 于 2008-12-27 12:59 发表






由此看来,为了解决问题,首先是要有比较全面的知识,也就是"独上高楼,望尽天涯路",广泛涉猎,尽可能地多看看其他人使用过的技巧和手段,这是一个"知道"阶段. 然后是花功夫理解和掌握它们,把它们变成自己的东 ...

1，解释精彩

2，答案精彩

3，分析精彩