sumproduct多条件求和

sumproduct多条件求和

ID:10881880

大小:39.50 KB

页数:3页

时间:2018-07-08

sumproduct多条件求和_第1页
sumproduct多条件求和_第2页
sumproduct多条件求和_第3页
资源描述:

《sumproduct多条件求和》由会员上传分享,免费在线阅读,更多相关内容在行业资料-天天文库

1、Excel多条件求和日常工作中,我们经常要用到多条件求和,方法有多种,第一类:使用基本功能来实现。主要有:筛选、分类汇总、数据透视表、多条件求和向导;第二类:使用公式来实现方法。主要有:使用SUM函数编写的数组公式、联用SUMIF和辅助列(将多条件变为单条件)、使用SUMPRODUCT函数、使用SUMIFS函数(限于Excel2007及以上的版本),方法千差万别、效果各有千秋。本人更喜欢用SUMPRODUCT函数。由于Excel帮助对SUMPRODUCT函数的解释太简短了,与SUMPRODUCT函数的作用相比实在不匹配,为了更好地掌握该函数,特将其整理如下。一、        基本用法在给定的

2、几组数组中,将数组间对应的元素相乘,并返回乘积之和。语法:SUMPRODUCT(array1,array2,array3,...)Array1,array2,array3,...为2到30个数组,其相应元素需要进行相乘并求和。公式:=SUMPRODUCT(A2:B4,C2:D4) ABCD1Array 1Array 1Array 2Array 2234273866741953公式解释:两个数组的所有元素对应相乘,然后把乘积相加,即3*2+4*7+8*6+6*7+1*5+9*3。计算结果为156二、        扩展用法1、  使用SUMPRODUCT进行多条件计数语法:=SUMPRODUCT

3、((条件1)*(条件2)*(条件3)*…(条件n))作用:统计同时满足条件1、条件2到条件n的记录的个数。实例:=SUMPRODUCT((A2:A10="男")*(B2:B10="中级职称"))公式解释:统计性别为男性且职称为中级职称的职工的人数2、  使用SUMPRODUCT进行多条件求和语法:=SUMPRODUCT((条件1)*(条件2)*(条件3)*…(条件n)*某区域)作用:汇总同时满足条件1、条件2到条件n的记录指定区域的汇总金额。实例:=SUMPRODUCT((A2:A10="男")*(B2:B10="中级职称")*C2:C10)公式解释:统计性别为男性且职称为中级职称的职工的工资

4、总和(假设C列为工资)三、        注意事项1、数组参数必须具有相同的维数,否则,函数SUMPRODUCT将返回错误值#VALUE!。2、SUMPRODUCT函数将非数值型的数组元素作为0处理。3、在SUMPRODUCT中,2003及以下版本不支持整列(行)引用,必须指明范围,不可在SUMPRODUCT函数使用A:A、B:B,Excel2007及以上版本可以整列(列)引用,但并不建议如此使用,公式计算速度慢。4、SUMPRODUCT函数不支持“*”和“?”通配符SUMPRODUCT函数不能象SUMIF、COUNTIF等函数一样使用“*”和“?”等通配符,要实现此功能可以用变通的方法,如使

5、用LEFT、RIGHT、ISNUMBER(FIND())或ISNUMBER(SEARCH())等函数来实现通配符的功能。如:=SUMPRODUCT((A2:A10="男")*(B2:B10="中级职称")*(LEFT(D2:D10,1)="龙")*C2:C10)=SUMPRODUCT((A2:A10="男")*(B2:B10="中级职称")*((ISNUMBER(FIND("龙逸凡",D2:D10)))*C2:C10))注:以上公式假设D列为职工姓名。ISNUMBER(FIND())、ISNUMBER(SEARCH())作用是实现“*”的通配功能,只是前者区分大小写,后者不区分大小写。5、SU

6、MPRODUCT函数多条件求和时使用“,”和“*”的区别:当拟求和的区域中无文本时两者无区别,当有文本时,使用“*”时会出错,返回错误值#VALUE!,而使用“,”时SUMPRODUCT函数会将非数值型的数组元素作为0处理,故不会报错。也就是说:公式1:=SUMPRODUCT((A2:A10="男")*(B2:B10="中级职称")*C2:C10)公式2:=SUMPRODUCT((A2:A10="男")*(B2:B10="中级职称"),C2:C10)当C2:C10中全为数值时,两者计算结果一样,当C2:C10中有文本时公式1会返回错误值#VALUE!,而公式2会返回忽略文本以后的结果。四、  

7、      网友们的精彩实例1、求指定区域的奇数列的数值之和=SUMPRODUCT(MOD(COLUMN(A1:F1),2)*A1:F1)2、求指定区域的偶数行的数值之和=SUMPRODUCT(((MOD(ROW(A1:A22),2))-1)*A1:A22)*(-1)3、求指定行中列号能被4整除的列的数值之和 =SUMPRODUCT((MOD(COLUMN(A1:P1),4)=0)*A1:P1)4

当前文档最多预览五页,下载文档查看全文

此文档下载收益归作者所有

当前文档最多预览五页,下载文档查看全文
温馨提示:
1. 部分包含数学公式或PPT动画的文件,查看预览时可能会显示错乱或异常,文件下载后无此问题,请放心下载。
2. 本文档由用户上传,版权归属用户,天天文库负责整理代发布。如果您对本文档版权有争议请及时联系客服。
3. 下载前请仔细阅读文档内容,确认文档内容符合您的需求后进行下载,若出现内容与标题不符可向本站投诉处理。
4. 下载文档时可能由于网络波动等原因无法下载或下载错误,付费完成后未能成功下载的用户请联系客服处理。