EXCEL中RANK.EQ函数引用范围扩展及去重复排名

杨青

摘 要： EXCEL改变了传统的数据统计过程，运用公式和函数对数据进行复杂的运算。以学生成绩排名为例，介绍RANK.EQ函数的定义，分析该函数的功能原理，详细介绍函数中三个参数的功能，加深对RANK.EQ函数的理解和掌握。利用RANK.EQ函数实现单个工作表中的成绩排名，对第三个参数设置不同的值，可以得到求优排名和求差排名；扩展第二个参数范围引用实现跨表总成绩排名；结合COUNTIF函数实现成绩去重复排名。

关键词： 排名； RANK.EQ； 跨表； COUNTIF； 去重复

中图分类号：TP317.3 文献标志码：A 文章编号：1006-8228（2018）09-45-03

Abstract： Excel changed the traditional data statistics process， and used formulas and functions to perform complex operations on data. This paper introduces the definition of RANK.EQ function， analyzes its function principle， introduces the functions of its three parameters in detail， and deepens the understanding and mastery of the RANK.EQ function. Using the RANK.EQ function， can achieve the ranking in a single worksheet， setting the third parameters with different values， can get the best ranking and the poor ranking， extending the second parameter range， can achieve the total score ranking of the cross tables， and combining with COUNTIF function can solve the score duplicate ranking.

Key words： ranking； RANK.EQ； cross table； COUNTIF； solution of duplicate

0 引言

在成绩统计过程中，经常会对成绩进行排名次。EXCEL对成绩的排名可以由排序功能和RANK.EQ函数来实现。

排序是根据某些字段的升序和降序对数据进行排序，数据会按指定的顺序重新排列，不会产生新的数据列，但会改变电子表格中数据原来的排列顺序。为保证原有数据顺序不变的情况下，使用RANK.EQ函数对数据进行排名，新增一列字段名，根据相应的字段进行排名，将排名的结果显示在新增的字段中。

1 RANK.EQ函数简介

1.1 RANK.EQ函数的定义

RANK.EQ函数是属于RANK函数的分支函数，返回某数字在一列数字中相对于其他数字的大小排名；如果多个数字排名相同，则返回该组数字最佳排名。函数语法及参数说明如表1所示。

1.2 RANK.EQ函数的工作原理

RANK.EQ属于统计类型的函数，主要用于数字排名，是指数字列表中每个数字依次与该列表中其他数字进行比较，得出每个数字在该列表中的最终排名。

1.2.1 单个工作表排名公式分析

现运用RANK.EQ函数对单张表中的成绩从高分到低分进行排名，得到的排名如图1所示。C2单元格中的公式“=RANK.EQ（B2，$B$2：$B$5，0）”表示A1学生的成绩在整个班级中的排名为3。公式中第一个参数B2表示成绩列中92所在的单元格地址；第二个参数$B$2：$B$5表示查找排名所在的絕对地址引用，即排名的范围始终在92，95，99，90四个数字之间；第三个参数0（可以省略不写）表示按照成绩降序排名，即排名是按照成绩列中四个数字从高到低进行排名。其余学生的排名可以拖动填充柄进行数据填充。

图2中D2单元格中的公式“=RANK.EQ（B2，$B$2：$B$5，1）”表示A1学生的成绩在整个班级排名中为倒数第2。D2和C2单元格中的公式只有第三个参数不同，公式中第三个参数1（可以是0以外的其他任何数字）表示按照成绩升序排名，即排名是按照成绩列中四个数字由低到高进行排名。

需要注意的是本例中RANK.EQ函数的第二个参数“$B$2：$B$5”应使用绝对值引用，表示需要排名的数字列表始终在一个区域内，任何一个数的排名都应该在这个区域中进行，否则排名将出现混乱。

1.2.2 排名过程分解

图 1中按分数从高到低排名，A1同学的成绩92分别与成绩列中其他数字比较，其中92小于95，99，即表示有两位同学的成绩大于92，故92排名第3；A2同学的成绩95分别与成绩列中其他数字比较，其中95只小于99，即表示仅有一位同学的成绩大于95，故95排名第2；A3同学的成绩99分别与成绩列中其他数字比较，99大于其他三个数92、95、90，即表示没有同学的成绩大于99，故99排名第一；A4同学的成绩90分别与成绩列中其他数字比较，其中90小于其他三个数92，95，99，即表示有三位同学的成绩大于90，故90排名第4，整个排名过程如图3所示。

2 RANK.EQ函数扩展引用范围

整个年级的成绩分布在多张工作表中，对每个班级的成绩排名在单张工作表中进行，若对整个年级的成绩排名则需要跨越多张工作表进行。运用RANK.EQ函数不仅可以实现班级排名还能实现多个班级的跨表总排名。

