Excel公式或VBA实现？按优先级返回项目整体状态

原始数据 #

需求说明 #

为每个项目（按ID/Name分组）确定整体状态，遵循优先级规则：Not started > In Progress > Completed。即：

• 只要分组内存在Not started，整体状态即为Not started
• 无Not started但有In Progress，整体状态为In Progress
• 仅存在Completed时，整体状态为Completed

之前尝试的公式逻辑错误，因为它是找出现次数最少的状态，与需求的优先级规则不匹配：

=INDEX(D2:D8,MATCH(MIN(IF(COUNTIF(D2:D8,D2:D8)=0,"",COUNTIF(D2:D8,D2:D8))),COUNTIF(D2:D8,D2:D8),0))

解决方案：纯Excel公式实现 #

不需要使用VBA，以下两种方法可满足需求：

方法1：通用公式（适配所有Excel版本） #

假设数据在A:C列，在E2单元格输入公式后下拉，即可得到对应行ID的整体状态：

=IF(COUNTIFS(A:A,A2,C:C,"Not started")>0,"Not started",IF(COUNTIFS(A:A,A2,C:C,"In Progress")>0,"In Progress","Completed"))

注意：若数据中存在大小写不一致（如Not Started和Not started），可将条件改为"*Not started*"来忽略大小写，或先统一状态文本的格式。

方法2：动态数组公式（Excel 365/2021及以上版本） #

一次性生成所有唯一ID的整体状态结果：

=LET(
    uniqueIDs, UNIQUE(A:A),
    statuses, MAP(uniqueIDs, LAMBDA(id,
        IF(COUNTIFS(A:A,id,C:C,"Not started")>0,"Not started",
        IF(COUNTIFS(A:A,id,C:C,"In Progress")>0,"In Progress","Completed"))
    )),
    HSTACK(uniqueIDs, statuses)
)

该公式会输出一个两列的动态数组，第一列为唯一ID，第二列为对应的整体状态。

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