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

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

Rank函数对于有重复数字的排名会把重复数字并排且会留出间隔，如果要求重复数字后的数字紧跟重复数字排名，需要用 Sum + If + CountIf、SumProduct + CountIf 或 Sum + If + FreQuency 组合实现。以下是Rank函数怎么用的11个实例，包含Rank函数的基本用法，Rank 与 Rank.EQ、Rank.AVG 的区别，有重复数字的并排或顺排自动排名、分段排序、把数字列表排序放到行和中国式排名。

一、Excel Rank函数的语法

1、表达式：RANK(Number, Ref, [Order])

中文表达式：RANK(要排位的数字, 数字列表, [排序方式])

2、说明：

A、Ref 为对包含数字列表的数组或单元格区域的引用；如果 Ref 中有非数字值将被忽略。

B、Order 为可选项，用于指定数字的排位方式；如果 Order 省略或为 0，则Rank函数返回的排位是基于数字列表按降序排列；如果Order 为非 0，则Rank函数返回的排位是基于数字列表按升序排列。

C、如果数字列表中有相同的数字，它们如何排位见“Rank函数怎么用的实例”。

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

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

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

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

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

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

三、Excel Rank函数怎么用的实例（Rank.EQ函数怎么用的实例）

（一）无重复数字且数字列表按降序或升序排列的实例

1、假如返回销量为 2685 按降序和按升序的排位。双击 D8 单元格，把公式 =RANK(2685,D2:D7) 复制到 D8，按回车，返回 2；双击 D9，把同一公式复制到 D9，然后在 D2:D7 后输入“,1”，按回车，返回 5；操作过程步骤，如图1所示：

图1

2、说明：

A、公式 =RANK(2685,D2:D7) 中，2685 为要排位的数字，D2:D7 为数字列表，公式省略了排序方式 Order 参数，D2:D7 默认按降序排列；返回 2685 在 D2:D7 中的排位为 2，正是 D2:D7 按降序排列的结果。

B、公式 =RANK(2685,D2:D7,1) 中，把排序方式设置为 1，则 D2:D7 按升序排位，返回 2685 在 D2:D7 中的排位为 5 正是 D2:D7 按升序排列的结果。

（二）无重复数字的自动排序实例

1、假如要把销量按降序排位。双击 E2 单元格，把公式 =RANK(D2,D$2:D$7) 复制到 E2，按回车，返回 2；选中 E2，把鼠标移到 E2 右下角的单元格填充柄上，鼠标变为加号后，双击左键，则返回剩余数值的排位，销量列也按降序排列好了；操作过程步骤，如图2所示：

图2

2、公式 =RANK(D2,D$2:D$7) 说明：

A、D2 是对列和行的相对引用，往下拖时，D2 会变为 D3、D4 等；D$2:D$7 中，$ 表示对行的绝对引用，往下拖时，D$2 不会变为 D3、D4 等，D$7 与 D$2 一样。

B、当公式在 D2 时，返回 D2 在 D2:D7 中的排位 2；当公式在 D3 时，返回 D3 在 D2:D7 中的排位 1；其它的以此类推。

（三）重复数字并排的自动排名次实例

1、假如要把价格按升序排名次。双击 E2 单元格，把公式 =RANK(C2,C$2:C$7,1) 复制到 E2，按回车，返回 4；选中 E2，用双击单元格填充柄的方法返回剩余数值的排位，价格列也随之排好了名次；操作过程步骤，如图3所示：

图3

2、公式 =RANK(C2,C$2:C$7,1) 说明：

A、公式 =RANK(C2,C$2:C$7,1) 与上例的公式 =RANK(D2,D$2:D$7) 是一个意思，只是这里把排序方式设置为 1，即 C2:C7 按升序排列。

B、C2:C7 中有一个重复数字 5，两个 5 都排在第 4 位，说明 Rank函数把重复数字并排。

（四）重复数字顺排的自动排名次实例

