欢迎光临
我们一直在努力

阶段汇总,一条道跑到黑

 日常工作中,经常会有一些按不同阶段汇总的计算。比如下图所示,在一组销售记录中汇总本年度的销售额。

阶段汇总,一条道跑到黑插图



D4单元格用到的公式为:

=SUMPRODUCT((YEAR(A2:A13)=YEAR(TODAY()))*B2:B13)

公式分别使用YEAR函数计算A2:A13单元格中日期的年份,结果为一个由年份组成的内存数组。用YEAR(TODAY())计算当前日期的年份,结果为2015。

将A2:A13的年份与当前日期的年份进行判断,得到由逻辑值TRUE或是FALSE组成的内存数组,再用逻辑值与B列的销售计划相乘。

在数组运算中,TRUE相当于1,而FALSE相当于0,也就是用1或是0乘以B列的销售计划,最后使用SUMPRODUCT函数计算出乘积之和,结果为737。



如果是计算当前月的销售计划总额,只需要将公式中的计算年份的YEAR函数换成MONTH函数就可以了,其计算过程是一样的。



如下图所示,假如需要计算当前周的销售计划总额,该怎么计算呢?

这里的一周,规则就按照大家的日常习惯,起始日期为星期一,结束日期为星期日。

阶段汇总,一条道跑到黑插图(1)



D4单元格中用到的公式为:

=SUMPRODUCT((WEEKNUM(–A2:A13,2)=WEEKNUM(TODAY(),2))*B2:B13)

公式用WEEKNUM函数计算日期在该年的周数。第二个参数为“2”,表示一周的开始日期为周一。

“WEEKNUM(–A2:A13,2)”返回A列日期在该年的周数数组。

WEEKNUM函数的第一参数不能直接使用日期区域,假如使用WEEKNUM(A2:A13),公式将返回错误,但是用“–日期区域”、“1*日期区域”、“0+日期区域”等形式将区域引用变成内存数组引用,则可以突破这一限制,结果将返回一个包含周数的内存数组。

其他的计算过程就是和前面咱们说到的按年度计算是相同的。



这个例子中,只能处理所有日期都是在一个年份中的情况,如果有不同年份的数据,则需要用遍历的方法来计算了:

=SUMPRODUCT(SUMIF(A2:A13,"="&(TODAY()-WEEKDAY(TODAY(),2)+ROW(1:7)),B2:B13))

WEEKDAY(TODAY(),2)部分,计算当前的星期值,用当前日期TODAY()减去当前的星期值,就是上一个周日的日期。再分别加上ROW(1:7),得到本周一至本周日的日期值。

接下来使用SUMIF函数,以A2:A13作为条件区域,分别以本周一至本周日的日期值作为求和条件,计算B2:B13单元格区域中与之对应的销售计划额,得到一个内存数组:

{80;83;77;80;0;88;80}

再用SUMPRODUCT函数计算出乘积之和。



示例文件下载http://pan.baidu.com/s/1dDwdtz7

 收藏 (0) 打赏

您可以选择一种方式赞助本站

支付宝扫一扫赞助

微信钱包扫描赞助

分享到: 生成海报