对于班级排名，引用范围在一张工作表内，这是常见的范围引用。如图4和图5所示，1班和2班的班级排名都显示在C列。1班A1学生的班级排名可通过C2单元格中的公式“=RANK.EQ（B2，$B$2：$B$5，0）”实现；2班B1学生的班级排名可通过C2单元格中的公式“=RANK.EQ（B2，$B$2：$B$4，0）”实现。其余学生的班级排名可以拖动填充柄进行数据填充。

两个班级的总排名，引用范围涉及两张工作表，因此需要扩展引用范围，实现跨表排名。如图4和图5所示，1班和2班的班级总排名都显示在D列。1班A1学生的年级总排名公式与2班B1学生的年级总排名公式均为“=RANK.EQ（B2，'1班：2班'！$B$2：$B$5，0）”。公式中第二个参数范围引用为“1班：2班'！$B$2：$B$5”，涉及“1班”和“2班”两张表中的“$B$2：$B$5”数据列，表示1班和2班的引用范围都是“$B$2：$B$5”区域，即每一个学生依次和两个班级中所有学生的成绩比较，最后得出该生在年级中的总排名。

实际应用中，每个班级的学生数不相同，要引用各个班级成绩工作表中记录最多的范围。在本例中

1班工作表共有4条记录，存放在“$B$2：$B$5”区域中；2班工作表共有3条记录，存放在“$B$2：$B$4”区域中，数据引用的范围以最大记录数1班的4条为基数，也就是“$B$2：$B$5”单元格地址。

整个公式的编辑过程：首先，选择名称是“1班”的工作表，点击A1学生所在的“总排名”单元格D2，然后在编辑栏输入“=RANK.EQ（B2，'1班：2班'！$B$2：$B$5，0）”，接着拖动填充柄进行数据填充；其次，选择名称是“2班”的工作表，点击B1学生所在的“总排名”单元格D2，然后在编辑栏输入“=RANK.EQ（B2，'1班：2班'！$B$2：$B$5，0）”，接着拖动填充柄进行数据填充，跨表完成成绩总排名。

有关‘1班：2班的输入技巧：可以按住Shift键，用鼠标依次单击“1班”和“2班”工作表，其中“'”和“：”在英文状态下输入。

3 去重复排名

在运用Rank.EQ函数排名的过程中，如果出现相同数字，则显示为同一排名。

图6中，A1和A2两位学生出现相同的成绩，排名中出现两个第二名，跳过第三名直接到第四名，重复成绩的存在影响后续数值的排位。

RANK.EQ函数与COUNTIF函数组合应用可以解决重复排名问题。COUNTIF函数是Excel中对指定区域中符合指定条件的单元格计数的一个函数。图 7中 D2单元格中的公式为“=COUNTIF（$B$2：B2，B2）”，$B$2：B2是一个动态区域，拖动填充柄进行数据填充，可以判定当前成绩第几次出现。D3单元格中的2就表示A2同学的95分是第二次出现。

在E2单元格中输入公式“=RANK.EQ（B2，$B$2：$B$5，0）+COUNTIF（$B$2：B2，B2）-1”，即E2=C2+D2-1=2+1-1=2。通过公式复制，E3单元格中公式为“=RANK.EQ（B3，$B$2：$B$5，0）+COUNTIF（$B$2：B3，B3）-1”，即E3=C3+D3-1=2+2-1=3，从图 7中C3和E3列的排名可以得出A2同学95分由于是第二次出现，未去重复前排名与A1同学并列第二名，去重复排名后排名變为三。去重复排名可以保证排名数与总人数一致。

4 总结

EXCEL函数功能强大，结合学生成绩表分析RANK.EQ函数功能及原理；通过扩展引用范围实现跨表年级总排名；结合COUNTIF函数，实现学生成绩排名去重复。RANK.EQ函数实现排名后，更改成绩表中任一学生的成绩，排名将会自动更新，实现动态排名。掌握函数的应用，大大节省了时间，提高了工作效率。

5 结束语

EXCEL是微软OFFICE组件中的一员，其内置函数库非常丰富，但根据需求差异，未必能全部满足，因此需要通过函数之间的组合使用实现某些功能。深度掌握EXCEL能轻松解决日常生活和工作中的实际问题，大大提高工作效率，全面解放双手，真正实现办公自动化。

参考文献（References）：

[1] 陈炜东.Excel中RANK函数的改进和应用[J].计算机时代，2015.7：51-52，56

[2] 王俊京，赵冉.如何使用Excel中的RANK函数为数据排名次[J].技术与市场，2011.18（7）.

[3] 加力戈.Excel中对数据进行分组中国式排名方法分析[J].经营管理者，2013.8X：P367

[4] 互联网+计算机教育研究院.2016WORD EXCEL PPT商务办公（第1版）[J].人民邮电出版社，2017.6.

[5] 侯冬梅.计算机应用基础（第二版）[J].中国铁道出版社，2014.