[第29期]高考最高分统计排序

guangyp

出题者：willin2000

原贴链接：http://club.excelhome.net/viewthread.php?tid=250134

答题要求和附件在11/3重新修改过. 请大家注意一下!

高考分数已经出来了,学校对学生的成绩也要做各种形式的统计,排名.下面这个统计的是各个学生科目中的最高分:

---

执行新要求后,至本贴修改时间已收到以下会员答案：

chrisfang/dengyf/gouweicao78/guangyp/HHAAMM/leeyong/lrf2003/Luckyguy2008/lws/moon_5iwww/songhuaegg/WDDN/XCD/yoka/封州大少/泓()/jjw530/ultra

[此贴子已经被willin2000于2007-11-21 21:40:24编辑过]

封州大少

题目比较好解，但我的公式太长了，三个公式用了650多个字符，比起版主的450个字符，真是惭愧！先发上，有时间现想想简化的办法

QUOTE:

不符合答题要求3,不过对答案公式进行非实质性修改成:
=INDIRECT(TEXT(MID(LARGE(IF($B$2:$J$21=SUBTOTAL(4,OFFSET($B$1,ROW($1:$20),,,9)),$B$2:$J$21*10^9+ROW($2:$21)*10^3+COLUMN($B:$J),0.0065536256),MIN(180,ROW(A1))),CHOOSE(COLUMN(A2),5,10,5),CHOOSE(COLUMN(A2),5,3,8)),CHOOSE(COLUMN(A2),"R0C1","R1C0","R0C000")),)&""
258个字符,基本能满足答题要求. (如果一个人的所有分数在100分以下，那就会出现错误结果。)

如何将含行列信息的最大值结果(最大值..行信息..列信息)按答题要求(字数/出错处理/右拉)分解在三列中的得到不同结果是本题需要解决的主要问题.

原则上"不符合答题要求"不能得分,但考虑到大家都花了一些时间来做题,并且本次答题者的答案中完全符合答题要求的不是很多,所以这次评分犹豫再三,为了既遵照竞赛区的规则,又能体现答题者的答案质量,评分略微放宽,大体上按以下原则执行:
1. 使用多格数组的不能得分.(右拉是考点,也是特别强调的)
2. 答题者的核心公式思路比较正确并且略加修改还可满足答题要求视情况可得1分.
3. 精彩部分另加分.

本答案基本能满足要求,评1分.                          - willin2000.

[此贴子已经被willin2000于2007-11-23 12:04:08编辑过]

lws

答案已发送,请查收!!!

=INDIRECT(TEXT(RIGHT(LARGE(IF(($B$2:$J$35=SUBTOTAL(4,OFFSET($B$2:$J$2,ROW($2:$35)-2,))),$B$2:$J$35*10^9+ROW($2:$35)*10^6+ROW($2:$35)^N(L$1<>"科目")*10^3+COLUMN($B:$J)^N(L$1<>"学生"),1099),ROW(A1)),5),"R0C000"),)&""

QUOTE:

公式长度227还好.对附件中的数据处理结果正确,思路是将用于排序的行号和取位置的行号分开,分数&行号&行号(随列变动)&列(随列变动). 其中COLUMN($B:$J)^N(L$1<>"学生")用的很好(本题考点之一)

不过有几个问题:

1.300行不出错的方法是通过加大数据区,不是很好. 并且答题要求说是300行以后,1000行也是300行后,但公式结果就不对了.

2.由于使用了两个行号,所以不能满足答案要求4. 更多学生如果是5位数个学生,那最大信息将超过15位(4+5+5+3),不能直接修改参数适应更多学生.

综合来说,思路方向正确,对附件数据处理的结果正确,但有些有求未完全符合. 评1分. -willin2000

[此贴子已经被willin2000于2007-11-23 12:03:24编辑过]

songhuaegg

答案已发送,请查收

邮件地址:songhuaegg@163.com 

QUOTE:

3列使用了三个不同的公式,不符合答题要求, 不得分. -willin2000

[此贴子已经被willin2000于2007-11-22 21:02:17编辑过]

xcd

右拉公式容错:249字符

多格数组(格式):168字符

已发送到：w829924@hotmail.com

 

QUOTE:

=INDIRECT(TEXT(MID(LARGE(IF($B$2:$J$21=SUBTOTAL(4,OFFSET($A$1,ROW($2:$21)-1,1,,9)),$B$2:$J$21*10^7+ROW($2:$21)*10^3+COLUMN($B:$J),1000999256),MIN(ROW()-1,COUNT($B$2:$J$21))),5+(L$1="科目")*3,3+(L$1="成绩")*3),"r"&(L$1="科目")+0&"c"&IF(L$1="学生",1,"000")),)

