【温馨提示】小伙伴们,阅读之前请您点击【关注】,您的支持将是我最大的动力!
今天在分享案例之前给小伙伴分享一段人生感悟,希望能发挥正能量。突然发现跟思政进课堂活动有异曲同工之意。
跟恋人讲道理,是不想爱了;跟老婆讲道理,是不想过了;跟同事讲道理,是不想混了;跟上司讲道理,是不想干了……人生在世,有许多地方是没道理可讲。
案例16 计算参保人数( SUMPRODUCT )
打开数据文件,在单元格E2中输入如下公式:
=SUMPRODUCT((C2:C11="是")*1)
按下[Enter]键后,公式将返回所有参保人数,结果如图1.22 所示。
公式说明:
SUMPRODUCT函数的功能是对给定的几组数组间对应的元素相乘,并返回乘
积之和。如果只有一-组 数据则直接返回该数组之和。
本例中SUMPRODUCT函数只有一个数组参数一- C2:C11。 C2:C11 属于区域
引用,而“C2:C11="是””的计算结果是由True和False组成的内存数组,“(C2:C11=
"是")*1”则可以生成1和0组成的内存数组,SUMPRODUCT函数的作用就是将
这些1和0汇总。
使用注意:
(1) SUMPRODUCT的参数可以是1~ 255个。如果只有1个参数则返回该数组的数值之和,如果有多组参数则将所有数组对应位置的值相乘,然后再将乘积汇总,所以SUMPRODUCT函数的功能其实是计算多组数据的乘积之和。
(2)SUMPRODUCT如果有多个数组参数,各数组的高度与宽度必须相等,例如,
第一参数是A1:A10,第二参数就不能是B1:B9或者C5:C30。
(3)如果函数SUMPRODUCT的参数中有非数值型数据,函数在计算时会
将非数值型的数组元素作为0处理。函数SUMPRODUCT的参数中如果有逻辑值TRUE或者False也都当作0处理,通常使用“*1”、“”或者N函数将逻辑值True转换成1、将False转换成0再参与下一步运算。如果不用“*]”,本例公式也可以修改为:
=SUMPRODUCT(N(C2:C11="是")或者-SUMPRODUC-((C2:C11="是"))
公式中N的作用是将数组中的逻辑值转换成数值。
案例链接:
思考:汇总图1.22中人事部人员的数量。
提示:相对于本例公式,将参数“=是”修改为“=”人事部“”即可。
案例 17 对一车间男性职工的工资求和( SUMPRODUCT )
打开数据文件,在单元格F2中输入如下公式:
=SUMPRODUCT(B2:B10="一车间")*(C2:C10="男")*D2:D10)
按下[Enter]键后,公式的统计结果如图1.23所示。
公式说明:
本公式以B、C两列数据作为限制条件对D列的数据汇总。”“(B2:B10="- 车
间")*(C2:C10="男")”表示同时满足两个条件才参与求和,如果还有更多条件也可
以一并罗列出来,利用“*”连接然后再与求和区域相乘,从而得到最后的汇总结果。
使用注意:
(1) SUMPRODUCT函数的功能大于SUM函数,一切能用SUM函数的地方
都可以使用SUMPRODUCT函数。当使用了SUM函数且必须按[Ctrl Shift Enter]
组合键才能得到结果时,通常会采用SUMPRODUCT函数替代SUM函数,从而简
化输入方式。换言之,本例中将SUMPRODUCT改成SUM也能得到正确结果,只
是需要输入公式后按[Ctrl Shift Enter]组合键。
(2)本例中“B2:B10="-.车间””的计算结果是True和False组成的数组,
当B2:B10中某个单元格等于“一车间”时计算结果是True,否则等于False。“C2:C10=
”男"”的计算结果也是True和False组成的数组,两个数组相乘将生成一个由I和
0组成的新数组。其中True乘以True等于1,True乘以False或者False乘以False
都等于0。
(3)双条件求和时,条件引用区域与求和区域可以在不同列,三个区域只需要
宽度、高度一致即可,它们所在的列允许不同,起止行也允许不同,甚至三个区域
分别位于不同的工作表中仍然可以得到需要的结果。
例如,条件区域在Sheet2工作表,求和区域在当前表,公式可以修改为:
=SUM((Sheet2!B2:B10="车间 ")*(Sheet2!C2:C10=" 男")*D2:D10)
案例链接:
思考:求一车间和三车间的女职工工资总和。
提示:相对于本例公式,将“一车间“”修改为“{" -车间","三车间"}”,
将“男”修改为“女”,然后再在外面加个SUM 函数即可。
今天就分享到这里里,小伙伴们,在使用Excel中还碰到过哪些问题,评论区留言一起讨论学习,坚持原创不易,您的点赞、转发、就是对文老师最大的支持,谢谢啦!
每天学一点、每天进步一点、我们就会更幸福、更快乐。加油 加油 加油
关注我吧
美好的事情即将发生……