Excel中RANK函数的改进和应用

陈炜东

摘  要： 在数据处理方面Excel表现了它的优势，它改变了传统的数据统计过程。但是Excel自带的函数已越来越不能满足人们日益增长的需求。RANK函数是Excel中常用的排位函数，一次只能对单一类别的数据进行排位，不能对多种类别的数据同时排位。研究了在RANK函数功能的基础上利用Excel VBA编程的方法重构新的排位函数，即FRANK函数，以解决RANK函数在实际应用中的不足，并以FRANK函数在班级成绩排名中的应用为例，进一步体现其改进的优势及可行性。

关键词： Excel; 数据处理; RANK; VBA; FRANK; 成绩排名

中图分类号：TP317.3          文献标志码：A     文章编号：1006-8228（2015）07-51-02

Improvement and application of RANK function in Excel

Chen Weidong

（Zhengda High School， Haimen， Jiangsu 226100， China）

Abstract： In the field of data processing， Microsoft Excel has demonstrated its advantages and changed the traditional statistical method， but the function of Excel is increasingly unable to meet the growing demand of people. RANK is a commonly used ranking function in Excel， can only rank a single category of data at a time. In order to solve the problem in the practical application， this paper proposes a new function named FRANK which is studied on the basis of RANK function and developed with Excel VBA， and taking the application of FRANK function in class performance ranking as an example， further demonstrates the improved advantage and the feasibility.

Key words： Excel; data analysis; RANK; VBA; FRANK; sort the scores

0 引言

数据处理软件Excel的出现改变了以往统计处理数据的方法，大大加快了数据处理的速度，提高了办公效率。人们对于Excel的使用，不仅仅是记录数据，主要还是对数据的分析处理。无论是各行各业各部门的办公人员，还是教师或学生都离不开办公应用软件的使用。教师用EXCEL对学生成绩进行管理更普遍，快捷，方便[1]。RANK函数是Excel自带的函数之一，它常用于数据的排位，但是RANK函数限制一次只能对相同类别的数据进行排位，不能对多种类别的数据同时排位，随着数据类别的增多，需要人工操作RANK函数的次数也就相对增多，降低了数据处理的效率。

1 现有的RANK函数

1.1 RANK函数的定义

在Microsoft Excel帮助文档中给出了RANK函数的定义：返回一个数字在数字列表中的排位[2]。数字的排位是其大小与列表中其他值的比值（如果列表已排序，则数字的排位就是它当前的位置）。其语法如表1所示。

表1  RANK函数的语法

[语法结构＼&RANK（number，ref，order）＼&参数定义＼&number＼&为需要找到排位的数字＼&ref＼&为数字列表数组或对数字列表的引用。ref 中的非数值型参数将被忽略＼&order＼&为一数字，指明排位的方式＼&]

·如果order为0（零）或省略，Microsoft Excel对数字的排位是基于ref为按照降序排列的列表。

·如果order不为零，Microsoft Excel 对数字的排位是基于ref为按照升序排列的列表。

1.2 RANK函数的示例

如果将示例复制到空白工作表中，可能更易于理解该示例。操作方法如下：

⑴ 创建空白工作簿或工作表;

⑵ 按要求填入数据。如图1;

⑶ 在任意其他空白单元格中填入排位函数，得出对应值的排位结果，解析如表2。

图1  填入的数据

表2  排位结果说明

1.3 RANK函数的不足之处

RANK函数可以实现排位的功能，得出每个数据在所处数据列表中的位置，但在学校的学生成绩数据处理中，要想快速高效，还可以做一些改进。

⑴ 不能实现多类别的同时排位

从上面的介绍以及操作结果可以看出，RANK函数识别的是用户所选择的区域，也就是参数ref所指向的范围，一次只能对所选区域的单一类别进行排位。在处理学生成绩数据时，有一项是各班学生在班级中的排名情况，利用RANK函数处理，一次只能对一个班级进行排位，班级的数量决定着要重复操作的次数。班级越多，操作RANK函数的次数也就越多，而且也更容易出错，大大降低了工作效率。

⑵ 对于空和0的判断不明显

如果ref数据区间中的数据至少有一项的数值为0，那么在进行排位的过程中，Excel就会自动将区间内为空的单元格作为0进行排位;如果ref数据区间中没有数值为0的单元格，但存在空单元格，那么系统就给出空单元格的排位结果为“#N/A”（值不可用）的提示。但是在学生成绩排名的实际应用中，考虑到这样一种情况，就是考试统计名单中同时存在没有考试的学生和参加考试但是成绩为0的学生，RANK函数不能明确区分。

