Google Sheets重复ID员工排班重叠过滤公式优化需求

需求说明 #

员工任职多家门店时，排班记录分行存储，当日上班标记为X或H。需筛选出两类记录：

• B列员工ID存在重复（即同一员工有多条门店排班记录）
• 该员工同一天在不同门店均有X/H标记（排班重叠）
  最终要显示所有存在该问题的员工的完整记录。

原公式问题分析 #

原公式：

=FILTER(TEST1!B3:AH, COUNTIF(TEST1!B3:B, TEST1!B3:B) > 1, BYROW(TEST1!D3:AH, LAMBDA(X, SUM(LEN(X) <> "") > 1)))

仅判断了「ID重复」和「单条记录有多个排班日期」，未检查同一ID的不同记录在同一日期是否重叠，因此会包含无重叠的多门店排班记录，导致结果不准确。

正确筛选公式 #

=FILTER(TEST1!B3:AH,
  BYROW(TEST1!B3:B, LAMBDA(currentID,
    SUMPRODUCT(
      (TEST1!B3:B = currentID) *
      BYCOL(TEST1!D3:AH, LAMBDA(dateCol,
        SUM((dateCol = "X") + (dateCol = "H")) > 1
      ))
    ) > 0
  ))
)

公式逻辑说明 #

1. 外层BYROW遍历每行ID：对每条记录的员工ID，判断该ID是否存在排班重叠情况
2. SUMPRODUCT统计重叠日期数：
   • 先筛选出当前ID的所有排班记录
   • 再遍历每个日期列（D到AH），统计该日期下当前ID的X/H标记数量是否大于1（即同一天多家门店有排班）
3. 筛选条件：只要该ID存在至少一个重叠日期，就保留该ID的所有记录

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