SUMPRODUCT函数在质量分析中的运用

SUMPRODUCT函数在质量分析中的运用 SUMPRODUCT函数在质量分析中的运用 关键词:SUMPRODUCT函数 质量分析 运用 Excel有许多功能，但它最大的功能还是Excel中大量的函数可完成各种数据的分析，快捷、强大、直观，带给使用者方便。下面是本人在学习Excel过程中对SUMPRODUCT函数运用的一些肤浅的认识。 一、SUMPRODUCT函数的理解 SUMPRODUCT函数语法:SUMPRODUCT(array1,array2,array3, ...)，array1, array2, array3, ... 为 2 到 30 个数组，其相应元素需要进行相乘并求和。 SUMPRODUCT函数语法可以理解为:在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。其中array就是数组的意思，array1就是数组1，array2就是数组2，SUMPRODUCT函数中最多可以容纳30个数组。 举例说明如下: 图一 在图一的工作 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf A5中输入=SUMPRODUCT(A2:B4, C2:D4)，结果为217。 “=SUMPRODUCT(A2:B4, C2:D4)”是两个数组(A2:B4, C2:D4)的所有元素对应相乘，然后把乘积相加，即A2*C2+B2*D2+A3*C3+B3*D3+A4*C4+B4*D4，也就是1*7 + 4*10 + 2*8 + 5*11 + 3*9 + 6*12。 二、SUMPRODUCT函数实例 图二 1、实例一 图二中要统计年龄在35岁及以上、工龄在15年及以上的人数，很快就能得出结果(5人)，但如果是一个含有上百人甚至上千人的工作表，就不容易找了， 1 而且容易出错。 SUMPRODUCT函数能够解决这个问 题 快递公司问题件快递公司问题件货款处理关于圆的周长面积重点题型关于解方程组的题及答案关于南海问题 。 在图二的工作表A8中输入=SUMPRODUCT((B2:B7>=35)*(C2:C7>=15))，结果为5。 (B2:B7>=35)是一个数组，返回的是6个逻辑值——TRUE;TRUE ;FALSE;TRUE;TRUE ;TRUE;TRUE。逻辑值TRUE与FALSE参与计算时分别为1和0。那么，(B2:B7>=35)表示的数组是{1;1;0;1;1;1}。 同理，(C2:C7>=15) 表示的数组是{1;1;0;1;1;1}。 “=SUMPRODUCT((B2:B7>=35)*(C2:C7>=15))”是二个数组 {1;1;0;1;1;1} {1;1;0;1;1;1} 的所有元素对应相乘，然后把乘积相加，即1*1+1*1+0*0+1*1+1*1+1*1=5。 2、实例二 如果在举例一提出的问题中还增加一个条件“具有中一职称”， SUMPRODUCT函数能够解决吗,回答是肯定的，因为SUMPRODUCT函数中最多可以容纳30个数组。 要计算图二中年龄在35岁及以上、工龄在15年及以上且具有中一职称的人数，在图二的工作表A9中输入=SUMPRODUCT((B2:B7>=35)*(C2:C7>=15)*(D2:D7=" 中一"))，结果为3。 =SUMPRODUCT((B2:B7>=35)*(C2:C7>=15)*(D2:D7="中一"))”是三个数组 “ {1;1;0;1;1;1} {1;1;0;1;1;1} {0;1;1;1;1;0} 的所有元素对应相乘，然后把乘积相加，即 1*1*0+1*1*1+0*0*1+1*1*1+1*1*1+1*1*0=3。 3、实例三 如果在举例一提出的问题中减少一个条件“工龄在15年及以上”， 即要计算图二中年龄在35岁及以上的人数，SUMPRODUCT函数能够解决吗,回答也是肯定的。 有人也许会问:在SUMPRODUCT函数语法中说“array1, array2, array3, ... 为 2 到 30 个数组”，它的最少数组数不是2个吗,回答也是肯定的。 解决的办法其实很简单，把变形公式中的第二个数组变成“1”就行了。 要计算图二中年龄在35岁及以上的人数，在图二的工作表A10中输入=SUMPRODUCT((B2:B7>=35)*1)，结果为5。 “=SUMPRODUCT((B2:B7>=35)*1)”是数组{1;1;0;1;1;1}和“1”的乘积的和，即1*1+1*1+0*1+1*1+1*1+1*1=5。 当然，这里可以用COUNTIF函数来解决，即“=COUNTIF(B2:B7,">=35")”。 四、SUMPRODUCT函数基本格式 用SUMPRODUCT函数计算符合多条件的数据和，其基本格式有三种: 第一种:=SUMPRODUCT(条件A*1)，条件只有一个。 其含义是:符合条件A的个数。 第二种:=SUMPRODUCT(区域1，区域2，„„) 其含义是:区域1、区域2等区域里的数对应相乘，然后把乘积相加。 2 第三种:=SUMPRODUCT(条件1*条件2*„„) 其含义是符合条件1、条件2等条件的个数。 第四种:=SUMPRODUCT(条件1*条件2*„„*求和数据区域)，每个区域(数组)包含的值的个数相同，区域(数组)数最多30个。 条件数组最多30个，前面的的区域是条件，最后一个区域是需要求和的区域，每个区域(数组)包含的值的个数相同。 第三种与第二种不同的是:第三种的区域连接用*连接，每个条件区域必须用()，第二种的区域连接用“，”连接。 以上所述仅限Excel2003版本，Excel2007-2013的版本虽然也适合，但Excel2007-2013的版本中可以用SUMIFS函数代替SUMPRODUCT函数。Excel2003版本中没有SUMIFS函数。 三、SUMPRODUCT函数在质量分析中的运用 图三是三个班级41人的成绩表，以语文学科为例统计班级人数、均分、合格人数与合格率、优质人数与优质率、班级名次、班级后N名均分、总分均分、总分分数段人数，SUMPRODUCT这一个函数就基本够用了。 3 图三 1、班级人数 图四 统计班级人数可用COUNTIF函数，即在O1单元格中输入 “=COUNTIF(B$2:B$42,N2)”，与“=SUMPRODUCT((B$2:B$42=N2)*1)”结果一样。 2、单科(语文)均分 图五 P2单元格“=SUMPRODUCT((B$2:B$42=N2)*C$2:C$42)/O2” 中 “SUMPRODUCT((B$2:B$42=N2)*C$2:C$42)”表示的是二个数组(B$2:B$42=N2)和C$2:C$42的所有元素对应对应相乘后的和。 “(B$2:B$42=N2)” 是由FALSE和TRUE组成的包含41个逻辑值的数组。当“B$2:B$42”中的某个单元格=N2时为TRUE，否则为FALSE，由此组成了数组{1;0;0;0;1;„„}。 “C$2:C$42”是由C列41个数字组成的数组，即{113;110;109;109;108;„„}。 “=SUMPRODUCT((B$2:B$42=N2)*C$2:C$42)”就是把二个数组{1;0;0;0;1;„„}和{113;110;109;109;108;„„}的所有元素对应相乘，然后把乘积相加，最后除以班级人数(O2)。 计算单科均分可以用SUMIF函数，SUMIF函数是对于计算符合某一个条件的数据求和，即在P1单元格输入“=SUMIF(B$2:B$42,N2,C$2:C$42)/O2”。 也可按照此 方法 快递客服问题件处理详细方法山木方法pdf计算方法pdf华与华方法下载八字理论方法下载 计算总分均分。 、单科(语文)合格人数与合格率、总分分数段人数 3 图六 Q2单元格“=SUMPRODUCT((B$2:B$42=N2)*(C$2:C$42>=72))”表示的是二个数 4 组(B$2:B$42=N2)和(C$2:C$42>=72) 的所有元素对应对应相乘后的和。 “(B$2:B$42=N2)”和“(C$2:C$42>=72)”是由FALSE和TRUE组成的分别包含41个逻辑值的数组。 这二个数组是{1;0;0;0;1;„„}和{1;1;1;1;1;„„}。 这二个数组实际上就是二个条件，如果提出的问题再复杂一点，可以增加公式中的数组，一个条件用一个数组，数组不超过30个就行了。 计算班级均分是单条件的数据求和，用SUMIF函数能够完成;计算语文合格人数是多条件的数据求和，用SUMIF函数就不能够完成了;要用SUMIF函数计算多条件的数据求和，必须借助辅助列来完成，而且使用起来比较复杂。 如果单科是120分的卷面，有时还可以分析得分率在90%以上即108分以上的情况，同理，用SUMPRODUCT函数可以计算>=96的人数。 按此方法可以计算班级总分某分数段人数。 4、单科(语文)优质人数与优质率 图七 S2单元格与Q2单元格的公式相似，不再赘述。 5、班级名次 RANK函数是用来计算名次的函数，某学生某学科(或总分)在班级的名次用RANK函数就很难实现了。 在图三的K2单元格输入 “=SUMPRODUCT((($B$2:$B$42=B2)*($J$2:$J$42)>J2)*1)+1”(以下简称公式一)就计算出某学生总分在班级的排名。如果要计算某学生语文在班级的排名，只需把上面公式中的J替换为C。 “=SUMPRODUCT((($B$2:$B$42=B2)*($J$2:$J$42)>=J2)*1)”和统计班级人数的公式 “=SUMPRODUCT((B$2:B$42=N2)*1)”原理一样。 (B$2:B$42=N2)是条件，“=SUMPRODUCT((B$2:B$42=N2)*1)”表示的是B列中等于N2(1班)的人数。 而(($B$2:$B$42=B2)*($J$2:$J$42)>=J2))也是条件，只不过这个条件稍微复杂一点，“=SUMPRODUCT((($B$2:$B$42=B2)*($J$2:$J$42)>=J2)*1)” 表示的是满足B列中等于N2(1班)并且在J列中>= J2的人数。 但是，用“=SUMPRODUCT((($B$2:$B$42=B2)*($J$2:$J$42)>=J2)*1)”计算排名，相同数据(分数)，排名是相同的。后面将分析“班级后N名均分”，假如某班级倒数第N名不止一个，尤其在单科中某一个分数有多人是很普遍的，那么，相同数据(分数)排名相同的情况就会影响“班级后N名均分”的分析。 怎样解决这个问题,在图三的K2单元格输入下面的公式可以解决。 =SUMPRODUCT((($B$2:$B$42=B2)*($J$2:$J$42)>=J2)*1)-SUMPRODUCT((($B$ 2:B2=B2)*($J$2:J2)=J2)*1)+1((以下简称公式二)) 公式前面部分是满足班级条件和J列数据(分数)大于等于J2的数(人数)， 5 中间部分是$J$2:J2区域内满足班级条件和和数据(分数)等于J2的数(人数)，前者减后者再加1就是最后要得到的结果。当公式向下复制时，$J$2:J2中J2发生变化，因此，用上面公式在K列中得出的结果(名次)不会因为J列中数据(分数)相同而相同。 图八 图八中，K行是用公式一计算的班级名次，L行是用公式二计算的班级名次，M行是K行与L行对应相减的结果。于是，不难发现，3班有2个学生总分429分，用公式一计算的班级名次均为第5名，用公式二计算的班级名次分别为第5名、第6名。 还有一种方法可以让分数相同班级排名不同，不过相对复杂一点，并且嵌套了ROW函数。 在图三的K2单元格输入下面的公式 6 =SUMPRODUCT((($B$2:$B$42=B2)*($J$2:$J$42*10000+ROW($2:$42))>=(J2*1 0000+ROW()))*1) 这个公式与“=SUMPRODUCT((($B$2:$B$42=B2)*($J$2:$J$42)>=J2)*1)”原理相同，有四个地方的区别，增加了*10000、ROW($2:$42)、*10000、ROW()，说明如下: ?ROW($2:$42)是一个2至42的自然数数组，即{2;3;4;„„42} ?ROW()返回单元格所在行号，ROW()在K2就返回2，ROW()在K3就返回3。 ?$J$2:$J$42表示数组{492;499;506;„„}， ROW($2:$42)表示数组{2;3;4;„„42}， $J$2:$J$42+ ROW()表示数组{492+2;499+3;506+4;„„} ?J2+ROW()是一个常数，公式一般与J2同在一行，那么J2+ROW()=J2+2 ?($J$2:$J$42+ROW($2:$42))>=J2+ROW()表示的是一个新的逻辑数组，即{492+2;499+3;506+4;„„}中大于等于 J2+2的数为TRUE，否则为FALSE，那么新的逻辑组是由0和1组成的数组{1;0;0;0;1;„„}。 假如$J$2:$J$42数组中大于J2的数有5个，等于J2的数有3个，那么， ($J$2:$J$42+ROW($2:$42) 数组大于J2的数就会有7(5+3-1)个，等于J2+ROW()的数只有一个。这就解决了同一分数排名相同的问题。 ?$J$2:$J$42为什么要乘以10000，J2也乘以10000, $J$2:$J$42+ ROW()表示数组{492+2;499+3;506+4;„„}，假如$J$2:$J$42 1，处在J42单元格，那么它在上面数组中的值为1+42;再假如$J$2:$J$42中某数为 中某数为30，处在J11单元格，那么它在上面数组中的值为30+11;这样，$J$2:$J$42+ ROW()数组与原数组$J$2:$J$42大小顺序会发生变化，$J$2:$J$42乘以一个足够大的数再加上ROW()后组成的新数组与原数组$J$2:$J$42大小顺序才不会发生变化，其实，按图三的实际数据，乘以100就足够了。当然，$J$2:$J$42乘以10000，J2也必须要乘以10000。 ?“=SUMPRODUCT((($B$2:$B$42=B2)* ($J$2:$J$42*10000+ROW($2:$42))>=(J2*10000+ROW()))*1)”中 ($B$2:$B$42=B2)数组是{1;0;0;0;1;„„}(等于B2的为1，否则为0)， ($J$2:$J$42*10000+ROW($2:$42))>=(J2*10000+ROW()))*1)也是由1和0组成的数组{1;0;0;0;1;„„}， 二个数组对应相乘，然后然后把乘积相加。 ?举一个简单的例子验证这个公式 7 图八 ($J$2:$J$10*10+ROW($2:$10))数组是{52;23;144;55;56;57;108;59;200} (J2*10+ROW())是52 ($J$2:$J$10*10+ROW($2:$10))>=(J2*10+ROW())数组是 {TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE} ($B$2:$B$10=B2) 数组是 {TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE} 那么，K2单元格的公式是{1;1;1;0;0;1;1;1;0}和数组 {1;0;1;0;0;1;1;1;0}的乘积然后相加， 即1*1+1*0+1*1+0*0+0*0+1*1+1*1+1*1+0*0=5。 6、班级总分后(前)N名的单科均分 图九 在U2单元格中 “=SUMPRODUCT((B$2:B$42=$N2)*($K$2:$K$42>(O$2-5))*($C$2:$C$42))/5” 实际是三个数组的所有元素对应相乘，然后把乘积相加，最后除以5(这里是 计算班级后5名均分，如果计算后10或20名均分只需把5改为10或20)。 (B$2:B$42=$N2)是由41个逻辑值(TRUE与FALSE)组成的数组。等于$N2(1 班)，逻辑值为TRUE，否则逻辑值为FALSE，TRUE与FALSE分别以1和0参与计 算。 ($K$2:$K$42>(O$2-5) 中O$2是班级人数，(O$2-5)是班级倒数第6名。 ($K$2:$K$42>(O$2-5)也是由41个逻辑值组成的数组。 ($C$2:$C$42))是C列(语文)的41个成绩组成的数组。 按照计算班级总分后N名的单科均分的方法，也可以计算班级总分前N名的 单科均分的均分。 四、学习收获 8 运用EXCEL在质量分析中进行数据分析，可以提高效率，事半功倍。但EXCEL函数多，语法复杂，即使是专业人员或者是高手也很难得把所有函数的语法记住。条条道路通罗马，学以致用，用才是目的。因此，在实际工作中，学好用活部分或者几个公式，对于初学者来说不能不说是提高兴趣的一个好方法。 以上有误之处恳请斧正。 9