首页
学习
活动
专区
工具
TVP
发布
精选内容/技术社群/优惠产品,尽在小程序
立即前往

Excel函数之Sumproduct,6个经典用法,你真的都了解吗?

Sumproduct,可以将其分为两个部分,Sum和Product,Sum是求和,Product是乘积,综合到一起,就是对各个数据参数先乘积,再返回乘积之和。

一、Sumproduct——常规用法

功能:返回相应的数据或区域乘积的和。

语法结构:=Sumproduct(数据1,[数据2],[数据3]……)。

目的:计算各类商品的总采购额。

方法:

在目标单元格中输入公式:=SUMPRODUCT(E3:E12,F3:F12)。

解读:

Sumproduct函数的功能是返回相应的数据或区域乘积的和,公式=SUMPRODUCT(E3:E12,F3:F12)中,数据区域有E3:E12和F3:F12两个,所以计算过程为:E3*F3+E4*F4+E5*F5+……+E11*F11+E12*F12,对应数据元素先乘积,后求和,得到最终的总采购额。

二、Sumproduct——单条件求和

目的:按【部门】统计总采购额。

方法:

在目标单元格中输入公式:=SUMPRODUCT((D3:D12=I3)*E3:E12*F3:F12)。

解读:

公式=SUMPRODUCT((D3:D12=I3)*E3:E12*F3:F12)中,有三个数据区域,分别为D3:D12=I3、E3:E12、F3:F12,当D3:D12区域中的值和I3单元格中的值相等时,返回1,否则返回0,然后三个数据对应元素先乘积,再契合,从而计算得到相应部门的总采购额。

三、Sumproduct——多条件求和

目的:相应【部门】采购相应【商品】的总采购额。

方法:

在目标单元格中输入公式:=SUMPRODUCT((B3:B12=J3)*(D3:D12=I3)*E3:E12*F3:F12)。

解读:

计算过程同“单条件求和”类似,只是增加了一个判断条件而已。

四、Sumproduct——单条件计数

目的:统计商品总采购【数量】。

方法:

在目标单元格中输入公式:=SUMPRODUCT((B3:B12=I3)*(E3:E12))。

解读:

公式=SUMPRODUCT((B3:B12=I3)*(E3:E12))中,有两个数据区域,一个为B3:B12=I3,另一个为E3:E12,第一个数据区域中当B3:B12=I3这个条件成立时,返回1,否则返回0,所以第一个数据区域对应的元素值为,与第二个数据元素的值先乘积,再求和,从而得到计数的结果。

五、Sumproduct——多条件计数

目的:对相应【采购部门】采购的【商品】进行计数统计。

方法:

在目标单元格中输入公式:=SUMPRODUCT((D3:D12=I3)*(B3:B12=J3)*E3:E12)。

解读:

具体的计算过程请参阅“单条件计数”的解读部门。

六、Sumproduct——中国式排名

目的:对单个【商品】的【采购额】进行排序。

方法:

在目标单元格中国输入公式:=SUMPRODUCT(($G$3:$G$12>G3)/COUNTIF($G$3:$G$12,$G$3:$G$12))+1。

解读:

1、从结果中可以看出,公式=SUMPRODUCT(($G$3:$G$12>G3)/COUNTIF($G$3:$G$12,$G$3:$G$12))+1的排序结果降序,如果要按升序排序,则只需将公式中的$G$3:$G$12>G7更改为>G3>$G$3:$G$12即可。

2、公式整体上可以看成3部分组成,一部分为:$G$3:$G$12>G3,另一部分为:COUNTIF($G$3:$G$12,$G$3:$G$12),最后一部分为附加值+1。计算时,首先判断$G$3:$G$12>G3是否成立,以H3单元格为例,则返回,而COUNTIF($G$3:$G$12,$G$3:$G$12)统计的是$G$3:$G$12区域中的每个值在$G$3:$G$12区域中的出现次数,即返回,对应的数据元素先进行除法(/)计算,返回,并返回给Sumproduct作为参数,而Sumpoduct中,当只有一个数据区域时,对该区域的值进行求和运算,即0+1+1+1+1+1+1+0+1+0,最后等于7,加上辅助值1,得到想要的结果8。

  • 发表于:
  • 原文链接https://page.om.qq.com/page/OERP1DAeCInEkb559fNnc7jA0
  • 腾讯「腾讯云开发者社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 cloudcommunity@tencent.com 删除。

相关快讯

扫码

添加站长 进交流群

领取专属 10元无门槛券

私享最新 技术干货

扫码加入开发者社群
领券