EXCEL公式应用乘积之和公式应用.doc

第一部分：SUMPRODUCT函数用法介绍SUMPRODUCT是什么?其实结合英语就能很好的理解SUMPRODUCT函数，sum是和，product是积，结合起来就是乘积之和。Excel中SUMPRODUCT函数是一个数组类型的函数。很多时候可以用SUMPRODUCT函数取代SUM函数的数组公式，就不需要按三键结束。SUMPRODUCT函数能够计算多个区域的数值相乘后之和。SUMPRODUCT函数的用法就是在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。SUMPRODUCT函数的语法：SUMPRODUCT(array1,array2,array3, .)其中Array1, array2, array3, . 为 2 到 30 个数组，其相应元素需要进行相乘并求和。SUMPRODUCT函数使用需要注意三点：第一，数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将返回错误值 #VALUE!。第二，函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理。第三，如果是一个数组，那么就是对这个数组的求和。我们先通过一个简单的工作表数据来认识SUMPRODUCT函数。第一，输入公式：=SUMPRODUCT(A2:B4*C2:D4)，就是将A2:B4和C2:D4两个区域的所有元素对应相乘，然后把乘积相加，即3*2+4*7+8*6+6*7+1*5+9*3，得到结果为156。第二，输入公式：=SUMPRODUCT(A2:B4)，得到结果为31。根据上面的要点介绍，如果是一个数组，那么就是对这个数组的求和，因此就是对3,4;8,6;1,9这个区域求和。提示：两个数组相乘是同一行的对应两个数相乘。数组数据用大括号括起来，行数据之间用分号;分隔，如果是同一行的数据，用逗号,分隔。第二部分：SUMPRODUCT函数应用案例介绍下图所示的是咱们IT部落窝6群(群号：92798512)的随机抽查的人员资料表。为了演示方便，只是截取了部分数据。通过上面的数据，我们结合SUMPRODUCT函数的用法来完成以下应用案例。第一部分，SUMPRODUCT函数在计数中的应用。SUMPRODUCT函数用于多条件计数，计算符合2个及以上条件的数据个数。有一个经典公式计数：SUMPRODUCT(条件1)*(条件2)*(条件3)*.)第一，统计C列性别列中女性有几个人。此题为单条件求和。首先要知道条件是什么，(C4:C33=女)区域中等于女的，这部分就是条件。如果直接输入=SUMPRODUCT(C4:C33=女)，得到结果为0。第一部分用法介绍里介绍：函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理，C4:C33=女，按F9键得到执行结果是true、false形式的逻辑值，所以等于0。那如何把逻辑值转换为数值呢，就要让逻辑值参加运算，可以用-，*1，+0等等。比如，=TRUE*1，结果为1。=FALSE*1，结果为0。因此在(C4:C33=女)外面加上-就可以了。公式为：=SUMPRODUCT(-(C4:C33=女)，结果为9人。第二，求E列潜水天数大于15天的男性有几人。此题有两个条件：第一，大于15天，用E4:E3315表示。第二，男性，用C4:C33=男表示。套用SUMPRODUCT(条件1)*(条件2)*(条件3)*.)，得到公式：=SUMPRODUCT(E4:E3315)*(C4:C33=男)，结果为8人。第三，统计2月份发言的男性有几人。D列最后发言时间有1月和2月的份。统计2月份，需要用到month函数来求月份。比如A1单元格：2011-2-25，A2单元格输入公式：=MONTH(A1)，返回2。还是套用SUMPRODUCT(条件1)*(条件2)*(条件3)*.)，得到公式：=SUMPRODUCT(MONTH(D4:D33)=2)*(C4:C33=男)，结果为16人。第四，统计不包括笑看今朝的男性有多少人。不包括，就是不等于，是表示。公式为：=SUMPRODUCT(A4:A33笑看今朝)*(C4:C33=男)，结果为20人。第五，统计有几个人的潜水天数是不一样的。比如，E列潜水天数为6的有6次，为5的有2次。如何让每个数字只出现一次呢?计数用countif函数。如何让每个数只计算一次呢，可以使用1/countif。比如5出现两次，就是两个1/2，最后汇总就得到1。最后再套用公式sumproduct(1/countif(区域，区域)。公式为：=SUMPRODUCT(1/COUNTIF(E4:E33,E4:E33) ，结果为14人。第二部分，SUMPRODUCT函数在求和中的应用。用函数SUMPRODUCT求和，函数需要的参数一个是进行判断的条件，另一个是用来求和的数据区域。SUMPRODUCT函数求和应用有一个经典的套用格式：SUMPRODUCT(条件1)*(条件2)*(条件3)*求和区域)第一，女性潜水总天数计算。套用格式，得到公式：=SUMPRODUCT(C4:C33=女)*E4:E33)，结果为134。第二，潜水时间大于15天的男性的潜水天数计算。套用格式，得到公式：=SUMPRODUCT(E4:E3315)*(C4:C33=男)*E4:E33)，结果为242。第三，2月份发言的男性的潜水天数计算。套用格式，得到公式：=SUMPRODUCT(MONTH(D4:D33)=2)*(C4:C33=男)*E4:E33)，结果为159。第四，QQ号首位是8的人的潜水天数计算。首先用left函数提取首位是8的，因为left提取的是文本，加就成了文本。然后套用格式，得到公式：=SUMPRODUCT(LEFT(B4:B33)=8)*E4:E33)，结果为77。第五，姓名字符数为2，不包括“月亮”的人的潜水天数计算。公式为：=SUMPRODUCT(LEN(A4:A33)=2)*(A4:A33月亮)*E4:E33)，得到结果：92。第六，“笑看今朝”和 “冷逸”的潜水天数计算。公式为：=SUMPRODUCT(A4:A33=笑看今朝,冷逸)*E4:E33)，得到结果13。公式中两个条件，可以这样写：笑看今朝,冷逸。第三部分，SUMPRODUCT函数在查找及排名中的应用。如下图所示，需要求出姓名列的潜水天数已经排名情况。在以前的讲座详细介绍了vlookup函数实现查找的方法，在本讲座中用sumproduct函数取代。利用单条件求和的特点来查找。根据前面的介绍，套用格式=SUMPRODUCT(姓名=I26)*天数)，得到公式：=SUMPRODUCT($A$4:$A$33=I26)*$E$4:$E$33)，然后下拉即可完成。在以往排名我们使用rank函数，不过我们也可使用SUMPRODUCT函数来完成。在K26输入公式：=SUMPRODUCT(-($J$26:$J$31J26)+1,下拉。思路就是如果区域中有几个大于本身，目标值就是大于的所有数+1。Excel中product是什么函数，一些朋友会忽略还有此函数。product函数就是用来将所有以参数形式给出的数字相乘，并返回最终的乘积值。有很多时候我们会直接使用四则运算的乘*号来代替product函数，但是在一些稍复杂的计算中，还是用PRODUCT函数来计算会更加方便。首先看看product函数的用法：PRODUCT（数1，数2，数30）为什么要到数30呢，因为PRODUCT函数最多可以有30个参数，如果超过30个参数的话，PRODUCT函数就会出错。PRODUCT函数在使用时需要注意：当参数为数字、逻辑值或数字的文字型表达式时可以被计算；当参数为错误值或是不能转换成数字的文字时，将导致错误。如果参数为数组或引用，只有其中的数字将被计算。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。文字说明总是枯燥的，下面我们以几个小实例来认识理解product函数。product函数实例一：利用product函数来求阶乘。比如要求1*2*3*4*5*6*7*8*9*10的乘积。可以使用数组公式：=PRODUCT(ROW(1:10)，结果为：3628800。公式解析：ROW(1:10)，用F9键可以看到结果为：1;2;3;4;5;6;7;8;9;10，也就是用PRODUCT函数来求这些数的乘积，相当于：=1*2*3*4*5*6*7*8*9*10的结果3628800。product函数实例二：取代PERMUT函数Excel中PERMUT函数可以用来计算从给定数目的对象集合中选取的若干对象的排列数。PERMUT(N,M) 可以来计算从N个数中选取M个数的排列数（N=M），其实这样的算法也可以使用product函数来实现。如下图：C2公式为：=PERMUT(A2,B2)，D2公式为数组公式：=PRODUCT(ROW(INDIRECT(A2-B2)+1&：&A2)。PERMUT函数和product函数都可以得到最终的结果。product函数实例三：取代combin函数Excel中combin函数是用来计算从给定数目的对象集合中提取若干对象的组合数，combin(N,M) N=M。用product函数也可以来实现，如下图：C2公式为：=COMBIN(A2,B2)，D2公式为数组公式：=PRODUCT(ROW(INDIRECT(A2-B2)+1&：&A2)/PRODUCT(ROW(INDIRECT(1:&B2)combin函数和product函数都可以得到最终的结果。product函数实例四：计算金额。上图所示：如果计算A列乘以B列，除了直接使用=A2*B2以外，如果是使用product函数来写公式，需要稍微转换，公式为：=PRODUCT(A2*1,B2*1)，如果直接使用：=PRODUCT(A2,B2)，得到结果为1，显然是不对的，因为单价都没有，结果应该为0。product函数实例五：活期利息计算如下图所示：D3单元格公式为： =PRODUCT(A3,B3,C3)，下拉复制。通过上面五个小小的实例，应该会使用product函数了。第一部分：迭代计算的概念EXCEL里的迭代计算是什么意思?要解决迭代问题，先来看看循环引用。什么是循环引用呢?在Excel中，如果公式引用自己所在的单元格，不论是直接还是间接的，都称为循环引用。只要打开的工作簿中有一个包含循环引用，Microsoft Excel 都将无法自动计算所有打开的工作簿。这时可取消循环引用，或利用先前的迭代计算结果计算循环引用中的每个单元格引用。如果不改变默认的迭代设置，Excel 将在 100 次迭代后或在两次相邻迭代得到的数值的变化小于 0.001 时，停止迭代运算。通过更改Excel 迭代计算公式的次数创建一个循环引用。Excel中迭代计算的设置如下介绍：第一，Excel2003： “工具”菜单上“选项”“重新计算”选项卡选中“迭代计算”复选框。第二，在Excel2007：单击office按钮“Excel选项“公式”选项卡选中“启用迭代计算”复选框。若要设置Excel 进行重新计算的最大次数，请在“最多迭代次数”框中键入迭代次数。迭代次数越高，Excel 用于计算工作表的时间越多。若要设置两次迭代结果之间可以接受的最大误差，请在“最大误差”框中键入所需的数值。数值越小，结果越精确，相应的Excel 用于计算工作表的时间也越多。第二，迭代计算的实例参考：A列输入数据，B列自动自动填充日期和时间IT部落窝小编下面通过一个实例给大家分享一下迭代计算的使用。我们在工作表中，当在A列输入数据后，要实现的结果是：在B列自动输入当前日期和时间。迭代计算实例操作步骤如下：首先设置excel的迭代计算，然后在B2单元格输入公式：=IF(A2=,IF(B2=,NOW(),B2)，并向下复制公式到B列下面的单元格即可。公式设置好后，当我们在A列输入数据后，B列会自动添加日期和时间。公式解析：公式的含义是：如果单元格A2中没有数据，那么在单元格B2中就不会输入任何数据，如果单元格A2中有数据，就会检查B2单元格中是否有数据(也就是自我检查)，如果B2单元格没有数据，就在单元格B2中输入当前的日期和时间，否则就保留原始数据部变。公式中使用第二个IF函数是为了确保函数NOW不被重算。分享题目：SUMPRODUCT函数的应用主讲老师：卢子老师第一部分：SUMPRODUCT函数用法介绍SUMPRODUCT是什么?其实结合英语就能很好的理解SUMPRODUCT函数，sum是和，product是积，结合起来就是乘积之和。Excel中SUMPRODUCT函数是一个数组类型的函数。很多时候可以用SUMPRODUCT函数取代SUM函数的数组公式，就不需要按三键结束。SUMPRODUCT函数能够计算多个区域的数值相乘后之和。SUMPRODUCT函数的用法就是在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。SUMPRODUCT函数的语法：SUMPRODUCT(array1,array2,array3, .)其中Array1, array2, array3, . 为 2 到 30 个数组，其相应元素需要进行相乘并求和。SUMPRODUCT函数使用需要注意三点：第一，数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将返回错误值 #VALUE!。第二，函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理。第三，如果是一个数组，那么就是对这个数组的求和。我们先通过一个简单的工作表数据来认识SUMPRODUCT函数。第一，输入公式：=SUMPRODUCT(A2:B4*C2:D4)，就是将A2:B4和C2:D4两个区域的所有元素对应相乘，然后把乘积相加，即3*2+4*7+8*6+6*7+1*5+9*3，得到结果为156。第二，输入公式：=SUMPRODUCT(A2:B4)，得到结果为31。根据上面的要点介绍，如果是一个数组，那么就是对这个数组的求和，因此就是对3,4;8,6;1,9这个区域求和。提示：两个数组相乘是同一行的对应两个数相乘。数组数据用大括号括起来，行数据之间用分号;分隔，如果是同一行的数据，用逗号,分隔。第二部分：SUMPRODUCT函数应用案例介绍下图所示的是咱们IT部落窝6群(群号：92798512)的随机抽查的人员资料表。为了演示方便，只是截取了部分数据。通过上面的数据，我们结合SUMPRODUCT函数的用法来完成以下应用案例。第一部分，SUMPRODUCT函数在计数中的应用。SUMPRODUCT函数用于多条件计数，计算符合2个及以上条件的数据个数。有一个经典公式计数：SUMPRODUCT(条件1)*(条件2)*(条件3)*.)第一，统计C列性别列中女性有几个人。此题为单条件求和。首先要知道条件是什么，(C4:C33=女)区域中等于女的，这部分就是条件。如果直接输入=SUMPRODUCT(C4:C33=女)，得到结果为0。第一部分用法介绍里介绍：函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理，C4:C33=女，按F9键得到执行结果是true、false形式的逻辑值，所以等于0。那如何把逻辑值转换为数值呢，就要让逻辑值参加运算，可以用-，*1，+0等等。比如，=TRUE*1，结果为1。=FALSE*1，结果为0。因此在(C4:C33=女)外面加上-就可以了。公式为：=SUMPRODUCT(-(C4:C33=女)，结果为9人。第二，求E列潜水天数大于15天的男性有几人。此题有两个条件：第一，大于15天，用E4:E3315表示。第二，男性，用C4:C33=男表示。套用SUMPRODUCT(条件1)*(条件2)*(条件3)*.)，得到公式：=SUMPRODUCT(E4:E3315)*(C4:C33=男)，结果为8人。第三，统计2月份发言的男性有几人。D列最后发言时间有1月和2月的份。统计2月份，需要用到month函数来求月份。比如A1单元格：2011-2-25，A2单元格输入公式：=MONTH(A1)，返回2。还是套用SUMPRODUCT(条件1)*(条件2)*(条件3)*.)，得到公式：=SUMPRODUCT(MONTH(D4:D33)=2)*(C4:C33=男)，结果为16人。第四，统计不包括笑看今朝的男性有多少人。不包括，就是不等于，是表示。公式为：=SUMPRODUCT(A4:A33笑看今朝)*(C4:C33=男)，结果为20人。第五，统计有几个人的潜水天数是不一样的。比如，E列潜水天数为6的有6次，为5的有2次。如何让每个数字只出现一次呢?计数用countif函数。如何让每个数只计算一次呢，可以使用1/countif。比如5出现两次，就是两个1/2，最后汇总就得到1。最后再套用公式sumproduct(1/countif(区域，区域)。公式为：=SUMPRODUCT(1/COUNTIF(E4:E33,E4:E33) ，结果为14人。第二部分，SUMPRODUCT函数在求和中的应用。用函数SUMPRODUCT求和，函数需要的参数一个是进行判断的条件，另一个是用来求和的数据区域。SUMPRODUCT函数求和应用有一个经典的套用格式：SUMPRODUCT(条件1)*(条件2)*(条件3)*求和区域)第一，女性潜水总天数计算。套用格式，得到公式：=SUMPRODUCT(C4:C33=女)*E4:E33)，结果为134。第二，潜水时间大于15天的男性的潜水天数计算。套用格式，得到公式：=SUMPRODUCT(E4:E3315)*(C4:C33=男)*E4:E33)，结果为242。第三，2月份发言的男性的潜水天数计算。套用格式，得到公式：=SUMPRODUCT(MONTH(D4:D33)=2)*(C4:C33=男)*E4:E33)，结果为159。第四，QQ号首位是8的人的潜水天数计算。首先用left函数提取首位是8的，因为left提取的是文本，加就成了文本。然后套用格式，得到公式：=SUMPRODUCT(LEFT(B4:B33)=8)*E4:E33)，结果为77。第五，姓名字符数为2，不包括“月亮”的人的潜水天数计算。公式为：=SUMPRODUCT(LEN(A4:A33)=2)*(A4:A33月亮)*E4:E33)，得到结果：92。第六，“笑看今朝”和 “冷逸”的潜水天数计算。公式为：=SUMPRODUCT(A4:A33=笑看今朝,冷逸)*E4:E33)，得到结果13。公式中两个条件，可以这样写：笑看今朝,冷逸。第三部分，SUMPRODUCT函数在查找及排名中的应用。如下图所示，需要求出姓名列的潜水天数已经排名情况。在以前的讲座详细介绍了vlookup函数实现查找的方法，在本讲座中用sumproduct函数取代。利用单条件求和的特点来查找。根据前面的介绍，套用格式=SUMPRODUCT(姓名=I26)*天数)，得到公式：=SUMPRODUCT($A$4:$A$33=I26)*$E$4:$E$33)，然后下拉即可完成。sumifs函数多条件求和实例2011-12-07 13:18:48 来源：IT部落窝 浏览：2092次内容提要：文章首先介绍sumifs函数基本用法，然后以一个综合的实例来剖析sumifs函数的详细深入使用。-第一部分，sumifs函数用法介绍excel中sumifs函数是2007以后版本新增的多条件求和函数。sumifs函数的语法是：SUMIFS(求和区域, 条件区域1,条件1, 条件区域2,条件2, .) 说明：以内的条件区域2、条件2为可选参数。最多允许 127 个区域/条件对。 第二部分，sumifs函数实例介绍项目一：客户A的销售额=SUMIFS(C2:C10,A2:A10,A2)项目二：客户A的1月份销售额=SUMIFS(C2:C10,A2:A10,A2,B2:B10,B2)项目三：客户A的1月份和3月份销售额=SUM(SUMIFS(C2:C10,A2:A10,A2,B2:B10,1,3)项目四：客户A和C的销售额=SUM(SUMIFS(C2:C10,A2:A10,A,C)项目五：客户A和C的1月份销售额合计=SUM(SUMIFS(C2:C10,A2:A10,A,C,B2:B10,B2)项目六：客户A的1月份和客户C的3月份销售额合计=SUM(SUMIFS(C2:C10,A2:A10,A,C,B2:B10,1,3)项目七：客户A和客户C的1月份3月份4月份销售额合计=SUM(SUMIFS(C2:C10,A2:A10,A,C,B2:B10,1;3;4)项目八：客户ABC的1月份3月份4月份销售额合计=SUM(SUMIFS(C2:C10,A2:A10,A,B,C,B2:B10,1;3;4)Sumifs函数与SumProduct函数都可以实现多条件求和。经测试，10000行数据，双条件求和，Sumifs函数比SumProduct快差不多三倍。第一部分：excel sumif函数的用法介绍Excel中sumif函数的用法是根据指定条件对若干单元格、区域或引用求和。sumif函数语法是：SUMIF(range，criteria，sum_range) sumif函数的参数如下：第一个参数：Range为条件区域，用于条件判断的单元格区域。第二个参数：Criteria是求和条件，由数字、逻辑表达式等组成的判定条件。第三个参数：Sum_range 为实际求和区域，需要求和的单元格、区域或引用。 当省略第三个参数时，则条件区域就是实际求和区域。criteria 参数中使用通配符（包括问号 (?) 和星号 (*)）。问号匹配任意单个字符；星号匹配任意一串字符。如果要查找实际的问号或星号，请在该字符前键入波形符 ()。 第二部分：excel sumif函数的实例介绍实例：求报表中各栏目的总流量结果如下图所示。选中F2单元格，输入公式：=SUMIF(B2:B19,E2,C2:C19)，输入公式完成后，按键盘上ctrl+shift+enter组合键（必须按此ctrl+shift+enter组合键数组公式才能得到正确结果），即可统计出办公软件栏目的总流量。以此类推，选中F3单元格，输入公式：=SUMIF(B2:B19,E3,C2:C19)，输入公式完成后，按键盘上ctrl+shift+enter组合键，可以求得网站运营栏目的总流量。选中F4单元格，输入公式：=SUMIF(B2:B19,E4,C2:C19)，输入公式完成后，按键盘上ctrl+shift+enter组合键，可以求得电脑医院栏目的总流量。选中F5单元格，输入公式：=SUMIF(