Google Sheets多对多表关联：计算员工重叠工时

核心思路 #

先将员工打卡行与同日期的所有支持人员打卡行一一关联，再计算每行的重叠工时，最后可按需汇总。

步骤1：生成关联匹配表 #

假设：

• 员工打卡表（表1）数据范围为员工打卡!A2:D（列：日期、姓名、上班时间、下班时间）
• 支持人员打卡表（表2）数据范围为支持人员打卡!A2:D（列：日期、姓名、上班时间、下班时间）

在输出表的A2单元格输入以下公式，自动生成所有日期匹配的关联行，并计算重叠工时：

=ARRAYFORMULA(
  LET(
    emp_data, 员工打卡!A2:D,
    sup_data, 支持人员打卡!A2:D,
    emp_dates, INDEX(emp_data,,1),
    sup_dates, INDEX(sup_data,,1),
    // 生成所有日期匹配的员工-支持人员关联对
    matched_pairs, FILTER(
      {
        FLATTEN(IF(emp_dates="",,emp_data)),
        XLOOKUP(FLATTEN(IF(emp_dates="",,emp_dates)), sup_dates, sup_data,,0)
      },
      NOT(ISNA(XLOOKUP(FLATTEN(IF(emp_dates="",,emp_dates)), sup_dates, INDEX(sup_data,,2),,0)))
    ),
    // 拆分关联对并计算重叠工时
    {
      INDEX(matched_pairs,,1), INDEX(matched_pairs,,2), INDEX(matched_pairs,,3), INDEX(matched_pairs,,4),
      INDEX(matched_pairs,,5), INDEX(matched_pairs,,6), INDEX(matched_pairs,,7),
      MAX(0, MIN(INDEX(matched_pairs,,4), INDEX(matched_pairs,,7)) - MAX(INDEX(matched_pairs,,3), INDEX(matched_pairs,,6)))
    }
  )
)

公式说明 #

1. LET：定义变量简化公式结构，避免重复引用数据范围
2. FLATTEN + XLOOKUP：将每个员工打卡行，匹配出所有同日期的支持人员打卡行，生成关联对
3. 重叠工时计算逻辑：
   • 取两个时间段的最晚上班时间和最早下班时间
   • 若下班时间晚于上班时间，差值即为重叠时长；否则为0（无重叠）

步骤2：汇总重叠工时 #

若需按员工或日期汇总，可使用QUERY函数，例如按员工姓名汇总：

=QUERY(输出表!A:H, "SELECT B, SUM(H) GROUP BY B", 1)

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