1、同样以把价格按升序排名次为例。双击 E2 单元格，把公式 =RANK(C2,C$2:C$7,1)+COUNTIF(C$2:C2,C2)-1 复制到 E2，按回车，返回4；选中 E2，同样用双击单元格填充柄的方法返回剩余数值的排位，价格列也随之按升序顺排好了名次；操作过程步骤，如图4所示：

图4

2、公式 =RANK(C2,C$2:C$7,1)+COUNTIF(C$2:C2,C2)-1 说明：

A、C$2:C2 用于返回 C2 至 C2 的数值，往下拖时，C2 会变 C3、C4 等，而 C$2 不会变，也就实现了从 C2 单元格到拖到单元格数字个数的统计。那么CountIf函数是怎么实现把重复数字顺排的？

B、当公式在 E2 时，COUNTIF(C$2:C2,C2) 在 C2 统计 C2（即 5）的个数，结果为 1；RANK(C2,C$2:C$7,1) 返回 C2 在 C2:C7 中的排位为 4，4 + 1 - 1 恰好等于 4。

C、当公式在 E3 时，COUNTIF(C$2:C3,C3) 在 C2:C3 统计 C3（即 3）的个数，结果也为 1；RANK(C3,C$2:C$7,1) 返回 C3 在 C2:C7 中的排位为 2，2 + 1 - 1 恰好等于 2。

D、当公式在 E4 时，COUNTIF(C$2:C4,C4) 在 C2:C4 统计 C4 的个数，结果为 2（C2:C4 有两个 5），而 RANK(C4,C$2:C$7,1) 返回 C4 在 C2:C7 中的排位为 4，4 + 2 - 1 恰好等于 5；这样就实现了重复数字 5 的顺排（一个排 4，另一个排 5）。

四、Excel Rank函数（Rank.EQ函数）与Rank.AVG函数的区别举例

（一）返回重复数字的排位

1、假如用三个函数分别返回 5 在“价格”中的排位。双击 E2 单元格，把公式 =RANK(5,C2:C7) 复制到 E2，按回车，返回 2；双击 F2，把公式 =RANK.EQ(5,C2:C7) 复制到 F2，按回车，也返回 2；双击 G2，把公式 =RANK.AVG(5,C2:C7) 复制到 G2，按回车，返回 2.5；操作过程步骤，如图5所示：

图5

2、公式说明：

从演示可知，Rank函数与Rank.EQ函数返回的结果一样，而Rank.AVG函数返回 2.5，这个 2.5 是怎么来的？C2:C7 中共有两个 5，按降序排列，一个 5 排在第 2 位，另一个 5 排在第 3 位，Rank.AVG 返回相同数值的平均排位，2 与 3 的平均值为 2.5，因此 5 的排位为 2.5。

（二）重复数字自动排序

1、假如用三个函数分别把“价格”按降序排位。双击 E2 单元格，把公式 =RANK(C2,C$2:C$7) 复制到 E2，按回车，返回 2；用双击单元格填充柄的方法返回剩余价格的排位。双击 F2，把公式 =RANK.EQ(C2,C$2:C$7) 复制到 F2，按回车，返回 2，用同样方法返回剩余价格的排位；再把公式 =RANK.AVG(C2,C$2:C$7) 复制到 G2，按回车，返回 2.5，用同样方法返回剩余价格的排位；操作过程步骤，如图6所示：

图6

2、公式说明：

从演示可知，Rank函数与Rank.EQ函数的排序结果完全一致，而Rank.AVG函数对于重复数字取平均值排位，两个 5 都排 2.5。

五、Excel Rank函数的扩展应用实例

（一）Rank + OffSet + Row 组合实现分段排序

1、假如要求把销量按季度分段排序。双击 C2 单元格，把公式 =RANK(B2,OFFSET(B$2:B$4,INT((ROW()-2)/3)*3,),1) 复制到 C2，按回车，返回 2；选中 C2，用双击单元格填充柄的方法返回其它数字的排位；操作过程步骤，如图7所示：

