用excel为学生成绩排名.doc

用excel为学生考试成绩排名第一种方法:最近在用Excel处理学生成绩时遇到了一个问题：在给成绩排序时，如何使所有相同的成绩其名次也相同，同时使后续名次空缺。例如：如果有3个学生成绩都是90分，其名次都应是“1”，则下一成绩为89分，其名次应为“4”，而“2”和“3”应该空缺。大家都知道，Excel的填充序列功能可以实现名次连续填充，但上述问题使用填充序列方法就无法解决了。分析过程如果按照上述要求排名，那么每一个名次都是根据前面的名次得出的，所以应该先给出第一个名次，即“1”（这一点与拖动填充柄填充序列相同）。然后判断：如果第二个成绩与第一个相同，则其名次也为“1”，否则应为上一名次值加1即“2”。依此类推，就可以使相同的成绩其名次也相同（使用“If()”函数实现）。但是这样排出的名次是连续的，并没有使应该空缺的名次空缺。也就是说，100个学生成绩排序之后，最后一名可能是第50名而不是第100名，还是不能满足要求。继续观察发现，在所操作的数据清单中，所有不重复的名次都等于该行行号减1，如果是在数据库中，就相当于该记录的记录号。既然Excel中的数据清单类似于数据库的数据清单，Excel中也应该有类似数据库中返回记录号的函数。于是在函数库中仔细查找，终于发现了函数“Row()”。该函数的功能是返回某一引用的行号，只需在“If()”函数中嵌套调用“Row()”函数，使“If()”的返回值之一为“Row()-1”即可。实际操作如图1所示，首先在G2单元格中输入第一个学生的名次“1”，然后单击选中G3单元格，输入公式“=If(F3=F2,G2,Row(G3)-1)”，这个公式的意义是：如果F3和F2两个单元格的值相等，G3的值就与G2的值相等，否则G3的值就等于G3的行号减1。回车后，G3单元格中就出现了正确的名次，下面的事情就简单了：拖动G3右下角的填充柄一直到最后一个名次处，大功告成！现在来看看，是不是按照原先的要求排好了名次？图1这里有一点需要指出的是：因为操作的数据清单正好处于工作表的最左上角，即数据清单的标题行位于第一行，所以公式中“Row()”函数要减去1，如果标题行不是在第一行，就不能减1了。可以简单地总结为：标题行行号为几，就让“Row()”函数减去几。其实实现上述功能还有其他方法，不必嵌套函数，一个函数调用就解决问题了，而且也不用输入初始值“1”。这次要用到的函数是“Rank()”。方法很简单：在G2单元格中输入公式“=Rank(F2,F$2:F$101)”（假定共有100个成绩）。这个公式的功能是：返回F2单元格中的值在从F2到F101所有单元格数据即所有成绩中的排位。需要注意的是，一定不要忘了那两个美元号“$”，否则就得不出正确结果了，接下来的操作同前一方法。第二种方法:第一步：打开Excel输入原始数据求出总分（总分=成绩1+成绩2+成绩3）如表1。表1第二步：将光标定位于名次栏G2单元格输入公式=RANK(F2,F$2:F$6,2)回车（有关RANK函数的格式及其功能以前的文章多有介绍，如不明白可通过Excel的帮助功能来查找，敬请读者谅解，结果为3说明该人成绩排名为第3（?如表2）。表2　　第三步：选定G2单元格将光标移动至其右下角当光标变为黑十字时双击，看全部自动填充（如表3），其中张双和贺家胜名次列数据均为1说明并列第一，现在我们已经顺利地完成了任务。表三第三种方法:3) 排序功能　　单击“工具”菜单，选择“排序”命令。　　选择“主要关键字”为“总分”，排序方式选择“递减”。　　所有学生的成绩按部分进行了排名。利用自动填充功能在J2到J24中填入序列号1-23；在K2中输入“1”，在K3中输入“=K2*(I3=I2)+J3*(I3I2)”。　　选中K3单元格，利用自动填充向下拖，就将名次排好了。4) 成绩统计　　在绘制每科成绩时，我们先统计出各分数段的人数。在新工作表中，输入成绩分布表的标题行和列，如图所示，然后单击C4单元格，输入“COUNTIF(期中成绩 !$D$2:$D$24,=90)”。　　单击C5单元格，输入“COUNTIF(期中成绩 !$D$2:$D$24,=80)-C4”，单击C6单元格，输入“=COUNTIF(期中成绩 !$D$2:$D$24,=70)-C4-C5”，单击C7单元格，输入“=COUNTIF(期中成绩 !$D$2:$D$24,=60)-C4-C5-C6”。这样，语文成绩的各分数段人数就统计出来了。