Excel排序问题

昨天看到一个excel的vlookup函数的问题，然后我就看了看，提了点我的建议和看法。然后我就和那位哥私聊了，发现是一个排名的问题。

问题：

这里的问题是因为多个数值相同，然后给的排名一致。哥的意思是让一样的数值，排名也不一致。

比较熟SQL的我，发现如果是这样，直接使用 row_number() over() 函数就OK了。

然后就想研究研究 Excel 里面的排序函数。

在 Excel 中，Rank函数用于返回指定数字在数字列表中的排位，应用此功能可实现把数字列表按升序或降序排序或排名次。在 Excel 新版本中，已用Rank.EQ函数和Rank.AVG函数取代了Rank函数。

Rank函数对于有重复数字的排名会把重复数字并排且会留出间隔（类似SQL里面的rank函数）；

如果要求重复数字后的数字紧跟重复数字排名，需要用 Sum + If + CountIf、SumProduct + CountIf 或 Sum + If + FreQuency 组合实现（类似SQL里面的dense_rank函数）；

如果要求重复数字后的排序不一致，可以结合 CountIf 函数进行实现（类似SQL里面的row_number函数）。

先看函数相关语法，再看实例操作：

Excel Rank函数的语法：

Rank函数的表达式：

RANK(Number, Ref, [Order])\\

参数说明：

• Number 就是我们想要排序的数据所在的单元格；
• Ref 为对包含数字列表的数组或单元格区域的引用；如果 Ref 中有非数字值将被忽略。
• Order 为可选项，用于指定数字的排位方式；如果 Order 省略或为 0，则Rank函数返回的排位是基于数字列表按降序排列；如果Order 不为0且不省略，则Rank函数返回的排位是基于数字列表按升序排列。

Excel Rank函数、Rank.EQ函数与Rank.AVG函数的区别：

Rank.EQ函数与Rank.AVG函数用于取代Rank函数，它们的区别如下：

• Rank.EQ 与 Rank 的功能完全一样，即 Rank.EQ 继承了 Rank 的所有功能，Rank.EQ 的表达式如下：

RANK.EQ(Number, Ref, [Order])\\

• Rank.AVG 与 Rank（Rank.EQ) 的区别主要表现在对相同数字的排位上；对相同数字，Rank.AVG 返回平均排位，Rank（Rank.EQ) 返回最佳排序，即并列排序。Rank.AVG 的表达式为：

RANK.AVG(Number, Ref, [Order])\\

实例：

1）无相同数据的数据排序：

这种情况直接使用rank函数即可。当我们不设置 order 参数时，结果如下（默认降序）

这里我们设置 order = 1，结果如下：

这里需要注意的是：记得要把 ref 设置的数据范围进行一个绝对引用。

下面不再强调 order 参数的设置，只是改变了排序的方式，下面我们着重学习如何进行根据需求进行排序。

2）有相同数据，排名并排处理（跳跃）：

这里语句还是和上面情况时的语句一致，但是要注意，这里实现的情况是跳跃排名，当第3名有多个存在时，那么后一名的排名就是3+n（n为第3名数据重复个数）。

3）有相同数据，排名并排处理（连续）：

这里就是实现SQL里面的dense_rank函数的功能，对于数据相同的，排名一致，后一名排名+1，而不是情况2）跳跃式的排名。

这里公式是：

SUMPRODUCT(($C$2:$C$6<C2)/COUNTIF($C$2:$C$6,$C$2:$C$6))+1。

4）有相同数据，排名不同处理：

这里使用的公式就是：

RANK(C2,$C$2:$C$6,1)+COUNTIF($C$2:C2,C2)-1

这里调用了 countif 函数，利用数据重复的条件，就比如上图，name为a、b、d所代表的行的数据都不是重复的，所以，他们的RANK(C2,$C$2:$C$6,1)+COUNTIF($C$2:C2,C2)-1就等价于RANK(C2,$C$2:$C$6,1)。

对于name为c和e的，就发生了变化，先看c，首先公式的前部分：RANK(C2,$C$2:$C$6,1)这里还是和以前一样，根据name为c的score数值在该列进行排序（这里是升序），所以RANK(C2,$C$2:$C$6,1)应该是3；然后看公式的后半部分COUNTIF($C$2:C2,C2)，因为该列到name为c的score数据行，该值出现了1次，所以COUNTIF($C$2:C2,C2)=1，结合起来整个公式就是 = 3 + 1 - 1 = 3。这个时候再看score一致的name为e的行，这里公式的前部分一样，RANK(C2,$C$2:$C$6,1) = 3，公式的后半部分发生了变化，因为该列到name为e的score数据行，该值出现了2次（name为c的score为8，name为e的score为8），所以COUNTIF($C$2:C2,C2)=2，结合一下就是 = 3 + 2 -1 = 4。（这就是给相同数据，不同名次的原理）

ps：文章公式大部分直接拿了 D2 单元格的公式就行描述，讨论其他单元格时，希望读者能够自行理解，不要认为其他单元格对应的公式不对，影响自己的快速理解。

参考文章：

https:// jingyan.baidu.com/artic le/d5c4b52bb5beb2da560dc5bc.html

Excel Rank函数怎么用的11个实例，含与Rank.EQ和Rank.AVG的区别、并顺自动排序和排名次及分段排序

SUMPRODUCT函数使用方法及示例_ExcelStudio-CSDN博客_sumproduct函数的使用方法及实例

Excel函数大全 | SUMPRODUCT函数：对输入数组对应相乘后再相加_哔哩哔哩_bilibili

使用SUMPRODUCT函数组内排序的方法_哔哩哔哩_bilibili