题目测试，欢迎参加，答对有奖

wangg913

为活跃论坛，测试一下大家对函数与公式的灵活运用能力，现出几个小题目，希望大家热情参与。
附件内容对每一道题目有明确的要求，如果题目不限定公式长度则无论长短都算正确完成。

按题目要求
1、同时完成 sheet1~sheet5 得 一个技术分；
2、同时完成 sheet6和sheet7得 一个技术分；
3、答题截止日为 2011-2-28，过期不候；到期我将在 3日 内总结；
4、无论是否按要求完成，视参与程度适当予以财富奖励。
5、第7题可能有点歧义，说明一下

主要是取2个最大的、2个最后面的。

假设1个4连，2个3连，取4连和最后那个3连
假设2个以上4连，取最后两个4连
假设2个以上4连，1个3连，取最后2个四连即可。

最终判定时，可能我自己比较主观，因此我将请其他版主协助。

参加测试请发占位贴以便加分，也欢迎版主参加，并将附件发送到我的邮箱 wangg913@163.com

请各位在占位贴告知发件人邮箱，以便对照 EH名与邮箱名。
经42楼提醒，可不必在帖子上公布邮箱名，发送邮件时请注明您的 EH网名

其实不要想得过于复杂，所有题目都是常用的用法，因此每个题目都不足以支撑1个技术分；
更不能使用名称、辅助列解决；
2011-3-1题目截止。

2011-2-28<wangg913>

wangg913-20110221.rar (7.61 KB, 下载次数: 623)

2011-2-22 09:36 上传

点击文件名下载附件

我的答案在79楼。
稍后再将整理好的各位E友的答案发上来。

piny

先占位 答案已發 ^^

第一题：
=SUM(--(WEEKDAY(DATE(A4,B4,ROW(INDIRECT("1:"&DAY(DATE(A4,B4+1,))))),2)<6))
=COUNT(0/(WEEKDAY(A4&"-"&B4&"-"&ROW($1:$31),2)<6))
=COUNT(0/(MOD(A4&"-"&B4&"-"&ROW($1:$31),7)>1))
=COUNT(0/(MOD(A4&"-"&B4&"-"&ROW($1:31),7)>1))
第二题：
=INDEX($A$3:$A$7,ROW(A4)/4)
第三题：
=IF(A3="",C2,A3)
=LOOKUP(2,1/(A$3:A3<>""),A$3:A3)
第四题：
=TEXT(WEEKDAY(A3),"[=1]周末;[=7]周末;AAAA")
=TEXT(MOD(WEEKDAY(A3)-1,6)+1,"[=1]周末;AAAA")
=IF(WEEKDAY(A3,2)>5,"周末",TEXT(WEEKDAY(A3),"AAAA"))
=MID(TEXT(WEEKDAY(A3),"AAAA周末"),1+3*(WEEKDAY(A3,2)>5),3)
=IF(MOD(A3,7)<2,"周末",TEXT(WEEKDAY(A3),"AAAA"))
=TEXT(MOD(A3,7),"[<2]周末;AAAA")
第五题：
=IF(COUNTIF(A3,"*章"),SMALL(IF(ISNUMBER(FIND("章",A$3:A$25)),ROW($3:$25),26),COUNTIF(A$3:A3,"*章")+1)-ROW()-1,"")
=IF(COUNTIF(A3,"*章"),IF(ISNA(MATCH("*章",A4:A$26,)),25-ROW(),MATCH("*章",A4:A$26,)-1),"")
=IF(COUNTIF(A3,"*章"),IF(COUNTIF(A4:A$25,"*章"),MATCH("*章",A4:A$25,)-1,25-ROW()),"")
=IF(RIGHT(A3)="章",IF(COUNTIF(A4:A$25,"*章"),MATCH("*章",A4:A$25,)-1,25-ROW()),"")
第六题：
=INDEX(A$3:A$13,IFERROR(MATCH(ROW(A1),SUBTOTAL(9,OFFSET(B$2,,,ROW($1:$11)))+ROW($1:$11),),11))&""
=OFFSET(A$2,IFERROR(MATCH(ROW(A1),SUBTOTAL(9,OFFSET(B$2,,,ROW($1:$11)))+ROW($1:$11),),11),)&""
第七题：
=ABS(SMALL(IF(A3:A21<>A4:A22,ROW(3:21)),MATCH(LARGE(IFERROR((FREQUENCY(IF(A3:A21=A4:A22,ROW(3:21)),IF(A3:A21<>A4:A22,ROW(3:21)))+1)*100+ROW(1:19),0),1),IFERROR((FREQUENCY(IF(A3:A21=A4:A22,ROW(3:21)),IF(A3:A21<>A4:A22,ROW(3:21)))+1)*100+ROW(1:19),0),))-SMALL(IF(A3:A21<>A4:A22,ROW(3:21)),MATCH(LARGE(IFERROR((FREQUENCY(IF(A3:A21=A4:A22,ROW(3:21)),IF(A3:A21<>A4:A22,ROW(3:21)))+1)*100+ROW(1:19),0),2),IFERROR((FREQUENCY(IF(A3:A21=A4:A22,ROW(3:21)),IF(A3:A21<>A4:A22,ROW(3:21)))+1)*100+ROW(1:19),0),)))+1

