如何计算不存在的数值在 Excel 序列中的排名？

有网友提了一个有意思的问题：对于在序列中不存在的数值，如何统计其排名？

比如，序列是 1、3、5，问你 2 在其中排第几？怎么计算？

案例：

计算下图 1 中 D 列的业绩在 B 列中从大到小的排名，要求计算以下两种情形：

只统计每一行业绩在 B 列中的排名，不把整个 D 列也一起放入排名序列；

将 B 和 D 列都纳入排序列，统计每一行业绩在两个列中的排名

效果如下图 2 所示。

解决方案：

通常计算排名可以用 rank 函数。但是 D 列中的部分数据不在被查询列 B 列中，所以用这个函数有可能查不出结果。

先试一下。

1. 在 E2 单元格内输入以下公式：

=RANK(D2,$B$2:$B$10)

结果为错误值，这是因为 11 在 B 列的序列中不存在。

2. 向下拖动公式。业绩为 5 的可以计算出排名，证明公式本身没错，只是在本案例中不适用。

下面是正解。

1. 在 E2 单元格中输入以下公式 --> 下拉复制公式：

=SUMPRODUCT((D2<$B$2:$B$10)*1)+1

公式释义：

SUMPRODUCT((D2<$B$2:$B$10)*1)：判断是否 D2<$B$2:$B$10，得到一组 true 或 false 组成的数组；将逻辑值 *1，就等到了 1 或 0 组成的数值；

...+1：将所有大于 D2 的个数 +1，就是 D2 的排名

但是看一下结果，为什么 11 和 8 的排名都是 2 呢？因为目前只是在 B 列的数值中进行排序，而并未将 D 列的数值也一起纳入其中进行排序，所以在 B 列中，11 和 8 单独来看确实都排第二名。

使用下面的公式，就能得出在 B 和 D 列中的排名。

2. 在 F2 单元格中输入以下公式 --> 下拉复制公式：

=SUMPRODUCT((D2<TOCOL($B$2:$D$10,1))*1)+1

公式释义：

TOCOL($B$2:$D$10,1)：将区域 $B$2:$D$10 转换为一列，参数 1 表示忽略空格；这样就能将 B、D 列的数值联结成一个连续的查询区域，用于查找排名