今天介绍一个非常强大的 sumproduct 函数,包罗万象、千变万化,比如,排名、计数、求和、有条件求和、求平均值,等等等等。 我用的版本是 Excel 2016,其他版本的界面可能略有不同。 案例1:统计排名 比如下表是德云社演员的年终奖(数字当然是我编的!连专场不多的高老板都网传年薪 500 万了)。怎么统计所有演员的年终奖排名? 解决方案: 1. 这个需求比较简单,用 rank 函数可以实现,今天教的是用 subproduct 函数实现。 公式:=SUMPRODUCT((B2<$B$2:$B$95)*1)+1 翻译:
案例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) 翻译:
|
|