如何在Excel中结合筛选按对应周汇总历史销售管道金额？

我有两张Excel表：

1. 交易数据表（deals_df）：每行对应一笔交易，包含Deal ID、当前金额、预计关闭日期、销售代表等字段，示例：

Deal ID   Amount    Closedate   Sales person
0001      100k      1/1/2024    Person1
0002      50k       2/1/2024    Person2

2. 历史金额表（deals_history_df）：按周记录每笔交易的历史金额，周维度和报表完全对齐，示例：

Deal ID   01/01   08/01   15/01   22/01   29/01   05/02  12/02                              
0001      $10K    $10K    $20K    $40K    $20K    $20K   $50K
0002      $100K   $120K   $120K   $80K    $80K    $50K   $50K

在报表标签页中，我需要按周汇总管道金额，同时支持按销售代表、预计关闭周等多条件筛选。之前用SUMIFS只能取当前金额，导致所有周显示相同数值，现在要结合历史金额表实现正确的分周多条件汇总。

我已经能通过INDEX+MATCH提取单交易对应周的金额，但不知道怎么结合原表的筛选条件进行汇总：

=INDEX(deals_history_df!$B$2:$O$410, MATCH(deals_df!$B$2:$B$1342, deals_history_df!$A$1:$A$410, 0), MATCH('Report'!E$6, deals_history_df!$B$1:$O$1, 0))

方法1：SUMPRODUCT+INDEX+MATCH（兼容旧版Excel） #

在报表的周列单元格中（比如Person1对应的01/01列），使用以下公式：

=SUMPRODUCT(
  --(deals_df!$D:$D=Report!$B2),  // 筛选销售代表为当前行的Person
  --(deals_df!$C:$C>=Report!G$6), // 筛选关闭日期>=周起始
  --(deals_df!$C:$C<Report!H$6),  // 筛选关闭日期<周结束
  INDEX(deals_history_df!$B$2:$O$410, MATCH(deals_df!$A$2:$A$1342, deals_history_df!$A$2:$A$410, 0), MATCH(Report!E$6, deals_history_df!$B$1:$O$1, 0))
)

公式说明：

• --(条件)：将布尔判断转为1/0，用于SUMPRODUCT的条件筛选
• INDEX+MATCH部分：根据Deal ID和周标签，从历史表中取出对应交易的当周金额
• SUMPRODUCT会自动将符合条件的交易金额求和

方法2：SUM+FILTER（Excel 365/2021及以上版本） #

如果使用支持动态数组的Excel版本，公式更简洁：

=SUM(
  FILTER(
    INDEX(deals_history_df!$B$2:$O$410, MATCH(deals_df!$A$2:$A$1342, deals_history_df!$A$2:$A$410, 0), MATCH(Report!E$6, deals_history_df!$B$1:$O$1, 0)),
    (deals_df!$D:$D=Report!$B2)*(deals_df!$C:$C>=Report!G$6)*(deals_df!$C:$C<Report!H$6)
  )
)

公式说明：

• FILTER先根据销售代表、关闭周条件筛选出符合要求的交易金额数组
• SUM直接对筛选后的数组求和

注意事项 #

• 确保deals_df和deals_history_df中的Deal ID完全匹配，避免MATCH返回错误值
• 周标签（如01/01）在报表和历史表中格式一致，否则MATCH会匹配失败
• 建议使用绝对引用（如$B$2）固定历史表的范围，避免拖动公式时范围偏移

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