Excel教程下载和软件下载中心,Microsoft技术社区联盟成员,全球领先的Excel门户,Office技术培训的最佳社区

阶段汇总,一条道跑到黑

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


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函数就可以了,其计算过程是一样的。

如下图所示,假如需要计算当前周的销售计划总额,该怎么计算呢?
这里的一周,规则就按照大家的日常习惯,起始日期为星期一,结束日期为星期日。


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

转载请注明来源"ExcelHome"并保留原文链接。
固定链接:http://www.excelhome.net/lesson/article/excel/1674.html
分享到:
Copyright 1999 - 2017 Excel Home.All Rights Reserved.
本站特聘法律顾问:徐怀玉律师 李志群律师   沪ICP备11019229号

沪公网安备 31011702000001号

征信