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))))