如何在Excel中基于Case ID、日期及状态条件生成新列

当前表格样式：

期望表格样式：

需求说明 #

计算D列的最终状态，需遵循以下规则：

• 同一Case ID（A列）且日期（B列）相同时，只要该组内存在状态为approved（C列），最终状态为approved
• 若同一组内无approved但存在denied，最终状态为partial denial

方法1：COUNTIFS嵌套IF公式（兼容多数Excel版本） #

在D2单元格输入以下公式，下拉填充至所有行：

=IF(COUNTIFS(A:A,A2,B:B,B2,C:C,"approved")>0,"approved",IF(COUNTIFS(A:A,A2,B:B,B2,C:C,"denied")>0,"partial denial",""))

逻辑说明：

1. 先用COUNTIFS统计当前行Case ID+日期对应的行中，approved状态的数量，大于0则直接返回approved
2. 若未找到approved，再统计同组内denied的数量，大于0则返回partial denial
3. 两种状态都不存在时返回空值，可根据实际需求修改默认值

方法2：SUMPRODUCT简化公式（适配旧版Excel） #

如果使用Excel 2019及更早版本，可使用SUMPRODUCT替代COUNTIFS，效果一致：

=IF(SUMPRODUCT((A:A=A2)*(B:B=B2)*(C:C="approved"))>0,"approved",IF(SUMPRODUCT((A:A=A2)*(B:B=B2)*(C:C="denied"))>0,"partial denial",""))

逻辑说明：
通过数组运算（*代表逻辑与）统计符合条件的行数，原理和方法1完全相同，部分旧版Excel中兼容性更好。

方法3：Excel 365动态数组公式（一键生成整列） #

若使用Excel 365/2021版本，可借助动态数组函数一次性生成整列结果，无需手动下拉：

=BYROW(A2:C10,LAMBDA(x,LET(caseid,INDEX(x,1),date,INDEX(x,2),IF(SUMPRODUCT((A:A=caseid)*(B:B=date)*(C:C="approved"))>0,"approved",IF(SUMPRODUCT((A:A=caseid)*(B:B=date)*(C:C="denied"))>0,"partial denial",""))))

注意：将公式中的A2:C10替换为你的实际数据区域，公式会自动扩展填充整列。

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