奔牛网>office>Excel应用>>关于excel sumproduct 多条件统计(多条件计数)的用法
2017年08月10日

关于excel sumproduct 多条件统计(多条件计数)的用法

任何一门学问都是无止境的,曾经我就写过过关于sumproduct的多条件求和的用法,然而没想到的是还有更多的用法,可见excel的博大精深,只有自己用过才会学的更多.

 

情况大概是这样,我需要计算每个同事的加班餐费,而餐费的计算没有一个统一的标准,必须按照公司的规章制度来计算,条件是平时加班大于4小时餐费补贴是5.5元,节假日8小时以内,按1.75元/小时计算,大于8小时则定义14元,大于12小时则为19.5元.

 

我用excel解决问题的原则是能用公式就用公式,其次才会考虑些程序来解决.解决的过程思考了一段时间,因为如果简单的sumproduct多条件求和,显然是不能达到要求的,因为即使我根据条件去计算加班时数,可是只有节假日的小于9小时的才需要统计求和时间乘1.75得到,其它的都是按加班时间来划分的,这部分时间是统计满足条件的次数,那么我自然想到了多条件统计次数.


大家可能都熟悉countif()函数的统计,但这个函数只允许一个条件,幸运的2007版本以后创造了countifs(),这个是适合2007版本,不能再低于这个版本的excel中正确显示结果,你不能去要求所有人为了自己而去升级excel.

 

索性就在网上找了起来,后来根据提示,发现sumproduct是可以多条件统计次数的,比如平时的多条件求和公式 SUMPRODUCT((($N$4:$AR$4="G2")+($N$4:$AR$4="G3"))*(N5:AR5<=8),N5:AR5), 当我完全再去理解这个公式的计算逻辑的时候,去掉了公式的部分内容SUMPRODUCT((($N$4:$AR$4="G2")+ ($N$4:$AR$4="G3"))*(N5:AR5<=8)),把,N5:AR5这个去掉了,结果发现这样的公式结果就是统计满足条件的计数,那么问题就可以完全解决了.

 

完整的公式是: =SUMPRODUCT(($N$4:$AR$4="G1")*(N5:AR5>=4))*5.5+SUMPRODUCT((($N$4:$AR$4="G2")+($N$4:$AR$4="G3"))*(N5:AR5<=8),N5:AR5)*1.75+SUMPRODUCT((($N$4:$AR$4="G2")+($N$4:$AR$4="G3"))*(N5:AR5>8)*(N5:AR5<12))*14+SUMPRODUCT((($N$4:$AR$4="G2")+($N$4:$AR$4="G3"))*(N5:AR5>=12))*19.5

 

其中G1 \G2\G3就是定义的节假日还是平时加班,这个计算方式在低版本excel中同样可以正确显示结果,不过对countifs()大家还是有必要学习,毕竟excel版本都会升级的,到时知道这个函数,相关运用也会方便些.

顶(0)
踩(0)
最新评论