图7

2、公式 =RANK(B2,OFFSET(B$2:B$4,INT((ROW()-2)/3)*3,),1) 说明：

A、公式中的 3 表示每段 3 个数字；ROW() 用于返回公式所在行的行号；Int函数用于取整；INT((ROW()-2)/3)*3,) 用于计算 OffSet 函数要往下移的行数。

B、B$2:B$4 为第一段；OFFSET(B$2:B$4,INT((ROW()-2)/3)*3,) 用于移到一下段，它是怎么实现移到下一段的？

第一段（B$2:B$4）：

当公式在 C2 时，ROW() 返回 2，INT((ROW()-2)/3)*3 变为 INT((2-2)/3)*3，结果为 0；则 OFFSET(B$2:B$4,INT((ROW()-2)/3)*3,)变为 OFFSET(B$2:B$4,0,)，意思是把 B$2:B$4 都下移 0 行，即不下移，则 OFFSET(B$2:B$4,0,) 返回 B$2:B$4；则公式变为 =RANK(B2,B$2:B$4)，最后在 B2 到 B4 中返回 B2 的排位 2。

当公式在 C3 时，ROW() 返回 3，(ROW()-2)/3 变为 INT(1/3)，接着取整，结果为 0，0 * 3 结果也是 0；OffSet 变为 OFFSET(B$2:B$4,0,)；则公式变为 =RANK(B3,B$2:B$4)，最后返回 B3 在 B2 至 B4 的排位 3。

当公式在 C4 时，ROW() 返回 4，(ROW()-2)/3 变为 INT(2/3)，接着取整，结果为 0，0 * 3 结果也是 0；OffSet 变为 OFFSET(B$2:B$4,0,)；则公式变为 =RANK(B4,B$2:B$4)，最后返回 B4 在 B2 至 B4 的排位 1。

第二段（B$5:B$7）：

当公式在 C5 时，ROW() 返回 5，(ROW()-2)/3 变为 INT(3/3)，接着取整，结果为 1，1 * 3 结果也是 3；OffSet 变为 OFFSET(B$2:B$4,3,)，意思是把 B$2:B$4 都下移三行，则 B$2:B$4 变为 B$5:B$7，也就移到了第二段；则公式变为 =RANK(B5,B$5:B$7)，最后返回 B5 在B$5:B$7 中的排位 1；其它的以此类推。

OFFSET(B$2:B$4,3,) 把 B$2:B$4 都下移三行的执行过程：第一次从 B2:B4 中取出 B2，然后返回 B2 下距 B2 3 行的引用，即返回 B5；第二次从 B2:B4 中取出 B3，然后返回 B3 下距 B3 3 行的引用，即返回 B6；第三次也是这样返回对 B7 的引用，从而实现把 B$2:B$4 都下移三行。

（二）Rank + OffSet + Column 组合实现把数字列表排序放到行

1、假如要把 A 列的数字排位放到行。双击 C2 单元格，把公式 =RANK(OFFSET($A1,COLUMN()-3,),$A1:$A6) 复制到 C2，按回车，返回 6；选中 C2，把鼠标移到 C2 右下角的单元格填充柄上，鼠标变为加号后，按住左键并往右拖，一直拖到排完所有数字；操作过程步骤，如图8所示：

图8

2、公式 =RANK(OFFSET($A1,COLUMN()-3,),$A1:$A6) 说明：

A、$A1 表示对列的绝对引用，当往右拖时，A1 不会变为 B1、C1 等；COLUMN() 用于返回公式所有列的列号。

B、OFFSET($A1,COLUMN()-3,) 用于返回 A1 至 A6 中的数值，具体如下：

当公式在 C2 时，COLUMN() 返回 3，则 OffSet 变为 OFFSET($A1,3-3,)，意思是返回距 A1 0 行的引用，即返回对 A1 的引用，也就是返回 6；则公式变为 =RANK(6,$A1:$A6)，接着用Rank函数返回 6 在 A1:A6 中的排位 6。

