请求指导计算Excel中的Relative Fiscal Quarter（相对财季）字段

需求回顾 #

• 基于dates字段（范围：2009-01-01 至 2037-12-31）生成无小数的正负整数类型的Relative Fiscal Quarter字段
• 财季规则：财季始于11月（例：2023-FQ1 包含 2022年11月、12月，2023年1月）
• 数值规则：当前财季为0，上一财季为-1，未来财季依次为1、2……

基于已有辅助列的解决方案 #

假设你已配置好以下辅助列：

• Fiscal Year：对应日期所属的财年（如2022年11月对应2023财年）
• Fiscal Quarter：对应日期所属的财季（取值1-4）

核心公式 #

先计算当前日期对应的财年和财季（可单独放在固定单元格，比如$F$1和$G$1）：

// 计算当前财年（放在$F$1）
=IF(MONTH(TODAY())>=11,YEAR(TODAY())+1,YEAR(TODAY()))

// 计算当前财季（放在$G$1）
=LOOKUP(MONTH(TODAY()),{1,2,3,4,5,6,7,8,9,10,11,12},{4,4,4,1,1,1,2,2,2,3,3,3})

然后在目标单元格输入相对财季公式（假设目标日期的财年在B2，财季在C2）：

=(B2 - $F$1)*4 + (C2 - $G$1)

公式逻辑说明 #

1. 财年差乘以4：每差一个完整财年，对应4个财季的差值
2. 加上财季差：同一财年下的财季偏移量
3. 两者相加即可得到符合要求的相对财季整数

验证示例（以当前日期为2023年12月为例） #

无辅助列直接版公式 #

如果不想依赖已有的Fiscal Year和Fiscal Quarter辅助列，可以直接使用整合公式（D2为目标日期单元格）：

=(IF(MONTH(D2)>=11,YEAR(D2)+1,YEAR(D2)) - IF(MONTH(TODAY())>=11,YEAR(TODAY())+1,YEAR(TODAY())))*4 + (LOOKUP(MONTH(D2),{1,2,3,4,5,6,7,8,9,10,11,12},{4,4,4,1,1,1,2,2,2,3,3,3}) - LOOKUP(MONTH(TODAY()),{1,2,3,4,5,6,7,8,9,10,11,12},{4,4,4,1,1,1,2,2,2,3,3,3}))

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