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

excel 教程 - 五月 4, 2011 - 来源:单点日志 - 1 Comment -

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

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

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

单点日志: http://spoint.babyshoot.cn(本文原创,转载请保留出处)

大家可能都熟悉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版本都会升级的,到时知道这个函数,相关运用也会方便些.

单点日志: http://spoint.babyshoot.cn(本文原创,转载请保留出处)



无觅相关文章插件,快速提升流量

《关于excel sumproduct 多条件统计(多条件计数)的用法》由单点日志原创提供!
转载请注明:http://spoint.babyshoot.cn/archives/2011/05/excel-sumproduct.html

1 Comment│赶紧发话! »

发表评论

您必须登录后才能发表评论。
8