如何在Google Sheets公式中迭代单元格以自动检测排课冲突

方案1：公式实现（无需写代码，兼容现有表格结构） #

不需要手动构建固定单元格数组，通过全列匹配自动提取相同时段的所有数据，插入行不会影响规则有效性，同时可自动返回冲突组别。

你可以直接在冲突检测列输入以下公式，根据你的实际表格调整单元格引用即可：

=ARRAYFORMULA(
  LET(
    target_time, A7, // 替换为当前行对应时段所在的单元格
    target_teacher, D7, // 替换为当前行对应教师所在的单元格
    // 匹配所有同一时段、且不是当前行的行号
    match_rows, FILTER(ROW(D:D), A:A=target_time, ROW(D:D)<>ROW(D7)),
    // 提取匹配行的教师姓名
    match_teachers, INDIRECT("D"&match_rows),
    // 自动提取匹配行对应的组别（适配你现有表格组名列在时段行上一行的结构）
    conflict_groups, REGEXEXTRACT(INDIRECT("A"&match_rows-1), "\((.*)组\)"),
    // 筛选出教师相同的冲突行
    conflict_idx, match_teachers=target_teacher,
    // 输出结果
    IF(COUNTIF(conflict_idx, TRUE)=0, "ok", "当前时段教师另有课程，冲突组别："&TEXTJOIN("、", TRUE, FILTER(conflict_groups, conflict_idx)))
  )
)

如果需要同时检测教室冲突，只需要在公式中新增对应教室列的匹配逻辑即可。

如果愿意把表格调整为结构化格式（新增固定列：时段、组别、星期，每行对应一条排课记录），公式可以更简洁稳定：

=LET(
  same_time_teacher, FILTER(B:B, (A:A=A2)*(C:C=C2)*(D:D=D2)*(ROW(B:B)<>ROW()), "无"),
  same_time_classroom, FILTER(B:B, (A:A=A2)*(C:C=C2)*(E:E=E2)*(ROW(B:B)<>ROW()), "无"),
  res, "ok",
  IF(same_time_teacher<>"无", res="教师冲突，冲突组别："&TEXTJOIN("、", TRUE, same_time_teacher),),
  IF(same_time_classroom<>"无", res=res&IF(res<>"ok","；","")&"教室冲突，冲突组别："&TEXTJOIN("、", TRUE, same_time_classroom),),
  res
)

方案2：Apps Script自定义函数（适合数据量较大的场景） #

如果排课数据超过100行，公式可能出现卡顿，可以用Google Apps Script写自定义函数实现，支持批量检测所有冲突，还可扩展自动高亮冲突单元格的功能。

操作步骤：

1. 点击表格顶部菜单「扩展程序 > Apps Script」打开脚本编辑器
2. 粘贴以下代码：

function CHECK_SCHEDULE_CONFLICT(range) {
  // range传入的每行格式要求：[时段, 组别, 星期, 教师, 教室]
  const conflictList = [];
  const timeDayMap = {};
  range.forEach(row => {
    const [time, group, day, teacher, classroom] = row;
    const uniqueKey = `${day}_${time}`;
    if(!timeDayMap[uniqueKey]) timeDayMap[uniqueKey] = {teacherMap: {}, classroomMap: {}};
    // 检测教师冲突
    if(timeDayMap[uniqueKey].teacherMap[teacher]) {
      conflictList.push(`[${day} ${time}] 教师${teacher} 与${timeDayMap[uniqueKey].teacherMap[teacher]}组冲突`);
    } else {
      timeDayMap[uniqueKey].teacherMap[teacher] = group;
    }
    // 检测教室冲突
    if(timeDayMap[uniqueKey].classroomMap[classroom]) {
      conflictList.push(`[${day} ${time}] 教室${classroom} 与${timeDayMap[uniqueKey].classroomMap[classroom]}组冲突`);
    } else {
      timeDayMap[uniqueKey].classroomMap[classroom] = group;
    }
  });
  return conflictList.length ? conflictList.join("\n") : "无冲突";
}

3. 保存项目后回到表格，直接在空白单元格输入=CHECK_SCHEDULE_CONFLICT(A2:E100)即可一次性返回所有冲突，其中A2:E100替换为你实际的排课数据范围。

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