有网友提了一个有意思的问题:对于在序列中不存在的数值,如何统计其排名?
比如,序列是 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 列的数值联结成一个连续的查询区域,用于查找排名