如何在EXCEL中使用rank函数不连续排序排序

来源：蜘蛛抓取(WebSpider) 时间：2016-07-12 10:20 标签： rank函数 多区域 排序
巧用Excel排位函数和排序功能
　　许多公司为了加强管理，都要对内部各个部门定期进行量化评分考核并排列名次，激励表现良好的部门，敦促落后部门改进，使公司管理不断完善。　　面对无序的积分，在不改变各个部门原有位置的情况下给它们进行排列名次可是一件十分棘手的事件，尤其存在相同积分相同名次的排列更增加了难度。　　但如果能巧妙应用排位函数“Rank”及灵活应用Excel排序功能，问题解决起来也就比较简单了。现以某鞋业有限公司的稽查评分总表（如图1）为例来阐述解决方案。
图片可点击放大　　在该稽查评分总表中，要根据总评分栏的积分在不改变各个部门原有位置的情况下求出各部门相应的名次。总评分最高者名次为1，然后随总评分递减而名次加1。若总评分相同则名次也相同，但该名次之后的部门名次仍然按该名次加1计算，例如有3个第4名则第4名后就紧跟第5名。　　1、应用Rank函数进行排位：用光标选定D4单元格后输入“=RANKC4$C$4$C$26”按回车键后便可计算出第一记录的“名次”。将光标移到E3单元格填充柄上变成细十字形光标后，按下鼠标左键往下拖（公式复制），就可将所部门的“名次”计算出来（说明：以上公式中$C$4∶$C$26是指绝对引用C4到C26单元格区域）。　　虽然Rank函数已将所有的部门按积分的高低进行名次排列，但在相同名次的后面的名次却没有达到我们的要求。如在本例中有两个第1名而后面出现的并不是我们期待的第2名而是第3名。为此，我们还得巧妙地应用排序功能来补充完成Rank函数尚未完成的工作。　　2、增加关键字段：为了保证各部门位置不变，我们可以借助增加关键字段再将其隐藏的方法来处理，具体操作如下：分别选定第D列和第F列点击“插入”菜单并单击“列”后便可自动增加2列（图2）。
图片可点击放大　　选定D3单元格输入一个新字段名“序列”，再选定D4单元格并输入1，将光标移到在D4单元格填充柄上变成细十字形光标后按住“Ctrl键”不放，并按下鼠标左键往下拖至D26后松开，这时在D4到D26出现从1到23的序列填充。将E3单元格的字段名改为“名次1”并在F3单元格输入字段名“名次”。　　3、根据需要进行相关排序：将光标选定在表格内任一单元格内，点击“数据”菜单→“排序”→在“主要关键字”下拉框内选定“名次1”并选定“递增”单选钮→确定。在F4单元格内输入1后将光标选定在F5单元格后输入“=IF（E4=E5，F4，F4+1）”，按回车键，然后依照上述方法将F5的公式复制到F26。为了还原有各部门的相关位置，依照前面排序方法再进行一次排序，但“主要关键字”则必须改为按“序列”字段进行递增排序。可是这时却发现经排序后的前几个记录却以“#VALUE!”的形式出现，这是因为“名次”字段的公式计算结果经排序后产生错误的缘故，为此，必须在“序列”字段排序之前将“名次”字段的公式计算结果转为数值常量后再进行排序。操作如下：选定E4到E26单元格区域→单击工具栏上复制命令按钮→点击“编辑”菜单→选定“选择性粘贴”→在弹出的对话框内选定“数值”单选钮确定后即可。最后依次选定D列、E列→点击“格式”菜单→列→隐藏“列”便可得到我们所要的效果了。
【责任编辑：】 ■ 相关内容查看: 5135|回复: 6
如何利用PERCENTRANK函数进行排序？
积分学习力
魅力值 影响力
消费券 Ti币好友
在线时间 小时
最后登录月度优秀 次管理次数 次
网站名称: Excel技巧网 | Excel专家栖息谷 | 微软中文技术社区合作伙伴
署名作者: lrlxxqxa
版权声明: 版权归本站与作者共有 除本站官方外非作者本人转载须经许可并注明出处
适用版本: 03以前版本&
语言环境: 简体中文
学习方法: 掌握Excel技巧的关键是动手操作
下载 ≠ 知识
免费注册成为本站会员，享用更多功能，结识更多Office办公高手！
才可以下载或查看，没有帐号？
本帖最后由 lrlxxqxa 于
09:02 编辑
先看一下PERCENTRANK函数的基础信息；其返回特定数值在一个数据集中的百分比排位。此函数可用于查看特定数据在数据集中所处的位置。例如，可以使用函数 PERCENTRANK 计算某个特定的能力测试得分在所有的能力测试得分中的位置。
语法结构为：PERCENTRANK(array,x,significance)
Array&&为定义相对位置的数组或数字区域。
X&&为数组中需要得到其排位的值。
Significance&&为可选项，表示返回的百分数值的有效位数。如果省略，函数 PERCENTRANK 保留 3 位小数。
小注：如果数组为空，函数 PERCENTRANK 返回错误值 #NUM!。
& && &&&如果 significance & 1，函数 PERCENTRANK 返回错误值 #NUM!。
再来看看PERCENTRANK是如何工作的
p1.jpg (21.23 KB, 下载次数: 1)
如何利用PERCENTRANK函数进行排序？
00:02 上传
所谓X的“排位”，即Array小于X的个数占整个Array中非空元素个数和的百分比
明白了原理再来看如下的实例
p2.jpg (62.06 KB, 下载次数: 0)
如何利用PERCENTRANK函数进行排序？
00:02 上传
可见要用PERCENTRANK函数实现附件中的排序效果，要分6步走：
1、利用PERCENTRANK($B$28:$B$42,B28)这一部分得到的是X在Array中的排位；
2、利用COUNTA($B$28:$B$42)返回Array中非空单元格个数；
3、利用COUNTA($B$30:$B$44)*(1-PERCENTRANK($B$30:$B$44,B40))返回Array中大于或等于X的排位（元素个数）；
4、利用COUNTIF($B$30:$B$44,B38)返回Array中等于X的元素个数；
5、利用第3步结果与第4步结果的差，得到Array中大于X的元素个数（有误差）；
6、利用MAX或MIN函数，调整第5步返回结果中两端值的误差，得到正确结果。
结合舍入取整函数运用如下：=FLOOR(COUNTA($B$30:$B$44)*(1-PERCENTRANK($B$30:$B$44,B30)),1)+2-COUNTIF($B$30:$B$44,B30)-(MIN($B$30:$B$44)=B30)复制代码1=ROUNDDOWN(COUNTA($B$30:$B$44)*(1-PERCENTRANK($B$30:$B$44,B30)),)+2-COUNTIF($B$30:$B$44,B30)-(MIN($B$30:$B$44)=B30)复制代码2=ROUNDUP(COUNTA($B$30:$B$44)*(1-PERCENTRANK($B$30:$B$44,B30)),)-COUNTIF($B$30:$B$44,B30)+1+(MAX($B$30:$B$44)=B30)复制代码3=INT(COUNTA($B$30:$B$44)*(1-PERCENTRANK($B$30:$B$44,B30)))+2-COUNTIF($B$30:$B$44,B30)-(MIN($B$30:$B$44)=B30)复制代码4=TRUNC(COUNTA($B$30:$B$44)*(1-PERCENTRANK($B$30:$B$44,B30)),)+2-COUNTIF($B$30:$B$44,B30)-(MIN($B$30:$B$44)=B30)复制代码5=CEILING(COUNTA($B$30:$B$44)*(1-PERCENTRANK($B$30:$B$44,B30)),1)-COUNTIF($B$30:$B$44,B30)+1+(MAX($B$30:$B$44)=B30)复制代码6
00:02 上传
点击文件名下载附件
下载积分: 消费券 -5 Ti币
5.65 KB, 下载次数: 51, 下载积分: 消费券 -5 Ti币
如何利用PERCENTRANK函数进行排序？
学office，哪能不关注全网最大的Office类微博（新浪）
初级三, 积分 308, 距离下一级还需 192 积分
积分学习力
魅力值 影响力
消费券 Ti币好友
在线时间 小时
最后登录月度优秀 次
学习，太复杂！
学office，哪能不关注全网最大的Office类微博（新浪）
积分学习力
魅力值 影响力
消费券 Ti币好友
在线时间 小时
最后登录月度优秀 次管理次数 次
PERCENTRANK函数相对完整的用法说明。
学office，哪能不关注全网最大的Office类微博（新浪）
积分学习力
魅力值 影响力
消费券 Ti币好友
在线时间 小时
最后登录月度优秀 次管理次数 次
用PERCENTRANK函数进行排序
学office，哪能不关注全网最大的Office类微博（新浪）
联系方式：请发站内消息给站长 apolloh
工程硕士GCT考试的成绩，也采用“百分比”排位的方式。
学office，哪能不关注全网最大的Office类微博（新浪）
初级一, 积分 17, 距离下一级还需 33 积分
积分学习力
魅力值 影响力
消费券 Ti币好友
在线时间 小时
最后登录月度优秀 次
用PERCENTRANK来排序
学office，哪能不关注全网最大的Office类微博（新浪）
高级一, 积分 1711, 距离下一级还需 189 积分
积分学习力
魅力值 影响力
消费券 Ti币好友
在线时间 小时
最后登录月度优秀 次
这个怎么这么复杂？
学office，哪能不关注全网最大的Office类微博（新浪）
Excel技巧网的会员探讨问题仅代表其个人意见，与网站的立场无关。任何违反国家和地方相关法律法规的言论，本站有义务协助政府相关部门追究发言者的责任！
本站中非注明转载文章与案例的版权为作者与Excel技巧网共有。若非原文作者，本站之外任何单位或个人未经允许，不得将其用于商业用途。
若非原文作者，任何形式的非商业性转载必须获得Excel技巧网或作者允许，并注明作者和出处。
会员发表的帖子如涉及版权纠纷，须自行负责。详情请参考注册时的网站服务条款。
本站特聘法律顾问：沈学律师
Powered by

我要回帖

更多关于 rank函数 多区域 排序 的文章

 

随机推荐