分享

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

 碧荷晨露 2019-02-11

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

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

案例1:统计排名

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

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

解决方案:

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

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

案例2:统计各小队排名

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

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

解决方案:

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

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

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

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

每个小队分别有多少人?

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

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

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

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

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

翻译:当队名相同时,求和

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

案例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

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

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。
    转藏 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多