基于动态优先级计算任务起始日期的Spreadsheet公式需求

假设你的表格结构如下（可根据实际调整列位置）：

• A列：执行人
• B列：优先级（数字越小优先级越高，先执行；若你的规则相反，见下文调整）
• C列：任务工时（单位：小时）
• D列：任务截止日期
• E列：需计算的起始日期
• F列（可选）：节假日列表

Google Sheets 动态数组公式 #

在E2单元格输入以下公式，自动填充所有行，且任务归属变更时自动更新：

=ARRAYFORMULA(IF(A2:A="",,
  WORKDAY(
    D2:D,
    -MMULT(
      --(A2:A=TRANSPOSE(A2:A))*(B2:B<=TRANSPOSE(B2:B)),
      C2:C/8
    ),
    $F:$F
  )
))

Excel 动态数组公式 #

Excel中使用WORKDAY.INTL替代WORKDAY，公式如下：

=IF(A2:A="","",
  WORKDAY.INTL(
    D2:D,
    -MMULT(
      (A2:A=TRANSPOSE(A2:A))*(B2:B<=TRANSPOSE(B2:B)),
      C2:C/8
    ),
    1,
    $F:$F
  )
)

公式逻辑拆解 #

1. 动态处理空行：IF(A2:A="",, ...) 跳过无执行人的空行。
2. 累计工时计算：
   • --(A2:A=TRANSPOSE(A2:A)) 生成匹配矩阵，同一执行人的单元格标记为1，否则为0。
   • (B2:B<=TRANSPOSE(B2:B)) 生成优先级匹配矩阵，标记出当前任务及优先级更高（需先执行）的任务。
   • MMULT 将两个矩阵相乘后，与C2:C/8（工时转工作日，按每日8小时计算）做矩阵乘法，得到每个任务对应的同一执行人下，需先完成的所有任务+自身的总工作日时长。
3. 倒推起始日期：WORKDAY 从任务截止日期倒推扣除累计工作日，自动排除周末和指定节假日。

调整规则 #

• 优先级逻辑反转：若你用数字越大表示优先级越高，将公式中的B2:B<=TRANSPOSE(B2:B)改为B2:B>=TRANSPOSE(B2:B)。
• 每日工作时长：把C2:C/8中的8改为你的实际每日工作小时数（如7.5）。
• 无需排除节假日：去掉公式中的最后一个参数$F:$F（Google Sheets）或设为""（Excel）。

为什么Filter/Sort组合没成功？ #

Filter+Sort只能对整个执行人的任务做排序后累加，但无法关联回原行的任务截止日期，且无法实现逐行的动态累计计算。MMULT矩阵乘法能精准实现“对每行任务，计算同一执行人下符合优先级规则的累计工时”，完美匹配你的需求。

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