| 楼主 悟空师弟 |
Q：如何将学生姓名按偏科程度排序？ 如下图： 偏科程度意思为： 1、语文、数学、英语三科中最高分与最低分的差数为偏科值。 2、偏科值越大说明越偏科。 3、按偏科程度由高到低排序。 结果如E列。  A：数组公式：
 如何将学生姓名按偏科程度排名？.rar★ 实际应用： 作为班主任，即时了解学生各种学习方面的信息尤为重要，而“是否偏科”也是非常值得班主任老师关注的一项重要指标，如果能从成绩表中将偏科学生名单根据偏科程度自动排好序则可以为班主任工作带来非常大的方便。 ★ 解题思路： 如下图：（见附件中“分步”工作表） 1、偏科值以是三科中最高分与最低分的差数而定，可用最大数（MAX)减最小数(MIN)来计算， 公式为：
2、将偏科值*100再加上行号可产生一组不重复的偏科权衡值（因行号为2至25，为保证后两位为代表行号的数值所以*100，如果行号达到三位数则应*1000） 公式为：
3、用LARGE函数对加权值进行降序排列。 公式为：
4、用RIGHT函数取后两位数值，即偏科值所在单元格的行号。 公式为：
５、用INDEX函数返回行号所对应的A列的值，即姓名。 公式为：
 实际解决问题的时候如果能不用辅助列尽量不使用辅助列，将以上辅助列公式合为一体需要用到数组公式及特殊函数的特殊用法。即上面说到的“数组公式”
1、ROW($1:$24) 这一段产生一个数组 选中这段按F9可得：{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24} 用来做OFFSET函数的第二个参数。 2、OFFSET(B$1:D$1,ROW($1:$24),) 这一段将产生24个一行三列的数据，即每个学生的三科成绩， 注：此段为多维数组，不能用F9查看。 3、SUBTOTAL({4,5},OFFSET(B$1:D$1,ROW($1:$24),)) 这一段产生一组24行两列的数据，每一行第一列为三科成绩中的最大值，第二列为最小值。 选中这段按F9可得： {85,77;89,64;91,50;86,52;93,69;99,52;98,68;89,52;81,65;83,63;92,80;100,61;99,62;100,55;84,50;90,55;65,52;98,65;99,64;82,58;86,70;82,60;79,54;96,63} 4、MMULT(SUBTOTAL({4,5},OFFSET(B$1:D$1,ROW($1:$24),)),{1;-1}) 这一段产生一组24行一列的数据，即最大值与最小值的差值。 选中这段按F9可得： {8;25;41;34;24;47;30;37;16;20;12;39;37;45;34;35;13;33;35;24;16;22;25;33} MMULT的第二个参数｛1,-1｝作用是将最大值保持正数，最小值改为负数，两者之和实际为差，即偏科值。 5、MMULT(SUBTOTAL({4,5},OFFSET(B$1:D$1,ROW($1:$24),)),{1;-1})*10^2+ROW($2:$25) 这一段产生一组24行一列的数据，即偏科值加上行号。 选中这段按F9可得：{802;2503;4104;3405;2406;4707;3008;3709;1610;2011;1212;3913;3714;4515;3416;3517;1318;3319;3520;2421;1622;2223;2524;3325} 6、LARGE(MMULT(SUBTOTAL({4,5},OFFSET(B$1:D$1,ROW($1:$24),)),{1;-1})*10^2+ROW($2:$25),ROW(A1)) 这一段产生一个数值，即权衡值中的第一个最大值。 选中这段按F9可得：{4707} 7、RIGHT(LARGE(MMULT(SUBTOTAL({4,5},OFFSET(B$1:D$1,ROW($1:$24),)),{1;-1})*10^2+ROW($2:$25),ROW(A1)),2) 这一段产生一个数值，为权衡值中的第一个最大值的后两位数值，即权衡值中第一个最大值所对应的行号。 选中这段按F9可得：{"07"}（第7行） 8、INDEX(A:A,RIGHT(LARGE(MMULT(SUBTOTAL({4,5},OFFSET(B$1:D$1,ROW($1:$24),)),{1;-1})*10^2+ROW($2:$25),ROW(A1)),2)) 这一段（即整个数组公式，输入完后按Ctrl + Shift + Enter三键结束）返回的即是“最偏科”的学生姓名。 选中整个公式按F9可得：姓名6 |
| 2楼 悟空师弟 |
公式中相关函数的学习资料： [函数与公式] MMULT经典用法 http://www.exceltip.net/thread-3489-1-1.html [函数与公式] 利用MMULT进行同RANK效果排序的方法及过程？ http://www.exceltip.net/thread-13335-1-1.html [函数与公式] MMULT函数的参数有什么限制？ http://www.exceltip.net/thread-6434-1-1.html [函数与公式] 【函数解读系列】之解读SUBTOTAL函数 http://www.exceltip.net/thread-1479-1-1.html [函数与公式] SUBTOTAL总结 http://www.exceltip.net/thread-8289-1-1.html [基础应用] 如何处理subtotal函数取得序号后遇到的筛选问题 http://www.exceltip.net/thread-8829-1-1.html [函数与公式] OFFSET函数 动态图解 http://www.exceltip.net/thread-8194-1-1.html [函数与公式] OFFSET函数格参数引用实例 http://www.exceltip.net/thread-3800-1-1.html [函数与公式] 怎样理解OFFSET函数的各个参数？ http://www.exceltip.net/thread-3581-1-1.html [函数与公式] OFFSET动态图解 http://www.exceltip.net/thread-7141-1-1.html [函数与公式] 为何INDEX对一维常量数组取值会返回单元素数组？ http://www.exceltip.net/thread-12264-1-1.html [函数与公式] INDEX+MATCH函数介绍 http://www.exceltip.net/thread-12134-1-1.html [函数与公式] INDEX函数动态图解 http://www.exceltip.net/thread-8257-1-1.html [函数与公式] INDEX动态图解 http://www.exceltip.net/thread-7142-1-1.html |
| 3楼 积少成多 |
学习下,谢谢老师 |
| 4楼 凡人兮 |
很好的帖子，好好学习！ |
| 5楼 bluexuemei |
悟空师弟一出必是精品! |
| 6楼 cpcz |
 |
| 7楼 海洋之星 |
学习了，太牛了，嘿嘿 |