求Excel中实现Snowflake窗口函数逻辑的7days_spend计算方案

以下是几种实现你需求的Excel公式方案，涵盖不同版本兼容性和场景：

方案1：SUMIFS动态数组版（Excel 365/2021+） #

适合支持动态数组的新版本Excel，直接实现多条件分区求和，自动适配下拉：

假设数据结构为：

• A列：DATE
• B列：WINDOW_DATE（DATE-7）
• C列：TOTAL_SPEND
• D列：COUNTRY（分区字段）
• E列：ID（分区字段）
• F列：7days_spend（目标列）

在F2单元格输入公式：

=IF(SUMIFS($C:$C, $A:$A, ">="&$B2, $A:$A, "<"&$A2, $D:$D, $D2, $E:$E, $E2)=0, "", SUMIFS($C:$C, $A:$A, ">="&$B2, $A:$A, "<"&$A2, $D:$D, $D2, $E:$E, $E2))

下拉填充即可。

逻辑说明： #

• 用SUMIFS筛选当前日期前7天内（≥WINDOW_DATE且<当前DATE）、同COUNTRY、同ID的记录，求和TOTAL_SPEND
• 用IF判断求和结果为0时返回空值（对应Snowflake的null），否则返回求和值

如果你的数据没有COUNTRY和ID分区（单组数据），可简化为：

=IF(SUMIFS($C:$C, $A:$A, ">="&$B2, $A:$A, "<"&$A2)=0, "", SUMIFS($C:$C, $A:$A, ">="&$B2, $A:$A, "<"&$A2))

方案2：SUMPRODUCT兼容版（全Excel版本） #

适合所有Excel版本，通过数组运算实现多条件求和：

在F2单元格输入公式：

=IF(SUMPRODUCT(($A$2:$A$100>=B2)*($A$2:$A$100<A2)*($D$2:$D$100=D2)*($E$2:$E$100=E2)*$C$2:$C$100)=0, "", SUMPRODUCT(($A$2:$A$100>=B2)*($A$2:$A$100<A2)*($D$2:$D$100=D2)*($E$2:$E$100=E2)*$C$2:$C$100))

注意：将$A$2:$A$100等范围替换为你实际的数据行范围，避免全列引用影响性能。

逻辑说明： #

• 用*连接多个条件（日期范围、分区匹配），将布尔值转为1/0后与TOTAL_SPEND相乘，最后求和
• 同样通过IF将无结果的0转为空值

单分区简化版：

=IF(SUMPRODUCT(($A$2:$A$100>=B2)*($A$2:$A$100<A2)*$C$2:$C$100)=0, "", SUMPRODUCT(($A$2:$A$100>=B2)*($A$2:$A$100<A2)*$C$2:$C$100))

方案3：OFFSET有序数据版（需日期按分区升序排列） #

如果你的数据已经按COUNTRY→ID→DATE升序排列，可以用OFFSET定位分区内的滑动窗口：

在F2单元格输入公式：

=LET(
    first_row, MATCH($D2&$E2, $D:$D&$E:$E, 0),
    row_diff, ROW()-first_row,
    IF(row_diff<1, "", IF(row_diff<7, SUM(OFFSET($C2, -row_diff, 0, row_diff, 1)), SUM(OFFSET($C2, -7, 0, 7, 1))))
)

逻辑说明： #

• 用MATCH找到当前分区（COUNTRY+ID）的第一行
• 计算当前行与分区首行的行数差，判断是否有足够的历史数据：
  • 行数差<1：当前是分区首行，返回空
  • 行数差<7：求和分区内当前行之前的所有记录
  • 行数差≥7：求和当前行之前7天的记录

单分区简化版：

=IF(ROW()-2<1, "", IF(ROW()-2<7, SUM(OFFSET($C2, -(ROW()-2), 0, ROW()-2, 1)), SUM(OFFSET($C2, -7, 0, 7, 1))))

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