Excel 2019：按ID组根据最大值状态生成条件求和列的方法

需求规则 #

• 按ID分组处理数据
• 若组内最大值对应的状态为"not ok"，结果返回0，仅显示在该组的最小值行或最大值行
• 若组内最大值对应的状态为"ok"，结果返回该组"sum"列的总和，仅显示在该组的最小值行或最大值行

假设数据结构 #

方案1：在最大值行显示结果 #

选中组内最大值行的E列单元格，输入以下公式后按Ctrl+Shift+Enter（Excel 2019需手动触发数组公式）：

=IF(MAXIFS(B:B,A:A,A2)=B2,IF(C2="ok",SUMIFS(D:D,A:A,A2),0),"")

公式逻辑：

1. MAXIFS(B:B,A:A,A2)：提取当前ID组的最大值
2. 判断当前行是否为组内最大值行，若是则继续判断状态：
   • 状态为"ok"：用SUMIFS计算该组sum列的总和
   • 状态为"not ok"：返回0
3. 非最大值行返回空值

方案2：在最小值行显示结果 #

选中组内最小值行的E列单元格，输入以下公式后按Ctrl+Shift+Enter：

=IF(MINIFS(B:B,A:A,A2)=B2,IF(INDEX(C:C,MATCH(MAXIFS(B:B,A:A,A2),B:B,0))="ok",SUMIFS(D:D,A:A,A2),0),"")

公式逻辑：

1. MINIFS(B:B,A:A,A2)=B2：判断当前行是否为组内最小值行
2. 若是，先通过MAXIFS+MATCH+INDEX找到组内最大值对应的状态
3. 根据状态返回对应结果：
   • 状态为"ok"：返回组内sum列总和
   • 状态为"not ok"：返回0
4. 非最小值行返回空值

示例验证 #

• ID"123"组：最大值为5（B列第5行），状态为"ok"，公式返回SUM(D:D,A:A,"123")=10+20+40+50+30=150
• ID"456"组：最大值为4（B列第8行），状态为"not ok"，公式返回0

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