物联网app开发 Excel日历条款乞降:何如用函数公式按季度或按周统计销量?
编按:Excel中何如用函数公式按季度乞降?何如用函数公式按周数乞降?这些问题实验皆是以日历为条款的乞降。
Excel中何如用函数公式按季度乞降?何如按周乞降?它的骨子便是以日历为条款的乞降,比方求第2季度或第5周的销量,便是统计销售日历属于第2季度或者第5周里的销量。与之访佛的按月、年乞降。
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"等。
☆日历数据的分隔符时时用“-”或“/”或翰墨“年月日”来示意,但不可使用秀美“.”来进行书写,不然会出错。
第二种:用提拔列将日历酿成季度在条款乞降
如下图,新增提拔列,行使公式=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,物联网app开发是以公式中的日历是统计年1月1日的上一个周日,比方2023年1月1日的上一个周日便是2022年12月25日; 2021年1月1日的上一个周日便是2020年12月27日。)
或者
=SUMPRODUCT((INT(($A$2:$A$52-"2022-12-19")/7)=E2)*$C$2:$C$52)
(防范:INT只保留整数,是以该公式中的日历是统计年度1月1日的上一个周一,比方2023年1月1日的上一个周一便是2022年12月19日; 2021年1月1日的上一个周1便是2020年12月21日。)
下方再简要先容其他三种日历条款乞降。
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())
1. 堪萨斯城竞技成立于1995年,球队获得过2次美职联冠军,4次美公开赛冠军。
图片
若用SUMPRODUCT函数,公式=SUMPRODUCT(((TODAY()-A2:A19)<5)*C2:C19)。
文中屡次用了SUMPRODUCT函数,公式比较SUMIFS皆更简练(SUMIFS公式复杂的原因是条款区域不可嵌套函数)。
好的,以上便是今天要共享给全球的Excel顶用函数公式按季度乞降或者按周乞降的才调。其实它们皆不错通过数据透视表来完成,仅仅莫得函数公式那么成功。
有关保举:
何如缱绻两个日历间的使命日天数?超实用的5类日历函数来了!
超等经典的8个函数组合,解决70%使命中的函数发愤
Excel教程:何如制作带有端倪和透视感的图表?
八大查找函数公式,平定处理数据中的多条款查找
版权声名:
本文作家纵脱;同期部落窝诠释享有独到使用权物联网app开发。若需转载请关连部落窝诠释。
本站仅提供存储奇迹,通盘内容均由用户发布,如发现存害或侵权内容,请点击举报。