如何在Excel中创建学生课程冲突标识列？

你有两张Excel表：一张记录学生选课情况（如下），另一张记录每门课程的日期和时间，现在要给选课表加conflict列标记是否有冲突，具体操作如下：

先明确表格结构 #

选课表（现有） #

课程时间表（需确保包含） #

至少要有这几列：Course（对应a-h）、Date（开课日期）、Start Time（开始时间）、End Time（结束时间）。

方法1：用Excel公式直接判断 #

步骤1：提取学生所选课程 #

在选课表新增一列（比如Selected），输入公式（以第2行学生1为例）：

=TEXTJOIN(",",TRUE,IF(B2:I2=1,$B$1:$I$1,""))

按Ctrl+Shift+Enter执行数组公式，会输出该学生选的课程，比如学生1会得到c,d,e,h。

步骤2：检查时间冲突 #

在conflict列输入公式，判断所选课程中是否存在同一日期下时间重叠的情况：

=IF(SUMPRODUCT(
    --(COUNTIF(课程表!$A:$A,TEXTSPLIT(J2,","))>0),
    --(COUNTIFS(课程表!$B:$B,课程表!$B:$B,课程表!$C:$C<课程表!$D:$D,课程表!$D:$D>课程表!$C:$C)>1)
)>0,"是","否")

公式里的J2替换为你刚才新增的Selected列单元格，课程表!$A:$A对应课程时间表的Course列，$B:$B是日期列，$C:$C是开始时间，$D:$D是结束时间。

方法2：用Power Query（适合大量数据） #

如果学生和课程数量多，公式容易卡顿，用Power Query更高效：

1. 把两张表都导入Power Query（点击「数据>自表格/区域」）；
2. 处理选课表：选中Student列，点击「转换>逆透视其他列」，筛选Value列等于1的行，删除Value列；
3. 合并表格：把处理后的选课表和课程时间表按Attribute（对应Course列）合并；
4. 分组检查冲突：按Student和Date分组，添加自定义列，用逻辑判断该组内是否有时间重叠的课程：

   List.Count(List.Select(List.Positions([Start Time]), (i) => List.Any(List.Positions([Start Time]), (j) => i<>j and [Start Time]{i} < [End Time]{j} and [End Time]{i} > [Start Time]{j}))) > 0
   

5. 把分组结果转成“是/否”，合并回原选课表后加载回Excel。

关键注意点 #

• 课程时间表的时间必须是Excel可识别的时间格式，不能用文本格式；
• 如果同一课程有多个开课时间，课程表要每条记录对应一个开课时间，不能合并；
• 公式引用的列要根据你实际表格的位置调整，不要直接照搬。

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