Excel技术需求：实现房间关联特定多缺陷的动态标记列表

需求回顾 #

• A列为房间号，B列为对应房间的缺陷项，同一房间号会重复出现在其所有缺陷项所在行
• 需在K列动态生成同时包含10.12、10.13、10.14缺陷的房间号列表，新增房间/缺陷数据时自动更新结果

原公式问题分析 #

你提供的公式仅能检测单一缺陷，且错误地将房间号与缺陷值做对比，无法实现「多缺陷同时存在」的验证逻辑。

解决方案（Excel 365/2021适用） #

使用动态数组函数组合实现需求，以下是两种可选公式：

方案1：全列动态适配（自动忽略空行）

=VSTACK("Room No", LET(
    rooms, UNIQUE(A4:A),
    required_defects, {"10.12","10.13","10.14"},
    valid_rooms, FILTER(rooms, BYROW(rooms, LAMBDA(r, SUM(COUNTIFS(A:A, r, B:B, required_defects))=COUNTA(required_defects)))),
    IFERROR(valid_rooms, "")
))

方案2：限定数据范围（提升计算效率）

如果数据量较大，建议限定具体数据范围避免全列计算：

=VSTACK("Room No", LET(
    last_row, COUNTA(A:A),
    room_col, A4:INDEX(A:A, last_row),
    defect_col, B4:INDEX(B:B, last_row),
    rooms, UNIQUE(room_col),
    required_defects, {"10.12","10.13","10.14"},
    valid_rooms, FILTER(rooms, BYROW(rooms, LAMBDA(r, SUM(COUNTIFS(room_col, r, defect_col, required_defects))=COUNTA(required_defects)))),
    IFERROR(valid_rooms, "")
))

公式说明 #

1. UNIQUE(room_col)：提取所有不重复的房间号，自动适配新增数据
2. required_defects：定义需要同时存在的缺陷集合，可根据需求修改内容或数量
3. BYROW(...)：对每个房间号执行检查：
   • 用COUNTIFS统计该房间包含每个目标缺陷的次数
   • 求和后与目标缺陷的总数对比，相等则说明该房间同时拥有所有指定缺陷
4. FILTER：筛选出符合条件的房间号
5. VSTACK：添加表头，IFERROR处理无符合条件房间时的空值输出

注意事项 #

• 若缺陷项为数值类型，将{"10.12","10.13","10.14"}改为{10.12,10.13,10.14}
• 仅支持Excel 365/2021及以上版本（需具备动态数组函数支持）

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