Excel 2016跨工作表实现教师登录时间与出勤状态自动匹配咨询

前提假设（可根据实际表结构调整） #

假设你有两个工作表：

• 课程表：存储教师课程安排，列结构为：
  A=教师姓名，B=课程日期，C=课程开始时间，D=迟到允许分钟数，E=课程结束时间
• 登录记录：存储教师实际登录数据，列结构为：
  A=教师姓名，B=登录日期，C=登录时间，D=出勤状态（待填充）

核心公式（Excel 2016 兼容） #

在登录记录!D2单元格输入以下公式，按Ctrl+Shift+Enter作为数组公式提交，再下拉填充：

=IFERROR(
    IF(C2 <= INDEX(课程表!$C:$C, MATCH(1, (课程表!$A:$A=A2)*(课程表!$B:$B=B2), 0)) + TIME(0, INDEX(课程表!$D:$D, MATCH(1, (课程表!$A:$A=A2)*(课程表!$B:$B=B2), 0)), 0),
        "Present",
        IF(C2 <= INDEX(课程表!$E:$E, MATCH(1, (课程表!$A:$A=A2)*(课程表!$B:$B=B2), 0)),
            "Late",
            "Absent"
        )
    ),
    "Absent"
)

公式逻辑说明 #

1. 多条件匹配课程信息：通过MATCH(1, (课程表!$A:$A=A2)*(课程表!$B:$B=B2), 0)定位当前教师对应日期的课程行，再用INDEX提取课程开始时间、迟到阈值、结束时间
2. 出勤状态判断：
   • 登录时间 ≤ 课程开始时间+迟到阈值 → Present
   • 登录时间 > 课程开始时间+迟到阈值，但 ≤ 课程结束时间 → Late
   • 登录时间远超课程结束时间，或无匹配课程记录 → Absent

简化调整（如果迟到阈值固定） #

若所有课程的迟到允许时间统一为10分钟，可简化公式：

=IFERROR(
    IF(C2 <= INDEX(课程表!$C:$C, MATCH(1, (课程表!$A:$A=A2)*(课程表!$B:$B=B2), 0)) + TIME(0,10,0),
        "Present",
        IF(C2 <= INDEX(课程表!$E:$E, MATCH(1, (课程表!$A:$A=A2)*(课程表!$B:$B=B2), 0)),
            "Late",
            "Absent"
        )
    ),
    "Absent"
)

注意事项 #

• 确保两个工作表中教师姓名、日期格式完全一致（日期需为Excel标准日期格式，避免文本格式导致匹配失败）
• 若同一教师单日有多节课，需在课程表和登录记录中增加「课程时段」列（如"第一节""第二节"），并在MATCH条件中补充*(课程表!$F:$F=登录记录!$F2)以精准匹配对应课程

内容的提问来源于stack exchange，提问作者Ranier Albert Lizada