sumproduct函数计数|求和|查找|排名详解

　　本站站长擅长用python自动处理excel，请多关注本站。

　　第一部分，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:E33>15表示。第二，男性，用C4:C33="男"表示。

　　套用SUMPRODUCT((条件1)*(条件2)*(条件3)*...)，得到公式：=SUMPRODUCT((E4:E33>15)*(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:E33>15)*(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$31>J26))+1,下拉。思路就是如果区域中有几个大于本身，目标值就是大于的所有数+1。

很赞哦！

python编程网提示：转载请注明来源www.python66.com。
有宝贵意见可添加站长微信(底部)，获取技术资料请到公众号(底部)。同行交流请加群