跨年度同期群分析中WEEKNUM函数的适配问题求助

针对你在同期群分析中遇到的周数计算问题，这里提供适配需求的Excel公式，确保每个日历年份仅保留1-52周，允许年末第52周与年初第1周重叠：

公式（支持Excel 365/2021及以上） #

=LET(
    date_year, YEAR(D2),
    iso_week, WEEKNUM(D2,15),
    iso_week_year, YEAR(DATE(date_year,1,1)+(iso_week-1)*7-WEEKDAY(DATE(date_year,1,1),2)+1),
    IF(iso_week_year<>date_year, 1, IF(iso_week>52,52,iso_week))
)

兼容旧版Excel的公式 #

=IF(YEAR(DATE(YEAR(D2),1,1)+(WEEKNUM(D2,15)-1)*7-WEEKDAY(DATE(YEAR(D2),1,1),2)+1)<>YEAR(D2),1,IF(WEEKNUM(D2,15)>52,52,WEEKNUM(D2,15)))

公式逻辑说明 #

1. 提取日历年份：YEAR(D2)获取日期所属的自然年份（如2022、2023）
2. 计算ISO周数：WEEKNUM(D2,15)沿用你原本的ISO周规则（周一为一周起始，第1周包含当年第一个周四）
3. 判断周数所属年份：通过计算该周的起始日期年份，判断当前日期的ISO周是否属于上一年（比如2023年1月1日的ISO周属于2022年）
4. 强制周数范围：
   • 若ISO周属于上一年，将当前日期归为所在日历年份的第1周
   • 若ISO周数超过52，强制设为52
   • 其余情况保留原ISO周数

这样处理后，2021-2023年的所有日期都会被映射到所属日历年份的1-52周内，完全满足你同期群分析的分组需求。

内容的提问来源于stack exchange，提问作者Caz H