当公式在 D2 时，COLUMN() 返回 4，则 OffSet 变为 OFFSET($A1,4-3,)，意思是返回距 A1 1 行的引用，即返回对 A2 的引用，也就是返回 24；则公式变为 =RANK(24,$A1:$A6)，接着用Rank函数返回 24 在 A1:A6 中的排位 4；其它的以此类推。

六、Excel Rank函数无法实现中国式排名的三种解决方法

如果要求重复数字并排，重复数字下一个数字接着重复数字排位而不是隔一位排，例如 5、3、3、2，若按降序排名次，要求两个 3 都排第 2 名，而 2 排第 3 名；这种排名方式通常称为中国式排名，用Rank函数无法实现，但用其它函数组合能实现，具体如下：

（一）Sum + If + CountIf

1、同样以把“价格”列降序与升序自动排名为例。双击 E2 单元格，把公式 =SUM(IF(C$2:C$7>=C2,1/COUNTIF(C$2:C$7,C$2:C$7),"")) 复制到 E2，按 Ctrl + Shift + 回车，返回 2；选中 E2，用双击 E2 右下角的单元格填充柄的方法返回剩余数字的排名；再双击 F2，把公式=SUM(IF(C$2:C$7<=C2,1/COUNTIF(C$2:C$7,C$2:C$7),"")) 复制到 F2，按 Ctrl + Shift + 回车，返回 4，同样用双击单元格填充柄的方法返回剩余排名；操作过程步骤，如图9所示：

图9

2、降序排名公式 =SUM(IF(C$2:C$7>=C2,1/COUNTIF(C$2:C$7,C$2:C$7),"")) 说明：

A、中国式排名的关键有两点，一点是找出重复数字，另一点是把重复数字后的排名上升一位。

B、C$2:C$7 以数组形式返回 C2 至 C7 中的值，$ 是对行的绝对引用，作用是往下拖时，C2 不会变 C3、C4 等，C7 不会变 C8、C9 等，以确保始终取 C2:C7 的数字。

C、1/COUNTIF(C$2:C$7,C$2:C$7) 用于找出重复数字；第一次执行时，从 C2:C7 中取出 C2（即 5），然后统计 C2 在 C2:C7 中的个数，结果为 2；第二次执行时，从 C2:C7 中取 C3（即 3），然后统计 C3 在 C2:C7 中的个数，结果为 1；其它的以此类推，最后返回 {2;1;2;1;1;1}。

D、则 1/COUNTIF(C$2:C$7,C$2:C$7) 变为 1/{2;1;2;1;1;1}，进一步计算，用 1 除以数组中的每个元素，结果为 {0.5;1;0.5;1;1;1}，这样就把重复的数字（两个 5）变为了一个，后面的步骤会把两个 0.5 相加，这其实就是过滤重复数字，在前面的篇章已经介绍过。

E、C$2:C$7>=C2 为 If 条件，用于找出 C2:C7 中所有大于等于 C2 的数值；执行时，第一次取出 C2（即 5），C2 >= C2 成立，返回 True；第二次取出 C3（即 3），C3 >= C2 不成立，也返回 False；其它的以此类推，最后返回 {TRUE;FALSE;TRUE;TRUE;FALSE;FALSE}。

F、则公式变为 =SUM(IF({TRUE;FALSE;TRUE;TRUE;FALSE;FALSE},{0.5;1;0.5;1;1;1},""))，执行时，第一次从条件数组中取出第一个元素True，由于它为真，所以返回第二个数组中与条件数组对应的元素，即返回第二个数组第一个元素 0.5；第二次从条件数组取出第二个元素 FALSE，由于它为假，所以返回 If 的第三个参数 ""；其它以此类推，最后返回 {0.5;"";0.5;1;"";""}。

