如何在Excel中按组和指定日期计算各项目最新值的总和？

核心需求：对每行数据，计算其所在组截至该行日期时，每个项目最后一条有效记录（日期≤当前行日期，同一日期同一项目取最后录入的记录）的数值总和。

方法1：Excel 365/2021 动态数组公式 #

假设数据区域为 A2:E15（A=行号，B=组，C=项目，D=日期，E=数值），在「组总和（预期输出）」列的第一个单元格（如F2）输入以下公式，下拉自动填充即可：

=SUM(XLOOKUP(UNIQUE(FILTER($C$2:$C$15,$B$2:$B$15=B2)),$C$2:$C$15,$E$2:$E$15,"",0,-1*(($B$2:$B$15=B2)*($D$2:$D$15<=D2)*ROW($A$2:$A$15))))

公式逻辑拆解 #

• FILTER($C$2:$C$15,$B$2:$B$15=B2)：筛选出当前行所在组的所有项目
• UNIQUE(...)：提取该组的唯一项目列表
• XLOOKUP(...)：针对每个唯一项目，在当前组、日期≤当前行日期的范围内，按行号降序查找最后一条匹配的记录，返回对应数值
• SUM(...)：将所有项目的最新数值求和，得到当前组截至当前日期的总和

方法2：旧版Excel（无动态数组）解法 #

需要先添加辅助列标记有效记录，再求和：

1. 添加辅助列（如G列）：在G2单元格输入以下数组公式（输入后按Ctrl+Shift+Enter确认），下拉填充：

   =MAX(IF(($B$2:$B$15=B2)*($C$2:$C$15=C2)*($D$2:$D$15<=D2),ROW($A$2:$A$15)))=ROW(A2)
   

   该公式会标记出每个项目在组内、当前日期前的最后一条记录（返回TRUE）

2. 求和计算：在「组总和（预期输出）」列的F2单元格输入以下公式，下拉填充：

   =SUMIFS($E$2:$E$15,$B$2:$B$15,B2,$G$2:$G$15,TRUE,$D$2:$D$15,"<="&D2)
   

   该公式汇总当前组内所有标记为有效记录且日期≤当前行日期的数值总和

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