【函中20130301】第十三讲D组作业上交帖（已开帖）

顺⑦.zì繎。

1、本贴已设置回贴仅作者可见，直接跟贴回复即可； 2、上交时直接贴公式，只上传附件的不予批改； 3、答题时必须注明【函中201301班】-组号-论坛ID,如【函中201301班】-C99-CheryBTL； 4、截止时间2013年8月3日17:00； 5、请误重复占楼，非本组同学跟贴回复者扣分。 下附课程相关链接: 函中201301班第十三讲课件

1982zyh

【函中201301班】-D16-1982zyh
1.{=SUM(TRANSPOSE(LARGE(INDIRECT(TEXT(IF($E$192:$G$194="",4^8,ROW($192:$194)*100+COLUMN(E:G)),"r0c00"),),ROW(1:3))))}
2.=RANK(I198,OFFSET($I$197,MATCH(D198,$D$198:$D$207,),,COUNTIF($D$198:$D$207,"="&D198),),0)
3.=INDEX(D211:I216,MATCH("*"&D218&"*",D211:D216,),6)
4.{=VLOOKUP(D227&E227&F227,CHOOSE({1,2},G221:G225&E221:E225&F221:F225,D221:D225),2,0)}
5.=OFFSET(E229,1,MOD(E232-E229,12))
6.{=LOOKUP("A",RIGHT(LEFT(D242,MATCH(1,-MID(0&D242,ROW($1:141),1))-1),ROW($1:141)))}

第一题答案不一样，不知道对不对

liziyuliziyu7

【函中201301班】-04-liziyuliziyu7
=CHOOSE(MOD(E232,12)+1,"猴","鸡","狗","猪","鼠","牛","虎","兔","龙","蛇","马","羊")

zac0226

【函中201301班】-D02-zac0226
1、不会
2、

1. =SUMPRODUCT((D198=$D$198:$D$207)*($I$198:$I$207>I198))+1

复制代码

3、

1. =INDEX(I211:I216,MATCH("*"&D218&"*",D211:D216,))

复制代码

4

1. =LOOKUP(1,0/(D227=G221:G225)*(E227=E221:E225)*(F227=F221:F225),D221:D225)

复制代码

5、

1. =INDEX(E230:P230,MATCH(TRUE,MOD((E232-E229:P229),12)=0,))

复制代码

6、

1. =MID(D236,MIN(FIND({0,1,2,3,4,5,6,7,8,9},D236&1234567890)),5)
   
2. =-LOOKUP(,-MID(D242,MIN(FIND({0,1,2,3,4,5,6,7,8,9},D242&1234567890)),ROW($1:$10)))

复制代码

笨笨四

【函中201301班】-D09-笨笨四
1.=SUM(SUBTOTAL(4,OFFSET(E191,{1,2,3},,,3)))
2.=SUM((D$198:D$207=D198)*(I198<I$198:I$207))+1
3.=SUMIF(D211:D216,"*"&D218&"*",I211)
4.=LOOKUP(1,0/(D227&E227&F227=G221:G225&E221:E225&F221:F225),D221:D225)
5.=OFFSET(E230,,MOD(E232,12))
6.1=-LOOKUP(1,-RIGHT(D236,ROW($1:9)))
6.2=MID(D242,MATCH(1=1,MID(D242,ROW($1:9),1)<"9",),COUNT(-MID(D242,ROW($1:9),1)))

林玲玲

【函中201301班】-D11-林玲玲
1、 =SUM(SUBTOTAL(4,OFFSET(E191:G191,{1,2,3},,,)))
2、=RANK(I198,OFFSET(I$197,MATCH(D198,D$198:D$207),,-COUNTIF(D$198:D$207,D198),))
3、=INDEX(I211:I216,MATCH("*"&D218&"*",D211:D216,))
4、=VLOOKUP(E227&F227&D227,CHOOSE({1,2},E221:E225&F221:F225&G221:G225,D221:D225),2,)
5、=INDEX(E230:P230,MATCH(12,E232-E229:P229,))
6、1.=RIGHT(D236,MATCH(99,--RIGHT(D236,ROW($1:$99)),1))
6、2.=MID(D242,MIN(FIND({0;1;2;3;4;5;6;7;8;9},D242&1234567890)),MATCH(99,--MID(D242,MIN(FIND({0;1;2;3;4;5;6;7;8;9},D242&1234567890)),ROW($1:$99)),1))

veryloveep

[函中201301班]-D22-veryloveep
1=SUM(LARGE(N(OFFSET(D191,(ROW(1:3)),4)),1))
2=RANK(I198,OFFSET($I$197,MATCH(D198,$D$198:$D$207,),,COUNTIF($D$198:$D$207,D198)))
3=INDEX(I211:I216,MATCH("*"&D218&"*",D211:D216,))
4=VLOOKUP(D227&E227&F227,IF({1,0},G221:G225&E221:E225&F221:F225,D221:D225),2,)
5=LOOKUP(MOD(E232,12),MOD(E229:P229,12),E230:P230)
6=--MID(D236,MATCH(TRUE,ISNUMBER(1*MID(D236,ROW($1:$9),1)),0),COUNT(1*MID(D236,ROW($1:$9),1)))

cg372101

【函中201301班】-D18-cg372101
题1：

1. =SUMPRODUCT(SUBTOTAL(4,OFFSET(E192:G192,ROW(1:3)-1,)))

复制代码

题2：

1. =SUMPRODUCT((MATCH(D$198:D$207&I$198:I$207,D$198:D$207&I$198:I$207,)=ROW($1:$10))*(I$198:I$207>I198)*(D$198:D$207=D198))+1

复制代码

题3：

1. =INDEX(I211:I216,MATCH("*"&D218&"汽*",D211:D216,))

复制代码

题4：（数组公式）

1. =VLOOKUP(D227&E227&F227,CHOOSE({1,2},G221:G225&E221:E225&F221:F225,D221:D225),2,)

复制代码

题5：

1. =INDEX(E230:P230,,MOD(E232-E229,12)+1)

复制代码

题6：（数组公式）

1. =MID(D236,MATCH(TRUE,ISNUMBER(1*MID(D236,ROW($1:$9),1)),0),COUNT(1*MID(D236,ROW($1:$9),1)))

复制代码

yunqingzi

1、{=SUM(SUBTOTAL(4,OFFSET(D191,{1,2,3},1,,3)))}
2、{=SUM(N(FREQUENCY(IF((D$198:D$207=D198)*I$198:I$207>=I198),I$198:I$207),I$198:I$207)>0))}
3、{=INDEX(I211:I216,MATCH("*"&D218&"*",D211:D216,))}
4、{=LOOKUP(1,0/((G221:G225=D227)*(E221:E225=E227)*(F221:F225=F227)),D221:D225)}
5、{=INDEX(E230:P230,MATCH(12,E232-E229:P229,))}
6.1、{=RIGHT(D236,SUM(N(CODE(MID(D236,ROW(INDIRECT("1:"&LEN(D236))),1))<57)))}
6.2、{=MID(D242,MATCH(0,MID(D242,ROW(INDIRECT("1:"&LEN(D242))),1)*0,0),SUM(N(CODE(MID(D236,ROW(INDIRECT("1:"&LEN(D236))),1))<57)))}