如果将列信息也变的化,公式能简洁一些. 可以发现其实只有列信息是不会影响排序结果的.

思路是将处理最大值信息和文本格式变化结合来完成不同列的结果:TEXT(MID(信息,变,变),变).

但如果一个人的所有分数在100分以下,那就会出现错误结果(多谢方兄提醒)。并且公式没有处理0值. 评1分. -willin2000

[此贴子已经被willin2000于2007-11-22 21:58:41编辑过]

Luckyguy2008

已经发邮件给willin2000版主，请批阅，谢谢

QUOTE:

=INDIRECT(TEXT(1+MID(LARGE(IF(($B$2:$J21=SUBTOTAL(4,OFFSET($B$1,ROW($1:20),,,9)))*LEN($B$2:$B21),$B$2:$J21*10^12+ROW($2:21)*10^6+COLUMN($A:$I)*1001+10^14+100,2/9),ROW(A1)),5+8*(COLUMN()=13),COLUMN(F1)),"R0C"&REPT(0,COLUMN(A2))),)&""

公式长度232,思路是text(MID(最大值信息(含列变),变,变),变).其中列变稍微处理的复杂了些.可以再优化一下.

结果正确,符合答题要求. 评2分. -willin2000

[此贴子已经被willin2000于2007-11-23 15:54:22编辑过]

guangyp

今晚按新要求作了一下，有一定难度，多格数组和右拉都用了198字符,邮件已发送到指定邮箱，请willin2000版主查收。

 

QUOTE:

谢谢gaungyp提供的多格数组公式方法1/2/4. 方法三公式:

=INDIRECT(TEXT(RIGHT(MAX(10^9-1,LARGE((SUBTOTAL(4,OFFSET($B$1,ROW($1:$98),,,9))=$B$2:$J$99)*($B$2:$J$99*10^9+ROW($1:$98)*10^IF(L$1=$M$1,5,2)+100+COLUMN($B:$J)^(L$1<>$L$1)),ROW()-1)),5),"r0c00"),)&""

1.其中300行的处理方法与3楼类似,答题要求300行后(不是内),所以N行后公式结果会不对.(另外数据区下面有些地方不能有东西)

2.行的方式是位置变化,公式里用了3位,但学生有5位时,同样信息也会超出15位数.不符合答案要求4

3.由于使用了MAX(10^9来屏蔽错误值,所以公式不适用于列3位数的情况. 因C999出错.

针对附件数据结果正确,公式长度也很短,思路方向正确.但有些要求没完全符合, 评1分 -willin2000

[此贴子已经被willin2000于2007-11-23 22:37:40编辑过]

wddn

三条公式的要求改成一条公式。

难度从林中漫步，一下子变为丛从的挑战的探险。

在论坛上，我所见到的关于成绩处理的函数题，没有不会做的。

本竞赛题，我几乎要交不出答案了。

公式取巧了，请版主指教。

另：

个人观点：数据表中数据，包括空值共180条记录，公式要求拉到300行没有错误，是否要求过高了，如果是三条公式，在260字符内则很容易办到。

QUOTE:

"要求拉到300行没有错误",竞赛的实际使用有所不同,竞赛的目的是为了某些考点.

没有用右拉公式,或长度超过260,不得分. -willin2000

[此贴子已经被willin2000于2007-11-23 22:35:30编辑过]

liangzh

水平有限，做了个253字符的，希望尽快看到高手们的高招。

QUOTE:

没有收到答案-willin2000

[此贴子已经被willin2000于2007-11-22 22:39:41编辑过]

leeyong

回复[第29期]高考最高分统计排序

答案已通过短消息发送!

QUOTE:

=INDIRECT(TEXT(MOD(LARGE((MOD(SMALL($B$2:$J$21+ROW($2:$21)*1000,ROW($1:$20)*9),1000)=$B$2:$J$21)*($B$2:$J$21*10^9+ROW($2:$21)*10^6+ROW($1:$20)*10^2*(L$1<>$M$1)+COLUMN($A:$I)*(L$1<>$L$1)+101),MIN(ROW()-1,COUNT($B$2:$J$21))),10^6),"R0C00;;R1C256"),0)&""

公式长度251个字符.公式思路TEXT(MAXVAL&row&row变&COL变,变).所以也存在答案要求4的问题. 评1分. -willin2000

[此贴子已经被willin2000于2007-11-22 22:44:37编辑过]