如何在Google Sheets中按成员统计跨周请求的周工作量

前提假设 #

假设「sample data」工作表的列结构为：

• A列：成员姓名（Peter/John/Harry）
• B列：预估处理工作量（天数）
• C列：开始工作日期
• D列：截止完成日期
• 第一行为表头，数据从第2行开始

目标工作表设置 #

1. 成员行：在目标表的A2:A4分别输入 Peter、John、Harry
2. 周数列：在目标表的B1:BA1输入年度周数1-52，可直接用公式生成：=SEQUENCE(1,52)

核心数组公式 #

在目标表的B2单元格输入以下公式，按回车后将自动填充所有成员的周工作量数据：

=ARRAYFORMULA(
  LET(
    members, $A$2:$A$4,
    weeks, B$1:BA$1,
    data, 'sample data'!$A$2:$D,
    member_col, INDEX(data, 0, 1),
    effort_col, INDEX(data, 0, 2),
    start_col, INDEX(data, 0, 3),
    end_col, INDEX(data, 0, 4),
    total_workdays, NETWORKDAYS(start_col, end_col),
    daily_rate, effort_col / IF(total_workdays = 0, 1, total_workdays),
    week_mondays, DATE(YEAR(TODAY()), 1, 1) + (weeks - 1)*7 - WEEKDAY(DATE(YEAR(TODAY()), 1, 1), 2) + 1,
    week_fridays, week_mondays + 4,
    MAP(members, LAMBDA(member,
      MAP(weeks, LAMBDA(week_num,
        SUM(
          IF(
            member_col = member,
            daily_rate * NETWORKDAYS(MAX(start_col, INDEX(week_mondays, 1, week_num)), MIN(end_col, INDEX(week_fridays, 1, week_num))),
            0
          )
        )
      ))
    ))
  )
)

公式逻辑说明 #

1. 变量定义：用LET简化公式，提取数据源和目标表的核心字段
2. 日分摊率计算：将预估工作量平摊到请求周期内的每个工作日（排除周末），避免除以0的异常情况
3. 周边界计算：自动计算每个周的周一和周五（符合周一至周五的工作时间范围）
4. 工作量汇总：通过MAP遍历每个成员和周数，筛选对应成员的请求，计算请求在当前周的重叠工作日数，乘以日分摊率后求和，得到该成员当周的总工作量

测试验证 #

以你提到的案例测试：

• 请求：Peter，预估2天，开始日期2024-01-08（周一），截止日期2024-01-19（周五）
• 总工作日数10天，日分摊率0.2天/日
• 该请求覆盖周2和周3，每周5个工作日，因此每周工作量为0.2*5=1，目标表中Peter的周2、周3单元格将显示1

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