G、则公式变为 =SUM({0.5;"";0.5;1;"";""})，最后用 Sum 把数组中的每个元素相加，由于忽略空值只把数字相加，所以结果为 2；两个0.5 是重复数字 C2 与 C4 中 5 的值，1 是大于 5 的值（即 C5 中的 12 ），把它们相加恰好是重复数字 5 的排名。

H、当公式在 C5 时，公式变为 =SUM(IF(C$2:C$7>=C5,1/COUNTIF(C$2:C$7,C$2:C$7),""))，1/COUNTIF(C$2:C$7,C$2:C$7) 同样返回{0.5;1;0.5;1;1;1}；C$2:C$7>=C5 返回 {FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}，即只有 C5（即 12）的位置为真；则公式变为：=SUM(IF

(FALSE;FALSE;FALSE;TRUE;FALSE;FALSE},{0.5;1;0.5;1;1;1},""))，进一步计算变为 =SUM({"";"";"";1;"";""})，数组中只有 C5 的位置为1，其它的都为 ""，最后返回 1，也就是最大值 C5 的排名。

另外，用这个公式 =SUM(IF(C$2:C$7<=C2,"",1/COUNTIF(C$2:C$7,C$2:C$7)))+1 也可以实现降序排名，即把 >= 改为 <=，同时 If 的后面两个参数要调换次序，并且还要在公式后加 1。

3、升序排名公式 =SUM(IF(C$2:C$7<=C2,1/COUNTIF(C$2:C$7,C$2:C$7),"")) 说明：

升序排名公式与降序排名公式的区别仅是 If 条件中的 >= 变为 <=，即由找出所有大于等于当前单元格（C2）的数字变为找出所有小于等于当前单元格的数字。

（二）SumProduct + CountIf

1、同样以把“价格”列降序与升序自动排名为例。双击 E2 单元格，把公式 =SUMPRODUCT((C$2:C$7>=C2)/COUNTIF(C$2:C$7,C$2:C$7)) 复制到 E2，按回车，返回 2；选中 E2，用双击单元格填充柄的方法返回剩余数字的排名；再双击 F2，把公式 =SUMPRODUCT((C$2:C$7<=C2)/COUNTIF(C$2:C$7,C$2:C$7)) 复制到 F2，按回车，返回 4，再用双击单元格填充柄的方法返回剩余排名；操作过程步骤，如图10所示：

图10

2、降序排名公式 =SUMPRODUCT((C$2:C$7>=C2)/COUNTIF(C$2:C$7,C$2:C$7)) 说明：

A、公式与 Sum + If + CountIf 是一个意思，只是这里用 SumProduct 代替了 Sum + If。

B、COUNTIF(C$2:C$7,C$2:C$7) 返回 {2;1;2;1;1;1}；(C$2:C$7>=C2) 返回 {TRUE;FALSE;TRUE;TRUE;FALSE;FALSE}。

C、则公式变为 =SUMPRODUCT({TRUE;FALSE;TRUE;TRUE;FALSE;FALSE}/{2;1;2;1;1;1})，进一步计算，第一次分别取出分子与分母数组的第一个元素 TRUE 和 2，计算时，TRUE 被转为 1、FALSE 被转为 0，1/2 = 0.5；第二次再分别取出它们的第二元素 FALSE 和 1，0/2 = 0；其它的以此类推。

D、则公式变为 =SUMPRODUCT({0.5;0;0.5;1;0;0})，最后对数组中的元素求和，结果为 2，即重复数字 5 的排名为 2。

另外，用这个公式 =SUMPRODUCT((C$2:C$7>C2)*(1/COUNTIF(C$2:C$7,C$2:C$7)))+1 也可以实现降序排名，也就是把条件 C$2:C$7>C2 提取出作为一个独立的条件，再用 1 比 COUNTIF(C$2:C$7,C$2:C$7) 作为第二个条件，并用 * 把两个条件连接起来表示“与”的关系，最后还要在公式后面加 1。

