二维码 购物车
部落窝在线教育欢迎您!

Excel中如何用函数公式按季度或按周求和?

 

作者:逍遥来源:部落窝教育发布时间:2023-05-19 13:03:53点击:2533

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

Excel中如何用函数公式按季度求和?如何用函数公式按周数求和?这些问题实际都是以日期为条件的求和。

Excel中如何用函数公式按季度求和?如何按周求和?它的本质就是以日期为条件的求和,譬如求第2季度或第5周的销量,就是统计销售日期属于第2季度或者第5周里的销量。与之类似的按月、年求和。

1. 用函数公式按季度求和方法

第一种:把季度具化为起止日期进行多条件求和

如下图所示,我们需要计算第1季度的销量之和,思路就是把汇总条件“1季度”具化为起止日期,即将大于等于11日的日期,小于41日的日期作为求和的两个条件。

公式=SUMIFS($C$2:$C$18,$A$2:$A$18,">=2021-1-1",$A$2:$A$18,"<2021-4-1")

该公式无法下拉填充,可以嵌套DATE函数优化为:

=SUMIFS($C$2:$C$18,$A$2:$A$18,">="&DATE(2021,E2*3-2,1),$A$2:$A$18,"<"&DATE(2021,E2*3+1,1))

 

 

提示:

☆可以直接在公式中输入日期数据,但必须包含在引号内,如"<=2023-4-8""<="&"2023-4-8""<4-8""4-3"等。

☆日期数据的分隔符通常用“-”或“/”或文字“年月日”来表示,但不能使用符号“.”来进行书写,否则会出错。

第二种:用辅助列将日期变成季度在条件求和

如下图,新增辅助列,运用公式=ROUNDUP(MONTH(B2)/3,0)将销售日期变成求和条件——季度数据。然后SUMIF条件求和=SUMIF(A2:A18,4,D2:D18)

 

 

第三种:用SUMPRODUCT函数嵌套季度条件判断直接求和

也可以使用SUMPRODUCT函数:

=SUMPRODUCT((LEN(2^MONTH($A$2:$A$18))=$E2)*$C$2:$C$18)

或者

=SUMPRODUCT((ROUNDUP(MONTH($A$2:$A$18)/3,0)=$E2)*$C$2:$C$18)

 

 

2.用函数公式按周求和方法

下图需要按周数进行汇总求和。

 


第一种:用辅助列将日期转化为周数再条件求和

先在A2中输入公式=WEEKNUM(B2,2),然后下拉填充。

接着在G2中输入公式=SUMIF($A$2:$A$52,F2,$D$2:$D$52)下拉填充即可。

 

 

第二种:将求和条件周数具化为起止日期

2023年第N周的起始日="2023-1-1"+(N-1)*7-IF(N=1,0,WEEKDAY("2023-1-1",2)-1)

2023年第N周的截止日="2023-1-1"+(N-1)*7+(7-WEEKDAY("2023-1-1",2)

将上面两个日期代入到本案例,公式比较长:

=SUMIFS($C$2:$C$52,$A$2:$A$52,">="&("2023-1-1" +(E2-1)*7-IF(E2=1,0,WEEKDAY("2023-1-1",2)-1)),$A$2:$A$52,

"<="&("2023-1-1"+(E2-1)*7+(7- WEEKDAY("2023-1-1",2))))

 

 

第三种:用SUMPRODUCT函数嵌套周数条件判断直接求和

如果是用SUMPRODUCT的话,公式很简洁:

=SUMPRODUCT((ROUNDUP(($A$2:$A$52-"2022-12-25")/7,0)=E2)*$C$2:$C$52)

(注意:ROUNDUP向上加1所以公式中的日期是统计年11日的上一个周日,譬如202311日的上一个周日就是20221225日; 202111日的上一个周日就是20201227日。)

或者

=SUMPRODUCT((INT(($A$2:$A$52-"2022-12-19")/7)=E2)*$C$2:$C$52)
(注意:INT只保留整数,所以该公式中的日期是统计年度11日的上一个周一,譬如202311日的上一个周一就是20221219日; 202111日的上一个周1就是20201221日。)

 

下方再简要介绍其他三种日期条件求和。

3.按月、年、最近N天求和

日期条件求和1:按月份汇总统计

按月份求和输入公式:=SUMIFS($C$2:$C$52,$A$2:$A$52,">="&DATE(2023,E2,1),$A$2:$A$52,"<"&DATE(2023,E2+1,1))

 

 

还可以写成这样:

=SUMPRODUCT((MONTH($A$2:$A$52)=E2)*$C$2:$C$52)

日期条件求和2:按年份汇总

F2中输入公式:

=SUMIFS($C$2:$C$33,$A$2:$A$33,">="&DATE(E2,1,1),$A$2:$A$33,"<="&DATE(E2,12,31))

 

 

SUMPRODUCT函数的话,公式=SUMPRODUCT((YEAR($A$2:$A$52)=E2)*$C$2:$C$52)

 

日期条件求和3:汇总最近5天的销售

输入公式=SUMIFS(C2:C19,A2:A19,">"&TODAY()-5,A2:A19,"<="&TODAY())

 

图形用户界面, 应用程序, 表格, Excel
描述已自动生成

 

若用SUMPRODUCT函数,公式=SUMPRODUCT(((TODAY()-A2:A19)<5)*C2:C19)

 

文中多次用了SUMPRODUCT函数,公式相比SUMIFS都更简练(SUMIFS公式复杂的原因是条件区域不能嵌套函数),想了解此函数的可以看专题文章:

SUMPRODUCT函数用法合集

加了*的 SUMPRODUCT函数无所不能

 

好的,以上就是今天要分享给大家的Excel中用函数公式按季度求和或者按周求和的方法。其实它们都可以通过数据透视表来完成,只是没有函数公式那么直接。

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

如何计算两个日期间的工作日天数?超实用的5类日期函数来了!

超级经典的8个函数组合,解决70%工作中的函数难题

Excel教程:如何制作带有层次和透视感的图表?

八大查找函数公式,轻松搞定数据中的多条件查找

版权申明:

本文作者逍遥;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。