如何使用Excel按成绩分数统计出同分数的相同排名?

标签:
单元格名次函数总分数据清单杂谈 |
分类: 名人堂 |
http://s16/middle/5a08efd4gafa94a7bab0f&690
该函数的功能是返回一个数字(上述函数中的E3,此处采用相对应用,填充时随行的变化而变化)在数字列表(上述函数中的E$3:E$80,此处采用绝对应用,填充时不发生变化)中的排位。数字的排位是其大小与列表中其他值的比值。该函数在使用时,即使总分没有排序,它也可以直接求出所对应总分的名次;如果总分已排过序,则数字的排位就是它当前的位置。上述函数中最后的“0”指明排位的方式,当其为 0或省略时,Excel 对数字的排位是按照降序排列的。 如果不为零, Excel 对数字的排位则是按照升序排列的。该函数对重复数的排位相同,而下一名次则将前面的重复数计算在内。
二、使用计数函数COUNTIF
在E3单元格中输入函数“=COUNTIF(E$3:E$80,">"&E3)+1”,然后向下自动填充即可实现该操作。方法同上;
该函数的功能是计算区域中满足给定条件的单元格个数。上述函数中采用绝对引用指定计算区域E$3:E$80,名次所对应的单元格E3采用相对引用。即求出在E3:E80区域中大于名次所对应总分的单元格的个数,然后加上1就可以得出该单元格中数值的名次。该操作不管有几个重复的,大于它的个数却是固定的,所以重复数据的名次也就是相同的了。同RANK函数一样,该函数不管是否已经按总分进行排序均可进行上述操作。
三、采用IF语句加COUNT函数
该操作与上述两种方法不同,必须先将总分按照降序排列。然后将第一名的名次“1”输入,再在F4单元格中输入函数“=IF(F4=E3,G3,COUNT($E$3:E4))”,确定后向下自动填充也可实现同分同名次的操作。
COUNT函数的功能是返回指定区域中数字单元格的个数。因为F列全是数字单元格,所以该总分处在第几位上名次就是几。上述语句的意思就是如果当前总分和上一个总分相同,则输入上一个总分的名次,否则执行COUNT语句,统计个数。
以上三种方法均可自动实现同分同名次操作。希望本文对您有所帮助!
最近在用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右下角的填充柄一直到最后一个名次处,大功告成!现在来看看,是不是按照原先的要求排好了名次?
http://s11/middle/5a08efd4gafa9502b8cfa&690
这里有一点需要指出的是:因为操作的数据清单正好处于工作表的最左上角,即数据清单的标题行位于第一行,所以公式中“Row()”函数要减去1,如果标题行不是在第一行,就不能减1了。可以简单地总结为:标题行行号为几,就让“Row()”函数减去几。
其实实现上述功能还有其他方法,不必嵌套函数,一个函数调用就解决问题了,而且也不用输入初始值“1”。这次要用到的函数是“Rank()”。方法很简单:在G2单元格中输入公式“=Rank(F2,F:F)”(假定共有100个成绩)。这个公式的功能是:返回F2单元格中的值在从F2到F101所有单元格数据即所有成绩中的排位。需要注意的是,一定不要忘了那两个美元号“保裨蚓偷貌怀稣方峁耍酉吕吹牟僮魍耙环椒ā?/p>
在用Excel统计成绩时,我们一般会根据成绩高低进行排序,然后按序列自动填充出名次。这种方法得出的名次与总分没有关联,即使成绩相同,也会填充出不同的名次。
http://s9/middle/5a08efd4gafa957706ce8&690
如果数据较少,我们可以采用手动的方法将成绩相同的人员改成相同的名次,但数据较多时就很麻烦了。经过实践,以下三种方法可以自动实现同分同名次的操作。假设有1000名考生,字段位置如上图所示。
一、使用排位函数RANK
在H2单元格中输入函数“=RANK(G2,G$2:G$1001,0)”,回车后H2单元格中会出现名次“1”,然后选定H2,拖动其填充柄向下填充即可自动实现。
该函数的功能是返回一个数字(上述函数中的G2,此处采用相对应用,填充时随行的变化而变化)在数字列表(上述函数中的G$2:G$1001,此处采用绝对应用,填充时不发生变化)中的排位。数字的排位是其大小与列表中其他值的比值。该函数在使用时,即使总分没有排序,它也可以直接求出所对应总分的名次;如果总分已排过序,则数字的排位就是它当前的位置。上述函数中最后的“0”指明排位的方式,当其为 0或省略时,Excel 对数字的排位是按照降序排列的。 如果不为零, Excel 对数字的排位则是按照升序排列的。该函数对重复数的排位相同,而下一名次则将前面的重复数计算在内。
二、使用计数函数COUNTIF
在H2单元格中输入函数“=COUNTIF(G$2:G$1001,">"&G2)+1”,然后向下自动填充即可实现该操作。
该函数的功能是计算区域中满足给定条件的单元格个数。上述函数中采用绝对引用指定计算区域G$2:G$1001,名次所对应的单元格G2采用相对引用。即求出在G2:G1001区域中大于名次所对应总分的单元格的个数,然后加上1就可以得出该单元格中数值的名次。该操作不管有几个重复的,大于它的个数却是固定的,所以重复数据的名次也就是相同的了。同RANK函数一样,该函数不管是否已经按总分进行排序均可进行上述操作。
三、采用IF语句加COUNT函数
该操作与上述两种方法不同,必须先将总分按照降序排列。然后将第一名的名次“1”输入,再在H3单元格中输入函数“=IF(G3=G2,H2,COUNT($G$2:G3))”,确定后向下自动填充也可实现同分同名次的操作。
COUNT函数的功能是返回指定区域中数字单元格的个数。因为G列全是数字单元格,所以该总分处在第几位上名次就是几。上述语句的意思就是如果当前总分和上一个总分相同,则输入上一个总分的名次,否则执行COUNT语句,统计个数。
以上三种方法均可自动实现同分同名次操作。本文虽然有孔乙己先生关于“回”字有四种写法的嫌疑,但笔者的目的却只有一个,那就是抛砖引玉,让Excel函数更好的为我们服务。