Excel SUMPRODUCT函数用法(乘积求和,分组排序) |
您所在的位置:网站首页 › excel两组数据排序 › Excel SUMPRODUCT函数用法(乘积求和,分组排序) |
SUMPRODUCT函数是Excel中功能比较强大的一个函数,可以实现sum,count等函数的功能,也可以实现一些基础函数无法直接实现的功能,常用来进行分类汇总,分组排序等 SUMPRODUCT 函数基础SUMPRODUCT函数先计算多个数组的元素之间的乘积再求和。SUMPRODUCT函数的语法为: SUMPRODUCT(array1,array2,array3, …),其中Array为数组,用于指定包含构成计算对象的值的数组或单元格区域SUMPRODUCT函数的使用注意事项: (1).数组参数必须具有相同的维数,即行数相同,否则,函数SUMPRODUCT将返回错误值#VALUE! (2).数据区域引用不能整列引用.如:A:A、B:B,必须是A2:A100这种 SUMPRODUCT 函数用法 用法1:乘积求和如下数据需要统计公司整体的花费,需要将数量乘以单价并汇总 这里用到了两写方法,第一个公式中用*(乘号)连接参数。第二个公式中用,(逗号)连接参数。本例中的数据源都是数值,所以两种方法返回的结果一致。如果当数据源中包含文本数据值,使用公式2仍然可以返回正确结果,SUMPRODUCT将非数值型的元素作为0处理。但如果用公式1则会导致数值和文本相乘,返回错误值:#VALUE!。 乘积求和是SUMPRODUCT 函数最基础的用法,也可以解释SUMPRODUCT 函数的原理:即将选取区域的数组相乘再相加 用法2:条件求和SUMPRODUCT函数中加入逻辑判断即可以实现sumif、sumifs函数的功能:条件求和。例如想要求市场部的总花费,即单条件求和,公式如下:=SUMPRODUCT((A2:A8="市场部")*C2:C8*D2:D8) 其中A2:A8="市场部"即为对应的条件判断,如果为市场部,则返回结果为True,对应值为1,并与后续数组中的数量和单价相乘,如果不为市场部,则返回结果为False,对应值为0,与后续数组中的数量和单价相乘后返回的结果为0,从而实现了条件求和。 当有多个条件求和时也可以按照同样的原理增加逻辑判断。例如求市场部笔记本总花费,对应公式为:=SUMPRODUCT((A2:A8="市场部")*(B2:B8="笔记本")*C2:C8*D2:D8) 用法3:条件计数条件计数的原理即为将SUMPRODUCT函数中的参数全部设为条件判断,判断结果为True的返回数值1,相加后即为满足条件的计数 例如求市场部对应的记录数量,公式为:=SUMPRODUCT(N(A2:A8="市场部"))其中部门列是文本形式,则需嵌套N函数,表示返回转化为数值后的值,从而统计对应的记录数。 如果需要对多条件进行计数,例如统计市场部单价大于5的记录数,则公式为:=SUMPRODUCT((A2:A8="市场部")*(D2:D8>5)) 用法4:分组排序SUMPRODUCT函数也常用于分组排序,例如需要将不同部门的商品单价排序,则对应公式为:=SUMPRODUCT(($A$2:$A$8=A2)*($D$2:$D$8>=D2)) 其中$A$2:$A$8=A2表示条件区域列判断是否等于A2,返回对应的判断结果True和False。$D$2:$D$8>D2判断单价区域列是否大于等于D2,同样返回一组判断结果True和False,通过SUMPRODUCT函数将两组数组相乘,得到的就是部门列为市场部,且单价大于等于10的相乘结果为1,其他结果为0,再相加后即可得到整个分组中大于D2的记录数,从而得到对应的排名。 省流版公式套路:=SUMPRODUCT((条件区域1=条件1)*(要进行排名的区域>数值))+1 用法5:多权重计算上述几种用法中SUMPRODUCT 函数中的数组区域都是一列的列数组,同样的一行的行数组也是可以的。例如下面需要根据笔试,面试和其他成绩及权重计算总成绩: 总成绩计算公式为:=SUMPRODUCT($B$17:$D$17,B18:D18),其中$B$17:$D$17为权重区域,```B18:D18``为成绩区域,注意两个区域的绝对引用和相对引用。SUMPRODUCT 函数对两个区域进行乘积求和。在权重项目比较多时使用SUMPRODUCT比一个一个乘要方便很多 用法6:隔列条件求和如下例中需要计算每一种产品的计划采购数量和实际采购数量 示例文件下载: https://download.csdn.net/download/qq_42692386/88855138 |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |