excel名次-Excel函数(四) – sumproduct函数计数、排名、求和等等

今天介绍一个非常强大的 sumproduct 函数，包罗万象、千变万化，比如，排名、计数、求和、有条件求和、求平均值，等等等等。

我用的版本是 Excel 2016，其他版本的界面可能略有不同。

案例1：统计排名

比如下表是德云社演员的年终奖（数字当然是我编的！连专场不多的高老板都网传年薪 500 万了）。怎么统计所有演员的年终奖排名？

解决方案：

1. 这个需求比较简单，用 rank 函数可以实现，今天教的是用 subproduct 函数实现。

公式：=SUMPRODUCT((B2<$B$2:$B$95)*1)+1

翻译：

• B2<$B$2:$B$95：将 B2 与 B 列所有值比较，返回的值是 true or fales，分别为 1 或 0
• *1：统计结果为 ture 的数量。这里 * 表示 and
• +1：如果有 3 个比自己大的，那么排名为 3+1=4

案例2：统计各小队排名

德云社一共 8 个小队，除了下表的前 5 位之外，都分别属于不同的队。现在需要统计他们的年终奖在自己小队的排名？

解决方案：

1. 公式：=SUMPRODUCT((A2=$A$2:$A$95)*(C2<$C$2:$C$95)*1)+1

翻译：在上一个需求的基础上，增加了(A2=$A$2:$A$95) 条件，即在“如果在同一个小队，则计算排名”

案例3：统计各小队的总人数

每个小队分别有多少人？

1. 公式：=SUMPRODUCT(N(A2=$A$2:$A$95))

翻译：计算相同队名的人数

案例4：统计各小队的总奖金数

1. 公式：=SUMPRODUCT((A2=$A$2:$A$95)*1,C$2:C$95)

翻译：当队名相同时，求和

案例5：统计各小队奖金高于本队平均数的人数

1. 在 H 列增加各队名称

公式：=SUMPRODUCT(($C$2:$C$95>AVERAGE($C$2:$C$95))*(H2=$A$2:$A$95)*1)

翻译：

• (($C$2:$C$95>AVERAGE($C$2:$C$95)：C列中大于平均数的人数
• H2=$A$2:$A$95：队名等于 H 列中的对应队名
• *：表示 and