|
|
http://www.biaogewang.com/tech.php?id=20100812
我们先看看简单的排名（从大到小）。
| A | B | B列公式 | | 1 | 4 | 2 | =RANK($A1,$A$1:$A$5) | | 2 | 2 | 4 | =RANK($A2,$A$1:$A$5) | | 3 | 4 | 2 | =RANK($A3,$A$1:$A$5) | | 4 | 1 | 5 | =RANK($A4,$A$1:$A$5) | | 5 | 5 | 1 | =RANK($A5,$A$1:$A$5) |
左面的公式是直接用RANK函数得出的结果。 A列中，5是最大（第5行），所以排第1；1是最小（第4行），所以排最后（第5）。 RANK函数对相同数字会返回一样的排名，可是会影响后面的排名。 比如A列中有两个4（第1行和第3行），都排第2，可是由于有两个第2，就没有第3了。这跟我们一般理解的排名也是一样的。
用SUMPRODUCT做简单的排名，同样要考虑条件。从大到小排名，实际上就是算出有多少个在列表里面的数是大于自己的。如下面Excel表格里的公式：
| A | B | B列公式 | | 1 | 4 | 2 | =SUMPRODUCT(($A$1:$A$5>$A1)*1)+1 | | 2 | 2 | 4 | =SUMPRODUCT(($A$1:$A$5>$A2)*1)+1 | | 3 | 4 | 2 | =SUMPRODUCT(($A$1:$A$5>$A3)*1)+1 | | 4 | 1 | 5 | =SUMPRODUCT(($A$1:$A$5>$A4)*1)+1 | | 5 | 5 | 1 | =SUMPRODUCT(($A$1:$A$5>$A5)*1)+1 |
如果你会用SUMPRODUCT做条件计数和多条件计数，这个对你应该一点难度都没有。
SUMPRODUCT(($A$1:$A$5>$A1)*1)算出了A列中有多少个数大于A1。+1就是自己的排名了。
你可能会想，我们为什么不把公式改成SUMPRODUCT(($A$1:$A$5>=$A1)*1)，让SUMPRODUCT在计算时包括了等于自己的项，不就不用+1了吗？
当列表中没有相同数值时，这是没有问题的。可是如果有相同数值，那就会出现问题了。
你可以试试把B1的公式改成SUMPRODUCT(($A$1:$A$5>=$A1)*1)，下拉到B2:B5，你会发现B1和B3的结果就会变成3，而不是我们希望的2了。
我们有时候会需要从小到大排名（如赛跑时间），用下面的公式就可以了：
| A | B | B列公式 | C | C列公式 | | 1 | 4 | 3 | =RANK($A1,$A$1:$A$5,1) | 3 | =SUMPRODUCT(($A$1:$A$5<$A1)*1)+1 | | 2 | 2 | 2 | =RANK($A2,$A$1:$A$5,1) | 2 | =SUMPRODUCT(($A$1:$A$5<$A2)*1)+1 | | 3 | 4 | 3 | =RANK($A3,$A$1:$A$5,1) | 3 | =SUMPRODUCT(($A$1:$A$5<$A3)*1)+1 | | 4 | 1 | 1 | =RANK($A4,$A$1:$A$5,1) | 1 | =SUMPRODUCT(($A$1:$A$5<$A4)*1)+1 | | 5 | 5 | 5 | =RANK($A5,$A$1:$A$5,1) | 5 | =SUMPRODUCT(($A$1:$A$5<$A5)*1)+1 |
学会了怎么用SUMPRODUCT替代RANK函数，条件排名 / 多条件排名实际就只是把条件直接用乘号放在SUMPRODUCT的数组里：
| A | B | C | C列公式（区分男女从大到小排名） | D | D列公式（区分男女从小到大排名） | | 1 | 男 | 4 | 1 | =SUMPRODUCT(($A$1:$A$5=$A1)*($B$1:$B$5>$B1)*1)+1 | 3 | =SUMPRODUCT(($A$1:$A$5=$A1)*($B$1:$B$5<$B1)*1)+1 | | 2 | 男 | 2 | 2 | =SUMPRODUCT(($A$1:$A$5=$A2)*($B$1:$B$5>$B2)*1)+1 | 2 | =SUMPRODUCT(($A$1:$A$5=$A2)*($B$1:$B$5<$B2)*1)+1 | | 3 | 女 | 4 | 2 | =SUMPRODUCT(($A$1:$A$5=$A3)*($B$1:$B$5>$B3)*1)+1 | 1 | =SUMPRODUCT(($A$1:$A$5=$A3)*($B$1:$B$5<$B3)*1)+1 | | 4 | 男 | 1 | 3 | =SUMPRODUCT(($A$1:$A$5=$A4)*($B$1:$B$5>$B4)*1)+1 | 1 | =SUMPRODUCT(($A$1:$A$5=$A4)*($B$1:$B$5<$B4)*1)+1 | | 5 | 女 | 5 | 1 | =SUMPRODUCT(($A$1:$A$5=$A5)*($B$1:$B$5>$B5)*1)+1 | 2 | =SUMPRODUCT(($A$1:$A$5=$A5)*($B$1:$B$5<$B5)*1)+1 |
|
|
[复制链接]
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2010-8-12 15:16
