sumproduct排名函数讲的如此详细,你还担心学不会吗?图文

2019年8月2日08:28:48 评论 744 views

求和除了用sum、sumif、sumifs,其实我们还可以用sumproduct函数

函数表面看,sumproduct是由2个英文单词组合而成。sum是求和,product是乘积,所以这个函数的作用,我们可以理解成:在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。

下面进入到我们今天的主题,利用sumproduct实现数据排名

一、sumproduct函数的语法格式。

=SUMPRODUCT(array1,[array2],[array3],…)。

参数array1,array2,...,是2到255个数组,所有数组的维数必须一样。

sumproduct排名函数讲的如此详细,你还担心学不会吗?

二、sumproduct函数实现不间断排名

要求:求出下图中成绩对应的实际排名是多少?

sumproduct排名函数讲的如此详细,你还担心学不会吗?

具体操作步骤如下:

1、选中C2单元格-- 在编辑栏中输入公式“=SUMPRODUCT(($B$2:$B$9>=B2)/COUNTIF($B$2:$B$9,$B$2:$B$9))“ -- 按回车键回车。

sumproduct排名函数讲的如此详细,你还担心学不会吗?

公式解析:

$B$2:$B$9>=B2:判断$B$2:$B$9单元格里面的值是否大于等于B2单元格的值,如果为真,返回TRUE,否则,返回FALSE。此时,该公式组成了一个逻辑数组“{TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE}”,其中逻辑值TRUE=1,FALSE=0,所以公式结果也等于“{1;1;1;1;1;0;0;1}”。

sumproduct排名函数讲的如此详细,你还担心学不会吗?

COUNTIF($B$2:$B$9,$B$2:$B$9):$B$2:$B$9中符合指定条件的单元格计数。该公式的结果为“{1;2;3;1;1;1;1;1}”。

sumproduct排名函数讲的如此详细,你还担心学不会吗?

所以公式($B$2:$B$9>=B2)/COUNTIF($B$2:$B$9,$B$2:$B$9)得到的结果为“{1;0.5;0.5;1;1;0;0;1}”。

sumproduct排名函数讲的如此详细,你还担心学不会吗?

最后利用sumproduct的原理,将得到的这些值“{1;0.5;0.5;1;1;0;0;1}”相加,即可得到C2单元格的值:5

2、动图演示如下。

sumproduct排名函数讲的如此详细,你还担心学不会吗?

我们给上图的排名按照升序进行排序,可以发现以上方法实现的排名是不间断的。如下图所示。

sumproduct排名函数讲的如此详细,你还担心学不会吗?

如果我们要实现间断的排名,C2单元格的公式可以这样写:=SUMPRODUCT(($B$2:$B$9>B2)*1)+1。实现的排名就是间断的,如下图所示。

sumproduct排名函数讲的如此详细,你还担心学不会吗?

除此之外,我们将C2单元格的公式改成“=RANK(B2,$B$2:$B$9)”,也可以实现不间断的排名。

sumproduct排名函数讲的如此详细,你还担心学不会吗?

间断排名可以用rank函数和sumproduct函数,不间断排名用sumproduct函数,以上的3个公式,你都看懂了吗?

如有不懂的地方,可以在评论区留下你的问题,会一一为您解答。也希望你可以转发和点赞,给与小编支持和鼓励,小编会努力写出更多使用的Excel函数教程。

想学更多的Word、Excel等办公技巧吗?欢迎关注小编哦,定期更新实用技巧供大家学习。

您的赞赏、关注、转发、评论、点赞和收藏都是对小编的鼓励和支持,谢谢您!

本文已取的原作者授权 禁止转载 原文链接为https://www.toutiao.com/item/6716089010923504142/

  • 微信扫码免费学习
  • 免费学习48小时
  • weinxin
  • 我的微信公众号
  • EXCEL880
  • weinxin

发表评论

您必须才能发表评论!