1.4 本章小结

介绍RANK函数的定义、语法结构以及使用方法，结合示例进行分析，指出RANK函数的缺陷，RANK函数不能实现多类别的同时排位，而且对于空和0的判断也不能满足要求，需要进行改进，以提高实际数据处理的效率。

2 改进的FRANK函数

2.1 FRANK函数的定义

VBA（Visual Basic for Application）是微软开发的，在其桌面应用程序中执行通用的自动化（OLE）任务的编程语言[3]。Excel VBA提供的功能能够在应用程序使用的过程中执行，并且能够将使用VBA代码编写的通用程序和自定义函数制作成通用的加载项，在不同的场合重复使用，进一步提高了数据处理的效率[4]。

FRANK函数利用Excel VBA编程的方法对现有的RANK函数进行改进实现。基于FRANK函数的功能，给出了这样的定义：返回一个数字在数字列表相同类别数据中的排位。其语法结构如表3。

表3  FRANK函数的语法

[语法结构＼&FRANK （number， category， fref， valcol， catcol， order）＼&参数定义＼&number＼&为需要找到排位的数字＼&category＼&为当前数字对应所在的类别＼&fref＼&为数组或对数据区域的引用。fref数字列表中的非数值型参数将被忽略＼&valcol＼&为一数字，表示当前排位数字的数字列表位于fref中的列号＼&catcol＼&为一数字，表示当前排位数字的类别列表位于fref中的列号＼&order＼&为一数字，指明排位的方式＼&]

·如果order为0（零）或省略，Microsoft Excel对数字的排位是基于fref为按照降序排列的列表。

·如果order不为零，Microsoft Excel对数字的排位是基于fref为按照升序排列的列表。

2.2 FRANK函数的应用

通过Excel VBA编程实现FRANK的功能，并应用到如下实例中。操作方法如下：

⑴ 创建空白工作簿或工作表;

⑵ 按要求填入数据。如图2;

图2  填入的数据

⑶ 在排位所在的C列填入函数，首先在C2处填写=FRANK（B2，A2，$A$2：$B$9，2，1），然后将自动填充柄向下拉动到C9处，完成各个数据在当前类别中的降序排位。完成后如图3所示。

图3  排位结果

2.3 FRANK函数的优势

对图3排位结果分析可知，班级中有“1班”和“2班”两种类别，假如要用RANK函数进行排位，首先要将数据按照班级列进行排序，然后分别对“1班”和“2班”使用RANK函数得出排位结果。而利用新的FRANK函数则不需要排序，只要对一个数字进行排位后，利用自动填充柄即可完成所有数据排位，效率上提高了，尤其体现在多类别的情况下;而且我们可以发现对于同时出现0和空单元格的情况，FRANK函数也能区分，并以“#N/A”标注空单元格。由此可见，FRANK函数很好地弥补了RANK函数的缺陷，大大提高了数据处理的效率。

2.4 本章小结

针对RANK函数在实际应用中不足，对RANK函数进行改进，采用VBA编程实现FRANK函数的功能，介绍改进后的FRANK函数的定义和结构。同时结合实例应用的分析，体现FRANK函数的可行性和优势。

3 结束语

Excel内置了很多有用的函数，本文就RANK函数的定义、功能及应用进行了研究，分析其存在的一些缺陷，然后结合FRANK函数的应用技巧，并结合班级成绩排名的应用实例，说明FRANK函数能够区分同一类别中同时出现0和空格的情况，而且最主要的是能够同时对多种类别的数据进行排位，大大节省了时间，提高了工作效率。

Excel有更多功能值得应用，例如Excel在其VBA功能的支持下能够高效率地实现数据处理的自动化，将工作人员从简单而重复的数据处理工作中解脱出来，更能通过VBA编程对Excel进行二次开发，实现很多高级功能，提高办公效率[5]。

参考文献：

[1] 张敏.基于EXCEL函数的学生成绩分析与管理[J].农业网络信息，

2006.5：157-158

[2] Microsoft Excel 2003 帮助

[3] 伍云辉.Excel VBA办公应用开发详解[M].电子工业出版社，2008.

[4] Excel Home.Excel VBA实战技巧精粹[M].人民邮电出版社，2008.

[5] 李小遐.Excel VBA在办公自动化中的应用[J].电子测试，2014.22：

105-106，95