excel函数分组（多条件）排名

数据源：

要求按组别来进行分组排名。

思路：统计同一组中，比其成绩高（低）的有几个。

如，组1，74，统计在组1中，比74大的有几个？

使用sumproduct函数

=SUMPRODUCT(($H$1:$H$10=H1)*($I$1:$I$10>I1))

因为最值（最大或最小）没有比其更大（更小）的成绩，

所以上面公式的结果可以是0，在公式后面 1即可。

=SUMPRODUCT(($H$1:$H$10=H1)*($I$1:$I$10>I1)) 1

结果：

---

数据源：

同样是排名，按总成绩从高到低排名，总成绩相同的，按照偏科程度从低到高排名。

思路：

1、先对总成绩进行排名，使用Rank函数。

=RANK(B2,$B$2:$B$6)

2、将总成绩进行分组，排名按照偏科程度来。

如，273有两个，那么273都是一个组别。

相同的总成绩为一组，应用上面的sumproduct函数即可。

=SUMPRODUCT(($B$2:$B$6=B2)*($C$2:$C$6<C2))

这里不需要在sumproduct函数后面加1。

将rank函数和sumproduct函数的结果相加，即为最后结果。

=RANK(B2,$B$2:$B$6) SUMPRODUCT(($B$2:$B$6=B2)*($C$2:$C$6<C2))

另外一个思路：

因为总成绩和偏科程度都是数值，因此可以用过放大或者缩小的方式来实现排名。

1、先建立辅助列，公式  ：=B2*10000 C2

假如直接将总成绩和偏科程度相加，那么偏科程度将会对排名造成错误的影响。

将总成绩放大10000倍，偏科程度的影响将减弱到几乎没影响。从而使得偏科程度在总成绩相同的情况下，真正决定了排名。

再使用rank函数比较放大后的数值大小即可得出排名。

（当然也可以将偏科程度缩小100倍，1000倍再和总成绩相加减）

将上面的步骤用一条公式写出结果：

=SUM(N(B2*10000 C2<$B$2:$B$6*10000 $C$2:$C$6)) 1

结果：

公式解释：

1、$B$2:$B$6*10000 $C$2:$C$6，抹黑，使用F9返回下面结果

{2730006;2730001;2560005.66666667;2790005;2760008}

2、B2*10000 C2<$B$2:$B$6*10000 $C$2:$C$6)返回结果

{FALSE;FALSE;FALSE;TRUE;TRUE}

3、N函数将false变成0，true变成1

N(B2*10000 C2<$B$2:$B$6*10000 $C$2:$C$6)

结果：

{0;0;0;1;1}

4、最后sum函数收尾，并在后面 1

假如，需要三个数值作为条件来排名，原理一样。

---

文件下载：