巧用EXCEL函数和公式排名

在Excel表格中排名次是一种非常广泛的应用。由于系统中的排序功能会改变原有数据的秩序，因此在平时的使用中不是很方便。如果要保证原始数据不变的情况下进行排名，那么就需要增加辅助列，用函数和公式进行排名。

常用的排名函数是RANK。下面就用这个函数对下表中的学生成绩进行排名。

1、无重复数据的排名

无重复数据的排名比较简单，直接用RANK函数就可以了。公式如下：

=RANK(H4,$H$4:$H$17,0)

2、含有重复数据的排名

如果有相同的总分，用rank函数排名时得到的排名是并列的，同时余下的排名将会跳过这些重复的排名。如下图所示有2个588分，它们的名次都是第1名，582直接变成了第3名，没有第2名。而在我们的习惯中，无论有几个并列，都应该再按顺序排名，即并列排名只占一名次。因此对重复数据的排名不能只用RANK函数，需要综合运用函数和公式。现举例如下：

1) RANK函数和COUNTIF函数组合

这个组合的思路是用RANK先排名，然后利用COUNTIF函数统计重复个数。组合成公式如下：

=RANK(H4,$H$4:$H$17,0)+COUNTIF($H$4:H4,H4)-1

2) 制造唯一值

这个方法是把需要排序的数据想办法用辅助列转换成唯一值，然后再对列唯一值排序。最简单的办法就是用部分加上所在的行号组合成一个新的具有唯一性的数值。

先用公式组合成新数据：=VALUE(H4&TEXT(ROW(),"00000"))。如下图：

再对组合后的数据直接用RANK排名如下：

=RANK(K4,$K$4:$K$17,0)

3) 名次并列且连续排名

这个方法是把需要排序的数据想办法用辅助列转换成唯一值，然后再对列唯一值排序。先用公式提取总分里的具有唯一性的数值：=IF(COUNTIF($H$4:H4,H4)=1,H4,"")。如下图：

再对组合后的数据直接用RANK排名如下。此处需要注意的是要用“总分”一列的值在“筛选唯一值”的区域里排序，否则要出错：

=RANK(H4,$O$4:$O$17)

3、多条件排名

在实际工作中，往往需要多条件排名。如先按总分，然后再按语文、数学、英语等的顺序排名。这时候也只需要把需要排序的按要求的顺序组合在一起，然后再对组合后的数据排名即可。

先在辅助列中用以下公式组合成新的数值：

=VALUE(H4&TEXT(B4,"000")&TEXT(C4,"000")&TEXT(D4,"000"))

再对组合后的数据直接用RANK排名如下：

=RANK(M4,$M$4:$M$17,0)

其他的排序均可用以上方法解决。