Excel 周累、月累、年累及同期数据计算


1. Demo

数据已脱敏处理,无实际意义

2. 公式及演示

U2 为「基准日期」所在单元格,值为 =Today()-1
假设 U2 单元格的值为 2024-08-13,则各时点日期如下:

时点 日期示例 公式
现期日 2024-08-13 U2
同期日 2023-08-13 EDATE(U2,-12)
现期周初 2024-08-12 (U2-WEEKDAY(U2,2)+1)
现期周末 2024-08-18 (U2-WEEKDAY(U2,2)+7)
现期周累 2024-08-13 (MIN(U2,U2-WEEKDAY(U2,2)+7))
同期周初 2023-08-12 (EDATE(U2-WEEKDAY(U2,2)+1,-12))
同期周末 2023-08-18 (EDATE(U2-WEEKDAY(U2,2)+7,-12))
同期周累 2023-08-13 (EDATE(MIN(U2,U2-WEEKDAY(U2,2)+7),-12))
现期月初 2024-08-01 (EOMONTH(U2,-1)+1)
现期月末 2024-08-31 (EOMONTH(U2,0))
现期月累 2024-08-13 (MIN(U2,EOMONTH(U2,0)))
同期月初 2023-08-01 (EOMONTH(U2,-13)+1)
同期月末 2023-08-31 (EOMONTH(U2,-12))
同期月累 2023-08-13 (MIN(EDATE(U2,-12),EOMONTH(U2,-12)))
现期年初 2024-01-01 (DATE(YEAR(U2),1,1))
现期年末 2024-12-31 (DATE(YEAR(U2),12,31))
现期年累 2024-08-13 (MIN(U2,DATE(YEAR(U2),12,31)))
同期年初 2023-01-01 (DATE(YEAR(U2)-1,1,1))
同期年末 2023-12-31 (DATE(YEAR(U2)-1,12,31))
同期年累 2023-08-13 (MIN(EDATE(U2,-12),DATE(YEAR(U2),12,31)))

注:月、年同期公式也可简单采用 EDATE({现期计算公式},-12) 的嵌套写法,表中不再列出,具体写法可参见周同期公式,缺点是增加一层计算,刷新速度较慢,不过这么点计算量的差异对人类来说感受不出来啦~

优势:
传统的同期计算方法为日期-365,但会因平年、闰年造成误差,若要规避,则需提取年后除以 4 取余判断是否为闰年(不考虑世纪年特例),条件判断输出日期结果,公式结构复杂。
而使用 EDATE、EOMONTH 函数计算日期相较 -365、-366 的同期计算方式更简便、精确,且无需考虑平年、闰年误差。

3. 搭配 SUMIFS 函数统计数据的演示

数据源:

日期 品牌 销额
1/1/2023 品牌1 641.9
1/1/2023 品牌2 534.1

以上表为例,格式为:=SUMIFS(销额列,品牌列,品牌名,日期列,">="&起始日期,日期列,"<="&结束日期)

到品牌的现期月累数据求和公式为:
=SUMIFS(品牌数据!C:C,品牌数据!B:B,B6,品牌数据!A:A,">="&(EOMONTH(U$2,-1)+1),品牌数据!A:A,"<="&(MIN(U$2,EOMONTH(U$2,0))))