Excel日期表中按指定日期范围求和对应季度数据的公式需求

原始数据表格 #

数据对应日期说明：Year列对应每年1月1日，March/June/Sept/December列分别对应当年3月1日、6月1日、9月1日、12月1日。

需求说明 #

输入指定日期后，自动求和两类季度数据：

1. 该日期所在月份第一天往前100天内的季度数据
2. 该日期所在月份最后一天往后250天内的季度数据
   举例：输入2003年11月17日，需计算53.8（2003年9月1日，在11月1日前100天内）+29.2（2003年12月1日）+87.1（2004年1月1日）+82.1（2004年3月1日），总和为252.2。

公式方案（适用于Excel 365及以后版本） #

假设输入日期在单元格A1，数据区域（Year列到December列）为A2:E4，可以用LET函数简化逻辑，公式如下：

=LET(
    InputDate,A1,
    MonthStart,EOMONTH(InputDate,-1)+1,
    MonthEnd,EOMONTH(InputDate,0),
    StartWindow,MonthStart-100,
    EndWindow,MonthEnd+250,
    Years,$A$2:$A$4,
    QtrDates,CHOOSE({1,2,3,4},DATE(Years,1,1),DATE(Years,3,1),DATE(Years,6,1),DATE(Years,9,1)),
    QtrVals,$B$2:$E$4,
    SUMPRODUCT(
        --((QtrDates>=StartWindow)*(QtrDates<MonthStart)+(QtrDates>MonthEnd)*(QtrDates<=EndWindow)),
        QtrVals
    )
)

公式解释 #

1. 定义变量：先把输入日期、月份起止、筛选窗口起止这些关键值存为变量，方便后续调用和修改
2. 生成季度日期数组：用CHOOSE和DATE函数，把每一行的年份和对应季度月份组合成具体日期，和数值区域一一对应
3. 筛选求和：用SUMPRODUCT判断每个季度日期是否在两个目标区间内，符合条件的数值自动求和

兼容旧版Excel的公式方案 #

如果是旧版Excel（不支持LET函数），可以用下面的数组公式（输入后按Ctrl+Shift+Enter确认）：

=SUMPRODUCT(
    --(
        (DATE($A$2:$A$4,1,1)>=EOMONTH(A1,-1)+1-100)*(DATE($A$2:$A$4,1,1)<EOMONTH(A1,-1)+1)+
        (DATE($A$2:$A$4,3,1)>=EOMONTH(A1,-1)+1-100)*(DATE($A$2:$A$4,3,1)<EOMONTH(A1,-1)+1)+
        (DATE($A$2:$A$4,6,1)>=EOMONTH(A1,-1)+1-100)*(DATE($A$2:$A$4,6,1)<EOMONTH(A1,-1)+1)+
        (DATE($A$2:$A$4,9,1)>=EOMONTH(A1,-1)+1-100)*(DATE($A$2:$A$4,9,1)<EOMONTH(A1,-1)+1)+
        (DATE($A$2:$A$4,12,1)>=EOMONTH(A1,-1)+1-100)*(DATE($A$2:$A$4,12,1)<EOMONTH(A1,-1)+1)+
        (DATE($A$2:$A$4,1,1)>EOMONTH(A1,0))*(DATE($A$2:$A$4,1,1)<=EOMONTH(A1,0)+250)+
        (DATE($A$2:$A$4,3,1)>EOMONTH(A1,0))*(DATE($A$2:$A$4,3,1)<=EOMONTH(A1,0)+250)+
        (DATE($A$2:$A$4,6,1)>EOMONTH(A1,0))*(DATE($A$2:$A$4,6,1)<=EOMONTH(A1,0)+250)+
        (DATE($A$2:$A$4,9,1)>EOMONTH(A1,0))*(DATE($A$2:$A$4,9,1)<=EOMONTH(A1,0)+250)+
        (DATE($A$2:$A$4,12,1)>EOMONTH(A1,0))*(DATE($A$2:$A$4,12,1)<=EOMONTH(A1,0)+250)
    ),
    $B$2:$E$4
)

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