SUMPRODUCT函数的应用：条件计数、求和和排序功能的使用介绍.doc

SUMPRODUCT函数可以方便地计算工作表内多列中对应值相乘后的和，其语法为：SUMPRODUCT(array1,array2,array3, )其中，Array1, array2, array3, 为 2 到 30 个数组，其相应元素需要进行相乘并求和。数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将返回错误值 #VALUE!。 该函数将非数值型的数组元素作为 0 处理。用法一：两个数组的所有元素对应相乘看一个例子就容易明白SUMPRODUCT的用法：A B C D （列号）1 数组1 数组1 数组2 数组2 （第1行）2 1 2 10 20 （第2行）3 3 4 30 40 （第3行）4 5 6 50 60 （第4行）公式： =SUMPRODUCT(A2:B4, C2:D4)说明：两个数组的所有元素对应相乘，然后把乘积相加，即 1*10 + 2*20 + 3*30 + 4*40 + 5*50 + 6*60(结果为910)用法二：多条件求和+求个数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列为工资）SUMPRODUCT(条件1*条件2*条件3.条件N)利用此函数进行多条件计数时，* ：满足所有条件；SUMPRODUCT(条件1+条件2+条件3.+条件N) + ：满足任一条件我找到了一个比较详细的解释=SUMPRODUCT($B2:$B26=F1)*($C2:$C26=20)*($C2:$C2630)=SUMPRODUCT(G1:G3=男)*(E1:E325)*1)3、汇总一班人员获奖次数=SUMPRODUCT(H265:H274=一班)*I265:I274)4、汇总一车间男性参保人数=SUMPRODUCT(G276:G284&H276:H284&I276:I284=一车间男是)*1)5、汇总所有车间人员工资=SUMPRODUCT(-NOT(ISERROR(FIND(车间,G286:G294),I286:I294)6、汇总业务员业绩=SUMPRODUCT(H296:H305=江西,广东)*(I296:I305=男)*J296:J305)使用注意：1、本例公式也可以不使用数组，改用+连接两个条件，公式如下:=SUMPRODUCT(H296:H305=江西)+(H296:H305=广东)*(I296:I305=男)*J296:J305)。2、公式中“+”连接的条件表示满足任意条件就求和，而“*”连接的条件则表示同时满足所有条件才求和。1、计算男性人数：=SUMPRODUCT(B2:B13=男)*1) 2、多条件求和，求男性及格人数：=SUMPRODUCT(C2:C13=60)*1,(B2:B13=男)*1) 3、汇总编号第一个字符为A的成绩总数=SUMPRODUCT(A2:A13=A*)*C2:C13) 注意：也可以将两个数组分成两个参数，但是第一参数需要利用*1或者其他方式将逻辑值转换成数值=SUMPRODUCT(B2:B13=男)*1,C2:C13) 4、多条件求和，汇总三班籍贯为浙男性人数：=SUMPRODUCT(B2:B13&D2:D13:C2:C13=男三班=60)*1) 注意：本条也可用如下方式实现=SUMPRODUCT(B2:B13=男)*1,(D2:D13=三班)*1,(E2:E13=浙)*1) 5、汇总所有编号包含A的学生成绩：=SUMPRODUCT(-NOT(ISERROR(FIND(A,A2:A13),C2:C13) 注意：SUMPRODUCT函数不支持通配符。 说明：FIND函数在A2:A13区间查找包含A的编号，如果找到则运算结果为一个数值标识该单元格的位置，如果找不到将长生一个错误值，再使用NOT(ISERROR()来判断哪些单元格包含A，得到一个由TRUE和FALSE组成的数组，再用-将这组逻辑值转换成数值，最后与C2:C13相乘得出汇总值。本例中按类别统计了销售记录表，此时需要统计出女式连衣裙和女式职业装两类的销售金额，我们可以直接使用sumproduct函数来实现。如下图所示。选中E8单元格，输入公式：=SUMPRODUCT(B2:B19=女式连衣裙)+(B2:B19=女式职业装),$C$2:$C$19) 用法三：实现有条件排名全市三所学校各个专业的学生成绩都放到了一个工作表中，格式如图1所示。为了做好成绩分析，主任要求做好两个排名：一是排出每位学生在全市相同专业的学生中的名次；二是排出每位学生在本校本专业中的名次；两个排名都以总分为依据。图1(点击看大图)使用了SUMPRODUCT函数来完成这个有条件的排名工作。具体实现过程如下：一、准备工作选定总分所在的H2:H1032单元格区域，点击功能区“公式”选项卡“定义的名称”功能组中“定义名称”按钮，在弹出的“新建名称”对话框“名称”输入框中输入为此区域定义的名称“zongfen”。此时，对话框下方的“引用位置”后的输入框中已经自动输入我们选定的单元格区域“=对口!$H$2:$H$1032”，如图2所示。图2按同样的方法，选定学校所在单元格区域I2:I1032、专业所在单元格区域J2:J1032，分别为它们指定名称“xuexiao”和“zhuanye”。完成后，这准备工作就算是结束了。二、排定名次在K1单元格输入标题“按专业排名”。点击K2单元格，输入公式“=SUMPRODUCT(zhuanye=$J2)*($H2zongfen)+1”，按下回车键，结果出来了吧？向下拖动其填充句柄至最后一行，OK，按专业排名就算完成了。在L1单元格输入标题