如何在Google Sheet中按人员容量及顺序自动分配任务？

核心逻辑 #

要实现按顺序先到先得且受容量限制的任务分配，需逐个任务判断：先统计每位员工已分配的任务数，筛选出未达最大容量的员工，再按员工列表顺序取第一个可用的进行分配。

具体公式实现（适配你的表格结构） #

假设你的表格结构如下：

• 任务列表在任务工作表的A2:A列
• 员工姓名在员工工作表的A2:A列，对应最大任务容量在员工工作表的B2:B列

在任务工作表的分配列（比如B2单元格），输入以下数组公式：

=ARRAYFORMULA(IF(任务!A2:A="", "", 
  LET(
    tasks, 任务!A2:A,
    employees, 员工!A2:A,
    capacities, 员工!B2:B,
    total_tasks, COUNTA(tasks),
    assign_list, MAP(SEQUENCE(total_tasks), LAMBDA(task_idx, 
      LET(
        // 统计当前任务之前，每位员工已分配的任务量
        prev_counts, COUNTIF(INDEX(任务!B2:B,1):INDEX(任务!B2:B,task_idx-1), employees),
        // 筛选出还有剩余容量的员工
        available_staff, FILTER(employees, prev_counts < capacities),
        // 取第一个符合条件的员工（先到先得）
        IFERROR(INDEX(available_staff, 1), "无可用员工")
      )
    )),
    assign_list
  )
))

公式拆解 #

• LET：定义变量，简化公式结构，避免重复引用
• MAP+SEQUENCE：遍历每一个任务，逐个计算分配对象
• COUNTIF：统计当前任务之前，每位员工已承接的任务数量
• FILTER+INDEX：筛选出未达容量上限的员工，按列表顺序取第一个完成分配
• IFERROR：处理任务总量超过所有员工总容量的情况，返回提示文本

实用提示 #

• 确保员工工作表的姓名和容量列无空值，否则筛选逻辑会出错
• 若任务总量超出所有员工的总容量，剩余任务会显示无可用员工，可根据需求修改提示内容
• 员工列表或容量更新时，公式会自动重新计算分配结果

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