前言 在看下面的文章之前先写几句 1、SUMPRODUCT函数很强大,在大部分的工作中都会用到这个函数 2、SUMPRODUCT函数使用的频率非常高,使用人数也非常广 3、SUMPRODUCT函数名字很长,而且第一眼感觉应该是很难,不容易学习和掌握 我列出上面的三点其实是想告诉你:SUMPRODUCT很好很强大,真的需要你用心去学习,而且也非常容易学习。 因此在接下来的文章中我会尽量用简洁的语言把SUMPRODUCT函数的强大、魅力展示给大家。 ============== 1、SUMIMPORT函数的功能 首先说一下SUMIMPORT的作用:对输入的多个参数(Range选区、数组)进行相乘,然后对相乘后的各个项求和,最后返回求和的结果。 另外,从字面上可以看出,sumproduct有两个英文单词组成,sum是和,product是积,所以是乘积之和的意思 2、SUMPRODUCT语法 = SUMPRODUCT(array1,[array2],…) 其中: array1、array2、...等为要输入参数。 返回的结果为:对输入的array1、array2、...进行相乘,然后对相乘之后得出的所有项相加,最后返回相加的结果 在使用的时候有以下几点需要说明: 1、函数的参数必须为数组,即Range选区、数组; 2、最多只能输入30个数组,且所有的数组都必须有相同的维数; 3、数组中的所有非数值内容都会按0来处理 3、如果只输入1个数组,就直接对这个数组里面的所有项相加,然后返回相加的结果。 上面就是对SUMPRODUCT的介绍,下面开始用例子一步一步让你体会到它的强大 3、根据单个条件进行计数、求和操作 员工销售表
有多少个员工卖出了小米(也就是统计小米手机对应多少条记录): =SUMPRODUCT(--(B2:B14="小米")) 小米手机的销售总额: =SUMPRODUCT(--(B2:B14="小米"),C2:C14) 当然用Excel的其它函数也是可以完成相同功能的,如:COUNTIF、SUMIF等,由于我们这次主要介绍SUMPRODUCT,就不再说其它函数了。 下面我们来一步一步的进行解释 a、首先我们要查找的对象为“小米”手机,因此需要对B列所有数据执行比较操作,即:B2:B14="小米" b、我们知道B2:B14是一个数组(即Range范围),当对数组执行比较操作时,其实是对数组中的每一项执行比较操作,因此B2:B14="小米"执行的结果为
c、可以通过上图看到,最后一列有3个TRUE,10个FALSE。 d、由于TRUE、FALSE是逻辑值,在SUMPRODUCT中直接使用的话是被当作0来处理的。因此需要前面加“--”以转为相应的1、0数值。 “--”符号用于把TRUE、FALSE转为1、0数值(其实是两个负号,也就是常说的减号,“负负得正”就是这个意思)。这也是常用的一个技巧,一定要牢记并理解和熟练运用
现在对应的Excel数据为
对于有多少个员工卖出了小米得计算过程为:
对于小米手机的销售总额:
4、满足多个条件求和操作
容麽麽小米手机总共卖多少钱: ==SUMPRODUCT(--(A2:A14="容麽麽"),--(B2:B14="小米"),C2:C14) 这次SUMPRODUCT有三个参数,前两个分别为姓名条件、和手机条件,最后一列为销售金额,执行后对应的表格数据实际为
这三列数据相乘,最后把结果相加即得到:3200 其实这三个参数的位置无所谓, 可以是:=SUMPRODUCT(--(B2:B14="小米"),--(A2:A14="容麽麽"),C2:C14) 也可以是:=SUMPRODUCT(C2:C14,--(B2:B14="小米"),--(A2:A14="容麽麽")) 另外除了上面这种输入三个参数外,还可以只输入一个参数就能得到相同的结果: =SUMPRODUCT((A2:A14="容麽麽")*(B2:B14="小米")*C2:C14) 和上面的对比一下发现有什么区别吗, 最开始已经说过,SUMPRODUCT的功能就是对输入的多个参数相乘,然后对相乘的结果相加求合。 因此,SUMPRODUCT(A,B,C)就等于SUMPRODUCT(A*B*C) (这里需要考虑数值转换) 5、满足任一条件求和操作
容麽麽和苏有朋总共销售额: =SUMPRODUCT((A2:A14="容麽麽")+(A2:A14="苏有朋"),C2:C14) 执行后对应的表格数据实际为
不知道大家找到找到规律没: 1、首先通过比较(相关、不等、大于、等于)得到一组TRUE、FALSE值 2、把TRUE、FALSE转换为1、0 3、对转换后的1、0进行各种操作 1x任何数=任何数(相当于保存这个数值) 0x任何数=0(相当于过滤这个数值) 6、总结: 1、TRUE、FALSE转为1、0("--":即负负得正) 2、灵活使用1、0的各种操作 3、SUMPRODUCT(A,B,C)就等于SUMPRODUCT(A*B*C) (这里需要考虑数值转换) 其实网上有很多的文章介绍Excel的函数,但都属于“授人以鱼 而不授人以渔” 因此对于重要的知识点或者技巧等怎样授人以渔都会写在总结里面 这篇只是基础入门,剩下还有3、5篇文章把高级技巧一一带给大家
|