Google Sheets教师排课IF条件求助：时段冲突判断失效

需求回顾 #

表格每行代表一门课程，每列对应一位教师，需用公式返回yes/no判断教师是否可授该课程，需满足三个条件：

• 每位教师最多教授5门课，超出后所有课程显示no
• 若某课程已分配教师，其他教师均显示no
• 教师若已在某Block时段授课，无法再教授同Block的其他课程

原方案问题分析 #

你之前通过TEXTJOIN合并教师已授课时段到单元格（如L11），用L$11=$J13判断时段冲突，这个逻辑仅当L11中只有当前时段时生效。当L11是多时段（如A,B），=判断会因为不完全匹配返回FALSE，导致之前的时段冲突判断失效。

解决方案 #

方案1：直接用COUNTIFS判断（推荐，无需辅助单元格）

放弃TEXTJOIN辅助单元格，直接在判断公式中用COUNTIFS统计教师在当前时段的授课数，公式如下：

=IF(
  OR(
    COUNTIF($K:$K, L$1) >= 5,
    $K13 <> "",
    COUNTIFS($J:$J, $J13, $K:$K, L$1) > 0
  ),
  "no",
  "yes"
)

各条件说明：

• COUNTIF($K:$K, L$1) >=5：统计该教师已分配的课程总数，超过5则返回no
• $K13 <> ""：当前课程已分配教师，其他教师均不可选
• COUNTIFS($J:$J, $J13, $K:$K, L$1) >0：统计该教师在当前课程的Block时段（$J13）已授课的数量，大于0说明时段冲突，返回no

方案2：改进文本包含判断（保留原辅助单元格）

如果要继续使用TEXTJOIN合并的时段单元格，将原判断条件L$11=$J13替换为ISNUMBER(SEARCH($J13, L$11))，修改后的公式：

=IF(OR(ISNUMBER(SEARCH($J13, L$11)), L$2>=5, $K13<>""), "no", "yes")

• SEARCH($J13, L$11)：在L11的时段文本中查找当前Block，找到则返回位置数字，找不到返回错误
• ISNUMBER(...)：判断是否找到匹配的时段，找到则说明冲突，返回no

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