基于Bag ID的多条件状态生成需求（Excel场景）

数据结构说明 #

• A列：存在重复值的BAG ID
• B列：包裹内的物品
• C列：物品的收货状态（包含"SIP"、"not sip"、"NOT SIP"等值）

需求规则 #

• a) 当前行C列为"SIP"，且同一BAG ID下所有物品状态均为"SIP" → 显示Bag & awb rcv
• b) 当前行C列为"not sip"/"NOT SIP"，且同一BAG ID下所有物品状态均为非"SIP" → 显示Bag not rcv
• c) 当前行C列为"not sip"/"NOT SIP"，且同一BAG ID下存在物品状态为"SIP" → 显示Bag rcv , awb SL

公式实现 #

在D2单元格输入以下公式，下拉填充即可：

=IF(UPPER(C2)="SIP",IF(COUNTIFS(A:A,A2,C:C,"<>SIP")=0,"Bag & awb rcv",""),IF(COUNTIFS(A:A,A2,C:C,"SIP")>0,"Bag rcv , awb SL","Bag not rcv"))

公式解释 #

1. UPPER(C2)="SIP"：统一转换C列值为大写，避免大小写差异导致判断失误，先判断当前行是否为"SIP"状态
2. COUNTIFS(A:A,A2,C:C,"<>SIP")=0：统计同一BAG ID下状态不为"SIP"的记录数，若为0则说明该BAG下所有物品都是"SIP"，返回对应文本
3. COUNTIFS(A:A,A2,C:C,"SIP")>0：统计同一BAG ID下存在"SIP"状态的记录数，若大于0则说明该BAG已部分收货，返回对应文本；否则返回"Bag not rcv"

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