一文搞定Excel日期类求和,按年/季度/月/周/天,建议收藏!
Excel中如何用函数公式按季度求和?如何按周求和?
无论条件如何变化,它的本质就是以日期为条件的求和,我们都可以用一个函数来搞定,那就是SUMIFS!
下面,跟着我们一起来看看吧。
1. 用函数公式按季度求和方法
第一种:把求和条件具化为起止日期进行多条件求和
如下图所示,我们需要计算第1季度的销量之和,思路就是把汇总条件“1季度”具化为起止日期,即将大于等于1月1日的日期,小于4月1日的日期作为求和的两个条件。
公式=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"等。
日期数据的分隔符通常用“-”或“/”或文字“年、月、日”来表示,但不能使用符号“.”来进行书写,否则会出错。
也可以使用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)
第二种:用辅助列将日期变成求和条件季度
如下图,新增辅助列,运用公式=ROUNDUP(MONTH(B2)/3,0)将销售日期变成求和条件——季度数据。然后SUMIF条件求和=SUMIF(A2:A18,4,D2:D18)。
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((ROUNDUP(($A$2:$A$52-"2022-12-25")/7,0)=E2)*$C$2:$C$52)
(注意:该公式中的日期是统计年度1月1日的上一个周日,譬如2023年1月1日的上一个周日就是2022年12月25日;2021年1月1日的上一个周日就是2020年12月27日。)
下方再简要介绍其他三种日期条件求和。
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())
若用SUMPRODUCT函数,公式=SUMPRODUCT(((TODAY()-A2:A19)<10)*C2:C19)。
文中多次用了SUMPRODUCT函数,公式相比SUMIFS都更简练,想了解此函数的可以看专题文章:
SUMPRODUCT函数用法合集
加了*的 SUMPRODUCT函数无所不能
好的,以上就是今天要分享给大家的Excel中用函数公式按季度求和或者按周求和的方法。其实它们都可以通过数据透视表来完成,只是没有函数公式那么直接。
需要课件的同学,扫码进群领取~
另外,Excel基础不扎实的同学,欢迎学习下面这套工作用得上的Excel训练营
Lookup查找四大金刚 解决查找无烦恼
LOOKUP系列函数是解决查询类问题的中坚力量,包括以下四个函数:LOOKUP,VLOOKUP,HLOOKUP,XLOOKUP.VLOOKUPExcel中最受欢迎的三个函数之一,另外两个是SUM和AVERAGE.=VLOOKUP(G6,C4:E12,3,0)在框选范围C4:E12的首列查找G6,找到后返回C4:E12中第3列的对应的值。HLOOKUP站长网2023-07-30 09:25:230000手机丢失后第一时间要怎么做?绝不是报警,看完叮嘱家人
站长网2023-07-29 13:11:100000单细胞 芯片 转录组测序的数据挖掘文章一比一复现
自己造轮子(生信技能树优秀学员“细心的网友”)马拉松授课线上直播课程,最近的一期是5月8号(今晚七点半)开课哦:生信入门&数据挖掘线上直播课5月班站长网2023-07-27 12:40:220000PPT制作技巧:WPS文档快速转化成演示文稿
编按:本篇文章主要跟大家分享如何快速将文档转化成WPS演示文稿,并进行智能美化的方法。公司要求提交年终总结,好不容易熬夜写了一版中规中矩的总结文档。踩着提交的deadline(最后期限)交上去,却被告知,要求格式是WPS演示文稿。还有10分钟,怎么办?没关系,这对于一个拥有WPS的钢铁打工人来说,时间上绰绰有余,3分钟完成蜕变!Part1从文档到演示文稿(1分钟)站长网2023-07-27 09:17:500001长按微信相册太厉害了!照片清晰度瞬间翻倍,不会用浪费了
站长网2023-07-27 09:17:230000