<wangg913>

piny-2-解答.rar (25.86 KB, 下载次数: 33)

2011-3-3 13:04 上传

点击文件名下载附件

第七题思路是看明白了，但是不限定版本，因此公式可以简化一下。                                               
                                               
原思路是先求出最大的连续行，再求出第二大的连续行，然后相减                                               
                                               
所以可以同时求出最大、二大，然后相减                                               
                                               
下面公式中的 IFERROR 函数可以省略掉，因为 MATCH 函数仅仅查找同类型的数值，所以有没有错误时无所谓的                                               
MATCH(LARGE(IFERROR((FREQUENCY(IF(A3:A21=A4:A22,ROW(3:21)),IF(A3:A21<>A4:A22,ROW(3:21)))+1)*100+ROW(1:19),0),1),IFERROR((FREQUENCY(IF(A3:A21=A4:A22,ROW(3:21)),IF(A3:A21<>A4:A22,ROW(3:21)))+1)*100+ROW(1:19),0),)                                       
                                               
MATCH(LARGE(IFERROR((FREQUENCY(IF(A3:A21=A4:A22,ROW(3:21)),IF(A3:A21<>A4:A22,ROW(3:21)))+1)*100+ROW(1:19),0),1),(FREQUENCY(IF(A3:A21=A4:A22,ROW(3:21)),IF(A3:A21<>A4:A22,ROW(3:21)))+1)*100+ROW(1:19),)                                               
                                               
既然没有版本限制，那么就无需考虑函数超过嵌套                                               
因此，最大、二大同时求出然后相减就可以了                                               
                                       
=ABS(SUM(RIGHT(SMALL(IF(A3:A21<>A4:A22,ROW(3:21)),MATCH(LARGE(IFERROR(FREQUENCY(IF(A3:A21=A4:A22,ROW(3:21)),IF(A3:A21<>A4:A22,ROW(3:21)))*100+ROW(1:19),), {1,2} ),FREQUENCY(IF(A3:A21=A4:A22,ROW(3:21)),IF(A3:A21<>A4:A22,ROW(3:21)))*100+ROW(1:19),)),2)*{1,-1}))+1       

[ 本帖最后由 wangg913 于 2011-3-3 13:05 编辑 ]

Zaezhong

已重新发邮件~~占位

===============================================================================
第一题
=SUM(N(WEEKDAY(ROW(INDIRECT(DATE(A4,B4,1)&":"&DATE(A4,B4+1,))),2)<6))
=COUNT(0/(MOD(A4&-B4&-ROW($1:$31),7)>1))
第二题
=OFFSET(A$3,ROW(A1)/4-1%,)
第三题
=LOOKUP(1,0/(A$3:A3<>""),A$3:A3)
第四题
=TEXT(MOD(A3,7),"[>1]aaaa;周末")
第五题
=IF(RIGHT(A3)="章",COUNTA(C4:C$26)-SUM(N(C4:C$26<>""),C4:C$26),"")
第六题
=IF(OR(ROW(A1)=MMULT(N(ROW($1:$10)>COLUMN(A:J)),B$3:B$12)+ROW($1:$10)),LOOKUP(ROW(A1),MMULT(N(ROW($1:$10)>COLUMN(A:J)),B$3:B$12)+ROW($1:$10),A$3:A$12),"")
第七题
=ABS(SUM(SMALL(IF(A3:A21<>A4:A22,ROW(3:21)),MOD(LARGE((FREQUENCY(IF(A3:A21=A4:A22,ROW(3:21)),IF(A3:A21<>A4:A22,ROW(3:21))))/1%+ROW(INDIRECT("1:"&1+COUNT(IF(A3:A21<>A4:A22,ROW(3:21))))),{1,2}),100))*{1,-1}))+1
<wangg913>
===============================================================================

Zaezhong.rar (22.07 KB, 下载次数: 37)

2011-3-3 13:22 上传

点击文件名下载附件

第六题，既然使用的是高版本EXCEL，完全可以使用 iferror函数 排除错误值的。
第七题，解题思路更巧妙一些，通过求出最大、二大相连的的位置，去截取对应不相等的两个行号之差
再处理一下小环节：
=ABS(SUM(SMALL(IF(A3:A21<>A4:A22,ROW(3:21)),MOD(LARGE((FREQUENCY(IF(A3:A21=A4:A22,ROW(3:21)),IF(A3:A21<>A4:A22,ROW(3:21))))/1%+ROW(INDIRECT("1:"&COUNT(,1/(A3:A21<>A4:A22)))),{1,2}),100))*{1,-1}))+1

[ 本帖最后由 wangg913 于 2011-3-3 13:27 编辑 ]

ZDH

只会做前面五个,已发

===============================================================================
第一题

=NETWORKDAYS(DATE(A4,B4,1),DATE(A4,B4+1,1)-1)

