如何用Excel公式检测周课程表日期时间重叠并实现条件高亮

适用场景 #

课程表按「星期、开始时间、结束时间」录入，需要自动高亮同一星期内时间段重叠的冲突行，无需VBA纯公式实现。

公式逻辑说明 #

在原有时间重叠判断的基础上，新增星期字符匹配逻辑，只有当两行存在共同的上课星期、且时间重叠时，才会判定为冲突。

公式使用 #

首先明确你的表格列对应关系（可根据实际情况调整引用）：

• 星期列：L列，全量数据范围为 $L$15:$L$26
• 开始时间列：M列，全量数据范围为 $M$15:$M$26
• 结束时间列：N列，全量数据范围为 $N$15:$N$26
• 当前行号为15（条件格式应用到全量数据时会自动迭代行号）

全版本兼容公式（支持所有Excel版本） #

直接复制到条件格式的公式输入框即可：

=SUMPRODUCT(--(MMULT(--ISNUMBER(SEARCH(MID(L15,ROW(INDIRECT("1:"&LEN(L15))),1),$L$15:$L$26)),ROW(INDIRECT("1:"&LEN(L15)))^0)>0)*(M15<$N$15:$N$26)*(N15>$M$15:$M$26))>1

新函数简化版（仅支持Excel 365/2021及以上版本） #

逻辑更清晰，可读性更高：

=SUMPRODUCT(--(BYROW($L$15:$L$26,LAMBDA(x,OR(ISNUMBER(SEARCH(TEXTSPLIT(L15,,1),x))))))*(M15<$N$15:$N$26)*(N15>$M$15:$M$26))>1

操作步骤 #

1. 选中所有需要校验的排课数据行
2. 依次点击「开始→条件格式→新建规则→使用公式确定要设置格式的单元格」
3. 粘贴上述适配好你表格范围的公式
4. 设置你需要的高亮格式（如红色填充、黄色字体等），点击确定即可生效

注意事项 #

• 请统一星期缩写规则，比如周一固定用M、周四固定用Th，避免同一星期出现多种缩写导致匹配失败
• 调整公式范围时注意绝对引用符号$的位置，全量数据的范围需要加$锁死，当前行的列/行不需要加$锁死

内容的提问来源于stack exchange，提问作者Jeano Ermitaño