3、升序排名公式 =SUMPRODUCT((C$2:C$7<=C2)/COUNTIF(C$2:C$7,C$2:C$7)) 说明：

升序排名公式与降序排名公式的区别仅是把分子 C$2:C$7<=C2 中的 >= 改为 <=，即由找出所有大于等于当前单元格（C2）的数字变为找出所有小于等于当前单元格的数字。

（三）Sum +If + FreQuency

1、同样以把“价格”列降序与升序自动排名为例。双击 E2 单元格，把公式 =SUM(--(IF(FREQUENCY(C$2:C$7,C$2:C$7),C$2:C$7>C2)))+1 复制到 E2，按 Ctrl + Shift + 回车，返回 2；选中 E2，双击 E2 的单元格填充柄返回剩余数字的排名；再双击 F2，把公式 =SUM(--(IF(FREQUENCY(C$2:C$7,C$2:C$7),C$2:C$7<=C2))) 复制到 F2，按回车，返回 4，再双击 F2 的单元格填充柄返回剩余排名；操作过程步骤，如图11所示：

图11

2、降序排名公式 =SUM(--(IF(FREQUENCY(C$2:C$7,C$2:C$7),C$2:C$7>C2)))+1 说明：

A、FREQUENCY(C$2:C$7,C$2:C$7) 用于找出 C2:C7 中每个数字在 C2:C7 出现的频率，功能有点像 COUNTIF(C$2:C$7,C$2:C$7)，但FreQuency 对重复数字只统计一次，而 CountIf 统计两次。

执行时，第一次先从第二个参数中取 C2（即 5），然后在 C2:C7 中统计小于等于 5 且大于 3.5 的数值个数（结果为 2），因为直接把C2:C7 作为 Frequency函数的 Bins_Array 参数（即对要统计的数值划分区间参数），而比 5 小的值是 3.5，因此 3.5 与 5 为一个区间；第二次先从第二个参数中取 C3（即 3），同样在 C2:C7 中统计小于等于 3 且大于 2.5 的数值个数（结果为 1）；第三次取出 5，由于 5 已经统计过不再统计，因此结果为 0；其它的以此类推，最后返回数组 {2;1;0;1;1;1;0}。

C2:C7 中只有 6 个数字，返回的数组为什么会有 7 个元素，因为FreQuency函数返回的数组会比 Bins_Array 参数多一个元素。

B、C$2:C$7>C2 返回 {FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}。

C、IF(FREQUENCY(C$2:C$7,C$2:C$7),C$2:C$7>C2) 变为 IF({2;1;0;1;1;1;0},{FALSE;FALSE;FALSE;TRUE;FALSE;FALSE})；执行时，第一次从条件数组取第一个元素 2，由于大于 0 的视为真、小于 0 的视为假，所以返回 If 的第二个参数，即第二个数组中与 2 对应的元素 FALSE；第二次从条件数组取第二个元素 1，由于为真，返回第二个数组中第二个元素 FALSE；第三次从条件数组取第三个元素 0，由于为假，所以返回 If 的第三个参数，由于 If 省略了第三个参数，默认返回 FALSE；其它的以此类推，最后返回{FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}。

D、则公式变为 =SUM(--{FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE})+1，进一步计算，把数组的所有元素转为数值，则公式变为 =SUM({0;0;0;1;0;0;0})+1，-- 用于把文本或逻辑值转为数值，相当于Value函数，最后用 Sum 把数组中的元素加起来再加 1，结果为 2。

3、升序排名公式 =SUM(--(IF(FREQUENCY(C$2:C$7,C$2:C$7),C$2:C$7<=C2))) 说明：

升序排名公式与降序排名公式的区别仅为把 C$2:C$7>C2 中的 > 改为 <=，即由找出所有大于当前单元格（C2）的数字变为找出所有小于等于当前单元格的数字，并且把公式后面的 1 去掉。