|
日常工作中，我们经常要用到多条件求和，方法有多种，第一类：使用基本功能来实现。主要有：筛选、分类汇总、数据透视表、多条件求和向导； 第二类：使用公式来实现方法。主要有：使用SUM函数编写的数组公式、联用SUMIF和辅助列（将多条件变为单条件）、使用SUMPRODUCT函数、使用SUMIFS函数(限于Excel2007及以上的版本)，方法千差万别、效果各有千秋。 本人更喜欢用SUMPRODUCT函数。由于Excel帮助对SUMPRODUCT函数的解释太简短了，与SUMPRODUCT函数的作用相比实在不匹配，为了更好地掌握该函数，特将其整理如下。 一、 基本用法 在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。 语法： SUMPRODUCT(array1,array2,array3,...) Array1,array2, array3, ... 为 2 到 30 个数组，其相应元素需要进行相乘并求和。 公式：=SUMPRODUCT(A2:B4,C2:D4)
公式解释：两个数组的所有元素对应相乘，然后把乘积相加，即 3*2 + 4*7 + 8*6 + 6*7 + 1*5 + 9*3。计算结果为156 一、 扩展用法 1、 使用SUMPRODUCT进行多条件计数 语法： ＝SUMPRODUCT((条件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) 公式解释： 统计性别为男性且职称为中级职称的职工的工资总和（假设C列为工资） 二、 注意事项 1、数组参数必须具有相同的维数（也就是各个参数的引用行数要一致）。否则，函数SUMPRODUCT 将返回错误值 #VALUE!。 2、SUMPRODUCT函数将非数值型的数组元素作为 0 处理。 3、在SUMPRODUCT中，2003及以下版本不支持整列（行）引用，必须指明范围，不可在SUMPRODUCT函数使用A:A、B:B，Excel2007及以上版本可以整列（列）引用，但并不建议如此使用，公式计算速度慢。 4、SUMPRODUCT函数不支持“*”和“？”通配符 SUMPRODUCT函数不能象SUMIF、COUNTIF等函数一样使用“*”和“？”等通配符，要实现此功能可以用变通的方法，如使用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、SUMPRODUCT函数多条件求和时使用“，”和“*”的区别： 当拟求和的区域中无文本时两者无区别，当有文本时，使用“*”时会出错，返回错误值 #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会返回忽略文本以后的结果。 三、 网友们的精彩实例 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、求某数值列前三个最大的数值之和 ＝SUMPRODUCT(LARGE(B1:B16,ROW(1:3))) 5、统计指定区域不重复记录的个数 ＝SUMPRODUCT(1/COUNTIF(V11:V15,V11:V15)) 图文作者：ExcelHome技术论坛高级会员 龙逸凡 【老朋友】→请点击右上角的按钮，将本文分享到朋友圈。 【新朋友】→请点击标题下的ExcelHome，添加关注。或者直接查找公众号iexcelhome |
|
|
