Google Sheet公式实现：基于ID判断计划时段是否在可用范围内

需求说明 #

根据M列的ID，校验Table 01的排班时段是否符合Table 02中对应ID的每日可用时段，输出三类状态：

• 计划在范围内：匹配ID后，排班日期时间完全处于对应日期的可用时段内
• 计划超出范围：匹配ID后，排班日期时间存在任何超出对应日期可用时段的情况
• 无可用时段：排班ID未出现在Table 02的可用时段列表中

单天排班校验公式（适用于排班仅覆盖单日） #

在N2单元格输入以下公式，下拉应用至整列：

=LET(
  当前ID, M2,
  排班日期, B2,
  排班开始时间, C2,
  排班结束时间, D2,
  可用时段数据, FILTER(Table02!A:D, Table02!A:A=当前ID),
  可用日期列, INDEX(可用时段数据,,2),
  可用开始列, INDEX(可用时段数据,,3),
  可用结束列, INDEX(可用时段数据,,4),
  匹配行号, XMATCH(排班日期, 可用日期列),
  IF(ISNA(匹配行号),
    "无可用时段",
    LET(
      开始时间合规, 排班开始时间 >= INDEX(可用开始列, 匹配行号),
      结束时间合规, 排班结束时间 <= INDEX(可用结束列, 匹配行号),
      IF(AND(开始时间合规, 结束时间合规), "计划在范围内", "计划超出范围")
    )
  )
)

公式逻辑

1. 用LET定义变量，简化重复引用的单元格与数据区域
2. FILTER筛选出Table 02中当前ID对应的所有可用时段数据
3. XMATCH定位当前排班日期在可用日期列表中的对应行
4. 无匹配行时直接返回「无可用时段」
5. 有匹配行时，校验排班的开始/结束时间是否完全落在当日可用时段内，根据结果返回对应状态

跨天排班校验公式（适用于排班覆盖多天） #

若排班涉及跨天，使用以下公式校验所有覆盖日期的时段合规性：

=LET(
  当前ID, M2,
  排班起始日期, B2,
  排班结束日期, E2,
  排班起始时间, C2,
  排班结束时间, D2,
  可用时段数据, FILTER(Table02!A:D, Table02!A:A=当前ID),
  IF(ROWS(可用时段数据)=0,
    "无可用时段",
    LET(
      排班覆盖日期, SEQUENCE(排班结束日期 - 排班起始日期 + 1, 1, 排班起始日期),
      日期校验结果, BYROW(排班覆盖日期, LAMBDA(单日,
        LET(
          可用行号, XMATCH(单日, INDEX(可用时段数据,,2)),
          IF(ISNA(可用行号),
            FALSE,
            AND(
              IF(单日=排班起始日期, 排班起始时间 >= INDEX(可用时段数据,,3,可用行号), TRUE),
              IF(单日=排班结束日期, 排班结束时间 <= INDEX(可用时段数据,,4,可用行号), TRUE)
            )
          )
        )
      )),
      IF(AND(日期校验结果), "计划在范围内", "计划超出范围")
    )
  )
)

公式逻辑

1. 用SEQUENCE生成排班覆盖的所有日期
2. 对每个日期单独校验：
   • 检查该日期是否在当前ID的可用日期列表中
   • 排班起始日期需校验开始时间，结束日期需校验结束时间，中间日期默认只要有可用时段即合规（可根据实际需求调整中间日期的校验规则）
3. 所有日期校验通过则返回「计划在范围内」，否则返回「计划超出范围」

注意事项 #

• 确保Table 02的区域引用正确（可根据实际表名、列位置调整公式中的Table02!A:D等范围）
• 日期、时间列需设置为Google Sheets支持的日期/时间格式，避免匹配错误
• 公式支持动态数组，若使用旧版Google Sheets，需确保启用动态数组功能

内容的提问来源于stack exchange，提问作者AUTO EXCEL GURU