[Excel 函数与公式] [第29期]高考最高分统计排序

不符合答题要求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.

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

不过有几个问题:

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

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

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

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

=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

=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

谢谢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

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

没有收到答案-willin2000

=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