=DAY(DATE(A4,B4+1,1)-1)-INT((WEEKDAY(DATE(A4,B4,1)-6,2)+DAY(DATE(A4,B4+1,1)-1)-1)/7)-INT((WEEKDAY(DATE(A4,B4,1),2)+DAY(DATE(A4,B4+1,1)-1)-1)/7)

第二题

=OFFSET($A$3,(ROW()-11)/4,)

第三题

=IF(A3<>"",A3,C2)

第四题不对，使用了自定义单元格格式

=IF(WEEKDAY(A3,2)>5,"周末",A3)

第五题做的很好，通过减去下面的单元格之和，这是常用的一种方法。

=IF(RIGHT(A3)="章",COUNTIF(A3:$A$25,"*页")-SUM(C4:$C$25),"")

<wangg913>
===============================================================================
感谢版主的指点,版主辛苦了,从贴中学到很多函数新的用法,真给力!!!!!

[ 本帖最后由 ZDH 于 2011-3-4 09:08 编辑 ]

liulangbaby

占位,同时问一句
加载宏\分析工具库中的函数限不限制使用?比如Newworkdays

已发,请查收
很有意思的题目,感谢大头!

==================================================================================

第一题：其实 DATE(A4,B4,)+1 可以 改为 DATE(A4,B4,1)

第五题，
=IF(RIGHT(A3)="页","",SMALL(IF(RIGHT(A3:A$25)="章",ROW(A3:A$25),26),2)-ROW()-1)

通过判定尾字是不是章，然后生成数组，思路正确

可以省略一些不必要的弯路，公式如下

=IF(RIGHT(A3)="页","",MIN(IF(RIGHT(A4:A$25)="章",ROW(3:$24),25))-ROW())

第六题，
=IF(ISNA(MATCH(ROW(),SUMIF(OFFSET(B$3,,,ROW(B$3:B$12)-2,),">0")+ROW(B$16:B$25)-B$3:B$12,)),"",INDEX(A:A,2+MATCH(ROW(),SUMIF(OFFSET(B$3,,,ROW(B$3:B$12)-2,),">0")+ROW(B$16:B$25)-B$3:B$12,)))

因为排错而是公式延长很多。
排错可以使用其他的方法的，可看看我79楼的答案。

第七题，只差一步，你就接近那个最简洁的思路了。

wangg913

6楼(liulangbaby)-3.rar (11.43 KB, 下载次数: 23)

2011-3-3 14:15 上传

点击文件名下载附件

感谢大头点评,我总是把很多问题想复杂了,这个贴子给了我很多启示.
再次感谢

[ 本帖最后由 liulangbaby 于 2011-3-3 16:20 编辑 ]

wangg913

没有限制版本啊。

wangjguo44

惭愧，只作出六题
（第七题终于做出来了，第二题漏了一个数，同时修改重发，不是考试，重做没问题吧）

===============================================================================
第一题
=NETWORKDAYS(DATE(A4,B4,1),DATE(A4,B4+1,0))
=DAY(DATE(A4,B4+1,0))-SUMPRODUCT((WEEKDAY(ROW(INDIRECT(DATE(A4,B4,1)&":"&DATE(A4,B4+1,0))),2)={6,7})*1)
第二题
=LOOKUP(ROW(A4)/4,{1;2;3;4;5},A$3:A$7)
第三题
=IF(A3<>"",A3,C2)
第四题
=IF(MOD(A3,7)<2,"周末",TEXT(A3,"aaaa"))
第五题
=IF(RIGHT(A3)="页","",SUM(COUNT(FIND("页",A3:A$25)))-SUM(C4:C$25))
第六题
=IF(SUM(--(ROW(A1)=MMULT(N(ROW($3:$12)>TRANSPOSE(ROW($3:$12))),B$3:B$12+1)+1))=1,LOOKUP(ROW(A1),MMULT(N(ROW($3:$12)>TRANSPOSE(ROW($3:$12))),B$3:B$12+1)+1,A$3:A$12),"")
第七题，使用了辅助列完成
=ABS(LOOKUP(,0/(MAX(IF((A3:A20=A4:A21)*(A5:A22<>A4:A21),1)*E4:E21+ROW(A1:A18)%)=IF((A3:A20=A4:A21)*(A5:A22<>A4:A21),1)*E4:E21+ROW(A1:A18)%),ROW(A1:A18))-LOOKUP(,0/(LARGE(IF((A3:A20=A4:A21)*(A5:A22<>A4:A21),1)*E4:E21+ROW(A1:A18)%,2)=IF((A3:A20=A4:A21)*(A5:A22<>A4:A21),1)*E4:E21+ROW(A1:A18)%),ROW(A1:A18)))+1

<wangg913>
===============================================================================

wangjguo44.rar (14.45 KB, 下载次数: 13)

2011-3-3 14:21 上传

点击文件名下载附件

[ 本帖最后由 wangg913 于 2011-3-3 14:22 编辑 ]

leroy

函数刚刚入门，先做个记号吧

没有收到邮件答案，wangg913

Zaezhong

不限制版本是否可以用07以上的？？做了6题，我的第七题答案可是很长

根本没有限制长度，更没有限定版本，能达到效果就可以了。<wangg913>