EXCEL计算名次的问题两种方法if和rank函数 sql rank 函数
EXCEL计算名次的问题
2011-03-28 01:27
在学校工作,要用EXCEL对学生的成绩进行名次计算。但是出现了一些问题,最后使用了比较麻烦的方法才把名次算出来。下面讲一下过程。
首先成绩表是这样的:
一、rank函数
第一个想到的方法是使用EXCEL中自带的rank函数。rank函数可以计算出单元格在某数列中的排名。函数编辑好之后对名次列(G列)进行公式填充。
使用rank函数后的效果,红框中代表的是函数:
但是我们如果对名次进行一个排序。就发现结果很奇怪,如图所示:
再仔细看编辑栏中的函数,就发现问题所在。原来在使用rank函数进行填充的时候函数中的数据列也跟着改变。
这个问题比较好解决,将函数中的相对引用转化成绝对引用就可以了(更改方法即是在行列中间插入一个"$"符号,例如A3更改为A$3),更改好了以后的图:
到这里笔者认为已经达成目标了,但是对名次列进行排序后又出现问题了:
注意红框中的内容,有两个同学并列第八名,但是往下就没有了第九名,直接到了第十名,并且只要有成绩并列的名次都出现了相同的问题。
可能rank函数不适用多个数值的排名。于是放弃rank函数,重新思考。
二、if函数+选择性粘贴
既然rank函数失败了,那么就转向新的思考方向。新办法的思路是这样:
1.对总成绩进行降序排序,并且确定第一名。
2.使用if函数,以总成绩第二名的同学为例。
⑴如果第二名同学的成绩比第一名同学的成绩小,那么第二名同学的名次就等于第一名同学的名次再加一。
⑵如果第二名同学的成绩不比第一名同学的成绩小,那么第二名同学的名次就等于第一名同学的名次。
3.以学号列为主要关键字进行排序,恢复表格的原始顺序。
1.对总成绩进行降序排列,在成绩最高的同学名次单元格上输入1:
2.在第二名同学的名次单元格中输入if函数,if(F3<F2,G2+1,G2)
该函数的目的在于,如果第二名同学的总成绩(F3)小于第一名同学的总成绩(F2),即(F3<F2)。那么第二名同学的名次等于第一名同学的名次加一(G2+1);如果不小于,那么就与第一名同学的名次相等(G2)。随后填充公式,发现刚才rank函数的问题已经解决,并列第八名的两位同学的下一个同学就为正常的第九名。
3.最后一步即是要将表格按照学号进行排序,恢复表格的原始数据排列。
可是排序时又出现了问题:
问题在于笔者计算排名的时候使用的是函数,重新进行排序以后顺序变动,无法找准if函数处理后得到的值造成的。
解决这个问题的办法是将函数得到的值直接转化为数字,再进行排列。
先选中名次列 -> 复制 -> 右键 -> 选择性粘贴,在对话框中选择数值
右键->选择性粘贴
选择数值
选择数值以后名次列的函数全部转化为数值
编辑栏中的函数变为数字
最后将学号列作为关键字进行升序排列,表格恢复到原始状态,并且排名列也填好,问题得以解决。
总结:由于笔者对于EXCEL了解不是很多,对函数部分了解得也太少。所以暂时只想到这个办法。但是这个方法始终过于繁琐,可能使用某个函数或者某几个函数嵌套的方法或者在EXCEL中嵌入VB语言可以实现一步到位的计算名次。希望知道更简单方法的朋友可以告之,也欢迎大家和我讨论好的方法。
更多阅读
删除系统临时文件的两种方法 系统临时文件
删除系统临时文件的两种方法——简介 删除系统临时文件的方法有两种,包括手动删除系统临时文件和使用第三方软件删除临时文件。下面就跟win7之家小编一起来了解一下吧!删除系统临时文件的两种方法——方法/步骤删除系统临时文件的两
如何在华硕官网下载驱动两种方法 华硕官网怎么下载驱动
如何在华硕官网下载驱动(两种方法)——简介我是华硕的用户,在华硕官网上不知道驱动如何下载,折腾了半天才找到,为了节省其他华硕用户的时间,特地把如何下载驱动的步骤发上来和大家共享如何在华硕官网下载驱动(两种方法)——进入搜索
宠物航空托运的两种方法及两种方法的利弊比较 深圳航空宠物托运
?我们在宠物航空托运时,常常会问到一个问题:如果人不回去,小狗小猫可否回去,这个问题很有普遍性,所以航强物流在这里给大家作一下解答:宠物托运实际上有两种方法:1、走货运渠道。2、随机托运 关于上面两种说法到底是什么意思呢?1、
iphone4s主题怎么换?两种方法教你更换主题 ppt怎么设置两种主题
iphone4s 主题怎么换, 两种方法教你轻松更换主题!要更换iphone4s的主题首先:你的iphone4s必须要完美越狱了iphone4s主题怎么换?两种方法教你更换主题——工具/原料更换主题所用到的工具有:WinterBoard、ifunboxifunbox(可以参考资料下载
Word2010中插入分页符的两种方法 word2010分页符
我们在使用Word2010编辑文档的过程中,有时需要在页面中插入分页符,以便于更灵活地设置页面格式。本篇经验介绍一下插入分页符的两种方法。Word2010中插入分页符的两种方法——工具/原料Word 2010Word2010中插入分页符的两种方法——