Excel/Google Sheets 动态计算季度至今(QTD)值的公式构建方法

基础前提 #

• 数据区域表头为mmm yy格式的月份标识，示例中表头位于第1行A1:E1，内容依次为Jan 21、Feb 21、Mar 21、Apr 21、May 21
• 各月目标值位于表头下一行，示例中为第2行A2:E2，数值依次为1385、1432、1654、1748、1654
• 公式以系统当前日期TODAY()为计算基准，自动随日期推移更新数值，季度切换时自动重置累计起点
• 计算逻辑完全匹配规则：季度内已完结月份取完整目标值，当月按「当月目标值/当月总天数*当月已过天数」折算，最终求和得到QTD结果

可用公式 #

Excel 365/Google Sheets 动态数组版本（推荐） #

公式逻辑清晰，后续调整数据区域方便，直接输入目标单元格即可生效：

=LET(
  curr_date,TODAY(),
  curr_month_end,EOMONTH(curr_date,0),
  q_start,EOMONTH(curr_date,-MOD(MONTH(curr_date)-1,3)-1)+1,
  past_month_count,MONTH(curr_date)-MONTH(q_start),
  past_months,EDATE(q_start,SEQUENCE(past_month_count,,0)),
  past_full_sum,SUM(INDEX(2:2,MATCH(TEXT(past_months,"mmm yy"),1:1,0))),
  curr_target,INDEX(2:2,MATCH(TEXT(curr_month_end,"mmm yy"),1:1,0)),
  curr_total_days,DAY(curr_month_end),
  curr_past_days,DAY(curr_date),
  past_full_sum + curr_target/curr_total_days*curr_past_days
)

旧版Excel兼容版本（无LET/动态数组支持） #

输入完成后需按Ctrl+Shift+Enter组合键确认数组公式：

=SUMPRODUCT((EOMONTH(DATEVALUE($A$1:$E$1),0)>=EOMONTH(TODAY(),-MOD(MONTH(TODAY())-1,3)-1)+1)*(EOMONTH(DATEVALUE($A$1:$E$1),0)<EOMONTH(TODAY(),0))*$A$2:$E$2)+INDEX($A$2:$E$2,MATCH(TEXT(EOMONTH(TODAY(),0),"mmm yy"),$A$1:$E$1,0))/DAY(EOMONTH(TODAY(),0))*DAY(TODAY())

调整提示：如果你的数据不在第1、2行，只需把公式中代表表头行的1:1、代表数值行的2:2、固定数据区域$A$1:$E$1/$A$2:$E$2替换为实际表格的对应范围即可。

结果校验 #

• 当当前日期为2月12日：公式自动识别季度起点为1月1日，累加1月完整值1385，叠加2月折算值1432/28*12，最终结果为1998.71，与参考值一致
• 当当前日期为5月10日：公式自动识别季度起点为4月1日，累加4月完整值1748，叠加5月折算值1654/31*10，最终结果为2281.54，与参考值一致
• 季度切换节点（如7月1日）：公式自动重置累计起点为7月1日，不再累加上个季度的历史数值，从新季度首月开始按规则计算

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