如何在Google Sheets中基于多条件格式化客房检查状态单元格区域？

问题根源分析 #

你的原有公式存在两个核心问题：

1. 使用整列D:D而非针对当前房间号匹配具体检查日期，导致条件判断逻辑混乱
2. 日期区间的比较逻辑颠倒（比如绿色条件写的是D:D<=today()-7，实际需要的是最近7天内检查）

修复后的条件格式规则（按优先级从高到低排序） #

条件格式规则的顺序至关重要，需按以下顺序添加，确保最特殊的规则优先生效：

1. 故障房（Out of Order）黑色背景

• 应用范围：H3:K18
• 格式样式：黑色背景
• 公式（替换$F:$F为你的故障房列表实际范围，比如$G$2:$G$15）：

=COUNTIF($F:$F, H3) > 0

2. 7天内已检查绿色背景

• 应用范围：H3:K18
• 格式样式：绿色背景
• 公式：

=AND(NOT(ISNA(VLOOKUP(H3, $A:$D, 1, FALSE))), TODAY() - VLOOKUP(H3, $A:$D, 4, FALSE) <= 7)

3. 8-14天前检查黄色背景

• 应用范围：H3:K18
• 格式样式：黄色背景
• 公式：

=AND(NOT(ISNA(VLOOKUP(H3, $A:$D, 1, FALSE))), TODAY() - VLOOKUP(H3, $A:$D, 4, FALSE) >= 8, TODAY() - VLOOKUP(H3, $A:$D, 4, FALSE) <= 14)

4. 超过14天未检查/房间不存在红色背景

• 应用范围：H3:K18
• 格式样式：红色背景
• 公式：

=OR(ISNA(VLOOKUP(H3, $A:$D, 1, FALSE)), TODAY() - VLOOKUP(H3, $A:$D, 4, FALSE) > 14)

关键优化说明 #

• 用VLOOKUP(H3, $A:$D, 4, FALSE)精准匹配当前房间号对应的检查日期，避免整列判断的逻辑错误
• 使用绝对引用$A:$D确保公式在整个状态区域应用时，查找范围保持不变
• 规则顺序：故障房→绿色→黄色→红色，确保特殊状态优先覆盖通用状态

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