excel 分组排序 分组topN SUMPRODUCT

原始数据抽象出来是以下模式，有group和value两个字段

分组倒序如图，取分组倒序top3就直接筛选1/2/3。

公式：

=SUMPRODUCT(($B$2:$B$10>B2)*($A$2:$A$10=A2))+1

这里SUMPRODUCT实际上只是执行了数组求和功能，所有SUM数组公式同样可以实现

{=SUM(($B$2:$B$10>B2)*($A$2:$A$10=A2))+1}

CRTL+SHIFT+ENTER

扩充：

SUMPRODUCT的功能

SUMPRODUCT是多个尺寸相同的数组，对应位置相乘再求和

本身格式为

SUMPRODUCT(array1,array2,...)

如：

=SUMPRODUCT({1,2,3},{4,5,6})

结果是1*4+2*5+3*6 = 32

也可以用*

=SUMPRODUCT({1,2,3}*{4,5,6})

结果还是32

但是如果数组元素是BOOL值结果就存在差异了

=SUMPRODUCT({TRUE,FALSE,TRUE},{TRUE,TRUE,TRUE})

结果为0

=SUMPRODUCT({TRUE,FALSE,TRUE}*{TRUE,TRUE,TRUE})

结果为2

SUMPRODUCT里面数组如果是BOOL只能用乘，不能用逗号传多个参数

所以用分组排序不能使用

=SUMPRODUCT(($B$2:$B$10>B2),($A$2:$A$10=A2))+1

为什么不能使用？

因为$B$2:$B$10>B2和$A$2:$A$10=A2得到的都是BOOL序列，多个BOOL序列以多个参数传入SUMPRODUCT的时候，SUMPRODUCT是按照字符串处理的，函数内部逻辑处理结果恒为0，所以可以用int进行类型转换

=SUMPRODUCT(INT(($B$2:$B$10>B2)),INT(($A$2:$A$10=A2)))+1

也可以得到正确结果

如果{=SUM(($B$2:$B$10>B2)*($A$2:$A$10=A2))+1}不用数组公式

直接=SUM(($B$2:$B$10>B2)*($A$2:$A$10=A2))+1

结果是每行均为1

因为虽然$B$2:$B$10>B2、$A$2:$A$10=A2是数组，但是不加大括号就只是对应行的值，每行都为FALSE，$A$2:$A$10=A2每行都为TRUE，相乘为0，SUM(0)还是0，最后+1